sql-tests/inputs/outer-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 -- SPARK-17099: Incorrect result when HAVING clause is added to group by query
0014 CREATE OR REPLACE TEMPORARY VIEW t1 AS SELECT * FROM VALUES
0015 (-234), (145), (367), (975), (298)
0016 as t1(int_col1);
0017
0018 CREATE OR REPLACE TEMPORARY VIEW t2 AS SELECT * FROM VALUES
0019 (-769, -244), (-800, -409), (940, 86), (-507, 304), (-367, 158)
0020 as t2(int_col0, int_col1);
0021
0022 SELECT
0023   (SUM(COALESCE(t1.int_col1, t2.int_col0))),
0024      ((COALESCE(t1.int_col1, t2.int_col0)) * 2)
0025 FROM t1
0026 RIGHT JOIN t2
0027   ON (t2.int_col0) = (t1.int_col1)
0028 GROUP BY GREATEST(COALESCE(t2.int_col1, 109), COALESCE(t1.int_col1, -449)),
0029          COALESCE(t1.int_col1, t2.int_col0)
0030 HAVING (SUM(COALESCE(t1.int_col1, t2.int_col0)))
0031             > ((COALESCE(t1.int_col1, t2.int_col0)) * 2);
0032
0033
0034 -- SPARK-17120: Analyzer incorrectly optimizes plan to empty LocalRelation
0035 CREATE OR REPLACE TEMPORARY VIEW t1 AS SELECT * FROM VALUES (97) as t1(int_col1);
0036
0037 CREATE OR REPLACE TEMPORARY VIEW t2 AS SELECT * FROM VALUES (0) as t2(int_col1);
0038
0039 SELECT *
0040 FROM (
0041 SELECT
0042     COALESCE(t2.int_col1, t1.int_col1) AS int_col
0043     FROM t1
0044     LEFT JOIN t2 ON false
0045 ) t where (t.int_col) is not null;