sql-tests/inputs/cte.sql

0001 create temporary view t as select * from values 0, 1, 2 as t(id);
0002 create temporary view t2 as select * from values 0, 1 as t(id);
0003
0004 -- WITH clause should not fall into infinite loop by referencing self
0005 WITH s AS (SELECT 1 FROM s) SELECT * FROM s;
0006
0007 WITH r AS (SELECT (SELECT * FROM r))
0008 SELECT * FROM r;
0009
0010 -- WITH clause should reference the base table
0011 WITH t AS (SELECT 1 FROM t) SELECT * FROM t;
0012
0013 -- WITH clause should not allow cross reference
0014 WITH s1 AS (SELECT 1 FROM s2), s2 AS (SELECT 1 FROM s1) SELECT * FROM s1, s2;
0015
0016 -- WITH clause should reference the previous CTE
0017 WITH t1 AS (SELECT * FROM t2), t2 AS (SELECT 2 FROM t1) SELECT * FROM t1 cross join t2;
0018
0019 -- SPARK-18609 CTE with self-join
0020 WITH CTE1 AS (
0021   SELECT b.id AS id
0022   FROM   T2 a
0023          CROSS JOIN (SELECT id AS id FROM T2) b
0024 )
0025 SELECT t1.id AS c1,
0026        t2.id AS c2
0027 FROM   CTE1 t1
0028        CROSS JOIN CTE1 t2;
0029
0030 -- CTE with column alias
0031 WITH t(x) AS (SELECT 1)
0032 SELECT * FROM t WHERE x = 1;
0033
0034 -- CTE with multiple column aliases
0035 WITH t(x, y) AS (SELECT 1, 2)
0036 SELECT * FROM t WHERE x = 1 AND y = 2;
0037
0038 -- CTE with duplicate column aliases
0039 WITH t(x, x) AS (SELECT 1, 2)
0040 SELECT * FROM t;
0041
0042 -- CTE with empty column alias list is not allowed
0043 WITH t() AS (SELECT 1)
0044 SELECT * FROM t;
0045
0046 -- CTEs with duplicate names are not allowed
0047 WITH
0048   t(x) AS (SELECT 1),
0049   t(x) AS (SELECT 2)
0050 SELECT * FROM t;
0051
0052 -- Clean up
0053 DROP VIEW IF EXISTS t;
0054 DROP VIEW IF EXISTS t2;