sankarh commented on code in PR #4636: URL: https://github.com/apache/hive/pull/4636#discussion_r1327925594
########## 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 + where age not in (select distinct(age) age from t4); + +select count(*) from t3 +where age not in (select distinct(age)age from t3 t1 where t1.age > 10); + +set hive.cbo.enable = true; + +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: Run explain on select query on sub-query with t5. ########## 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: Run explain query on other query with sub-query on t5 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: [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]
