semi-joins.test

====
---- 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
====
---- QUERY
# Testing LEFT ANTI JOIN with ON FALSE should return all rows from left table
# because no rows from right table can match.
SELECT a.* FROM SemiJoinTblA a LEFT ANTI JOIN SemiJoinTblB b ON false
---- 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 ANTI JOIN with ON FALSE should return all rows from right table
# because no rows from left table can match.
SELECT b.* FROM SemiJoinTblA a RIGHT ANTI JOIN SemiJoinTblB b ON false
---- 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 with ON TRUE should return empty result set
# because all rows from right table match.
SELECT a.* FROM SemiJoinTblA a LEFT ANTI JOIN SemiJoinTblB b ON true
---- RESULTS
---- TYPES
INT, INT, INT
====
---- QUERY
# Testing RIGHT ANTI JOIN with ON TRUE should return empty result set
# because all rows from left table match.
SELECT b.* FROM SemiJoinTblA a RIGHT ANTI JOIN SemiJoinTblB b ON true
---- RESULTS
---- TYPES
INT, INT, INT
====
---- QUERY
# Testing LEFT ANTI JOIN with constant expression 1=0 (equivalent to FALSE)
SELECT a.* FROM SemiJoinTblA a LEFT ANTI JOIN SemiJoinTblB b ON 1=0
---- 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 LEFT ANTI JOIN with constant expression 1=1 (equivalent to TRUE)
SELECT a.* FROM SemiJoinTblA a LEFT ANTI JOIN SemiJoinTblB b ON 1=1
---- RESULTS
---- TYPES
INT, INT, INT
====
---- QUERY
# Testing LEFT ANTI JOIN with ON FALSE and WHERE clause
# Should return rows from left table that satisfy WHERE condition
SELECT a.* FROM SemiJoinTblA a LEFT ANTI JOIN SemiJoinTblB b ON false
WHERE a.a = 1
---- RESULTS
1,1,1
1,1,10
1,2,10
1,3,10
---- TYPES
INT, INT, INT
====
---- QUERY
# Testing LEFT ANTI JOIN with ON TRUE and WHERE clause
# Should return empty result (anti-join filters all, then WHERE is applied)
SELECT a.* FROM SemiJoinTblA a LEFT ANTI JOIN SemiJoinTblB b ON true
WHERE a.a = 1
---- RESULTS
---- TYPES
INT, INT, INT
====
---- QUERY
# Testing LEFT SEMI JOIN with ON FALSE should return empty result
# because no rows from right table can match.
SELECT a.* FROM SemiJoinTblA a LEFT SEMI JOIN SemiJoinTblB b ON false
---- RESULTS
---- TYPES
INT, INT, INT
====
---- QUERY
# Testing LEFT SEMI JOIN with ON TRUE should return all rows from left table
SELECT a.* FROM SemiJoinTblA a LEFT SEMI JOIN SemiJoinTblB b ON true
---- 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 nested query with LEFT ANTI JOIN ON FALSE
SELECT * FROM (
  SELECT a.* FROM SemiJoinTblA a LEFT ANTI JOIN SemiJoinTblB b ON false
) v WHERE v.a = 2
---- RESULTS
2,4,30
2,NULL,20
---- TYPES
INT, INT, INT
====
---- QUERY
# Testing multiple anti-joins with constant predicates
SELECT a.* FROM SemiJoinTblA a
LEFT ANTI JOIN SemiJoinTblB b ON false
LEFT ANTI JOIN SemiJoinTblB c ON true
---- RESULTS
---- TYPES
INT, INT, INT
====
---- QUERY
# Testing anti-join with mixed constant and regular predicates (ON FALSE OR a.a = b.a)
# The OR with FALSE should be optimized away, leaving just a.a = b.a
# Only rows with NULL in column a are returned because NULL doesn't match anything
SELECT a.* FROM SemiJoinTblA a LEFT ANTI JOIN SemiJoinTblB b ON (false OR a.a = b.a)
---- RESULTS
NULL,NULL,30
---- TYPES
INT, INT, INT
====
---- QUERY
# Testing anti-join with mixed constant and regular predicates (ON TRUE AND a.a = b.a)
# The AND with TRUE should be optimized away, leaving just a.a = b.a
# Only rows with NULL in column a are returned because NULL doesn't match anything
SELECT a.* FROM SemiJoinTblA a LEFT ANTI JOIN SemiJoinTblB b ON (true AND a.a = b.a)
---- RESULTS
NULL,NULL,30
---- TYPES
INT, INT, INT
====
---- QUERY
# Testing anti-join with inline view on the left side and ON FALSE
# The inline view should not be marked as having an empty result set
SELECT v.* FROM (SELECT * FROM SemiJoinTblA WHERE a = 1) v
LEFT ANTI JOIN SemiJoinTblB b ON false
---- RESULTS
1,1,1
1,1,10
1,2,10
1,3,10
---- TYPES
INT, INT, INT
====
---- QUERY
# Testing nested inline views with anti-join and ON FALSE
# Verifies that constant predicates don't propagate incorrectly through nested views
SELECT * FROM (
  SELECT v.* FROM (SELECT * FROM SemiJoinTblA WHERE a = 2) v
  LEFT ANTI JOIN SemiJoinTblB b ON false
) v2 WHERE v2.a = 2
---- RESULTS
2,4,30
2,NULL,20
---- TYPES
INT, INT, INT
====
---- QUERY
# Testing anti-join with inline view and mixed predicates
# Constant FALSE should not be migrated into the inline view
SELECT v.* FROM (SELECT * FROM SemiJoinTblA WHERE a = 1) v
LEFT ANTI JOIN SemiJoinTblB b ON (false OR v.a = b.a)
---- RESULTS
---- TYPES
INT, INT, INT
====