Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 0.9.0-incubating
-
Fix Version/s: 0.9.2-incubating
-
Component/s: None
-
Labels:None
Description
Following query reproduces the problem:
SELECT ss_item_sk, ss_ticket_number, ss_customer_sk, sr_return_quantity FROM store_sales LEFT OUTER JOIN store_returns ON (store_returns.sr_item_sk = store_sales.ss_item_sk AND store_returns.sr_ticket_number = store_sales.ss_ticket_number), reason WHERE store_returns.sr_reason_sk = reason.r_reason_sk AND r_reason_desc = 'reason 28';
The Join graph given is:
(SS --> SR) – Reason gets transformed in to
(SS x Reason) --> SR
This is an invalid reordering.
A short term solution may be to split join graph on outer join.
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Problem reproduces regardless; modified query:
SELECT ss_item_sk, ss_ticket_number, ss_customer_sk, sr_return_quantity FROM store_sales LEFT OUTER JOIN store_returns ON (store_returns.sr_item_sk = store_sales.ss_item_sk AND store_returns.sr_ticket_number = store_sales.ss_ticket_number) inner join reason on store_returns.sr_reason_sk = reason.r_reason_sk AND r_reason_desc = 'reason 28';
Fixed in https://github.com/julianhyde/optiq/tree/optiq-435; still needs to be committed to master.
Bulk update closing "fixed" issues that are more than a year old.
Does this issue reproduce if you use "JOIN ... ON" rather than ", ... WHERE"? It help clarify, because I am not quite sure about the relative precedence of JOIN and ",".