====
---- 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
====