Details
-
Bug
-
Status: Resolved
-
Major
-
Resolution: Fixed
-
None
-
None
-
None
-
None
Description
select sum (ss_quantity) from store_sales, store, customer_demographics, customer_address, date_dim where s_store_sk = ss_store_sk and ss_sold_date_sk = d_date_sk and d_year = 1998 and ( ( cd_demo_sk = ss_cdemo_sk and cd_marital_status = 'M' and cd_education_status = '4 yr Degree' and ss_sales_price between 100.00 and 150.00 ) or ( cd_demo_sk = ss_cdemo_sk and cd_marital_status = 'M' and cd_education_status = '4 yr Degree' and ss_sales_price between 50.00 and 100.00 ) or ( cd_demo_sk = ss_cdemo_sk and cd_marital_status = 'M' and cd_education_status = '4 yr Degree' and ss_sales_price between 150.00 and 200.00 ) ) and ( ( ss_addr_sk = ca_address_sk and ca_country = 'United States' and ca_state in ('KY', 'GA', 'NM') and ss_net_profit between 0 and 2000 ) or (ss_addr_sk = ca_address_sk and ca_country = 'United States' and ca_state in ('MT', 'OR', 'IN') and ss_net_profit between 150 and 3000 ) or (ss_addr_sk = ca_address_sk and ca_country = 'United States' and ca_state in ('WI', 'MO', 'WV') and ss_net_profit between 50 and 25000 ) )
See the following query(TPC-DS Query48). The join condition of this query is in the repeated OR clause as following:
cd_demo_sk = ss_cdemo_sk and cd_marital_status = 'M' and cd_education_status = '4 yr Degree'
Tajo planner makes the logical for this query with CROSS JOIN because the planner can't find JOIN condition. So this query does not finish. This query can be changed to the following.
select sum (ss_quantity) from store_sales, store, customer_demographics, customer_address, date_dim where s_store_sk = ss_store_sk and ss_sold_date_sk = d_date_sk and d_year = 1998 and (cd_demo_sk = ss_cdemo_sk and cd_marital_status = 'M' and cd_education_status = '4 yr Degree' and ( (ss_sales_price between 50.00 and 100.00) or (ss_sales_price between 100.00 and 150.00) or (ss_sales_price between 150.00 and 200.00) )) and ( ss_addr_sk = ca_address_sk and ca_country = 'United States' and ( (ca_state in ('KY', 'GA', 'NM') and ss_net_profit between 0 and 2000) or (ca_state in ('MT', 'OR', 'IN') and ss_net_profit between 150 and 3000) or (ca_state in ('WI', 'MO', 'WV') and ss_net_profit between 50 and 25000) ) )
Other solution also have same problem. See the following issues.
This issue is related with TPC-DS query 13, 48, 85.
Attachments
Issue Links
- is related to
-
TAJO-680 Improve the IN operator to support sub queries
- Resolved