Victoria Markman created DRILL-4095: ---------------------------------------
Summary: Regression: TPCDS query 30 throws can not plan exception Key: DRILL-4095 URL: https://issues.apache.org/jira/browse/DRILL-4095 Project: Apache Drill Issue Type: New Feature Components: Query Planning & Optimization Affects Versions: 1.2.0, 1.3.0 Reporter: Victoria Markman Fails in: mapr-drill-1.2.0.201510190924-1.noarch.rpm mapr-drill-1.3.0.201511112348-1.noarch.rpm It looks like this bug was fixed as part of DRILL-2949 , but has regressed since then. Reproduction with data shipped with drill (TPCH): {code} 0: jdbc:drill:schema=dfs> select . . . . . . . . . . . . > count(*) . . . . . . . . . . . . > from . . . . . . . . . . . . > cp.`tpch/nation.parquet` a, . . . . . . . . . . . . > cp.`tpch/region.parquet` b . . . . . . . . . . . . > where . . . . . . . . . . . . > a.n_regionkey > (select max(b.r_regionkey) from cp.`tpch/region.parquet` b where b.r_nationkey = a.n_nationkey); Error: UNSUPPORTED_OPERATION ERROR: This query cannot be planned possibly due to either a cartesian join or an inequality join [Error Id: a52ca497-f654-46ba-b1a7-20d1c0147129 on atsqa4-133.qa.lab:31010] (state=,code=0) {code} If you remove table from the join, query executes: {code} 0: jdbc:drill:schema=dfs> select . . . . . . . . . . . . > count(*) . . . . . . . . . . . . > from . . . . . . . . . . . . > cp.`tpch/nation.parquet` a . . . . . . . . . . . . > --cp.`tpch/region.parquet` b . . . . . . . . . . . . > where . . . . . . . . . . . . > a.n_regionkey > (select max(b.r_regionkey) from cp.`tpch/region.parquet` b where b.r_nationkey = a.n_nationkey); +---------+ | EXPR$0 | +---------+ | 0 | +---------+ 1 row selected (0.921 seconds) {code} This affects TPCDS query 30: {code} 0: jdbc:drill:schema=dfs> WITH customer_total_return . . . . . . . . . . . . > AS (SELECT wr_returning_customer_sk AS ctr_customer_sk, . . . . . . . . . . . . > ca_state AS ctr_state, . . . . . . . . . . . . > Sum(wr_return_amt) AS ctr_total_return . . . . . . . . . . . . > FROM web_returns, . . . . . . . . . . . . > date_dim, . . . . . . . . . . . . > customer_address . . . . . . . . . . . . > WHERE wr_returned_date_sk = d_date_sk . . . . . . . . . . . . > AND d_year = 2000 . . . . . . . . . . . . > AND wr_returning_addr_sk = ca_address_sk . . . . . . . . . . . . > GROUP BY wr_returning_customer_sk, . . . . . . . . . . . . > ca_state) . . . . . . . . . . . . > SELECT c_customer_id, . . . . . . . . . . . . > c_salutation, . . . . . . . . . . . . > c_first_name, . . . . . . . . . . . . > c_last_name, . . . . . . . . . . . . > c_preferred_cust_flag, . . . . . . . . . . . . > c_birth_day, . . . . . . . . . . . . > c_birth_month, . . . . . . . . . . . . > c_birth_year, . . . . . . . . . . . . > c_birth_country, . . . . . . . . . . . . > c_login, . . . . . . . . . . . . > c_email_address, . . . . . . . . . . . . > c_last_review_date, . . . . . . . . . . . . > ctr_total_return . . . . . . . . . . . . > FROM customer_total_return ctr1, . . . . . . . . . . . . > customer_address, . . . . . . . . . . . . > customer . . . . . . . . . . . . > WHERE ctr1.ctr_total_return > (SELECT Avg(ctr_total_return) * 1.2 . . . . . . . . . . . . > FROM customer_total_return ctr2 . . . . . . . . . . . . > WHERE ctr1.ctr_state = ctr2.ctr_state) . . . . . . . . . . . . > AND ca_address_sk = c_current_addr_sk . . . . . . . . . . . . > AND ca_state = 'IN' . . . . . . . . . . . . > AND ctr1.ctr_customer_sk = c_customer_sk . . . . . . . . . . . . > LIMIT 100; Error: UNSUPPORTED_OPERATION ERROR: This query cannot be planned possibly due to either a cartesian join or an inequality join {code} -- This message was sent by Atlassian JIRA (v6.3.4#6332)