Diksha628 commented on code in PR #4636: URL: https://github.com/apache/hive/pull/4636#discussion_r1327934731
########## ql/src/test/queries/clientpositive/notInTest.q: ########## @@ -0,0 +1,66 @@ +create table t3 (id int,name string, age int); +insert into t3 values(1,'Sagar',23),(2,'Sultan',NULL),(3,'Surya',23),(4,'Raman',45),(5,'Scott',23),(6,'Ramya',5),(7,'',23),(8,'',23),(9,'ron',3),(10,'Sam',22),(11,'nick',19),(12,'fed',18),(13,'kong',13),(14,'hela',45); + +create table t4 (id int,name string, age int); +insert into t4 values(1,'Sagar',23),(3,'Surya',23),(4,'Raman',45),(5,'Scott',23),(6,'Ramya',5),(7,'',23),(8,'',23); + +create table t5 (id int,name string, ages int); +insert into t5 values(1,'Sagar',23),(3,'Surya',NULL),(4,'Raman',45),(5,'Scott',23),(6,'Ramya',5),(7,'',23),(8,'',23); + +set hive.cbo.enable = false; + +select * from t3 +where age in (select distinct(age) age from t4) +order by age ; + +select * from t3 +where age not in (select distinct(age) age from t4 ) +order by age ; + + +select * from t3 +where age not in (select distinct(ages) ages from t5 where t5.ages is not null) +order by age ; + + +select * from t3 +where age not in (select distinct(ages) ages from t5 ) +order by age ; + +select count(*) from t3 +where age not in (23,22, null ); + +explain select * from t3 Review Comment: done, added explain plan for the other subquery as well. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: gitbox-unsubscr...@hive.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: gitbox-unsubscr...@hive.apache.org For additional commands, e-mail: gitbox-h...@hive.apache.org
