[ https://issues.apache.org/jira/browse/HIVE-27801?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17796099#comment-17796099 ]
Denys Kuzmenko commented on HIVE-27801: --------------------------------------- Merged. to master. Thanks for the review [~ayushsaxena]! > Exists subquery rewrite results in a wrong plan > ----------------------------------------------- > > Key: HIVE-27801 > URL: https://issues.apache.org/jira/browse/HIVE-27801 > Project: Hive > Issue Type: Bug > Affects Versions: 4.0.0, 4.0.0-beta-1 > Reporter: Denys Kuzmenko > Assignee: Denys Kuzmenko > Priority: Critical > Labels: Regression, hive-4.0.0-must, pull-request-available > Fix For: 4.0.0 > > Attachments: Screenshot 2023-10-10 at 20.14.03.png > > > reproduce (no rows should be returned): > {code} > set hive.explain.user=false; > set hive.support.concurrency=true; > set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; > set hive.stats.autogather=false; > set hive.exec.dynamic.partition.mode=nonstrict; > drop table if exists store_sales; > create table store_sales (ss_promo_sk int, ss_sales_price int, ss_list_price > int) stored as orc tblproperties('transactional'='true'); > insert into store_sales values (1, 20, 15), (1, 15, 20), (1, 10, 15); > explain cbo > select * from store_sales A where exists ( > select 1 from store_sales B > where a.ss_promo_sk=b.ss_promo_sk and A.ss_sales_price>B.ss_list_price > and A.ss_sales_price<B.ss_sales_price); > > select * from store_sales A where exists( > select 1 from store_sales B > where A.ss_promo_sk=B.ss_promo_sk and A.ss_sales_price>B.ss_list_price > and A.ss_sales_price<B.ss_sales_price); > > explain cbo > select * from store_sales A > LEFT SEMI JOIN store_sales B > ON a.ss_promo_sk=b.ss_promo_sk and A.ss_sales_price>B.ss_list_price and > A.ss_sales_price<B.ss_sales_price; > select * from store_sales A > LEFT SEMI JOIN store_sales B > ON a.ss_promo_sk=b.ss_promo_sk and A.ss_sales_price>B.ss_list_price and > A.ss_sales_price<B.ss_sales_price; > {code} > plan diff: > [^Screenshot 2023-10-10 at 20.14.03.png] > expectation: query should return 0 rows -- This message was sent by Atlassian Jira (v8.20.10#820010)