| ==== |
| ---- QUERY |
| # Verification that the setup of SEMIJOIN tables was correct. (SemiJoinTblA) |
| SELECT * FROM SemiJoinTblA |
| ---- RESULTS |
| 1,1,1 |
| 1,1,10 |
| 1,2,10 |
| 1,3,10 |
| NULL,NULL,30 |
| 2,4,30 |
| 2,NULL,20 |
| ---- TYPES |
| INT, INT, INT |
| ==== |
| ---- QUERY |
| # Verification that the setup of SEMIJOIN tables was correct. (SemiJoinTblB) |
| SELECT * FROM SemiJoinTblB |
| ---- RESULTS |
| 1,1,1 |
| 1,1,10 |
| 1,2,5 |
| 1,NULL,10 |
| 2,10,NULL |
| 3,NULL,NULL |
| 3,NULL,50 |
| ---- TYPES |
| INT, INT, INT |
| ==== |
| ---- QUERY |
| # Testing left anti join |
| select j.* from functional_parquet.JoinTbl j |
| left anti join functional_parquet.DimTbl d |
| on j.test_id = d.id |
| ---- RESULTS |
| 1106,'Name6',94612,5000 |
| 1106,'Name16',94612,5000 |
| 1106,'Name6',94616,5000 |
| 1106,'Name16',94616,5000 |
| 1106,'Name6',94612,15000 |
| 1106,'Name16',94612,15000 |
| 1106,'Name6',94616,15000 |
| 1106,'Name16',94616,15000 |
| ---- TYPES |
| bigint, string, int, int |
| ==== |
| ---- QUERY |
| # Testing left anti join on string column |
| select j.* from functional_parquet.JoinTbl j |
| left anti join functional_parquet.DimTbl d |
| on j.test_name = d.name |
| ---- RESULTS |
| 1006,'Name16',94612,5000 |
| 1106,'Name16',94612,5000 |
| 1006,'Name16',94616,5000 |
| 1106,'Name16',94616,5000 |
| 1006,'Name16',94612,15000 |
| 1106,'Name16',94612,15000 |
| 1006,'Name16',94616,15000 |
| 1106,'Name16',94616,15000 |
| ---- TYPES |
| bigint, string, int, int |
| ==== |
| ---- QUERY |
| # Testing multi-way joins that include a left anti join |
| select count(*) from functional_parquet.JoinTbl j |
| left anti join functional_parquet.DimTbl d |
| on j.test_id = d.id |
| left outer join functional_parquet.JoinTbl k |
| on j.test_id = k.test_id |
| ---- RESULTS |
| 64 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # Testing multi-way joins that include only left anti joins |
| select count(*) from functional_parquet.JoinTbl j |
| left anti join functional_parquet.DimTbl d |
| on j.test_id = d.id |
| left anti join functional_parquet.JoinTbl k |
| on j.test_id = k.test_id |
| ---- RESULTS |
| 0 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # Regression test for IMPALA-1160. Proper handling of left anti joins with NULLs |
| SELECT a.* FROM SemiJoinTblA a |
| LEFT ANTI JOIN SemiJoinTblB b ON a.b = b.b |
| ---- RESULTS |
| 2,4,30 |
| 1,3,10 |
| 2,NULL,20 |
| NULL,NULL,30 |
| ---- TYPES |
| INT, INT, INT |
| ==== |
| ---- QUERY |
| # Regression test for IMPALA-1175: Anti join query crashes Impala. |
| SELECT * FROM SemiJoinTblA A LEFT ANTI JOIN |
| (SELECT count(*) `$c$2`, B.b `$c$1` FROM SemiJoinTblB B GROUP BY B.b) `$a$1` |
| ON A.a != `$a$1`.`$c$2` AND `$a$1`.`$c$1` = A.b |
| ---- RESULTS |
| 1,2,10 |
| 1,3,10 |
| NULL,NULL,30 |
| 2,4,30 |
| 2,NULL,20 |
| ---- TYPES |
| INT, INT, INT |
| ==== |
| ---- QUERY |
| # Regression tests for IMPALA-1177: Incorrect results in query with ANTI JOIN on tinyint |
| # column with nulls. |
| SELECT COUNT(*) FROM functional_parquet.alltypesagg t1 |
| LEFT ANTI JOIN functional_parquet.alltypes t2 |
| ON t2.tinyint_col = t1.tinyint_col |
| ---- RESULTS |
| 2000 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| SELECT COUNT(*) FROM functional_parquet.alltypesagg t1 |
| LEFT ANTI JOIN functional_parquet.alltypes t2 |
| ON t2.tinyint_col = t1.tinyint_col and t1.day = 1 |
| ---- RESULTS |
| 10100 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| SELECT COUNT(*) FROM functional_parquet.alltypesagg t1 |
| LEFT ANTI JOIN functional_parquet.alltypes t2 |
| ON t2.tinyint_col = t1.tinyint_col and t2.month = 1 |
| ---- RESULTS |
| 2000 |
| ==== |
| ---- QUERY |
| # Regression test for IMPALA-1204: ANTI JOIN crash running complicated query with right |
| # joins. |
| SELECT 1 FROM functional_parquet.alltypestiny t1 |
| INNER JOIN functional_parquet.alltypestiny t2 |
| ON t2.bigint_col = t1.tinyint_col AND t2.tinyint_col = t1.id |
| LEFT ANTI JOIN |
| (SELECT 1 `$c$2`, tt6.id `$c$1` |
| FROM functional_parquet.alltypes tt5 |
| RIGHT OUTER JOIN functional_parquet.alltypestiny tt6 |
| ON tt6.month = tt5.bigint_col) `$a$1` ON t2.int_col = `$a$1`.`$c$1` |
| ---- RESULTS |
| ---- TYPES |
| TINYINT |
| ==== |
| ---- QUERY |
| # left semi-join on bigint |
| select d.* from functional_parquet.DimTbl d |
| left semi join functional_parquet.JoinTbl j |
| on (d.id = j.test_id) |
| ---- RESULTS |
| 1001,'Name1',94611 |
| 1002,'Name2',94611 |
| 1003,'Name3',94612 |
| 1004,'Name4',94612 |
| 1005,'Name5',94613 |
| 1006,'Name6',94613 |
| ---- TYPES |
| bigint, string, int |
| ==== |
| ---- QUERY |
| # left semi-join on string |
| select d.* from functional_parquet.DimTbl d |
| left semi join functional_parquet.JoinTbl j |
| on (j.test_name = d.name) |
| ---- RESULTS |
| 1001,'Name1',94611 |
| 1002,'Name2',94611 |
| 1003,'Name3',94612 |
| 1004,'Name4',94612 |
| 1005,'Name5',94613 |
| 1006,'Name6',94613 |
| ---- TYPES |
| bigint, string, int |
| ==== |
| ---- QUERY |
| # left semi-join on int |
| select d.* from functional_parquet.DimTbl d |
| left semi join functional_parquet.JoinTbl j |
| on (j.test_zip = d.zip) |
| ---- RESULTS |
| 1001,'Name1',94611 |
| 1002,'Name2',94611 |
| 1003,'Name3',94612 |
| 1004,'Name4',94612 |
| ---- TYPES |
| bigint, string, int |
| ==== |
| ---- QUERY |
| # Regression test for IMPALA-1249. Left anti join on empty build side. |
| SELECT a.* FROM SemiJoinTblA a LEFT ANTI JOIN |
| (SELECT b.* FROM SemiJoinTblB b WHERE b.a > 10) v ON a.b = v.b |
| ---- RESULTS |
| 1,1,1 |
| 1,1,10 |
| 1,2,10 |
| 1,3,10 |
| NULL,NULL,30 |
| 2,4,30 |
| 2,NULL,20 |
| ---- TYPES |
| INT, INT, INT |
| ==== |
| ---- QUERY |
| # Testing right semi join |
| select b.* FROM SemiJoinTblA a |
| right semi join SemiJoinTblB b on a.b = b.b |
| ---- RESULTS |
| 1,1,10 |
| 1,1,1 |
| 1,2,5 |
| ---- TYPES |
| INT, INT, INT |
| ==== |
| ---- QUERY |
| # Testing right semi join with duplicates |
| SELECT b.int_col FROM functional_parquet.tinyinttable a |
| RIGHT SEMI JOIN functional_parquet.tinyinttable b |
| ON a.int_col % 2 = b.int_col % 2 |
| ---- RESULTS |
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| # Testing right semi join with duplicates and other conjuncts |
| SELECT b.int_col FROM functional_parquet.tinyinttable a |
| RIGHT SEMI JOIN functional_parquet.tinyinttable b |
| ON a.int_col % 2 = b.int_col % 2 AND a.int_col + b.int_col > 9 |
| ---- RESULTS |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| # Testing right anti joins |
| select b.* FROM SemiJoinTblA a |
| right anti join SemiJoinTblB b on a.b = b.b |
| ---- RESULTS |
| 2,10,NULL |
| 1,NULL,10 |
| 3,NULL,50 |
| 3,NULL,NULL |
| ---- TYPES |
| INT, INT, INT |
| ==== |
| ---- QUERY |
| # Testing right anti join with duplicates and other conjuncts |
| SELECT b.int_col FROM functional_parquet.tinyinttable a |
| RIGHT ANTI JOIN functional_parquet.tinyinttable b |
| ON a.int_col % 2 = b.int_col % 2 AND a.int_col + b.int_col > 9 |
| ---- RESULTS |
| 0 |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| # Anti joins have a uni-directional value transfer (IMPALA-1249). |
| select a.* FROM SemiJoinTblA a |
| left anti join |
| (select * from SemiJoinTblB where b <= 3) b |
| on a.b = b.b |
| ---- RESULTS |
| 1,3,10 |
| 2,4,30 |
| 2,NULL,20 |
| NULL,NULL,30 |
| ---- TYPES |
| INT, INT, INT |
| ==== |
| ---- QUERY |
| # Regression test for IMPALA-1351: PHJ::GetNext() returns -1 in NAAJ w/o error. |
| # NOTE: Commented out because it takes a few hours to run on a single node. |
| # TODO: Casey will try to simplify, also consider moving to semi-joins-exhaustive. |
| #SELECT COUNT(t2.month) AS int_col_1 |
| #FROM alltypesagg t1 CROSS |
| #JOIN alltypesagg t2 FULL |
| #OUTER JOIN alltypestiny t3 ON t3.year = t2.int_col |
| #AND t3.int_col = t2.year |
| #WHERE t1.day NOT IN |
| # (SELECT tt1.year AS int_col_1 |
| # FROM alltypes tt1 |
| # RIGHT JOIN alltypes tt2 ON tt2.month = tt1.year |
| # WHERE t3.tinyint_col = tt2.tinyint_col |
| # AND t3.bigint_col = tt2.bigint_col |
| # AND t3.int_col = tt1.id) |
| #AND t1.day NOT IN |
| # (SELECT ttt1.tinyint_col AS tinyint_col_1 |
| # FROM alltypestiny ttt1 |
| # WHERE t1.tinyint_col = ttt1.month |
| # AND ttt1.month IS NULL |
| # AND ttt1.tinyint_col NOT IN |
| # (SELECT tttt2.int_col AS int_col_1 |
| # FROM alltypesagg tttt1 |
| # RIGHT JOIN alltypes tttt2 ON tttt2.id = tttt1.year) |
| # AND ttt1.month NOT IN |
| # (SELECT MIN(tttt2.year + tttt1.smallint_col * tttt1.int_col - tttt2.bigint_col) + |
| # MAX(tttt2.tinyint_col) AS bigint_col_1 |
| # FROM alltypestiny tttt1 |
| # INNER JOIN alltypestiny tttt2 ON tttt2.month = tttt1.smallint_col) |
| # AND ttt1.month IS NOT NULL |
| # AND t2.smallint_col = ttt1.bigint_col) |
| #---- RESULTS |
| #121000000 |
| #---- TYPES |
| #BIGINT |
| #==== |
| # Testing right anti join with empty probe side. |
| SELECT b.* FROM (SELECT a.* from SemiJoinTblA a where a.a > 10) v |
| RIGHT ANTI JOIN SemiJoinTblB b on v.b = b.b |
| ---- RESULTS |
| 1,1,1 |
| 1,1,10 |
| 1,2,5 |
| 1,NULL,10 |
| 2,10,NULL |
| 3,NULL,NULL |
| 3,NULL,50 |
| ---- TYPES |
| INT, INT, INT |
| ==== |
