Vineet Garg created HIVE-18490: ---------------------------------- Summary: Query with EXISTS and NOT EXISTS with non-equi predicate can produce wrong result Key: HIVE-18490 URL: https://issues.apache.org/jira/browse/HIVE-18490 Project: Hive Issue Type: Bug Reporter: Vineet Garg Assignee: Vineet Garg Attachments: HIVE-18490.1.patch
Queries such as following can produce wrong result {code:sql} select count(ws_order_number) from web_sales ws1 where and exists (select * from web_sales ws2 where ws1.ws_order_number = ws2.ws_order_number and ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk) and not exists(select * from web_returns wr1 where ws1.ws_order_number = wr1.wr_order_number); {code} This query is simplified version of tpcds query 94. Such queries are rewritten into LEFT SEMI JOIN and LEFT OUTER JOIN with residual predicate/filter (non-equi join key). Problem is that these joins are being merged, we shouldn't be merging since semi join has non-equi join filter. Basically the underlying issue is that if a query has multiple join with LEFT SEMI JOIN with non-equi join key it is being merged with other joins. Merge logic should check such cases and avoid merging. -- This message was sent by Atlassian JIRA (v7.6.3#76005)