| ==== |
| ---- QUERY |
| # Constant on LHS of [NOT] IN. Basics to test: |
| # LHS constant: [not-null, null] |
| # Predicate : [IN, NOT IN] |
| # RHS : [NULL, Empty, Non-empty: has match, has no match/no null, |
| # has no match/null, (un)correlated] |
| # LHS not-null, Predicate IN, RHS NULL. Expect no results. |
| SELECT a.id, a.int_col, a.string_col FROM alltypessmall a |
| WHERE 1 IN (SELECT null); |
| ---- RESULTS |
| ---- TYPES |
| INT, INT, STRING |
| ==== |
| ---- QUERY |
| # LHS null, Predicate IN, RHS NULL. Expect no results. |
| SELECT a.id, a.int_col, a.string_col FROM alltypessmall a |
| WHERE NULL IN (SELECT null); |
| ---- RESULTS |
| ---- TYPES |
| INT, INT, STRING |
| ==== |
| ---- QUERY |
| # LHS not-null, Predicate NOT IN, RHS NULL. Expect no results. |
| SELECT a.id, a.int_col, a.string_col FROM alltypessmall a |
| WHERE NULL NOT IN (SELECT null); |
| ---- RESULTS |
| ---- TYPES |
| INT, INT, STRING |
| ==== |
| ---- QUERY |
| # LHS not-null, Predicate IN, RHS NULL. Expect no results. |
| SELECT a.id, a.int_col, a.string_col FROM alltypessmall a |
| WHERE 1 IN (SELECT null); |
| ---- RESULTS |
| ---- TYPES |
| INT, INT, STRING |
| ==== |
| ---- QUERY |
| # LHS not-null, Predicate IN, RHS empty. Expect no results. |
| SELECT a.id, a.int_col, a.string_col FROM alltypessmall a |
| WHERE 1 IN (SELECT int_col from alltypessmall where int_col < 0); |
| ---- RESULTS |
| ---- TYPES |
| INT, INT, STRING |
| ==== |
| ---- QUERY |
| # LHS null, Predicate IN, RHS empty. Expect no results. |
| SELECT a.id, a.int_col, a.string_col FROM alltypessmall a |
| WHERE NULL IN (SELECT int_col from alltypessmall where int_col < 0); |
| ---- RESULTS |
| ---- TYPES |
| INT, INT, STRING |
| ==== |
| ---- QUERY |
| # LHS not-null, Predicate NOT IN, RHS has no results. Expect no results. |
| SELECT a.id, a.int_col, a.string_col FROM alltypessmall a |
| WHERE 1 NOT IN (SELECT id FROM alltypestiny WHERE bool_col = false) and a.id < 5; |
| ---- RESULTS |
| ---- TYPES |
| INT, INT, STRING |
| ==== |
| ---- QUERY |
| # LHS not-null, Predicate NOT IN, RHS empty. Expect all results. |
| SELECT count(*) FROM alltypessmall a |
| WHERE 1 NOT IN (SELECT int_col from alltypessmall where int_col < 0); |
| ---- RESULTS |
| 100 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # LHS null, Predicate NOT IN, RHS empty. Expect all results. |
| SELECT count(*) FROM alltypessmall a |
| WHERE NULL NOT IN (SELECT int_col from alltypessmall where int_col < 0); |
| ---- RESULTS |
| 100 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # LHS not-null, Predicate IN, RHS not-empty with match. Uncorrelated. Expect all results. |
| SELECT count(*) FROM alltypessmall a |
| WHERE 1 IN (SELECT int_col FROM alltypestiny); |
| ---- RESULTS |
| 100 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # LHS not-null, Predicate IN, RHS not empty/correlated. Expect results. |
| SELECT a.id, a.int_col, a.string_col FROM alltypessmall a |
| WHERE 1 IN (SELECT id FROM alltypestiny WHERE bool_col = false AND id = a.id) |
| ---- RESULTS |
| 1,1,'1' |
| ---- TYPES |
| INT, INT, STRING |
| ==== |
| ---- QUERY |
| # LHS not-null, Predicate IN, RHS has matches. Expect all |
| # records with id < 5 are returned since subquery is always true. |
| SELECT a.id, a.int_col, a.string_col FROM alltypessmall a |
| WHERE 1 IN (SELECT id FROM alltypestiny WHERE bool_col = false) and a.id < 5 |
| ---- RESULTS |
| 0,0,'0' |
| 1,1,'1' |
| 2,2,'2' |
| 3,3,'3' |
| 4,4,'4' |
| ---- TYPES |
| INT, INT, STRING |
| ==== |
| ---- QUERY |
| # LHS null, Predicate IN, RHS not-empty with match. Uncorrelated. Expect no results. |
| SELECT count(*) FROM alltypessmall a WHERE NULL IN (SELECT d FROM nulltable); |
| ---- RESULTS |
| 0 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # LHS not-null, Predicate IN, RHS not-empty with match. Correlated. Expect 4 results, |
| # where int_col = 1. |
| SELECT count(*) FROM alltypessmall a |
| WHERE 1 IN (SELECT int_col FROM alltypestiny b where b.id = a.id); |
| ---- RESULTS |
| 4 |
| ---- TYPES |
| BIGINT |
| ==== |
| ==== |
| ---- QUERY |
| # LHS null, Predicate IN, RHS not-empty with match. Correlated. Expect no results, |
| # where int_col = 1. |
| SELECT count(*) FROM alltypessmall a |
| WHERE NULL IN (SELECT NULL FROM alltypestiny b where b.id = a.id); |
| ---- RESULTS |
| 0 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # LHS not-null, Predicate IN, RHS not-empty with no match. Uncorrelated. |
| # Expect no results. |
| SELECT a.id, a.int_col, a.string_col FROM alltypessmall a |
| WHERE 1 IN (SELECT int_col FROM alltypestiny where int_col != 1); |
| ---- RESULTS |
| ---- TYPES |
| INT, INT, STRING |
| ==== |
| ---- QUERY |
| # LHS not-null, Predicate IN, RHS not-empty with no match, but with null. Uncorrelated. |
| # Expect no results. |
| SELECT a.id, a.int_col, a.string_col FROM alltypessmall a |
| WHERE 1 IN (SELECT CASE WHEN id = 1 THEN NULL ELSE ID END FROM alltypestiny); |
| ---- RESULTS |
| ---- TYPES |
| INT, INT, STRING |
| ==== |
| ---- QUERY |
| # LHS not-null, Predicate NOT IN, RHS not-empty with match. Uncorrelated. |
| # Expect no results. |
| SELECT a.id, a.int_col, a.string_col FROM alltypessmall a |
| WHERE 1 NOT IN (SELECT int_col FROM alltypestiny); |
| ---- RESULTS |
| ---- TYPES |
| INT, INT, STRING |
| ==== |
| ---- QUERY |
| # LHS null, Predicate NOT IN, RHS not-empty with match. Uncorrelated. Expect no results. |
| SELECT a.id, a.int_col, a.string_col FROM alltypessmall a |
| WHERE NULL NOT IN (SELECT NULL FROM alltypestiny); |
| ---- RESULTS |
| ---- TYPES |
| INT, INT, STRING |
| ==== |
| ----QUERY |
| # LHS not-null, Predicate NOT IN, RHS not-empty with no match. Uncorrelated. |
| # Expect all results. |
| SELECT count(*) FROM alltypessmall a |
| WHERE 1 NOT IN (SELECT int_col FROM alltypestiny where int_col != 1); |
| ---- RESULTS |
| 100 |
| ---- TYPES |
| BIGINT |
| ==== |
| ----QUERY |
| # LHS null, Predicate NOT IN, RHS not-empty with no match. Uncorrelated. |
| # Expect no results. |
| SELECT count(*) FROM alltypessmall a |
| WHERE NULL NOT IN (SELECT int_col FROM alltypestiny where int_col != 1); |
| ---- RESULTS |
| 0 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # LHS not-null, Predicate NOT IN, RHS not-empty with no match, but null. Uncorrelated. |
| # Expect no results. |
| SELECT a.id, a.int_col, a.string_col FROM alltypessmall a |
| WHERE 1 NOT IN (SELECT CASE WHEN id = 1 then null ELSE id END FROM alltypestiny); |
| ---- RESULTS |
| ---- TYPES |
| INT, INT, STRING |
| ==== |
| ---- QUERY |
| # LHS null, Predicate NOT IN, RHS not-empty with no match, but null. Uncorrelated. |
| # Expect no results. |
| SELECT a.id, a.int_col, a.string_col FROM alltypessmall a |
| WHERE NULL NOT IN (SELECT CASE WHEN id = 1 then null ELSE id END FROM alltypestiny); |
| ---- RESULTS |
| ---- TYPES |
| INT, INT, STRING |
| ==== |
| ---- QUERY |
| # LHS is non-null, Predicate is IN, RHS is an aggregate. |
| SELECT count(*) FROM alltypessmall a WHERE 0 IN (SELECT MIN(int_col) from alltypestiny); |
| ---- RESULTS |
| 100 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # LHS is non-null, Predicate is NOT IN, RHS is an aggregate. |
| SELECT a.id FROM alltypessmall a WHERE 0 NOT IN (SELECT MIN(int_col) from alltypestiny); |
| ---- RESULTS |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| # LHS is not-null, Predicate is IN, RHS is group by without aggregation. Expect all results. |
| SELECT count(*) from alltypessmall a where "a" IN (SELECT a from nulltable group by a); |
| ---- RESULTS |
| 100 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # LHS is null, Predicate is IN, RHS is group by without aggregation. Expect no results. |
| SELECT a.id from alltypessmall a where NULL IN (SELECT d from nulltable group by d); |
| ---- RESULTS |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| # LHS is null, Predicate is NOT IN, RHS is group by without aggregation. Expect no results. |
| # Note: using functional.nulltable explicitly to avoid inconsistent results due to |
| # different null handling across formats (specifically, hbase). See IMPALA-6276. |
| SELECT a.id from alltypessmall a where |
| NULL NOT IN (SELECT d from functional.nulltable group by d); |
| ---- RESULTS |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| # LHS is non-null, Predicate is IN, RHS is select list is "*". |
| SELECT a.id FROM alltypessmall a WHERE |
| 1 IN (SELECT * FROM (select int_col from alltypestiny) tmp) and a.id < 3; |
| ---- RESULTS |
| 0 |
| 1 |
| 2 |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| # LHS is non-null, Predicate is NOT IN, RHS is select list is "*". |
| SELECT a.id FROM alltypessmall a WHERE |
| 1 NOT IN (SELECT * FROM (select int_col from alltypestiny) tmp); |
| ---- RESULTS |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| # LHS is non-null, Predicate is IN, RHS select list is "*" and table is an inline view. |
| SELECT count(*) from alltypessmall a WHERE 1 IN (SELECT * FROM |
| (SELECT int_col from alltypessmall) b); |
| ---- RESULTS |
| 100 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # LHS is non-null, Predicate is NOT IN, RHS is select list is "*" and table is an |
| # inline view. |
| SELECT count(*) from alltypessmall a WHERE 1 NOT IN (SELECT * FROM |
| (SELECT int_col from alltypessmall) b); |
| ---- RESULTS |
| 0 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # LHS is non-null, Predicate is IN, RHS includes nested subqueries. |
| SELECT a.id FROM alltypessmall a WHERE |
| 1 IN (SELECT int_col FROM alltypessmall WHERE |
| -10000 IN (SELECT * FROM (select int_col from alltypestiny) tmp)); |
| ---- RESULTS |
| ---- TYPES |
| INT |
| ==== |