summaryrefslogtreecommitdiffstats
path: root/tests/fixtures/optimizer
diff options
context:
space:
mode:
Diffstat (limited to 'tests/fixtures/optimizer')
-rw-r--r--tests/fixtures/optimizer/merge_subqueries.sql10
-rw-r--r--tests/fixtures/optimizer/optimizer.sql72
-rw-r--r--tests/fixtures/optimizer/pushdown_projections.sql20
-rw-r--r--tests/fixtures/optimizer/qualify_columns.sql36
-rw-r--r--tests/fixtures/optimizer/qualify_columns_ddl.sql12
-rw-r--r--tests/fixtures/optimizer/qualify_tables.sql20
-rw-r--r--tests/fixtures/optimizer/tpc-ds/tpc-ds.sql98
-rw-r--r--tests/fixtures/optimizer/tpc-h/tpc-h.sql4
-rw-r--r--tests/fixtures/optimizer/unnest_subqueries.sql12
9 files changed, 177 insertions, 107 deletions
diff --git a/tests/fixtures/optimizer/merge_subqueries.sql b/tests/fixtures/optimizer/merge_subqueries.sql
index 7fc4b25..7bc45a7 100644
--- a/tests/fixtures/optimizer/merge_subqueries.sql
+++ b/tests/fixtures/optimizer/merge_subqueries.sql
@@ -226,7 +226,7 @@ FROM
t1
WHERE
row_num = 1;
-WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.a AS a, t1.b AS b FROM t1 WHERE t1.row_num = 1;
+WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.a AS a, t1.b AS b FROM t1 AS t1 WHERE t1.row_num = 1;
# title: Test preventing merge of window expressions join clause
with t1 as (
@@ -241,7 +241,7 @@ SELECT
t1.a,
t1.b
FROM t1 JOIN y ON t1.a = y.c AND t1.row_num = 1;
-WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.a AS a, t1.b AS b FROM t1 JOIN y AS y ON t1.a = y.c AND t1.row_num = 1;
+WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.a AS a, t1.b AS b FROM t1 AS t1 JOIN y AS y ON t1.a = y.c AND t1.row_num = 1;
# title: Test preventing merge of window expressions agg function
with t1 as (
@@ -256,7 +256,7 @@ SELECT
SUM(t1.row_num) as total_rows
FROM
t1;
-WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT SUM(t1.row_num) AS total_rows FROM t1;
+WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT SUM(t1.row_num) AS total_rows FROM t1 AS t1;
# title: Test prevent merging of window if in group by func
with t1 as (
@@ -274,7 +274,7 @@ FROM
t1
GROUP BY t1.row_num
ORDER BY t1.row_num;
-WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.row_num AS row_num, SUM(t1.a) AS total FROM t1 GROUP BY t1.row_num ORDER BY row_num;
+WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.row_num AS row_num, SUM(t1.a) AS total FROM t1 AS t1 GROUP BY t1.row_num ORDER BY row_num;
# title: Test prevent merging of window if in order by func
with t1 as (
@@ -291,7 +291,7 @@ SELECT
FROM
t1
ORDER BY t1.row_num, t1.a;
-WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.row_num AS row_num, t1.a AS a FROM t1 ORDER BY t1.row_num, t1.a;
+WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.row_num AS row_num, t1.a AS a FROM t1 AS t1 ORDER BY t1.row_num, t1.a;
# title: Test allow merging of window function
with t1 as (
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index ea29e3e..8f4a551 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -79,7 +79,7 @@ WITH "cte" AS (
)
SELECT
"cte"."a" AS "a"
-FROM "cte";
+FROM "cte" AS "cte";
# title: Chained CTEs
WITH cte1 AS (
@@ -103,11 +103,11 @@ WITH "cte1" AS (
)
SELECT
"cte1"."a" AS "a"
-FROM "cte1"
+FROM "cte1" AS "cte1"
UNION ALL
SELECT
"cte1"."a" + 1 AS "a"
-FROM "cte1";
+FROM "cte1" AS "cte1";
# title: Correlated subquery
SELECT a, SUM(b) AS sum_b
@@ -223,14 +223,14 @@ WITH "m" AS (
SELECT
"m"."a" AS "a",
"m"."b" AS "b"
- FROM "m"
+ FROM "m" AS "m"
WHERE
"m"."a" = 1
), "o" AS (
SELECT
"m"."a" AS "a",
"m"."b" AS "b"
- FROM "m"
+ FROM "m" AS "m"
WHERE
"m"."a" = 2
)
@@ -238,10 +238,10 @@ SELECT
"n"."a" AS "a",
"n"."b" AS "b",
"o"."b" AS "b"
-FROM "n"
+FROM "n" AS "n"
JOIN "n" AS "n2"
ON "n"."a" = "n2"."a"
-FULL JOIN "o"
+FULL JOIN "o" AS "o"
ON "n"."a" = "o"."a"
WHERE
"o"."b" > 0;
@@ -355,7 +355,7 @@ WITH RECURSIVE "base" AS (
"cte1"."a" AS "a",
"cte1"."b" AS "b",
"cte1"."c" AS "c"
- FROM "cte1"
+ FROM "cte1" AS "cte1"
WHERE
"cte1"."a" < 1
)
@@ -363,7 +363,7 @@ SELECT
"cte1"."a" AS "a",
"cte1"."b" AS "b",
"cte1"."c" AS "c"
-FROM "cte1";
+FROM "cte1" AS "cte1";
# title: right join should not push down to from
SELECT x.a, y.b
@@ -747,7 +747,7 @@ SELECT
`_q_0`.`second_half_sales` AS `second_half_sales`
FROM `produce` AS `produce` UNPIVOT((`first_half_sales`, `second_half_sales`) FOR `semesters` IN ((`produce`.`q1`, `produce`.`q2`) AS 'semester_1', (`produce`.`q3`, `produce`.`q4`) AS 'semester_2')) AS `_q_0`;
-# title: quoting is maintained
+# title: quoting is preserved
# dialect: snowflake
with cte1("id", foo) as (select 1, 2) select "id" from cte1;
WITH "CTE1" AS (
@@ -756,7 +756,7 @@ WITH "CTE1" AS (
)
SELECT
"CTE1"."id" AS "id"
-FROM "CTE1";
+FROM "CTE1" AS "CTE1";
# title: ensures proper quoting happens after all optimizations
# execute: false
@@ -1090,7 +1090,7 @@ WITH "m" AS (
)
SELECT
COALESCE("m"."a", "foo"."a") AS "a"
-FROM "m"
+FROM "m" AS "m"
JOIN "n" AS "foo"("a")
ON "foo"."a" = "m"."a";
@@ -1170,7 +1170,7 @@ SELECT
"alias3"."m_od" AS "m_od",
COALESCE("alias3"."c_od", 0) AS "c_od"
FROM "table1" AS "table1"
-LEFT JOIN "alias3"
+LEFT JOIN "alias3" AS "alias3"
ON "alias3"."cid" = "table1"."cid";
# title: CTE with EXPLODE cannot be merged
@@ -1205,7 +1205,7 @@ WITH `T` AS (
)
SELECT
`T`.`CoL` AS `CoL`
-FROM `T`;
+FROM `T` AS `T`;
# title: override mysql's settings so it normalizes to lowercase
# dialect: mysql, normalization_strategy = lowercase
@@ -1217,7 +1217,7 @@ WITH `t` AS (
)
SELECT
`t`.`CoL` AS `CoL`
-FROM `t`;
+FROM `t` AS `t`;
# title: top-level query is parenthesized
# execute: false
@@ -1239,11 +1239,11 @@ WITH "x" AS (
(
SELECT
"x"."a" AS "a"
- FROM "x"
+ FROM "x" AS "x"
UNION ALL
SELECT
"x"."a" AS "a"
- FROM "x"
+ FROM "x" AS "x"
LIMIT 10
)
LIMIT 10;
@@ -1273,3 +1273,41 @@ JOIN "stops" AS "d"("id", "name")
AND (
"c"."name" = 'Craiglockhart' OR "d"."name" = 'Tollcross'
);
+
+# title: avoid dag cycles with unnesting subqueries
+# execute: false
+# dialect: snowflake
+SELECT
+ A.ACCOUNT_ID,
+ A.NAME,
+ C.EMAIL_DOMAIN
+FROM ACCOUNTS AS A
+LEFT JOIN CONTACTS AS C
+ ON C.ACCOUNT_ID = A.ACCOUNT_ID
+ AND C.EMAIL_DOMAIN IN (
+ SELECT
+ D.DOMAIN
+ FROM DOMAINS D
+ WHERE
+ TYPE = 'education'
+ );
+WITH "_u_0" AS (
+ SELECT
+ "D"."DOMAIN" AS "DOMAIN"
+ FROM "DOMAINS" AS "D"
+ WHERE
+ "D"."TYPE" = 'education'
+ GROUP BY
+ "D"."DOMAIN"
+)
+SELECT
+ "A"."ACCOUNT_ID" AS "ACCOUNT_ID",
+ "A"."NAME" AS "NAME",
+ "C"."EMAIL_DOMAIN" AS "EMAIL_DOMAIN"
+FROM "ACCOUNTS" AS "A"
+LEFT JOIN "CONTACTS" AS "C"
+ ON "A"."ACCOUNT_ID" = "C"."ACCOUNT_ID"
+LEFT JOIN "_u_0" AS "_u_0"
+ ON "C"."EMAIL_DOMAIN" = "_u_0"."DOMAIN"
+WHERE
+ NOT "_u_0"."DOMAIN" IS NULL;
diff --git a/tests/fixtures/optimizer/pushdown_projections.sql b/tests/fixtures/optimizer/pushdown_projections.sql
index 8497c5b..b7103ef 100644
--- a/tests/fixtures/optimizer/pushdown_projections.sql
+++ b/tests/fixtures/optimizer/pushdown_projections.sql
@@ -23,19 +23,19 @@ SELECT a FROM (SELECT a, b FROM x UNION ALL SELECT a, b FROM x);
SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x UNION ALL SELECT x.a AS a FROM x AS x) AS _q_0;
WITH t1 AS (SELECT x.a AS a, x.b AS b FROM x UNION ALL SELECT z.b AS b, z.c AS c FROM z) SELECT a, b FROM t1;
-WITH t1 AS (SELECT x.a AS a, x.b AS b FROM x AS x UNION ALL SELECT z.b AS b, z.c AS c FROM z AS z) SELECT t1.a AS a, t1.b AS b FROM t1;
+WITH t1 AS (SELECT x.a AS a, x.b AS b FROM x AS x UNION ALL SELECT z.b AS b, z.c AS c FROM z AS z) SELECT t1.a AS a, t1.b AS b FROM t1 AS t1;
SELECT a FROM (SELECT a, b FROM x UNION SELECT a, b FROM x);
SELECT _q_0.a AS a FROM (SELECT x.a AS a, x.b AS b FROM x AS x UNION SELECT x.a AS a, x.b AS b FROM x AS x) AS _q_0;
WITH y AS (SELECT * FROM x) SELECT a FROM y;
-WITH y AS (SELECT x.a AS a FROM x AS x) SELECT y.a AS a FROM y;
+WITH y AS (SELECT x.a AS a FROM x AS x) SELECT y.a AS a FROM y AS y;
WITH z AS (SELECT * FROM x), q AS (SELECT b FROM z) SELECT b FROM q;
-WITH z AS (SELECT x.b AS b FROM x AS x), q AS (SELECT z.b AS b FROM z) SELECT q.b AS b FROM q;
+WITH z AS (SELECT x.b AS b FROM x AS x), q AS (SELECT z.b AS b FROM z AS z) SELECT q.b AS b FROM q AS q;
WITH z AS (SELECT * FROM x) SELECT a FROM z UNION SELECT a FROM z;
-WITH z AS (SELECT x.a AS a FROM x AS x) SELECT z.a AS a FROM z UNION SELECT z.a AS a FROM z;
+WITH z AS (SELECT x.a AS a FROM x AS x) SELECT z.a AS a FROM z AS z UNION SELECT z.a AS a FROM z AS z;
SELECT b FROM (SELECT a, SUM(b) AS b FROM x GROUP BY a);
SELECT _q_0.b AS b FROM (SELECT SUM(x.b) AS b FROM x AS x GROUP BY x.a) AS _q_0;
@@ -50,7 +50,7 @@ SELECT x FROM UNNEST([1, 2]) AS q(x, y);
SELECT q.x AS x FROM UNNEST(ARRAY(1, 2)) AS q(x, y);
WITH t1 AS (SELECT cola, colb FROM UNNEST([STRUCT(1 AS cola, 'test' AS colb)]) AS "q"("cola", "colb")) SELECT cola FROM t1;
-WITH t1 AS (SELECT "q".cola AS cola FROM UNNEST(ARRAY(STRUCT(1 AS cola, 'test' AS colb))) AS "q"("cola", "colb")) SELECT t1.cola AS cola FROM t1;
+WITH t1 AS (SELECT "q".cola AS cola FROM UNNEST(ARRAY(STRUCT(1 AS cola, 'test' AS colb))) AS "q"("cola", "colb")) SELECT t1.cola AS cola FROM t1 AS t1;
SELECT x FROM VALUES(1, 2) AS q(x, y);
SELECT q.x AS x FROM (VALUES (1, 2)) AS q(x, y);
@@ -59,7 +59,7 @@ SELECT i.a FROM x AS i LEFT JOIN (SELECT a, b FROM (SELECT a, b FROM x)) AS j ON
SELECT i.a AS a FROM x AS i LEFT JOIN (SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0) AS j ON i.a = j.a;
WITH cte AS (SELECT source.a AS a, ROW_NUMBER() OVER (PARTITION BY source.id, source.timestamp ORDER BY source.a DESC) AS index FROM source AS source QUALIFY index) SELECT cte.a AS a FROM cte;
-WITH cte AS (SELECT source.a AS a FROM source AS source QUALIFY ROW_NUMBER() OVER (PARTITION BY source.id, source.timestamp ORDER BY source.a DESC)) SELECT cte.a AS a FROM cte;
+WITH cte AS (SELECT source.a AS a FROM source AS source QUALIFY ROW_NUMBER() OVER (PARTITION BY source.id, source.timestamp ORDER BY source.a DESC)) SELECT cte.a AS a FROM cte AS cte;
WITH cte AS (SELECT 1 AS x, 2 AS y, 3 AS z) SELECT cte.a FROM cte AS cte(a);
WITH cte AS (SELECT 1 AS x) SELECT cte.a AS a FROM cte AS cte(a);
@@ -71,13 +71,13 @@ WITH cte(x, y, z) AS (SELECT 1, 2, 3) SELECT a, z FROM (SELECT * FROM cte AS cte
WITH cte AS (SELECT 1 AS x, 3 AS z) SELECT cte.a AS a, cte.z AS z FROM (SELECT cte.b AS a, cte.z AS z FROM cte AS cte(b)) AS cte;
WITH y AS (SELECT a FROM x) SELECT 1 FROM y;
-WITH y AS (SELECT 1 AS _ FROM x AS x) SELECT 1 AS "1" FROM y;
+WITH y AS (SELECT 1 AS _ FROM x AS x) SELECT 1 AS "1" FROM y AS y;
WITH y AS (SELECT SUM(a) FROM x) SELECT 1 FROM y;
-WITH y AS (SELECT MAX(1) AS _ FROM x AS x) SELECT 1 AS "1" FROM y;
+WITH y AS (SELECT MAX(1) AS _ FROM x AS x) SELECT 1 AS "1" FROM y AS y;
WITH y AS (SELECT a FROM x GROUP BY a) SELECT 1 FROM y;
-WITH y AS (SELECT 1 AS _ FROM x AS x GROUP BY x.a) SELECT 1 AS "1" FROM y;
+WITH y AS (SELECT 1 AS _ FROM x AS x GROUP BY x.a) SELECT 1 AS "1" FROM y AS y;
--------------------------------------
-- Unknown Star Expansion
@@ -102,7 +102,7 @@ SELECT a FROM (SELECT * FROM (SELECT * FROM aa));
SELECT _q_1.a AS a FROM (SELECT _q_0.a AS a FROM (SELECT aa.a AS a FROM aa AS aa) AS _q_0) AS _q_1;
with cte1 as (SELECT cola, colb FROM tb UNION ALL SELECT colc, cold FROM tb2) SELECT cola FROM cte1;
-WITH cte1 AS (SELECT tb.cola AS cola FROM tb AS tb UNION ALL SELECT tb2.colc AS colc FROM tb2 AS tb2) SELECT cte1.cola AS cola FROM cte1;
+WITH cte1 AS (SELECT tb.cola AS cola FROM tb AS tb UNION ALL SELECT tb2.colc AS colc FROM tb2 AS tb2) SELECT cte1.cola AS cola FROM cte1 AS cte1;
SELECT * FROM ((SELECT c FROM t1) JOIN t2);
SELECT * FROM ((SELECT t1.c AS c FROM t1 AS t1) AS _q_0, t2 AS t2);
diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql
index 5641ed4..ad197db 100644
--- a/tests/fixtures/optimizer/qualify_columns.sql
+++ b/tests/fixtures/optimizer/qualify_columns.sql
@@ -227,9 +227,22 @@ SELECT x.a AS a FROM x AS x WHERE x.b IN (SELECT y.c AS c FROM y AS y);
SELECT (SELECT c FROM y) FROM x;
SELECT (SELECT y.c AS c FROM y AS y) AS _col_0 FROM x AS x;
+# execute: false
+WITH t(c) AS (SELECT 1) SELECT (SELECT c) FROM t;
+WITH t AS (SELECT 1 AS c) SELECT (SELECT t.c AS c) AS _col_0 FROM t AS t;
+
+# execute: false
+WITH t1(c1) AS (SELECT 1), t2(c2) AS (SELECT 2) SELECT (SELECT c1 FROM t2) FROM t1;
+WITH t1 AS (SELECT 1 AS c1), t2 AS (SELECT 2 AS c2) SELECT (SELECT t1.c1 AS c1 FROM t2 AS t2) AS _col_0 FROM t1 AS t1;
+
SELECT a FROM (SELECT a FROM x) WHERE a IN (SELECT b FROM (SELECT b FROM y));
SELECT _q_1.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_1 WHERE _q_1.a IN (SELECT _q_0.b AS b FROM (SELECT y.b AS b FROM y AS y) AS _q_0);
+# dialect: mysql
+# execute: false
+SELECT * FROM table_a as A WHERE A.col1 IN (SELECT MAX(B.col2) FROM table_b as B UNION ALL SELECT MAX(C.col2) FROM table_b as C);
+SELECT * FROM table_a AS `A` WHERE `A`.col1 IN (SELECT MAX(`B`.col2) AS _col_0 FROM table_b AS `B` UNION ALL SELECT MAX(`C`.col2) AS _col_0 FROM table_b AS `C`);
+
--------------------------------------
-- Correlated subqueries
--------------------------------------
@@ -291,28 +304,28 @@ SELECT s.b AS b FROM (SELECT t1.b AS b FROM t1 AS t1 UNION ALL SELECT t2.b AS b
-- CTEs
--------------------------------------
WITH z AS (SELECT x.a AS a FROM x) SELECT z.a AS a FROM z;
-WITH z AS (SELECT x.a AS a FROM x AS x) SELECT z.a AS a FROM z;
+WITH z AS (SELECT x.a AS a FROM x AS x) SELECT z.a AS a FROM z AS z;
WITH z(a) AS (SELECT a FROM x) SELECT * FROM z;
-WITH z AS (SELECT x.a AS a FROM x AS x) SELECT z.a AS a FROM z;
+WITH z AS (SELECT x.a AS a FROM x AS x) SELECT z.a AS a FROM z AS z;
WITH z AS (SELECT a FROM x) SELECT * FROM z as q;
WITH z AS (SELECT x.a AS a FROM x AS x) SELECT q.a AS a FROM z AS q;
WITH z AS (SELECT a FROM x) SELECT * FROM z;
-WITH z AS (SELECT x.a AS a FROM x AS x) SELECT z.a AS a FROM z;
+WITH z AS (SELECT x.a AS a FROM x AS x) SELECT z.a AS a FROM z AS z;
WITH z AS (SELECT a FROM x), q AS (SELECT * FROM z) SELECT * FROM q;
-WITH z AS (SELECT x.a AS a FROM x AS x), q AS (SELECT z.a AS a FROM z) SELECT q.a AS a FROM q;
+WITH z AS (SELECT x.a AS a FROM x AS x), q AS (SELECT z.a AS a FROM z AS z) SELECT q.a AS a FROM q AS q;
WITH z AS (SELECT * FROM x) SELECT * FROM z UNION SELECT * FROM z;
-WITH z AS (SELECT x.a AS a, x.b AS b FROM x AS x) SELECT z.a AS a, z.b AS b FROM z UNION SELECT z.a AS a, z.b AS b FROM z;
+WITH z AS (SELECT x.a AS a, x.b AS b FROM x AS x) SELECT z.a AS a, z.b AS b FROM z AS z UNION SELECT z.a AS a, z.b AS b FROM z AS z;
WITH z AS (SELECT * FROM x), q AS (SELECT b FROM z) SELECT b FROM q;
-WITH z AS (SELECT x.a AS a, x.b AS b FROM x AS x), q AS (SELECT z.b AS b FROM z) SELECT q.b AS b FROM q;
+WITH z AS (SELECT x.a AS a, x.b AS b FROM x AS x), q AS (SELECT z.b AS b FROM z AS z) SELECT q.b AS b FROM q AS q;
WITH z AS ((SELECT b FROM x UNION ALL SELECT b FROM y) ORDER BY b) SELECT * FROM z;
-WITH z AS ((SELECT x.b AS b FROM x AS x UNION ALL SELECT y.b AS b FROM y AS y) ORDER BY b) SELECT z.b AS b FROM z;
+WITH z AS ((SELECT x.b AS b FROM x AS x UNION ALL SELECT y.b AS b FROM y AS y) ORDER BY b) SELECT z.b AS b FROM z AS z;
WITH cte(x) AS (SELECT 1) SELECT * FROM cte AS cte(a);
WITH cte AS (SELECT 1 AS x) SELECT cte.a AS a FROM cte AS cte(a);
@@ -322,7 +335,7 @@ WITH cte AS (SELECT 1 AS x, 2 AS y) SELECT cte.a AS a, cte.y AS y FROM cte AS ct
# execute: false
WITH player AS (SELECT player.name, player.asset.info FROM players) SELECT * FROM player;
-WITH player AS (SELECT players.player.name AS name, players.player.asset.info AS info FROM players AS players) SELECT player.name AS name, player.info AS info FROM player;
+WITH player AS (SELECT players.player.name AS name, players.player.asset.info AS info FROM players AS players) SELECT player.name AS name, player.info AS info FROM player AS player;
--------------------------------------
-- Except and Replace
@@ -346,6 +359,9 @@ SELECT x.b AS b FROM x AS x;
SELECT * EXCEPT (a, b) FROM x;
SELECT * EXCEPT (x.a, x.b) FROM x AS x;
+SELECT COALESCE(t1.a, '') AS a, t2.* EXCEPT (a) FROM x AS t1, x AS t2;
+SELECT COALESCE(t1.a, '') AS a, t2.b AS b FROM x AS t1, x AS t2;
+
--------------------------------------
-- Using
--------------------------------------
@@ -354,7 +370,7 @@ SELECT x.b AS b FROM x AS x JOIN y AS y ON x.b = y.b;
# execute: false
WITH cte AS (SELECT a.b.c.d.f.g FROM tbl1) SELECT g FROM (SELECT g FROM tbl2) tbl2 JOIN cte USING(g);
-WITH cte AS (SELECT tbl1.a.b.c.d.f.g AS g FROM tbl1 AS tbl1) SELECT COALESCE(tbl2.g, cte.g) AS g FROM (SELECT tbl2.g AS g FROM tbl2 AS tbl2) AS tbl2 JOIN cte ON tbl2.g = cte.g;
+WITH cte AS (SELECT tbl1.a.b.c.d.f.g AS g FROM tbl1 AS tbl1) SELECT COALESCE(tbl2.g, cte.g) AS g FROM (SELECT tbl2.g AS g FROM tbl2 AS tbl2) AS tbl2 JOIN cte AS cte ON tbl2.g = cte.g;
SELECT x.b FROM x JOIN y USING (b) JOIN z USING (b);
SELECT x.b AS b FROM x AS x JOIN y AS y ON x.b = y.b JOIN z AS z ON x.b = z.b;
@@ -408,7 +424,7 @@ SELECT a FROM t1 JOIN t2 USING(a);
SELECT COALESCE(t1.a, t2.a) AS a FROM t1 AS t1 JOIN t2 AS t2 ON t1.a = t2.a;
WITH m(a) AS (SELECT 1), n(b) AS (SELECT 1) SELECT * FROM m JOIN n AS foo(a) USING (a);
-WITH m AS (SELECT 1 AS a), n AS (SELECT 1 AS b) SELECT COALESCE(m.a, foo.a) AS a FROM m JOIN n AS foo(a) ON m.a = foo.a;
+WITH m AS (SELECT 1 AS a), n AS (SELECT 1 AS b) SELECT COALESCE(m.a, foo.a) AS a FROM m AS m JOIN n AS foo(a) ON m.a = foo.a;
--------------------------------------
-- Hint with table reference
diff --git a/tests/fixtures/optimizer/qualify_columns_ddl.sql b/tests/fixtures/optimizer/qualify_columns_ddl.sql
index 87e0f6d..907780b 100644
--- a/tests/fixtures/optimizer/qualify_columns_ddl.sql
+++ b/tests/fixtures/optimizer/qualify_columns_ddl.sql
@@ -1,6 +1,6 @@
# title: Create with CTE
WITH cte AS (SELECT b FROM y) CREATE TABLE s AS SELECT * FROM cte;
-WITH cte AS (SELECT y.b AS b FROM y AS y) CREATE TABLE s AS SELECT cte.b AS b FROM cte;
+WITH cte AS (SELECT y.b AS b FROM y AS y) CREATE TABLE s AS SELECT cte.b AS b FROM cte AS cte;
# title: Create without CTE
CREATE TABLE foo AS SELECT a FROM tbl;
@@ -8,15 +8,15 @@ CREATE TABLE foo AS SELECT tbl.a AS a FROM tbl AS tbl;
# title: Create with complex CTE with derived table
WITH cte AS (SELECT a FROM (SELECT a from x)) CREATE TABLE s AS SELECT * FROM cte;
-WITH cte AS (SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0) CREATE TABLE s AS SELECT cte.a AS a FROM cte;
+WITH cte AS (SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0) CREATE TABLE s AS SELECT cte.a AS a FROM cte AS cte;
# title: Create wtih multiple CTEs
WITH cte1 AS (SELECT b FROM y), cte2 AS (SELECT b FROM cte1) CREATE TABLE s AS SELECT * FROM cte2;
-WITH cte1 AS (SELECT y.b AS b FROM y AS y), cte2 AS (SELECT cte1.b AS b FROM cte1) CREATE TABLE s AS SELECT cte2.b AS b FROM cte2;
+WITH cte1 AS (SELECT y.b AS b FROM y AS y), cte2 AS (SELECT cte1.b AS b FROM cte1 AS cte1) CREATE TABLE s AS SELECT cte2.b AS b FROM cte2 AS cte2;
# title: Create with multiple CTEs, selecting only from the first CTE (unnecessary code)
WITH cte1 AS (SELECT b FROM y), cte2 AS (SELECT b FROM cte1) CREATE TABLE s AS SELECT * FROM cte1;
-WITH cte1 AS (SELECT y.b AS b FROM y AS y), cte2 AS (SELECT cte1.b AS b FROM cte1) CREATE TABLE s AS SELECT cte1.b AS b FROM cte1;
+WITH cte1 AS (SELECT y.b AS b FROM y AS y), cte2 AS (SELECT cte1.b AS b FROM cte1 AS cte1) CREATE TABLE s AS SELECT cte1.b AS b FROM cte1 AS cte1;
# title: Create with multiple derived tables
CREATE TABLE s AS SELECT * FROM (SELECT b FROM (SELECT b FROM y));
@@ -24,11 +24,11 @@ CREATE TABLE s AS SELECT _q_1.b AS b FROM (SELECT _q_0.b AS b FROM (SELECT y.b A
# title: Create with a CTE and a derived table
WITH cte AS (SELECT b FROM y) CREATE TABLE s AS SELECT * FROM (SELECT b FROM (SELECT b FROM cte));
-WITH cte AS (SELECT y.b AS b FROM y AS y) CREATE TABLE s AS SELECT _q_1.b AS b FROM (SELECT _q_0.b AS b FROM (SELECT cte.b AS b FROM cte) AS _q_0) AS _q_1;
+WITH cte AS (SELECT y.b AS b FROM y AS y) CREATE TABLE s AS SELECT _q_1.b AS b FROM (SELECT _q_0.b AS b FROM (SELECT cte.b AS b FROM cte AS cte) AS _q_0) AS _q_1;
# title: Insert with CTE
WITH cte AS (SELECT b FROM y) INSERT INTO s SELECT * FROM cte;
-WITH cte AS (SELECT y.b AS b FROM y AS y) INSERT INTO s SELECT cte.b AS b FROM cte;
+WITH cte AS (SELECT y.b AS b FROM y AS y) INSERT INTO s SELECT cte.b AS b FROM cte AS cte;
# title: Insert without CTE
INSERT INTO foo SELECT a FROM tbl;
diff --git a/tests/fixtures/optimizer/qualify_tables.sql b/tests/fixtures/optimizer/qualify_tables.sql
index 0ebfcd3..1426aa7 100644
--- a/tests/fixtures/optimizer/qualify_tables.sql
+++ b/tests/fixtures/optimizer/qualify_tables.sql
@@ -19,9 +19,13 @@ SELECT 1 FROM x.y.z AS z;
SELECT 1 FROM y.z AS z, z.a;
SELECT 1 FROM c.y.z AS z, z.a;
-# title: cte can't be qualified
+# title: single cte
WITH a AS (SELECT 1 FROM z) SELECT 1 FROM a;
-WITH a AS (SELECT 1 FROM c.db.z AS z) SELECT 1 FROM a;
+WITH a AS (SELECT 1 FROM c.db.z AS z) SELECT 1 FROM a AS a;
+
+# title: two ctes that are self-joined
+WITH a AS (SELECT 1 FROM z) SELECT 1 FROM a CROSS JOIN a;
+WITH a AS (SELECT 1 FROM c.db.z AS z) SELECT 1 FROM a AS a CROSS JOIN a AS a;
# title: query that yields a single column as projection
SELECT (SELECT y.c FROM y AS y) FROM x;
@@ -130,3 +134,15 @@ SELECT x FROM c.db.t AS t, LATERAL UNNEST(t.xs) AS _q_0;
# title: table with ordinality
SELECT * FROM t CROSS JOIN JSON_ARRAY_ELEMENTS(t.response) WITH ORDINALITY AS kv_json;
SELECT * FROM c.db.t AS t CROSS JOIN JSON_ARRAY_ELEMENTS(t.response) WITH ORDINALITY AS kv_json;
+
+# title: alter table
+ALTER TABLE t ADD PRIMARY KEY (id) NOT ENFORCED;
+ALTER TABLE c.db.t ADD PRIMARY KEY (id) NOT ENFORCED;
+
+# title: create statement with cte
+CREATE TABLE t1 AS (WITH cte AS (SELECT x FROM t2) SELECT * FROM cte);
+CREATE TABLE c.db.t1 AS (WITH cte AS (SELECT x FROM c.db.t2 AS t2) SELECT * FROM cte AS cte);
+
+# title: insert statement with cte
+WITH cte AS (SELECT b FROM y) INSERT INTO s SELECT * FROM cte;
+WITH cte AS (SELECT b FROM c.db.y AS y) INSERT INTO c.db.s SELECT * FROM cte AS cte;
diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
index d38c3cc..5ea51e0 100644
--- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
+++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
@@ -191,7 +191,7 @@ WITH "wscs" AS (
ELSE NULL
END
) AS "sat_sales"
- FROM "wscs"
+ FROM "wscs" AS "wscs"
JOIN "date_dim" AS "date_dim"
ON "date_dim"."d_date_sk" = "wscs"."sold_date_sk"
GROUP BY
@@ -206,7 +206,7 @@ SELECT
ROUND("wswscs"."thu_sales" / "wswscs_2"."thu_sales", 2) AS "_col_5",
ROUND("wswscs"."fri_sales" / "wswscs_2"."fri_sales", 2) AS "_col_6",
ROUND("wswscs"."sat_sales" / "wswscs_2"."sat_sales", 2) AS "_col_7"
-FROM "wswscs"
+FROM "wswscs" AS "wswscs"
JOIN "date_dim" AS "date_dim"
ON "date_dim"."d_week_seq" = "wswscs"."d_week_seq" AND "date_dim"."d_year" = 1998
JOIN "wswscs" AS "wswscs_2"
@@ -821,7 +821,7 @@ WITH "salesreturns" AS (
"ssr"."sales" AS "sales",
"ssr"."returns1" AS "returns1",
"ssr"."profit" - "ssr"."profit_loss" AS "profit"
- FROM "ssr"
+ FROM "ssr" AS "ssr"
UNION ALL
SELECT
'catalog channel' AS "channel",
@@ -829,7 +829,7 @@ WITH "salesreturns" AS (
"csr"."sales" AS "sales",
"csr"."returns1" AS "returns1",
"csr"."profit" - "csr"."profit_loss" AS "profit"
- FROM "csr"
+ FROM "csr" AS "csr"
UNION ALL
SELECT
'web channel' AS "channel",
@@ -837,7 +837,7 @@ WITH "salesreturns" AS (
"wsr"."sales" AS "sales",
"wsr"."returns1" AS "returns1",
"wsr"."profit" - "wsr"."profit_loss" AS "profit"
- FROM "wsr"
+ FROM "wsr" AS "wsr"
)
SELECT
"x"."channel" AS "channel",
@@ -2203,7 +2203,7 @@ WITH "item_2" AS (
), "_u_1" AS (
SELECT
"avg_sales"."average_sales" AS "average_sales"
- FROM "avg_sales"
+ FROM "avg_sales" AS "avg_sales"
), "_u_0" AS (
SELECT
"item"."i_item_sk" AS "ss_item_sk"
@@ -2996,7 +2996,7 @@ WITH "frequent_ss_items" AS (
SELECT
"customer"."c_customer_sk" AS "c_customer_sk"
FROM "store_sales" AS "store_sales"
- CROSS JOIN "max_store_sales"
+ CROSS JOIN "max_store_sales" AS "max_store_sales"
JOIN "customer_2" AS "customer"
ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk"
GROUP BY
@@ -3006,13 +3006,13 @@ WITH "frequent_ss_items" AS (
), "_u_1" AS (
SELECT
"frequent_ss_items"."item_sk" AS "item_sk"
- FROM "frequent_ss_items"
+ FROM "frequent_ss_items" AS "frequent_ss_items"
GROUP BY
"frequent_ss_items"."item_sk"
), "_u_2" AS (
SELECT
"best_ss_customer"."c_customer_sk" AS "c_customer_sk"
- FROM "best_ss_customer"
+ FROM "best_ss_customer" AS "best_ss_customer"
GROUP BY
"best_ss_customer"."c_customer_sk"
), "date_dim_4" AS (
@@ -3137,14 +3137,14 @@ WITH "ssales" AS (
), "_u_0" AS (
SELECT
0.05 * AVG("ssales"."netpaid") AS "_col_0"
- FROM "ssales"
+ FROM "ssales" AS "ssales"
)
SELECT
"ssales"."c_last_name" AS "c_last_name",
"ssales"."c_first_name" AS "c_first_name",
"ssales"."s_store_name" AS "s_store_name",
SUM("ssales"."netpaid") AS "paid"
-FROM "ssales"
+FROM "ssales" AS "ssales"
CROSS JOIN "_u_0" AS "_u_0"
WHERE
"ssales"."i_color" = 'papaya'
@@ -4095,17 +4095,17 @@ WITH "customer_address_2" AS (
SELECT
"ss"."i_manufact_id" AS "i_manufact_id",
"ss"."total_sales" AS "total_sales"
- FROM "ss"
+ FROM "ss" AS "ss"
UNION ALL
SELECT
"cs"."i_manufact_id" AS "i_manufact_id",
"cs"."total_sales" AS "total_sales"
- FROM "cs"
+ FROM "cs" AS "cs"
UNION ALL
SELECT
"ws"."i_manufact_id" AS "i_manufact_id",
"ws"."total_sales" AS "total_sales"
- FROM "ws"
+ FROM "ws" AS "ws"
)
SELECT
"tmp1"."i_manufact_id" AS "i_manufact_id",
@@ -5534,7 +5534,7 @@ SELECT
"v1"."sum_sales" AS "sum_sales",
"v1_lag"."sum_sales" AS "psum",
"v1_lead"."sum_sales" AS "nsum"
-FROM "v1"
+FROM "v1" AS "v1"
JOIN "v1" AS "v1_lag"
ON "v1"."i_brand" = "v1_lag"."i_brand"
AND "v1"."i_category" = "v1_lag"."i_category"
@@ -6455,7 +6455,7 @@ WITH "cs_or_ws_sales" AS (
), "my_revenue" AS (
SELECT
SUM("store_sales"."ss_ext_sales_price") AS "revenue"
- FROM "my_customers"
+ FROM "my_customers" AS "my_customers"
JOIN "customer_address" AS "customer_address"
ON "customer_address"."ca_address_sk" = "my_customers"."c_current_addr_sk"
JOIN "store_sales" AS "store_sales"
@@ -6480,7 +6480,7 @@ SELECT
CAST((
"my_revenue"."revenue" / 50
) AS INT) * 50 AS "segment_base"
-FROM "my_revenue"
+FROM "my_revenue" AS "my_revenue"
GROUP BY
CAST((
"my_revenue"."revenue" / 50
@@ -6681,17 +6681,17 @@ WITH "customer_address_2" AS (
SELECT
"ss"."i_item_id" AS "i_item_id",
"ss"."total_sales" AS "total_sales"
- FROM "ss"
+ FROM "ss" AS "ss"
UNION ALL
SELECT
"cs"."i_item_id" AS "i_item_id",
"cs"."total_sales" AS "total_sales"
- FROM "cs"
+ FROM "cs" AS "cs"
UNION ALL
SELECT
"ws"."i_item_id" AS "i_item_id",
"ws"."total_sales" AS "total_sales"
- FROM "ws"
+ FROM "ws" AS "ws"
)
SELECT
"tmp1"."i_item_id" AS "i_item_id",
@@ -6813,7 +6813,7 @@ SELECT
"v1"."sum_sales" AS "sum_sales",
"v1_lag"."sum_sales" AS "psum",
"v1_lead"."sum_sales" AS "nsum"
-FROM "v1"
+FROM "v1" AS "v1"
JOIN "v1" AS "v1_lag"
ON "v1"."cc_name" = "v1_lag"."cc_name"
AND "v1"."i_brand" = "v1_lag"."i_brand"
@@ -7014,14 +7014,14 @@ SELECT
(
"ss_items"."ss_item_rev" + "cs_items"."cs_item_rev" + "ws_items"."ws_item_rev"
) / 3 AS "average"
-FROM "ss_items"
-JOIN "cs_items"
+FROM "ss_items" AS "ss_items"
+JOIN "cs_items" AS "cs_items"
ON "cs_items"."cs_item_rev" <= 1.1 * "ss_items"."ss_item_rev"
AND "cs_items"."cs_item_rev" >= 0.9 * "ss_items"."ss_item_rev"
AND "cs_items"."item_id" = "ss_items"."item_id"
AND "ss_items"."ss_item_rev" <= 1.1 * "cs_items"."cs_item_rev"
AND "ss_items"."ss_item_rev" >= 0.9 * "cs_items"."cs_item_rev"
-JOIN "ws_items"
+JOIN "ws_items" AS "ws_items"
ON "cs_items"."cs_item_rev" <= 1.1 * "ws_items"."ws_item_rev"
AND "cs_items"."cs_item_rev" >= 0.9 * "ws_items"."ws_item_rev"
AND "ss_items"."item_id" = "ws_items"."item_id"
@@ -7193,7 +7193,7 @@ WITH "wss" AS (
"wss"."thu_sales" AS "thu_sales2",
"wss"."fri_sales" AS "fri_sales2",
"wss"."sat_sales" AS "sat_sales2"
- FROM "wss"
+ FROM "wss" AS "wss"
JOIN "date_dim" AS "d"
ON "d"."d_month_seq" <= 1219
AND "d"."d_month_seq" >= 1208
@@ -7212,7 +7212,7 @@ SELECT
"wss"."thu_sales" / "x"."thu_sales2" AS "_col_7",
"wss"."fri_sales" / "x"."fri_sales2" AS "_col_8",
"wss"."sat_sales" / "x"."sat_sales2" AS "_col_9"
-FROM "wss"
+FROM "wss" AS "wss"
JOIN "date_dim" AS "d"
ON "d"."d_month_seq" <= 1207
AND "d"."d_month_seq" >= 1196
@@ -7379,17 +7379,17 @@ WITH "customer_address_2" AS (
SELECT
"ss"."i_item_id" AS "i_item_id",
"ss"."total_sales" AS "total_sales"
- FROM "ss"
+ FROM "ss" AS "ss"
UNION ALL
SELECT
"cs"."i_item_id" AS "i_item_id",
"cs"."total_sales" AS "total_sales"
- FROM "cs"
+ FROM "cs" AS "cs"
UNION ALL
SELECT
"ws"."i_item_id" AS "i_item_id",
"ws"."total_sales" AS "total_sales"
- FROM "ws"
+ FROM "ws" AS "ws"
)
SELECT
"tmp1"."i_item_id" AS "i_item_id",
@@ -7921,7 +7921,7 @@ WITH "cs_ui" AS (
ON "ad1"."ca_address_sk" = "store_sales"."ss_addr_sk"
JOIN "customer_demographics" AS "cd1"
ON "cd1"."cd_demo_sk" = "store_sales"."ss_cdemo_sk"
- JOIN "cs_ui"
+ JOIN "cs_ui" AS "cs_ui"
ON "cs_ui"."cs_item_sk" = "store_sales"."ss_item_sk"
JOIN "customer" AS "customer"
ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk"
@@ -10343,8 +10343,8 @@ WITH "date_dim_2" AS (
"ss"."sales" AS "sales",
COALESCE("sr"."returns1", 0) AS "returns1",
"ss"."profit" - COALESCE("sr"."profit_loss", 0) AS "profit"
- FROM "ss"
- LEFT JOIN "sr"
+ FROM "ss" AS "ss"
+ LEFT JOIN "sr" AS "sr"
ON "sr"."s_store_sk" = "ss"."s_store_sk"
UNION ALL
SELECT
@@ -10353,8 +10353,8 @@ WITH "date_dim_2" AS (
"cs"."sales" AS "sales",
"cr"."returns1" AS "returns1",
"cs"."profit" - "cr"."profit_loss" AS "profit"
- FROM "cs"
- CROSS JOIN "cr"
+ FROM "cs" AS "cs"
+ CROSS JOIN "cr" AS "cr"
UNION ALL
SELECT
'web channel' AS "channel",
@@ -10362,8 +10362,8 @@ WITH "date_dim_2" AS (
"ws"."sales" AS "sales",
COALESCE("wr"."returns1", 0) AS "returns1",
"ws"."profit" - COALESCE("wr"."profit_loss", 0) AS "profit"
- FROM "ws"
- LEFT JOIN "wr"
+ FROM "ws" AS "ws"
+ LEFT JOIN "wr" AS "wr"
ON "wr"."wp_web_page_sk" = "ws"."wp_web_page_sk"
)
SELECT
@@ -10546,12 +10546,12 @@ SELECT
COALESCE("ws"."ws_qty", 0) + COALESCE("cs"."cs_qty", 0) AS "other_chan_qty",
COALESCE("ws"."ws_wc", 0) + COALESCE("cs"."cs_wc", 0) AS "other_chan_wholesale_cost",
COALESCE("ws"."ws_sp", 0) + COALESCE("cs"."cs_sp", 0) AS "other_chan_sales_price"
-FROM "ss"
-LEFT JOIN "cs"
+FROM "ss" AS "ss"
+LEFT JOIN "cs" AS "cs"
ON "cs"."cs_customer_sk" = "ss"."ss_customer_sk"
AND "cs"."cs_item_sk" = "cs"."cs_item_sk"
AND "cs"."cs_sold_year" = "ss"."ss_sold_year"
-LEFT JOIN "ws"
+LEFT JOIN "ws" AS "ws"
ON "ss"."ss_customer_sk" = "ws"."ws_customer_sk"
AND "ss"."ss_item_sk" = "ws"."ws_item_sk"
AND "ss"."ss_sold_year" = "ws"."ws_sold_year"
@@ -10850,7 +10850,7 @@ WITH "date_dim_2" AS (
"ssr"."sales" AS "sales",
"ssr"."returns1" AS "returns1",
"ssr"."profit" AS "profit"
- FROM "ssr"
+ FROM "ssr" AS "ssr"
UNION ALL
SELECT
'catalog channel' AS "channel",
@@ -10858,7 +10858,7 @@ WITH "date_dim_2" AS (
"csr"."sales" AS "sales",
"csr"."returns1" AS "returns1",
"csr"."profit" AS "profit"
- FROM "csr"
+ FROM "csr" AS "csr"
UNION ALL
SELECT
'web channel' AS "channel",
@@ -10866,7 +10866,7 @@ WITH "date_dim_2" AS (
"wsr"."sales" AS "sales",
"wsr"."returns1" AS "returns1",
"wsr"."profit" AS "profit"
- FROM "wsr"
+ FROM "wsr" AS "wsr"
)
SELECT
"x"."channel" AS "channel",
@@ -11249,10 +11249,10 @@ SELECT
(
"sr_items"."sr_item_qty" + "cr_items"."cr_item_qty" + "wr_items"."wr_item_qty"
) / 3.0 AS "average"
-FROM "sr_items"
-JOIN "cr_items"
+FROM "sr_items" AS "sr_items"
+JOIN "cr_items" AS "cr_items"
ON "cr_items"."item_id" = "sr_items"."item_id"
-JOIN "wr_items"
+JOIN "wr_items" AS "wr_items"
ON "sr_items"."item_id" = "wr_items"."item_id"
ORDER BY
"sr_items"."item_id",
@@ -12312,14 +12312,14 @@ WITH "ws_wh" AS (
), "_u_0" AS (
SELECT
"ws_wh"."ws_order_number" AS "ws_order_number"
- FROM "ws_wh"
+ FROM "ws_wh" AS "ws_wh"
GROUP BY
"ws_wh"."ws_order_number"
), "_u_1" AS (
SELECT
"web_returns"."wr_order_number" AS "wr_order_number"
FROM "web_returns" AS "web_returns"
- JOIN "ws_wh"
+ JOIN "ws_wh" AS "ws_wh"
ON "web_returns"."wr_order_number" = "ws_wh"."ws_order_number"
GROUP BY
"web_returns"."wr_order_number"
@@ -12475,8 +12475,8 @@ SELECT
ELSE 0
END
) AS "store_and_catalog"
-FROM "ssci"
-FULL JOIN "csci"
+FROM "ssci" AS "ssci"
+FULL JOIN "csci" AS "csci"
ON "csci"."customer_sk" = "ssci"."customer_sk" AND "csci"."item_sk" = "ssci"."item_sk"
LIMIT 100;
diff --git a/tests/fixtures/optimizer/tpc-h/tpc-h.sql b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
index 36f096c..bf624da 100644
--- a/tests/fixtures/optimizer/tpc-h/tpc-h.sql
+++ b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
@@ -882,7 +882,7 @@ WITH "revenue" AS (
), "_u_0" AS (
SELECT
MAX("revenue"."total_revenue") AS "_col_0"
- FROM "revenue"
+ FROM "revenue" AS "revenue"
)
SELECT
"supplier"."s_suppkey" AS "s_suppkey",
@@ -891,7 +891,7 @@ SELECT
"supplier"."s_phone" AS "s_phone",
"revenue"."total_revenue" AS "total_revenue"
FROM "supplier" AS "supplier"
-JOIN "revenue"
+JOIN "revenue" AS "revenue"
ON "revenue"."supplier_no" = "supplier"."s_suppkey"
JOIN "_u_0" AS "_u_0"
ON "_u_0"."_col_0" = "revenue"."total_revenue"
diff --git a/tests/fixtures/optimizer/unnest_subqueries.sql b/tests/fixtures/optimizer/unnest_subqueries.sql
index f83191d..3d1e7ec 100644
--- a/tests/fixtures/optimizer/unnest_subqueries.sql
+++ b/tests/fixtures/optimizer/unnest_subqueries.sql
@@ -41,7 +41,7 @@ LEFT JOIN (
GROUP BY
y.a
) AS _u_1
- ON x.a = "_u_1"."a"
+ ON x.a = _u_1.a
LEFT JOIN (
SELECT
y.b AS b
@@ -49,7 +49,7 @@ LEFT JOIN (
GROUP BY
y.b
) AS _u_2
- ON x.a = "_u_2"."b"
+ ON x.a = _u_2.b
LEFT JOIN (
SELECT
y.a AS a
@@ -57,7 +57,7 @@ LEFT JOIN (
GROUP BY
y.a
) AS _u_3
- ON x.a = "_u_3"."a"
+ ON x.a = _u_3.a
LEFT JOIN (
SELECT
SUM(y.b) AS b,
@@ -157,9 +157,9 @@ LEFT JOIN (
ON _u_21._u_22 = x.a
WHERE
x.a = _u_0.a
- AND NOT "_u_1"."a" IS NULL
- AND NOT "_u_2"."b" IS NULL
- AND NOT "_u_3"."a" IS NULL
+ AND NOT _u_1.a IS NULL
+ AND NOT _u_2.b IS NULL
+ AND NOT _u_3.a IS NULL
AND x.a = _u_4.b
AND x.a > _u_6.b
AND x.a = _u_8.a
generated by cgit v1.2.3 (git 2.39.1) at 2024-05-23 16:08:47 +0000