Diksha628 commented on code in PR #4636: URL: https://github.com/apache/hive/pull/4636#discussion_r1323999199
########## ql/src/test/queries/clientpositive/notInTest.q: ########## @@ -0,0 +1,79 @@ +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); + +explain +from t3 b +INSERT OVERWRITE TABLE t4 + select * + where b.age not in + (select a.age + from t3 a + ) + order by age + +INSERT OVERWRITE TABLE t5 + select * + where b.age in + (select c.age + from t3 c + ) +; + +drop table if exists t3; +drop table if exists t4; +drop table if exists t5; + +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 count(*) from t3 +where age not in (select distinct(age) age from t4); + +select count(*) from t3 Review Comment: added in the tests -- 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
