Support Questions
Find answers, ask questions, and share your expertise

Cant run query on Drill

Explorer

Hello

Im performing a tpch benchmark on Apache Drill, when i try to run query 21 (i'll put the code next) it gives the error "UNSUPPORTED_OPERATION ERROR: This query cannot be planned possibly due to either a cartesian join or an inequality join". I tried with all tables on the from line but the result is the same. Why is this happening? can someone provide a version of the queries that might work with the same result?

<code>SELECT S_NAME, COUNT(*) AS NUMWAIT
FROM hive.tpch_flat_orc_30.supplier, hive.tpch_flat_orc_30.nation
join hive.tpch_flat_orc_30.LINEITEM L1 on S_SUPPKEY = L1.L_SUPPKEY
join hive.tpch_flat_orc_30.ORDERS on O_ORDERKEY = L1.L_ORDERKEY
where O_ORDERSTATUS = 'F'
AND L1.L_RECEIPTDATE> L1.L_COMMITDATE
AND EXISTS (SELECT *
FROM hive.tpch_flat_orc_30.lINEITEM L2
WHERE L2.L_ORDERKEY = L1.L_ORDERKEY
AND L2.L_SUPPKEY <> L1.L_SUPPKEY)
AND NOT EXISTS (SELECT *
FROM hive.tpch_flat_orc_30.lineitem L3
WHERE L3.L_ORDERKEY = L1.L_ORDERKEY
AND L3.L_SUPPKEY <> L1.L_SUPPKEY
AND L3.L_RECEIPTDATE > L3.L_COMMITDATE)
AND S_NATIONKEY = N_NATIONKEY
AND N_NAME = 'SAUDI ARABIA'
GROUP BY S_NAME
ORDER BY NUMWAIT DESC, S_NAME
LIMIT 100;
1 ACCEPTED SOLUTION

Accepted Solutions

Re: Cant run query on Drill

@mÁRIO Rodrigues

This seems to be known issue and Jira DRILL-5214 has been raised for the same.

You could remove "L3.L_SUPPKEY <> L1.L_SUPPKEY" and "L2.L_SUPPKEY <> L1.L_SUPPKEY", and try using "NOT IN" with sub-query.

View solution in original post

1 REPLY 1

Re: Cant run query on Drill

@mÁRIO Rodrigues

This seems to be known issue and Jira DRILL-5214 has been raised for the same.

You could remove "L3.L_SUPPKEY <> L1.L_SUPPKEY" and "L2.L_SUPPKEY <> L1.L_SUPPKEY", and try using "NOT IN" with sub-query.

View solution in original post