inputs/udf/udf-outer-join.sql

0001 -- This test file was converted from outer-join.sql.
0002
0003 -- SPARK-17099: Incorrect result when HAVING clause is added to group by query
0004 CREATE OR REPLACE TEMPORARY VIEW t1 AS SELECT * FROM VALUES
0005 (-234), (145), (367), (975), (298)
0006 as t1(int_col1);
0007
0008 CREATE OR REPLACE TEMPORARY VIEW t2 AS SELECT * FROM VALUES
0009 (-769, -244), (-800, -409), (940, 86), (-507, 304), (-367, 158)
0010 as t2(int_col0, int_col1);
0011
0012 SELECT
0013   (udf(SUM(udf(COALESCE(t1.int_col1, t2.int_col0))))),
0014      (udf(COALESCE(t1.int_col1, t2.int_col0)) * 2)
0015 FROM t1
0016 RIGHT JOIN t2
0017   ON udf(t2.int_col0) = udf(t1.int_col1)
0018 GROUP BY udf(GREATEST(COALESCE(udf(t2.int_col1), 109), COALESCE(t1.int_col1, udf(-449)))),
0019          COALESCE(t1.int_col1, t2.int_col0)
0020 HAVING (udf(SUM(COALESCE(udf(t1.int_col1), udf(t2.int_col0)))))
0021             > (udf(COALESCE(t1.int_col1, t2.int_col0)) * 2);
0022
0023
0024 -- SPARK-17120: Analyzer incorrectly optimizes plan to empty LocalRelation
0025 CREATE OR REPLACE TEMPORARY VIEW t1 AS SELECT * FROM VALUES (97) as t1(int_col1);
0026
0027 CREATE OR REPLACE TEMPORARY VIEW t2 AS SELECT * FROM VALUES (0) as t2(int_col1);
0028
0029 -- Set the cross join enabled flag for the LEFT JOIN test since there's no join condition.
0030 -- Ultimately the join should be optimized away.
0031 set spark.sql.crossJoin.enabled = true;
0032 SELECT *
0033 FROM (
0034 SELECT
0035     udf(COALESCE(udf(t2.int_col1), udf(t1.int_col1))) AS int_col
0036     FROM t1
0037     LEFT JOIN t2 ON false
0038 ) t where (udf(t.int_col)) is not null;
0039 set spark.sql.crossJoin.enabled = false;
0040
0041