Merging upstream version 20.11.0.

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

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