sql-tests/inputs/union.sql

0001 CREATE OR REPLACE TEMPORARY VIEW t1 AS VALUES (1, 'a'), (2, 'b') tbl(c1, c2);
0002 CREATE OR REPLACE TEMPORARY VIEW t2 AS VALUES (1.0, 1), (2.0, 4) tbl(c1, c2);
0003
0004 -- Simple Union
0005 SELECT *
0006 FROM   (SELECT * FROM t1
0007         UNION ALL
0008         SELECT * FROM t1);
0009
0010 -- Type Coerced Union
0011 SELECT *
0012 FROM   (SELECT * FROM t1
0013         UNION ALL
0014         SELECT * FROM t2
0015         UNION ALL
0016         SELECT * FROM t2);
0017
0018 -- Regression test for SPARK-18622
0019 SELECT a
0020 FROM (SELECT 0 a, 0 b
0021       UNION ALL
0022       SELECT SUM(1) a, CAST(0 AS BIGINT) b
0023       UNION ALL SELECT 0 a, 0 b) T;
0024
0025 -- Regression test for SPARK-18841 Push project through union should not be broken by redundant alias removal.
0026 CREATE OR REPLACE TEMPORARY VIEW p1 AS VALUES 1 T(col);
0027 CREATE OR REPLACE TEMPORARY VIEW p2 AS VALUES 1 T(col);
0028 CREATE OR REPLACE TEMPORARY VIEW p3 AS VALUES 1 T(col);
0029 SELECT 1 AS x,
0030        col
0031 FROM   (SELECT col AS col
0032         FROM (SELECT p1.col AS col
0033               FROM   p1 CROSS JOIN p2
0034               UNION ALL
0035               SELECT col
0036               FROM p3) T1) T2;
0037
0038 -- SPARK-24012 Union of map and other compatible columns.
0039 SELECT map(1, 2), 'str'
0040 UNION ALL
0041 SELECT map(1, 2, 3, NULL), 1;
0042
0043 -- SPARK-24012 Union of array and other compatible columns.
0044 SELECT array(1, 2), 'str'
0045 UNION ALL
0046 SELECT array(1, 2, 3, NULL), 1;
0047
0048
0049 -- Clean-up
0050 DROP VIEW IF EXISTS t1;
0051 DROP VIEW IF EXISTS t2;
0052 DROP VIEW IF EXISTS p1;
0053 DROP VIEW IF EXISTS p2;
0054 DROP VIEW IF EXISTS p3;