sql-tests/inputs/inner-join.sql

0001 -- There are 2 dimensions we want to test
0002 --  1. run with broadcast hash join, sort merge join or shuffle hash join.
0003 --  2. run with whole-stage-codegen, operator codegen or no codegen.
0004
0005 --CONFIG_DIM1 spark.sql.autoBroadcastJoinThreshold=10485760
0006 --CONFIG_DIM1 spark.sql.autoBroadcastJoinThreshold=-1,spark.sql.join.preferSortMergeJoin=true
0007 --CONFIG_DIM1 spark.sql.autoBroadcastJoinThreshold=-1,spark.sql.join.preferSortMergeJoin=false
0008
0009 --CONFIG_DIM2 spark.sql.codegen.wholeStage=true
0010 --CONFIG_DIM2 spark.sql.codegen.wholeStage=false,spark.sql.codegen.factoryMode=CODEGEN_ONLY
0011 --CONFIG_DIM2 spark.sql.codegen.wholeStage=false,spark.sql.codegen.factoryMode=NO_CODEGEN
0012
0013 CREATE TEMPORARY VIEW t1 AS SELECT * FROM VALUES (1) AS GROUPING(a);
0014 CREATE TEMPORARY VIEW t2 AS SELECT * FROM VALUES (1) AS GROUPING(a);
0015 CREATE TEMPORARY VIEW t3 AS SELECT * FROM VALUES (1), (1) AS GROUPING(a);
0016 CREATE TEMPORARY VIEW t4 AS SELECT * FROM VALUES (1), (1) AS GROUPING(a);
0017
0018 CREATE TEMPORARY VIEW ta AS
0019 SELECT a, 'a' AS tag FROM t1
0020 UNION ALL
0021 SELECT a, 'b' AS tag FROM t2;
0022
0023 CREATE TEMPORARY VIEW tb AS
0024 SELECT a, 'a' AS tag FROM t3
0025 UNION ALL
0026 SELECT a, 'b' AS tag FROM t4;
0027
0028 -- SPARK-19766 Constant alias columns in INNER JOIN should not be folded by FoldablePropagation rule
0029 SELECT tb.* FROM ta INNER JOIN tb ON ta.a = tb.a AND ta.tag = tb.tag;