sql-tests/inputs/cte-nested.sql

0001 -- CTE in CTE definition
0002 WITH t as (
0003   WITH t2 AS (SELECT 1)
0004   SELECT * FROM t2
0005 )
0006 SELECT * FROM t;
0007
0008 -- CTE in subquery
0009 SELECT max(c) FROM (
0010   WITH t(c) AS (SELECT 1)
0011   SELECT * FROM t
0012 );
0013
0014 -- CTE in subquery expression
0015 SELECT (
0016   WITH t AS (SELECT 1)
0017   SELECT * FROM t
0018 );
0019
0020 -- CTE in CTE definition shadows outer
0021 WITH
0022   t AS (SELECT 1),
0023   t2 AS (
0024     WITH t AS (SELECT 2)
0025     SELECT * FROM t
0026   )
0027 SELECT * FROM t2;
0028
0029 -- CTE in CTE definition shadows outer 2
0030 WITH
0031   t(c) AS (SELECT 1),
0032   t2 AS (
0033     SELECT (
0034       SELECT max(c) FROM (
0035         WITH t(c) AS (SELECT 2)
0036         SELECT * FROM t
0037       )
0038     )
0039   )
0040 SELECT * FROM t2;
0041
0042 -- CTE in CTE definition shadows outer 3
0043 WITH
0044   t AS (SELECT 1),
0045   t2 AS (
0046     WITH t AS (SELECT 2),
0047     t2 AS (
0048       WITH t AS (SELECT 3)
0049       SELECT * FROM t
0050     )
0051     SELECT * FROM t2
0052   )
0053 SELECT * FROM t2;
0054
0055 -- CTE in subquery shadows outer
0056 WITH t(c) AS (SELECT 1)
0057 SELECT max(c) FROM (
0058   WITH t(c) AS (SELECT 2)
0059   SELECT * FROM t
0060 );
0061
0062 -- CTE in subquery shadows outer 2
0063 WITH t(c) AS (SELECT 1)
0064 SELECT sum(c) FROM (
0065   SELECT max(c) AS c FROM (
0066     WITH t(c) AS (SELECT 2)
0067     SELECT * FROM t
0068   )
0069 );
0070
0071 -- CTE in subquery shadows outer 3
0072 WITH t(c) AS (SELECT 1)
0073 SELECT sum(c) FROM (
0074   WITH t(c) AS (SELECT 2)
0075   SELECT max(c) AS c FROM (
0076     WITH t(c) AS (SELECT 3)
0077     SELECT * FROM t
0078   )
0079 );
0080
0081 -- CTE in subquery expression shadows outer
0082 WITH t AS (SELECT 1)
0083 SELECT (
0084   WITH t AS (SELECT 2)
0085   SELECT * FROM t
0086 );
0087
0088 -- CTE in subquery expression shadows outer 2
0089 WITH t AS (SELECT 1)
0090 SELECT (
0091   SELECT (
0092     WITH t AS (SELECT 2)
0093     SELECT * FROM t
0094   )
0095 );
0096
0097 -- CTE in subquery expression shadows outer 3
0098 WITH t AS (SELECT 1)
0099 SELECT (
0100   WITH t AS (SELECT 2)
0101   SELECT (
0102     WITH t AS (SELECT 3)
0103     SELECT * FROM t
0104   )
0105 );
0106
0107 -- CTE in subquery expression shadows outer 4
0108 WITH t(c) AS (SELECT 1)
0109 SELECT * FROM t
0110 WHERE c IN (
0111   WITH t(c) AS (SELECT 2)
0112   SELECT * FROM t
0113 );
0114
0115 -- forward name conflict is not a real conflict
0116 WITH
0117   t AS (
0118     WITH t2 AS (SELECT 1)
0119     SELECT * FROM t2
0120   ),
0121   t2 AS (SELECT 2)
0122 SELECT * FROM t;
0123
0124 -- case insensitive name conflicts: in other CTE relations
0125 WITH
0126   abc AS (SELECT 1),
0127   t AS (
0128     WITH aBc AS (SELECT 2)
0129     SELECT * FROM aBC
0130   )
0131 SELECT * FROM t;
0132
0133 -- case insensitive name conflicts: in subquery expressions
0134 WITH abc AS (SELECT 1)
0135 SELECT (
0136   WITH aBc AS (SELECT 2)
0137   SELECT * FROM aBC
0138 );