Merging upstream version 15.0.0.

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

21 files changed, 559 insertions, 395 deletions

diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql
index ea695c9..0a1e305 100644
--- a/tests/fixtures/identity.sql
+++ b/tests/fixtures/identity.sql
@@ -174,6 +174,8 @@ SET variable = value
 SET GLOBAL variable = value
 SET LOCAL variable = value
 SET @user OFF
+@x
+@"x"
 COMMIT
 USE db
 USE role x
@@ -183,6 +185,7 @@ USE schema x.y
 NOT 1
 NOT NOT 1
 SELECT * FROM test
+SELECT * FROM db.FOO()
 SELECT *, 1 FROM test
 SELECT * FROM a.b
 SELECT * FROM a.b.c
@@ -288,10 +291,6 @@ SELECT a FROM test ORDER BY a, b
 SELECT x FROM tests ORDER BY a DESC, b DESC, c
 SELECT a FROM test ORDER BY a > 1
 SELECT * FROM test ORDER BY DATE DESC, TIMESTAMP DESC
-SELECT * FROM test DISTRIBUTE BY y SORT BY x DESC ORDER BY l
-SELECT * FROM test CLUSTER BY y
-SELECT * FROM test CLUSTER BY y
-SELECT * FROM test WHERE RAND() <= 0.1 DISTRIBUTE BY RAND() SORT BY RAND()
 SELECT a, b FROM test GROUP BY 1
 SELECT a, b FROM test GROUP BY a
 SELECT a, b FROM test WHERE a = 1 GROUP BY a HAVING a = 2
@@ -414,7 +413,7 @@ SELECT 1 AS delete, 2 AS alter
 SELECT * FROM (x)
 SELECT * FROM ((x))
 SELECT * FROM ((SELECT 1))
-SELECT * FROM (x LATERAL VIEW EXPLODE(y) JOIN foo)
+SELECT * FROM (x CROSS JOIN foo LATERAL VIEW EXPLODE(y))
 SELECT * FROM (SELECT 1) AS x
 SELECT * FROM (SELECT 1 UNION SELECT 2) AS x
 SELECT * FROM (SELECT 1 UNION ALL SELECT 2) AS x
@@ -447,10 +446,6 @@ SELECT 1 UNION (SELECT 2)
 (SELECT 1) ORDER BY x LIMIT 1 OFFSET 1
 (SELECT 1 UNION SELECT 2) UNION (SELECT 2 UNION ALL SELECT 3)
 (SELECT 1 UNION SELECT 2) ORDER BY x LIMIT 1 OFFSET 1
-(SELECT 1 UNION SELECT 2) CLUSTER BY y DESC
-(SELECT 1 UNION SELECT 2) SORT BY z
-(SELECT 1 UNION SELECT 2) DISTRIBUTE BY z
-(SELECT 1 UNION SELECT 2) DISTRIBUTE BY z SORT BY x
 SELECT 1 UNION (SELECT 2) ORDER BY x
 (SELECT 1) UNION SELECT 2 ORDER BY x
 SELECT * FROM (((SELECT 1) UNION SELECT 2) ORDER BY x LIMIT 1 OFFSET 1)
@@ -563,6 +558,7 @@ CREATE TABLE foo (bar INT REFERENCES baz(baz_id) ON UPDATE SET DEFAULT)
 CREATE TABLE asd AS SELECT asd FROM asd WITH NO DATA
 CREATE TABLE asd AS SELECT asd FROM asd WITH DATA
 CREATE TABLE products (x INT GENERATED BY DEFAULT AS IDENTITY)
+CREATE TABLE products (x INT GENERATED BY DEFAULT ON NULL AS IDENTITY)
 CREATE TABLE products (x INT GENERATED ALWAYS AS IDENTITY)
 CREATE TABLE konyvszerzo (szerzo_azon INT CONSTRAINT konyvszerzo_szerzo_fk REFERENCES szerzo)
 CREATE TABLE IF NOT EXISTS customer (pk BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (INCREMENT BY 1))
@@ -610,6 +606,7 @@ CREATE FUNCTION a.b(x INT) RETURNS INT AS RETURN x + 1
 CREATE FUNCTION a.b.c()
 CREATE INDEX abc ON t (a)
 CREATE INDEX abc ON t (a, b, b)
+CREATE INDEX abc ON t (a NULLS LAST)
 CREATE UNIQUE INDEX abc ON t (a, b, b)
 CREATE UNIQUE INDEX IF NOT EXISTS my_idx ON tbl (a, b)
 CREATE SCHEMA x
@@ -685,6 +682,7 @@ INSERT OVERWRITE TABLE a.b IF EXISTS SELECT * FROM y
 INSERT OVERWRITE DIRECTORY 'x' SELECT 1
 INSERT OVERWRITE LOCAL DIRECTORY 'x' SELECT 1
 INSERT OVERWRITE LOCAL DIRECTORY 'x' ROW FORMAT DELIMITED FIELDS TERMINATED BY '1' COLLECTION ITEMS TERMINATED BY '2' MAP KEYS TERMINATED BY '3' LINES TERMINATED BY '4' NULL DEFINED AS '5' SELECT 1
+LOAD foo
 LOAD DATA INPATH 'x' INTO TABLE y PARTITION(ds = 'yyyy')
 LOAD DATA LOCAL INPATH 'x' INTO TABLE y PARTITION(ds = 'yyyy')
 LOAD DATA LOCAL INPATH 'x' INTO TABLE y PARTITION(ds = 'yyyy') INPUTFORMAT 'y'
@@ -721,9 +719,9 @@ SELECT ((SELECT 1) + 1)
 SELECT * FROM project.dataset.INFORMATION_SCHEMA.TABLES
 SELECT * FROM (table1 AS t1 LEFT JOIN table2 AS t2 ON 1 = 1)
 SELECT * FROM (tbl1 LEFT JOIN tbl2 ON 1 = 1)
-SELECT * FROM (tbl1 JOIN tbl2 JOIN tbl3)
-SELECT * FROM (tbl1 JOIN (tbl2 JOIN tbl3) ON bla = foo)
-SELECT * FROM (tbl1 JOIN LATERAL (SELECT * FROM bla) AS tbl)
+SELECT * FROM (tbl1, tbl2 JOIN tbl3 ON TRUE)
+SELECT * FROM (tbl1 JOIN (tbl2 CROSS JOIN tbl3) ON bla = foo)
+SELECT * FROM (tbl1, LATERAL (SELECT * FROM bla) AS tbl)
 SELECT CAST(x AS INT) /* comment */ FROM foo
 SELECT a /* x */, b /* x */
 SELECT a /* x */ /* y */ /* z */, b /* k */ /* m */
@@ -826,3 +824,5 @@ SELECT NEXT VALUE FOR db.schema.sequence_name OVER (ORDER BY foo), col
 SELECT PERCENTILE_CONT(x, 0.5) OVER ()
 SELECT PERCENTILE_CONT(x, 0.5 RESPECT NULLS) OVER ()
 SELECT PERCENTILE_CONT(x, 0.5 IGNORE NULLS) OVER ()
+WITH my_cte AS (SELECT 'a' AS desc) SELECT desc AS description FROM my_cte
+WITH my_cte AS (SELECT 'a' AS asc) SELECT asc AS description FROM my_cte
diff --git a/tests/fixtures/optimizer/canonicalize.sql b/tests/fixtures/optimizer/canonicalize.sql
index ccf2f16..1fc44ef 100644
--- a/tests/fixtures/optimizer/canonicalize.sql
+++ b/tests/fixtures/optimizer/canonicalize.sql
@@ -10,8 +10,8 @@ SELECT CAST(1 AS VARCHAR) AS "a" FROM "w" AS "w";
 SELECT CAST(1 + 3.2 AS DOUBLE) AS a FROM w AS w;
 SELECT 1 + 3.2 AS "a" FROM "w" AS "w";
 
-SELECT CAST("2022-01-01" AS DATE) + INTERVAL '1' day;
-SELECT CAST("2022-01-01" AS DATE) + INTERVAL '1' day AS "_col_0";
+SELECT CAST('2022-01-01' AS DATE) + INTERVAL '1' day;
+SELECT CAST('2022-01-01' AS DATE) + INTERVAL '1' day AS "_col_0";
 
 --------------------------------------
 -- Ensure boolean predicates
diff --git a/tests/fixtures/optimizer/eliminate_subqueries.sql b/tests/fixtures/optimizer/eliminate_subqueries.sql
index 4fa63dd..4c79c22 100644
--- a/tests/fixtures/optimizer/eliminate_subqueries.sql
+++ b/tests/fixtures/optimizer/eliminate_subqueries.sql
@@ -35,8 +35,8 @@ SELECT * FROM (SELECT * FROM (SELECT a FROM x) AS x) AS y JOIN (SELECT * FROM x)
 WITH x_2 AS (SELECT a FROM x), y AS (SELECT * FROM x_2 AS x), z AS (SELECT * FROM x) SELECT * FROM y AS y JOIN z AS z ON x.a = y.a;
 
 -- Name conflicts with table alias
-SELECT a FROM (SELECT a FROM (SELECT a FROM x) AS y) AS z JOIN q AS y;
-WITH y AS (SELECT a FROM x), z AS (SELECT a FROM y AS y) SELECT a FROM z AS z JOIN q AS y;
+SELECT a FROM (SELECT a FROM (SELECT a FROM x) AS y) AS z CROSS JOIN q AS y;
+WITH y AS (SELECT a FROM x), z AS (SELECT a FROM y AS y) SELECT a FROM z AS z CROSS JOIN q AS y;
 
 -- Name conflicts with existing CTE
 WITH y AS (SELECT a FROM (SELECT a FROM x) AS y) SELECT a FROM y;
@@ -63,12 +63,12 @@ SELECT a FROM x WHERE b = (SELECT c FROM y WHERE y.a = x.a);
 SELECT a FROM x WHERE b = (SELECT c FROM y WHERE y.a = x.a);
 
 -- Duplicate CTE
-SELECT a FROM (SELECT b FROM x) AS y JOIN (SELECT b FROM x) AS z;
-WITH y AS (SELECT b FROM x) SELECT a FROM y AS y JOIN y AS z;
+SELECT a FROM (SELECT b FROM x) AS y CROSS JOIN (SELECT b FROM x) AS z;
+WITH y AS (SELECT b FROM x) SELECT a FROM y AS y CROSS JOIN y AS z;
 
 -- Doubly duplicate CTE
 SELECT * FROM (SELECT * FROM x JOIN (SELECT * FROM x) AS y) AS z JOIN (SELECT * FROM x JOIN (SELECT * FROM x) AS y) AS q;
-WITH y AS (SELECT * FROM x), z AS (SELECT * FROM x JOIN y AS y) SELECT * FROM z AS z JOIN z AS q;
+WITH y AS (SELECT * FROM x), z AS (SELECT * FROM x, y AS y) SELECT * FROM z AS z, z AS q;
 
 -- Another duplicate...
 SELECT x.id FROM (SELECT * FROM x AS x JOIN y AS y ON x.id = y.id) AS x JOIN (SELECT * FROM x AS x JOIN y AS y ON x.id = y.id) AS y ON x.id = y.id;
@@ -79,8 +79,8 @@ WITH x_2 AS (SELECT * FROM x AS x JOIN y AS y ON x.id = y.id) SELECT x.id FROM x
 (WITH cte AS (SELECT * FROM x) SELECT * FROM cte AS cte) LIMIT 1;
 
 -- Existing duplicate CTE
-WITH y AS (SELECT a FROM x) SELECT a FROM (SELECT a FROM x) AS y JOIN y AS z;
-WITH y AS (SELECT a FROM x) SELECT a FROM y AS y JOIN y AS z;
+WITH y AS (SELECT a FROM x) SELECT a FROM (SELECT a FROM x) AS y CROSS JOIN y AS z;
+WITH y AS (SELECT a FROM x) SELECT a FROM y AS y CROSS JOIN y AS z;
 
 -- Nested CTE
 WITH cte1 AS (SELECT a FROM x) SELECT a FROM (WITH cte2 AS (SELECT a FROM cte1) SELECT a FROM cte2);
diff --git a/tests/fixtures/optimizer/expand_laterals.sql b/tests/fixtures/optimizer/expand_laterals.sql
deleted file mode 100644
index 09bbd0f..0000000
--- a/tests/fixtures/optimizer/expand_laterals.sql
+++ /dev/null
@@ -1,40 +0,0 @@
-# title: expand alias reference
-SELECT
-  x.a + 1 AS i,
-  i + 1 AS j,
-  j + 1 AS k
-FROM x;
-SELECT
-  x.a + 1 AS i,
-  x.a + 1 + 1 AS j,
-  x.a + 1 + 1 + 1 AS k
-FROM x;
-
-# title: noop - reference comes before alias
-SELECT
-  b + 1 AS j,
-  x.a + 1 AS i
-FROM x;
-SELECT
-  b + 1 AS j,
-  x.a + 1 AS i
-FROM x;
-
-
-# title: subquery
-SELECT
-  *
-FROM (
-  SELECT
-    x.a + 1 AS i,
-    i + 1 AS j
-  FROM x
-);
-SELECT
-  *
-FROM (
-  SELECT
-    x.a + 1 AS i,
-    x.a + 1 + 1 AS j
-  FROM x
-);
diff --git a/tests/fixtures/optimizer/expand_multi_table_selects.sql b/tests/fixtures/optimizer/expand_multi_table_selects.sql
deleted file mode 100644
index a5a4664..0000000
--- a/tests/fixtures/optimizer/expand_multi_table_selects.sql
+++ /dev/null
@@ -1,11 +0,0 @@
---------------------------------------
--- Multi Table Selects
---------------------------------------
-SELECT * FROM x AS x, y AS y WHERE x.a = y.a;
-SELECT * FROM x AS x CROSS JOIN y AS y WHERE x.a = y.a;
-
-SELECT * FROM x AS x, y AS y WHERE x.a = y.a AND x.a = 1 and y.b = 1;
-SELECT * FROM x AS x CROSS JOIN y AS y WHERE x.a = y.a AND x.a = 1 AND y.b = 1;
-
-SELECT * FROM x AS x, y AS y WHERE x.a > y.a;
-SELECT * FROM x AS x CROSS JOIN y AS y WHERE x.a > y.a;
diff --git a/tests/fixtures/optimizer/isolate_table_selects.sql b/tests/fixtures/optimizer/isolate_table_selects.sql
index 93c0f7c..43540e8 100644
--- a/tests/fixtures/optimizer/isolate_table_selects.sql
+++ b/tests/fixtures/optimizer/isolate_table_selects.sql
@@ -4,20 +4,20 @@ SELECT * FROM (SELECT * FROM x AS x) AS x, (SELECT * FROM y AS y) AS y2;
 SELECT * FROM x AS x WHERE x = 1;
 SELECT * FROM x AS x WHERE x = 1;
 
-SELECT * FROM x AS x JOIN y AS y;
-SELECT * FROM (SELECT * FROM x AS x) AS x JOIN (SELECT * FROM y AS y) AS y;
+SELECT * FROM x AS x CROSS JOIN y AS y;
+SELECT * FROM (SELECT * FROM x AS x) AS x CROSS JOIN (SELECT * FROM y AS y) AS y;
 
-SELECT * FROM (SELECT 1) AS x JOIN y AS y;
-SELECT * FROM (SELECT 1) AS x JOIN (SELECT * FROM y AS y) AS y;
+SELECT * FROM (SELECT 1) AS x CROSS JOIN y AS y;
+SELECT * FROM (SELECT 1) AS x CROSS JOIN (SELECT * FROM y AS y) AS y;
 
 SELECT * FROM x AS x JOIN (SELECT * FROM y) AS y;
-SELECT * FROM (SELECT * FROM x AS x) AS x JOIN (SELECT * FROM y) AS y;
+SELECT * FROM (SELECT * FROM x AS x) AS x, (SELECT * FROM y) AS y;
 
 WITH y AS (SELECT *) SELECT * FROM x AS x;
 WITH y AS (SELECT *) SELECT * FROM x AS x;
 
-WITH y AS (SELECT * FROM y AS y2 JOIN x AS z2) SELECT * FROM x AS x JOIN y as y;
-WITH y AS (SELECT * FROM (SELECT * FROM y AS y) AS y2 JOIN (SELECT * FROM x AS x) AS z2) SELECT * FROM (SELECT * FROM x AS x) AS x JOIN y AS y;
+WITH y AS (SELECT * FROM y AS y2 CROSS JOIN x AS z2) SELECT * FROM x AS x CROSS JOIN y as y;
+WITH y AS (SELECT * FROM (SELECT * FROM y AS y) AS y2 CROSS JOIN (SELECT * FROM x AS x) AS z2) SELECT * FROM (SELECT * FROM x AS x) AS x CROSS JOIN y AS y;
 
-SELECT * FROM x AS x JOIN xx AS y;
-SELECT * FROM (SELECT * FROM x AS x) AS x JOIN xx AS y;
+SELECT * FROM x AS x CROSS JOIN xx AS y;
+SELECT * FROM (SELECT * FROM x AS x) AS x CROSS JOIN xx AS y;
diff --git a/tests/fixtures/optimizer/merge_subqueries.sql b/tests/fixtures/optimizer/merge_subqueries.sql
index 4c06e42..1124a79 100644
--- a/tests/fixtures/optimizer/merge_subqueries.sql
+++ b/tests/fixtures/optimizer/merge_subqueries.sql
@@ -48,8 +48,8 @@ SELECT r.b FROM (SELECT b FROM x AS x) AS q JOIN (SELECT b FROM x) AS r ON q.b =
 SELECT x_2.b AS b FROM x AS x JOIN x AS x_2 ON x.b = x_2.b;
 
 # title: WHERE clause in joined derived table is merged to ON clause
-SELECT x.a, y.c FROM x JOIN (SELECT b, c FROM y WHERE c > 1) AS y ON x.b = y.b;
-SELECT x.a AS a, y.c AS c FROM x AS x JOIN y AS y ON x.b = y.b AND y.c > 1;
+SELECT x.a, y.c FROM x JOIN (SELECT b, c FROM y WHERE c > 1) AS y ON x.b = y.b ORDER BY x.a;
+SELECT x.a AS a, y.c AS c FROM x AS x JOIN y AS y ON x.b = y.b AND y.c > 1 ORDER BY x.a;
 
 # title: Comma JOIN in outer query
 SELECT x.a, y.c FROM (SELECT a FROM x) AS x, (SELECT c FROM y) AS y;
@@ -57,7 +57,7 @@ SELECT x.a AS a, y.c AS c FROM x AS x, y AS y;
 
 # title: Comma JOIN in inner query
 SELECT x.a, x.c FROM (SELECT x.a, z.c FROM x, y AS z) AS x;
-SELECT x.a AS a, z.c AS c FROM x AS x CROSS JOIN y AS z;
+SELECT x.a AS a, z.c AS c FROM x AS x, y AS z;
 
 # title: (Regression) Column in ORDER BY
 SELECT * FROM (SELECT * FROM (SELECT * FROM x)) ORDER BY a LIMIT 1;
diff --git a/tests/fixtures/optimizer/normalize.sql b/tests/fixtures/optimizer/normalize.sql
index 803a474..12bc388 100644
--- a/tests/fixtures/optimizer/normalize.sql
+++ b/tests/fixtures/optimizer/normalize.sql
@@ -42,3 +42,6 @@ A OR ((((B OR C) AND (B OR D)) OR C) AND (((B OR C) AND (B OR D)) OR D));
 
 SELECT * FROM x WHERE (A AND B) OR C;
 SELECT * FROM x WHERE (A OR C) AND (B OR C);
+
+dt2 between '2022-01-01 12:00:00' and '2022-12-31' and dt2 >= '2022-05-01 12:00:00' or dt2 = '2021-06-01 12:00:00';
+(dt2 <= '2022-12-31' OR dt2 = '2021-06-01 12:00:00') AND (dt2 = '2021-06-01 12:00:00' OR dt2 >= '2022-01-01 12:00:00') AND (dt2 = '2021-06-01 12:00:00' OR dt2 >= '2022-05-01 12:00:00')
diff --git a/tests/fixtures/optimizer/lower_identities.sql b/tests/fixtures/optimizer/normalize_identifiers.sql
index cea346f..ddb755f 100644
--- a/tests/fixtures/optimizer/lower_identities.sql
+++ b/tests/fixtures/optimizer/normalize_identifiers.sql
@@ -1,11 +1,19 @@
 SELECT a FROM x;
 SELECT a FROM x;
 
+# dialect: snowflake
+SELECT A FROM X;
+SELECT A FROM X;
+
 SELECT "A" FROM "X";
 SELECT "A" FROM "X";
 
 SELECT a AS A FROM x;
-SELECT a AS A FROM x;
+SELECT a AS a FROM x;
+
+# dialect: snowflake
+SELECT A AS a FROM X;
+SELECT A AS A FROM X;
 
 SELECT * FROM x;
 SELECT * FROM x;
@@ -13,29 +21,37 @@ SELECT * FROM x;
 SELECT A FROM x;
 SELECT a FROM x;
 
+# dialect: snowflake
+SELECT a FROM X;
+SELECT A FROM X;
+
 SELECT a FROM X;
 SELECT a FROM x;
 
+# dialect: snowflake
+SELECT A FROM x;
+SELECT A FROM X;
+
 SELECT A AS A FROM (SELECT a AS A FROM x);
-SELECT a AS A FROM (SELECT a AS a FROM x);
+SELECT a AS a FROM (SELECT a AS a FROM x);
 
 SELECT a AS B FROM x ORDER BY B;
-SELECT a AS B FROM x ORDER BY B;
+SELECT a AS b FROM x ORDER BY b;
 
 SELECT A FROM x ORDER BY A;
 SELECT a FROM x ORDER BY a;
 
 SELECT A AS B FROM X GROUP BY A HAVING SUM(B) > 0;
-SELECT a AS B FROM x GROUP BY a HAVING SUM(b) > 0;
+SELECT a AS b FROM x GROUP BY a HAVING SUM(b) > 0;
 
 SELECT A AS B, SUM(B) AS C FROM X GROUP BY A HAVING C > 0;
-SELECT a AS B, SUM(b) AS C FROM x GROUP BY a HAVING C > 0;
+SELECT a AS b, SUM(b) AS c FROM x GROUP BY a HAVING c > 0;
 
 SELECT A FROM X UNION SELECT A FROM X;
 SELECT a FROM x UNION SELECT a FROM x;
 
 SELECT A AS A FROM X UNION SELECT A AS A FROM X;
-SELECT a AS A FROM x UNION SELECT a AS A FROM x;
+SELECT a AS a FROM x UNION SELECT a AS a FROM x;
 
 (SELECT A AS A FROM X);
-(SELECT a AS A FROM x);
+(SELECT a AS a FROM x);
diff --git a/tests/fixtures/optimizer/optimize_joins.sql b/tests/fixtures/optimizer/optimize_joins.sql
index b64544e..b8e39c3 100644
--- a/tests/fixtures/optimizer/optimize_joins.sql
+++ b/tests/fixtures/optimizer/optimize_joins.sql
@@ -10,11 +10,23 @@ SELECT * FROM x JOIN z ON x.a = z.a AND TRUE JOIN y ON y.a = z.a;
 SELECT * FROM x LEFT JOIN y ON y.a = 1 JOIN z ON x.a = z.a AND y.a = z.a;
 SELECT * FROM x JOIN z ON x.a = z.a AND TRUE LEFT JOIN y ON y.a = 1 AND y.a = z.a;
 
-SELECT * FROM x INNER JOIN z;
-SELECT * FROM x JOIN z;
+SELECT * FROM x INNER JOIN z ON x.id = z.id;
+SELECT * FROM x JOIN z ON x.id = z.id;
 
 SELECT * FROM x LEFT OUTER JOIN z;
 SELECT * FROM x LEFT JOIN z;
 
 SELECT * FROM x CROSS JOIN z;
 SELECT * FROM x CROSS JOIN z;
+
+SELECT * FROM x JOIN z;
+SELECT * FROM x CROSS JOIN z;
+
+SELECT * FROM x NATURAL JOIN z;
+SELECT * FROM x NATURAL JOIN z;
+
+SELECT * FROM x RIGHT JOIN z;
+SELECT * FROM x RIGHT JOIN z;
+
+SELECT * FROM x JOIN z USING (id);
+SELECT * FROM x JOIN z USING (id);
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index 9e7880c..e0567d7 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -101,10 +101,10 @@ SELECT
   "x"."a" AS "a",
   SUM("y"."b") AS "sum_b"
 FROM "x" AS "x"
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "x"."b" = "_u_0"."_u_1"
 JOIN "y" AS "y"
   ON "x"."b" = "y"."b"
+LEFT JOIN "_u_0" AS "_u_0"
+  ON "x"."b" = "_u_0"."_u_1"
 WHERE
   "_u_0"."_col_0" >= 0 AND "x"."a" > 1
 GROUP BY
@@ -502,3 +502,120 @@ WHERE
   "unioned"."source_system" = 'bamboohr' OR "unioned"."source_system" = 'workday'
 QUALIFY
   ROW_NUMBER() OVER (PARTITION BY "unioned"."unique_filter_key" ORDER BY "unioned"."sort_order" DESC, 1) = 1;
+
+# title: pivoted source with explicit selections
+# execute: false
+SELECT * FROM (SELECT a, b, c FROM sc.tb) PIVOT (SUM(c) FOR b IN ('x','y','z'));
+SELECT
+  "_q_1"."a" AS "a",
+  "_q_1"."x" AS "x",
+  "_q_1"."y" AS "y",
+  "_q_1"."z" AS "z"
+FROM (
+  SELECT
+    "tb"."a" AS "a",
+    "tb"."b" AS "b",
+    "tb"."c" AS "c"
+  FROM "sc"."tb" AS "tb"
+) AS "_q_0" PIVOT(SUM("_q_0"."c") FOR "_q_0"."b" IN ('x', 'y', 'z')) AS "_q_1";
+
+# title: pivoted source with implicit selections
+# execute: false
+SELECT * FROM (SELECT * FROM u) PIVOT (SUM(f) FOR h IN ('x', 'y'));
+SELECT
+  "_q_1"."g" AS "g",
+  "_q_1"."x" AS "x",
+  "_q_1"."y" AS "y"
+FROM (
+  SELECT
+    "u"."f" AS "f",
+    "u"."g" AS "g",
+    "u"."h" AS "h"
+  FROM "u" AS "u"
+) AS "_q_0" PIVOT(SUM("_q_0"."f") FOR "_q_0"."h" IN ('x', 'y')) AS "_q_1";
+
+# title: selecting explicit qualified columns from pivoted source with explicit selections
+# execute: false
+SELECT piv.x, piv.y FROM (SELECT f, h FROM u) PIVOT (SUM(f) FOR h IN ('x', 'y')) AS piv;
+SELECT
+  "piv"."x" AS "x",
+  "piv"."y" AS "y"
+FROM (
+  SELECT
+    "u"."f" AS "f",
+    "u"."h" AS "h"
+  FROM "u" AS "u"
+) AS "_q_0" PIVOT(SUM("_q_0"."f") FOR "_q_0"."h" IN ('x', 'y')) AS "piv";
+
+# title: selecting explicit unqualified columns from pivoted source with implicit selections
+# execute: false
+SELECT x, y FROM u PIVOT (SUM(f) FOR h IN ('x', 'y'));
+SELECT
+  "_q_0"."x" AS "x",
+  "_q_0"."y" AS "y"
+FROM "u" AS "u" PIVOT(SUM("u"."f") FOR "u"."h" IN ('x', 'y')) AS "_q_0";
+
+# title: selecting all columns from a pivoted CTE source, using alias for the aggregation and generating bigquery
+# execute: false
+# dialect: bigquery
+WITH u_cte(f, g, h) AS (SELECT * FROM u) SELECT * FROM u_cte PIVOT(SUM(f) AS sum FOR h IN ('x', 'y'));
+WITH `u_cte` AS (
+  SELECT
+    `u`.`f` AS `f`,
+    `u`.`g` AS `g`,
+    `u`.`h` AS `h`
+  FROM `u` AS `u`
+)
+SELECT
+  `_q_0`.`g` AS `g`,
+  `_q_0`.`sum_x` AS `sum_x`,
+  `_q_0`.`sum_y` AS `sum_y`
+FROM `u_cte` AS `u_cte` PIVOT(SUM(`u_cte`.`f`) AS `sum` FOR `u_cte`.`h` IN ('x', 'y')) AS `_q_0`;
+
+# title: selecting all columns from a pivoted source and generating snowflake
+# execute: false
+# dialect: snowflake
+SELECT * FROM u PIVOT (SUM(f) FOR h IN ('x', 'y'));
+SELECT
+  "_q_0"."G" AS "G",
+  "_q_0"."'x'" AS "'x'",
+  "_q_0"."'y'" AS "'y'"
+FROM "U" AS "U" PIVOT(SUM("U"."F") FOR "U"."H" IN ('x', 'y')) AS "_q_0"
+;
+
+# title: selecting all columns from a pivoted source and generating spark
+# note: spark doesn't allow pivot aliases or qualified columns for the pivot's "field" (`h`)
+# execute: false
+# dialect: spark
+SELECT * FROM u PIVOT (SUM(f) FOR h IN ('x', 'y'));
+SELECT
+  `_q_0`.`g` AS `g`,
+  `_q_0`.`x` AS `x`,
+  `_q_0`.`y` AS `y`
+FROM (
+  SELECT
+    *
+  FROM `u` AS `u` PIVOT(SUM(`u`.`f`) FOR `h` IN ('x', 'y'))
+) AS `_q_0`;
+
+# title: quoting is maintained
+# dialect: snowflake
+with cte1("id", foo) as (select 1, 2) select "id" from cte1;
+WITH "CTE1" AS (
+  SELECT
+    1 AS "id"
+)
+SELECT
+  "CTE1"."id" AS "id"
+FROM "CTE1";
+
+# title: ensures proper quoting happens after all optimizations
+# execute: false
+SELECT "foO".x FROM (SELECT 1 AS x) AS "foO";
+WITH "foO" AS (
+  SELECT
+    1 AS "x"
+)
+SELECT
+  "foO"."x" AS "x"
+FROM "foO" AS "foO";
diff --git a/tests/fixtures/optimizer/pushdown_predicates.sql b/tests/fixtures/optimizer/pushdown_predicates.sql
index dd318a2..83a353d 100644
--- a/tests/fixtures/optimizer/pushdown_predicates.sql
+++ b/tests/fixtures/optimizer/pushdown_predicates.sql
@@ -4,8 +4,8 @@ SELECT x.a AS a FROM (SELECT x.a FROM x AS x WHERE x.a = 1 AND x.b = 1) AS x JOI
 WITH x AS (SELECT y.a FROM y) SELECT * FROM x WHERE x.a = 1;
 WITH x AS (SELECT y.a FROM y WHERE y.a = 1) SELECT * FROM x WHERE TRUE;
 
-SELECT x.a FROM (SELECT * FROM x) AS x JOIN y WHERE y.a = 1 OR (x.a = 1 AND x.b = 1);
-SELECT x.a FROM (SELECT * FROM x) AS x JOIN y WHERE (x.a = 1 AND x.b = 1) OR y.a = 1;
+SELECT x.a FROM (SELECT * FROM x) AS x CROSS JOIN y WHERE y.a = 1 OR (x.a = 1 AND x.b = 1);
+SELECT x.a FROM (SELECT * FROM x) AS x CROSS JOIN y WHERE (x.a = 1 AND x.b = 1) OR y.a = 1;
 
 SELECT x.a FROM (SELECT * FROM x) AS x JOIN y WHERE (x.a = y.a AND x.a = 1 AND x.b = 1) OR x.a = y.a;
 SELECT x.a FROM (SELECT * FROM x) AS x JOIN y ON x.a = y.a WHERE TRUE;
diff --git a/tests/fixtures/optimizer/pushdown_projections.sql b/tests/fixtures/optimizer/pushdown_projections.sql
index 6ff9383..0821339 100644
--- a/tests/fixtures/optimizer/pushdown_projections.sql
+++ b/tests/fixtures/optimizer/pushdown_projections.sql
@@ -61,9 +61,13 @@ SELECT q.x AS x FROM (VALUES (1, 2)) AS q(x, y);
 SELECT i.a FROM x AS i LEFT JOIN (SELECT a, b FROM (SELECT a, b FROM x)) AS j ON i.a = j.a;
 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;
+
 --------------------------------------
 -- Unknown Star Expansion
 --------------------------------------
+
 SELECT a FROM (SELECT * FROM zz) WHERE b = 1;
 SELECT _q_0.a AS a FROM (SELECT zz.a AS a, zz.b AS b FROM zz AS zz) AS _q_0 WHERE _q_0.b = 1;
 
diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql
index f077647..7be2c7f 100644
--- a/tests/fixtures/optimizer/qualify_columns.sql
+++ b/tests/fixtures/optimizer/qualify_columns.sql
@@ -5,7 +5,7 @@ SELECT a FROM x;
 SELECT x.a AS a FROM x AS x;
 
 SELECT "a" FROM x;
-SELECT x."a" AS "a" FROM x AS x;
+SELECT x.a AS a FROM x AS x;
 
 # execute: false
 SELECT a FROM zz GROUP BY a ORDER BY a;
@@ -396,8 +396,39 @@ SELECT x.a + 1 AS a, ROW_NUMBER() OVER (PARTITION BY x.b ORDER BY x.a) AS row_nu
 
 # dialect: bigquery
 SELECT ROW_NUMBER() OVER (PARTITION BY a ORDER BY b) AS row_num FROM x QUALIFY row_num = 1;
-SELECT ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.b) AS row_num FROM x AS x QUALIFY row_num = 1;
+SELECT ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.b) AS row_num FROM x AS x QUALIFY ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.b) = 1;
 
 # dialect: bigquery
 SELECT x.b, x.a FROM x LEFT JOIN y ON x.b = y.b QUALIFY ROW_NUMBER() OVER(PARTITION BY x.b ORDER BY x.a DESC) = 1;
 SELECT x.b AS b, x.a AS a FROM x AS x LEFT JOIN y AS y ON x.b = y.b QUALIFY ROW_NUMBER() OVER (PARTITION BY x.b ORDER BY x.a DESC) = 1;
+
+SELECT * FROM x QUALIFY COUNT(a) OVER (PARTITION BY b) > 1;
+SELECT x.a AS a, x.b AS b FROM x AS x QUALIFY COUNT(x.a) OVER (PARTITION BY x.b) > 1;
+
+--------------------------------------
+-- Expand laterals
+--------------------------------------
+
+# title: expand alias reference
+SELECT
+  x.a + 1 AS i,
+  i + 1 AS j,
+  j + 1 AS k
+FROM x;
+SELECT x.a + 1 AS i, x.a + 1 + 1 AS j, x.a + 1 + 1 + 1 AS k FROM x AS x;
+
+# title: noop - reference comes before alias
+# execute: false
+SELECT i + 1 AS j, x.a + 1 AS i FROM x;
+SELECT i + 1 AS j, x.a + 1 AS i FROM x AS x;
+
+# title: subquery
+SELECT
+  *
+FROM (
+  SELECT
+    x.a + 1 AS i,
+    i + 1 AS j
+  FROM x
+);
+SELECT _q_0.i AS i, _q_0.j AS j FROM (SELECT x.a + 1 AS i, x.a + 1 + 1 AS j FROM x AS x) AS _q_0;
diff --git a/tests/fixtures/optimizer/qualify_columns__invalid.sql b/tests/fixtures/optimizer/qualify_columns__invalid.sql
index f0f9f87..d2d4959 100644
--- a/tests/fixtures/optimizer/qualify_columns__invalid.sql
+++ b/tests/fixtures/optimizer/qualify_columns__invalid.sql
@@ -1,7 +1,7 @@
 SELECT z.a FROM x;
 SELECT z.* FROM x;
 SELECT x FROM x;
-INSERT INTO x VALUES (1, 2);
+SELECT x FROM VALUES (1, 2);
 SELECT a FROM x AS z JOIN y AS z;
 SELECT a FROM x JOIN (SELECT b FROM y WHERE y.b = x.c);
 SELECT a FROM x AS y JOIN (SELECT a FROM y) AS q ON y.a = q.a;
diff --git a/tests/fixtures/optimizer/qualify_columns__with_invisible.sql b/tests/fixtures/optimizer/qualify_columns__with_invisible.sql
index 05253f3..1e07015 100644
--- a/tests/fixtures/optimizer/qualify_columns__with_invisible.sql
+++ b/tests/fixtures/optimizer/qualify_columns__with_invisible.sql
@@ -10,11 +10,11 @@ SELECT x.b AS b FROM x AS x;
 --------------------------------------
 -- Derived tables
 --------------------------------------
-SELECT x.a FROM x AS x JOIN (SELECT * FROM x);
-SELECT x.a AS a FROM x AS x JOIN (SELECT x.a AS a FROM x AS x) AS _q_0;
+SELECT x.a FROM x AS x CROSS JOIN (SELECT * FROM x);
+SELECT x.a AS a FROM x AS x CROSS JOIN (SELECT x.a AS a FROM x AS x) AS _q_0;
 
-SELECT x.b FROM x AS x JOIN (SELECT b FROM x);
-SELECT x.b AS b FROM x AS x JOIN (SELECT x.b AS b FROM x AS x) AS _q_0;
+SELECT x.b FROM x AS x CROSS JOIN (SELECT b FROM x);
+SELECT x.b AS b FROM x AS x CROSS JOIN (SELECT x.b AS b FROM x AS x) AS _q_0;
 
 --------------------------------------
 -- Expand *
@@ -22,11 +22,11 @@ SELECT x.b AS b FROM x AS x JOIN (SELECT x.b AS b FROM x AS x) AS _q_0;
 SELECT * FROM x;
 SELECT x.a AS a FROM x AS x;
 
-SELECT * FROM y JOIN z ON y.b = z.b;
-SELECT y.b AS b, z.b AS b FROM y AS y JOIN z AS z ON y.b = z.b;
+SELECT * FROM y CROSS JOIN z ON y.b = z.b;
+SELECT y.b AS b, z.b AS b FROM y AS y CROSS JOIN z AS z ON y.b = z.b;
 
-SELECT * FROM y JOIN z ON y.c = z.c;
-SELECT y.b AS b, z.b AS b FROM y AS y JOIN z AS z ON y.c = z.c;
+SELECT * FROM y CROSS JOIN z ON y.c = z.c;
+SELECT y.b AS b, z.b AS b FROM y AS y CROSS JOIN z AS z ON y.c = z.c;
 
 SELECT a FROM (SELECT * FROM x);
 SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0;
diff --git a/tests/fixtures/optimizer/qualify_tables.sql b/tests/fixtures/optimizer/qualify_tables.sql
index 0ad155a..24d1b65 100644
--- a/tests/fixtures/optimizer/qualify_tables.sql
+++ b/tests/fixtures/optimizer/qualify_tables.sql
@@ -16,9 +16,12 @@ WITH a AS (SELECT 1 FROM c.db.z AS z) SELECT 1 FROM a;
 SELECT (SELECT y.c FROM y AS y) FROM x;
 SELECT (SELECT y.c FROM c.db.y AS y) FROM c.db.x AS x;
 
--------------------------
+SELECT * FROM x PIVOT (SUM(a) FOR b IN ('a', 'b'));
+SELECT * FROM c.db.x AS x PIVOT(SUM(a) FOR b IN ('a', 'b')) AS _q_0;
+
+----------------------------
 -- Expand join constructs
--------------------------
+----------------------------
 
 -- This is valid in Trino, so we treat the (tbl AS tbl) as a "join construct" per postgres' terminology.
 SELECT * FROM (tbl AS tbl) AS _q_0;
diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql
index a2cd859..5c8d371 100644
--- a/tests/fixtures/optimizer/simplify.sql
+++ b/tests/fixtures/optimizer/simplify.sql
@@ -201,6 +201,21 @@ A AND B AND C AND D;
 (((((A) AND B)) AND C)) AND D;
 A AND B AND C AND D;
 
+(x + 1) + 2;
+x + 3;
+
+x + (1 + 2);
+x + 3;
+
+(x * 2) * 4 + (1 + 3) + 5;
+x * 8 + 9;
+
+(x - 1) - 2;
+(x - 1) - 2;
+
+x - (3 - 2);
+x - 1;
+
 --------------------------------------
 -- Comparison and Pruning
 --------------------------------------
@@ -574,4 +589,4 @@ x > 3;
 TRUE;
 
 x = 2018 OR x <> 2018;
-x <> 2018 OR x = 2018;
\ No newline at end of file
+x <> 2018 OR x = 2018;
diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
index 9908756..a6ee325 100644
--- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
+++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
@@ -46,12 +46,12 @@ WITH "customer_total_return" AS (
 SELECT
   "customer"."c_customer_id" AS "c_customer_id"
 FROM "customer_total_return" AS "ctr1"
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "ctr1"."ctr_store_sk" = "_u_0"."_u_1"
 JOIN "store" AS "store"
   ON "store"."s_state" = 'TN' AND "store"."s_store_sk" = "ctr1"."ctr_store_sk"
 JOIN "customer" AS "customer"
   ON "ctr1"."ctr_customer_sk" = "customer"."c_customer_sk"
+LEFT JOIN "_u_0" AS "_u_0"
+  ON "ctr1"."ctr_store_sk" = "_u_0"."_u_1"
 WHERE
   "ctr1"."ctr_total_return" > "_u_0"."_col_0"
 ORDER BY
@@ -211,9 +211,8 @@ 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"
   ON "wswscs"."d_week_seq" = "wswscs_2"."d_week_seq" - 53
-CROSS JOIN "date_dim" AS "date_dim_2"
-WHERE
-  "date_dim_2"."d_week_seq" = "wswscs_2"."d_week_seq" AND "date_dim_2"."d_year" = 1999
+JOIN "date_dim" AS "date_dim_2"
+  ON "date_dim_2"."d_week_seq" = "wswscs_2"."d_week_seq" AND "date_dim_2"."d_year" = 1999
 ORDER BY
   "d_week_seq1";
 
@@ -953,13 +952,13 @@ JOIN "date_dim" AS "date_dim"
   ON "date_dim"."d_month_seq" = "_u_0"."_col_0"
 JOIN "store_sales" AS "store_sales"
   ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+JOIN "customer" AS "customer"
+  ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk"
+  AND "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
 JOIN "item" AS "item"
   ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
 LEFT JOIN "_u_1" AS "_u_1"
   ON "_u_1"."_u_2" = "item"."i_category"
-JOIN "customer" AS "customer"
-  ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk"
-  AND "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
 WHERE
   "item"."i_current_price" > 1.2 * "_u_1"."_col_0"
 GROUP BY
@@ -1256,9 +1255,9 @@ LIMIT 100;
 WITH "a1" AS (
   SELECT
     SUBSTR("customer_address"."ca_zip", 1, 5) AS "ca_zip"
-  FROM "customer_address" AS "customer_address", "customer" AS "customer"
-  WHERE
-    "customer"."c_preferred_cust_flag" = 'Y'
+  FROM "customer_address" AS "customer_address"
+  JOIN "customer" AS "customer"
+    ON "customer"."c_preferred_cust_flag" = 'Y'
     AND "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
   GROUP BY
     "customer_address"."ca_zip"
@@ -1612,17 +1611,17 @@ SELECT
   "customer_demographics"."cd_dep_college_count" AS "cd_dep_college_count",
   COUNT(*) AS "cnt6"
 FROM "customer" AS "customer"
+JOIN "customer_address" AS "customer_address"
+  ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
+  AND "customer_address"."ca_county" IN ('Lycoming County', 'Sheridan County', 'Kandiyohi County', 'Pike County', 'Greene County')
+JOIN "customer_demographics" AS "customer_demographics"
+  ON "customer_demographics"."cd_demo_sk" = "customer"."c_current_cdemo_sk"
 LEFT JOIN "_u_0" AS "_u_0"
   ON "customer"."c_customer_sk" = "_u_0"."_u_1"
 LEFT JOIN "_u_2" AS "_u_2"
   ON "customer"."c_customer_sk" = "_u_2"."_u_3"
 LEFT JOIN "_u_4" AS "_u_4"
   ON "customer"."c_customer_sk" = "_u_4"."_u_5"
-JOIN "customer_address" AS "customer_address"
-  ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
-  AND "customer_address"."ca_county" IN ('Lycoming County', 'Sheridan County', 'Kandiyohi County', 'Pike County', 'Greene County')
-JOIN "customer_demographics" AS "customer_demographics"
-  ON "customer_demographics"."cd_demo_sk" = "customer"."c_current_cdemo_sk"
 WHERE
   NOT "_u_0"."_u_1" IS NULL
   AND (
@@ -2179,33 +2178,30 @@ WITH "item_2" AS (
     "ics"."i_class_id" AS "i_class_id",
     "ics"."i_category_id" AS "i_category_id"
   FROM "catalog_sales" AS "catalog_sales"
-  CROSS JOIN "item_2" AS "ics"
-  CROSS JOIN "d1" AS "d2"
-  WHERE
-    "catalog_sales"."cs_item_sk" = "ics"."i_item_sk"
-    AND "catalog_sales"."cs_sold_date_sk" = "d2"."d_date_sk"
+  JOIN "item_2" AS "ics"
+    ON "catalog_sales"."cs_item_sk" = "ics"."i_item_sk"
+  JOIN "d1" AS "d2"
+    ON "catalog_sales"."cs_sold_date_sk" = "d2"."d_date_sk"
   INTERSECT
   SELECT
     "iws"."i_brand_id" AS "i_brand_id",
     "iws"."i_class_id" AS "i_class_id",
     "iws"."i_category_id" AS "i_category_id"
   FROM "web_sales" AS "web_sales"
-  CROSS JOIN "item_2" AS "iws"
-  CROSS JOIN "d1" AS "d3"
-  WHERE
-    "web_sales"."ws_item_sk" = "iws"."i_item_sk"
-    AND "web_sales"."ws_sold_date_sk" = "d3"."d_date_sk"
+  JOIN "item_2" AS "iws"
+    ON "web_sales"."ws_item_sk" = "iws"."i_item_sk"
+  JOIN "d1" AS "d3"
+    ON "web_sales"."ws_sold_date_sk" = "d3"."d_date_sk"
 ), "_q_0" AS (
   SELECT
     "iss"."i_brand_id" AS "brand_id",
     "iss"."i_class_id" AS "class_id",
     "iss"."i_category_id" AS "category_id"
   FROM "store_sales" AS "store_sales"
-  CROSS JOIN "item_2" AS "iss"
-  CROSS JOIN "d1" AS "d1"
-  WHERE
-    "store_sales"."ss_item_sk" = "iss"."i_item_sk"
-    AND "store_sales"."ss_sold_date_sk" = "d1"."d_date_sk"
+  JOIN "item_2" AS "iss"
+    ON "store_sales"."ss_item_sk" = "iss"."i_item_sk"
+  JOIN "d1" AS "d1"
+    ON "store_sales"."ss_sold_date_sk" = "d1"."d_date_sk"
   INTERSECT
   SELECT
     "cte_4"."i_brand_id" AS "i_brand_id",
@@ -2242,6 +2238,14 @@ WITH "item_2" AS (
   SELECT
     AVG("x"."quantity" * "x"."list_price") AS "average_sales"
   FROM "x" AS "x"
+), "date_dim_2" AS (
+  SELECT
+    "date_dim"."d_date_sk" AS "d_date_sk",
+    "date_dim"."d_year" AS "d_year",
+    "date_dim"."d_moy" AS "d_moy"
+  FROM "date_dim" AS "date_dim"
+  WHERE
+    "date_dim"."d_moy" = 11 AND "date_dim"."d_year" = 2001
 ), "_u_0" AS (
   SELECT
     "item"."i_item_sk" AS "ss_item_sk"
@@ -2256,14 +2260,6 @@ WITH "item_2" AS (
   SELECT
     "avg_sales"."average_sales" AS "average_sales"
   FROM "avg_sales"
-), "date_dim_2" AS (
-  SELECT
-    "date_dim"."d_date_sk" AS "d_date_sk",
-    "date_dim"."d_year" AS "d_year",
-    "date_dim"."d_moy" AS "d_moy"
-  FROM "date_dim" AS "date_dim"
-  WHERE
-    "date_dim"."d_moy" = 11 AND "date_dim"."d_year" = 2001
 ), "cte_9" AS (
   SELECT
     'store' AS "channel",
@@ -2273,13 +2269,13 @@ WITH "item_2" AS (
     SUM("store_sales"."ss_quantity" * "store_sales"."ss_list_price") AS "sales",
     COUNT(*) AS "number_sales"
   FROM "store_sales" AS "store_sales"
-  LEFT JOIN "_u_0" AS "_u_0"
-    ON "store_sales"."ss_item_sk" = "_u_0"."ss_item_sk"
-  CROSS JOIN "_u_1" AS "_u_1"
   JOIN "item_2" AS "item"
     ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
   JOIN "date_dim_2" AS "date_dim"
     ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+  LEFT JOIN "_u_0" AS "_u_0"
+    ON "store_sales"."ss_item_sk" = "_u_0"."ss_item_sk"
+  CROSS JOIN "_u_1" AS "_u_1"
   WHERE
     NOT "_u_0"."ss_item_sk" IS NULL
   GROUP BY
@@ -2297,13 +2293,13 @@ WITH "item_2" AS (
     SUM("catalog_sales"."cs_quantity" * "catalog_sales"."cs_list_price") AS "sales",
     COUNT(*) AS "number_sales"
   FROM "catalog_sales" AS "catalog_sales"
-  LEFT JOIN "_u_0" AS "_u_2"
-    ON "catalog_sales"."cs_item_sk" = "_u_2"."ss_item_sk"
-  CROSS JOIN "_u_1" AS "_u_3"
   JOIN "item_2" AS "item"
     ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
   JOIN "date_dim_2" AS "date_dim"
     ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
+  LEFT JOIN "_u_0" AS "_u_2"
+    ON "catalog_sales"."cs_item_sk" = "_u_2"."ss_item_sk"
+  CROSS JOIN "_u_1" AS "_u_3"
   WHERE
     NOT "_u_2"."ss_item_sk" IS NULL
   GROUP BY
@@ -2321,13 +2317,13 @@ WITH "item_2" AS (
     SUM("web_sales"."ws_quantity" * "web_sales"."ws_list_price") AS "sales",
     COUNT(*) AS "number_sales"
   FROM "web_sales" AS "web_sales"
-  LEFT JOIN "_u_0" AS "_u_4"
-    ON "web_sales"."ws_item_sk" = "_u_4"."ss_item_sk"
-  CROSS JOIN "_u_1" AS "_u_5"
   JOIN "item_2" AS "item"
     ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
   JOIN "date_dim_2" AS "date_dim"
     ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
+  LEFT JOIN "_u_0" AS "_u_4"
+    ON "web_sales"."ws_item_sk" = "_u_4"."ss_item_sk"
+  CROSS JOIN "_u_1" AS "_u_5"
   WHERE
     NOT "_u_4"."ss_item_sk" IS NULL
   GROUP BY
@@ -2492,10 +2488,6 @@ SELECT
   SUM("catalog_sales"."cs_ext_ship_cost") AS "total shipping cost",
   SUM("catalog_sales"."cs_net_profit") AS "total net profit"
 FROM "catalog_sales" AS "catalog_sales"
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "catalog_sales"."cs_order_number" = "_u_0"."_u_1"
-LEFT JOIN "_u_3" AS "_u_3"
-  ON "catalog_sales"."cs_order_number" = "_u_3"."_u_4"
 JOIN "date_dim" AS "date_dim"
   ON "catalog_sales"."cs_ship_date_sk" = "date_dim"."d_date_sk"
   AND "date_dim"."d_date" >= '2002-3-01'
@@ -2508,6 +2500,10 @@ JOIN "customer_address" AS "customer_address"
 JOIN "call_center" AS "call_center"
   ON "call_center"."cc_county" IN ('Williamson County', 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County')
   AND "catalog_sales"."cs_call_center_sk" = "call_center"."cc_call_center_sk"
+LEFT JOIN "_u_0" AS "_u_0"
+  ON "catalog_sales"."cs_order_number" = "_u_0"."_u_1"
+LEFT JOIN "_u_3" AS "_u_3"
+  ON "catalog_sales"."cs_order_number" = "_u_3"."_u_4"
 WHERE
   "_u_3"."_u_4" IS NULL
   AND ARRAY_ANY("_u_0"."_u_2", "_x" -> "catalog_sales"."cs_warehouse_sk" <> "_x")
@@ -3012,13 +3008,21 @@ 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"
   JOIN "customer_2" AS "customer"
     ON "store_sales"."ss_customer_sk" = "customer"."c_customer_sk"
+  CROSS JOIN "max_store_sales"
   GROUP BY
     "customer"."c_customer_sk"
   HAVING
     SUM("store_sales"."ss_quantity" * "store_sales"."ss_sales_price") > 0.95 * MAX("max_store_sales"."tpcds_cmax")
+), "date_dim_4" AS (
+  SELECT
+    "date_dim"."d_date_sk" AS "d_date_sk",
+    "date_dim"."d_year" AS "d_year",
+    "date_dim"."d_moy" AS "d_moy"
+  FROM "date_dim" AS "date_dim"
+  WHERE
+    "date_dim"."d_moy" = 6 AND "date_dim"."d_year" = 1998
 ), "_u_1" AS (
   SELECT
     "frequent_ss_items"."item_sk" AS "item_sk"
@@ -3031,36 +3035,28 @@ WITH "frequent_ss_items" AS (
   FROM "best_ss_customer"
   GROUP BY
     "best_ss_customer"."c_customer_sk"
-), "date_dim_4" AS (
-  SELECT
-    "date_dim"."d_date_sk" AS "d_date_sk",
-    "date_dim"."d_year" AS "d_year",
-    "date_dim"."d_moy" AS "d_moy"
-  FROM "date_dim" AS "date_dim"
-  WHERE
-    "date_dim"."d_moy" = 6 AND "date_dim"."d_year" = 1998
 ), "_q_1" AS (
   SELECT
     "catalog_sales"."cs_quantity" * "catalog_sales"."cs_list_price" AS "sales"
   FROM "catalog_sales" AS "catalog_sales"
+  JOIN "date_dim_4" AS "date_dim"
+    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   LEFT JOIN "_u_1" AS "_u_1"
     ON "catalog_sales"."cs_item_sk" = "_u_1"."item_sk"
   LEFT JOIN "_u_2" AS "_u_2"
     ON "catalog_sales"."cs_bill_customer_sk" = "_u_2"."c_customer_sk"
-  JOIN "date_dim_4" AS "date_dim"
-    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   WHERE
     NOT "_u_1"."item_sk" IS NULL AND NOT "_u_2"."c_customer_sk" IS NULL
   UNION ALL
   SELECT
     "web_sales"."ws_quantity" * "web_sales"."ws_list_price" AS "sales"
   FROM "web_sales" AS "web_sales"
+  JOIN "date_dim_4" AS "date_dim"
+    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
   LEFT JOIN "_u_1" AS "_u_3"
     ON "web_sales"."ws_item_sk" = "_u_3"."item_sk"
   LEFT JOIN "_u_2" AS "_u_4"
     ON "web_sales"."ws_bill_customer_sk" = "_u_4"."c_customer_sk"
-  JOIN "date_dim_4" AS "date_dim"
-    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
   WHERE
     NOT "_u_3"."item_sk" IS NULL AND NOT "_u_4"."c_customer_sk" IS NULL
 )
@@ -3717,13 +3713,13 @@ SELECT
   "customer"."c_last_review_date" AS "c_last_review_date",
   "ctr1"."ctr_total_return" AS "ctr_total_return"
 FROM "customer_total_return" AS "ctr1"
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "ctr1"."ctr_state" = "_u_0"."_u_1"
 JOIN "customer" AS "customer"
   ON "ctr1"."ctr_customer_sk" = "customer"."c_customer_sk"
 JOIN "customer_address" AS "customer_address"
   ON "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
   AND "customer_address"."ca_state" = 'IN'
+LEFT JOIN "_u_0" AS "_u_0"
+  ON "ctr1"."ctr_state" = "_u_0"."_u_1"
 WHERE
   "ctr1"."ctr_total_return" > "_u_0"."_col_0"
 ORDER BY
@@ -3957,10 +3953,10 @@ SELECT
 FROM "catalog_sales_2" AS "catalog_sales"
 JOIN "item" AS "item"
   ON "item"."i_item_sk" = "catalog_sales"."cs_item_sk" AND "item"."i_manufact_id" = 610
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "_u_0"."_u_1" = "item"."i_item_sk"
 JOIN "date_dim_2" AS "date_dim"
   ON "date_dim"."d_date_sk" = "catalog_sales"."cs_sold_date_sk"
+LEFT JOIN "_u_0" AS "_u_0"
+  ON "_u_0"."_u_1" = "item"."i_item_sk"
 WHERE
   "catalog_sales"."cs_ext_discount_amt" > "_u_0"."_col_0"
 LIMIT 100;
@@ -4032,20 +4028,7 @@ FROM   (SELECT *
 GROUP  BY i_manufact_id
 ORDER  BY total_sales
 LIMIT 100;
-WITH "item_2" AS (
-  SELECT
-    "item"."i_item_sk" AS "i_item_sk",
-    "item"."i_manufact_id" AS "i_manufact_id"
-  FROM "item" AS "item"
-), "_u_0" AS (
-  SELECT
-    "item"."i_manufact_id" AS "i_manufact_id"
-  FROM "item" AS "item"
-  WHERE
-    "item"."i_category" IN ('Books')
-  GROUP BY
-    "item"."i_manufact_id"
-), "date_dim_2" AS (
+WITH "date_dim_2" AS (
   SELECT
     "date_dim"."d_date_sk" AS "d_date_sk",
     "date_dim"."d_year" AS "d_year",
@@ -4060,19 +4043,32 @@ WITH "item_2" AS (
   FROM "customer_address" AS "customer_address"
   WHERE
     "customer_address"."ca_gmt_offset" = -5
+), "item_2" AS (
+  SELECT
+    "item"."i_item_sk" AS "i_item_sk",
+    "item"."i_manufact_id" AS "i_manufact_id"
+  FROM "item" AS "item"
+), "_u_0" AS (
+  SELECT
+    "item"."i_manufact_id" AS "i_manufact_id"
+  FROM "item" AS "item"
+  WHERE
+    "item"."i_category" IN ('Books')
+  GROUP BY
+    "item"."i_manufact_id"
 ), "ss" AS (
   SELECT
     "item"."i_manufact_id" AS "i_manufact_id",
     SUM("store_sales"."ss_ext_sales_price") AS "total_sales"
   FROM "store_sales" AS "store_sales"
-  JOIN "item_2" AS "item"
-    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
-  LEFT JOIN "_u_0" AS "_u_0"
-    ON "item"."i_manufact_id" = "_u_0"."i_manufact_id"
   JOIN "date_dim_2" AS "date_dim"
     ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_address_2" AS "customer_address"
     ON "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "item_2" AS "item"
+    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+  LEFT JOIN "_u_0" AS "_u_0"
+    ON "item"."i_manufact_id" = "_u_0"."i_manufact_id"
   WHERE
     NOT "_u_0"."i_manufact_id" IS NULL
   GROUP BY
@@ -4082,14 +4078,14 @@ WITH "item_2" AS (
     "item"."i_manufact_id" AS "i_manufact_id",
     SUM("catalog_sales"."cs_ext_sales_price") AS "total_sales"
   FROM "catalog_sales" AS "catalog_sales"
-  JOIN "item_2" AS "item"
-    ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
-  LEFT JOIN "_u_0" AS "_u_1"
-    ON "item"."i_manufact_id" = "_u_1"."i_manufact_id"
   JOIN "date_dim_2" AS "date_dim"
     ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_address_2" AS "customer_address"
     ON "catalog_sales"."cs_bill_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "item_2" AS "item"
+    ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
+  LEFT JOIN "_u_0" AS "_u_1"
+    ON "item"."i_manufact_id" = "_u_1"."i_manufact_id"
   WHERE
     NOT "_u_1"."i_manufact_id" IS NULL
   GROUP BY
@@ -4099,14 +4095,14 @@ WITH "item_2" AS (
     "item"."i_manufact_id" AS "i_manufact_id",
     SUM("web_sales"."ws_ext_sales_price") AS "total_sales"
   FROM "web_sales" AS "web_sales"
-  JOIN "item_2" AS "item"
-    ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
-  LEFT JOIN "_u_0" AS "_u_2"
-    ON "item"."i_manufact_id" = "_u_2"."i_manufact_id"
   JOIN "date_dim_2" AS "date_dim"
     ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_address_2" AS "customer_address"
     ON "web_sales"."ws_bill_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "item_2" AS "item"
+    ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
+  LEFT JOIN "_u_0" AS "_u_2"
+    ON "item"."i_manufact_id" = "_u_2"."i_manufact_id"
   WHERE
     NOT "_u_2"."i_manufact_id" IS NULL
   GROUP BY
@@ -4382,16 +4378,16 @@ SELECT
   AVG("customer_demographics"."cd_dep_college_count") AS "_col_16",
   MAX("customer_demographics"."cd_dep_college_count") AS "_col_17"
 FROM "customer" AS "customer"
+JOIN "customer_address" AS "customer_address"
+  ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
+JOIN "customer_demographics" AS "customer_demographics"
+  ON "customer_demographics"."cd_demo_sk" = "customer"."c_current_cdemo_sk"
 LEFT JOIN "_u_0" AS "_u_0"
   ON "customer"."c_customer_sk" = "_u_0"."_u_1"
 LEFT JOIN "_u_2" AS "_u_2"
   ON "customer"."c_customer_sk" = "_u_2"."_u_3"
 LEFT JOIN "_u_4" AS "_u_4"
   ON "customer"."c_customer_sk" = "_u_4"."_u_5"
-JOIN "customer_address" AS "customer_address"
-  ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
-JOIN "customer_demographics" AS "customer_demographics"
-  ON "customer_demographics"."cd_demo_sk" = "customer"."c_current_cdemo_sk"
 WHERE
   NOT "_u_0"."_u_1" IS NULL
   AND (
@@ -4468,7 +4464,7 @@ ROLLUP (
 )
 ORDER BY
   "lochierarchy" DESC,
-  CASE WHEN "lochierarchy" = 0 THEN "item"."i_category" END,
+  CASE WHEN "lochierarchy" = 0 THEN "i_category" END,
   "rank_within_parent"
 LIMIT 100;
 
@@ -5349,10 +5345,6 @@ SELECT
   "customer_address"."ca_state" AS "ca_state",
   SUM("web_sales"."ws_sales_price") AS "_col_2"
 FROM "web_sales" AS "web_sales"
-JOIN "item" AS "item"
-  ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "item"."i_item_id" = "_u_0"."i_item_id"
 JOIN "customer" AS "customer"
   ON "web_sales"."ws_bill_customer_sk" = "customer"."c_customer_sk"
 JOIN "customer_address" AS "customer_address"
@@ -5361,6 +5353,10 @@ JOIN "date_dim" AS "date_dim"
   ON "date_dim"."d_qoy" = 1
   AND "date_dim"."d_year" = 2000
   AND "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
+JOIN "item" AS "item"
+  ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
+LEFT JOIN "_u_0" AS "_u_0"
+  ON "item"."i_item_id" = "_u_0"."i_item_id"
 WHERE
   NOT "_u_0"."i_item_id" IS NULL
   OR SUBSTR("customer_address"."ca_zip", 1, 5) IN ('85669', '86197', '88274', '83405', '86475', '85392', '85460', '80348', '81792')
@@ -6145,7 +6141,7 @@ WITH web_v1 AS
 (
          SELECT   ws_item_sk item_sk,
                   d_date,
-                  sum(Sum(ws_sales_price)) OVER (partition BY ws_item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND      CURRENT ROW) cume_sales
+                  sum(Sum(ws_sales_price)) OVER (partition BY ws_item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND      CURRENT row) cume_sales
          FROM     web_sales ,
                   date_dim
          WHERE    ws_sold_date_sk=d_date_sk
@@ -6156,7 +6152,7 @@ WITH web_v1 AS
 (
          SELECT   ss_item_sk item_sk,
                   d_date,
-                  sum(sum(ss_sales_price)) OVER (partition BY ss_item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND      CURRENT ROW) cume_sales
+                  sum(sum(ss_sales_price)) OVER (partition BY ss_item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND      CURRENT row) cume_sales
          FROM     store_sales ,
                   date_dim
          WHERE    ss_sold_date_sk=d_date_sk
@@ -6171,8 +6167,8 @@ FROM     (
                            d_date ,
                            web_sales ,
                            store_sales ,
-                           max(web_sales) OVER (partition BY item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND      CURRENT ROW)   web_cumulative ,
-                           max(store_sales) OVER (partition BY item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND      CURRENT ROW) store_cumulative
+                           max(web_sales) OVER (partition BY item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND      CURRENT row)   web_cumulative ,
+                           max(store_sales) OVER (partition BY item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND      CURRENT row) store_cumulative
                   FROM     (
                                            SELECT
                                                            CASE
@@ -6533,10 +6529,6 @@ WITH "cs_or_ws_sales" AS (
     SUM("store_sales"."ss_ext_sales_price") AS "revenue"
   FROM "my_customers"
   CROSS JOIN "date_dim" AS "date_dim"
-  JOIN "_u_0" AS "_u_0"
-    ON "date_dim"."d_month_seq" >= "_u_0"."_col_0"
-  JOIN "_u_1" AS "_u_1"
-    ON "date_dim"."d_month_seq" <= "_u_1"."_col_0"
   JOIN "store_sales" AS "store_sales"
     ON "my_customers"."c_customer_sk" = "store_sales"."ss_customer_sk"
     AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
@@ -6545,6 +6537,10 @@ WITH "cs_or_ws_sales" AS (
   JOIN "store" AS "store"
     ON "customer_address"."ca_county" = "store"."s_county"
     AND "customer_address"."ca_state" = "store"."s_state"
+  JOIN "_u_0" AS "_u_0"
+    ON "date_dim"."d_month_seq" >= "_u_0"."_col_0"
+  JOIN "_u_1" AS "_u_1"
+    ON "date_dim"."d_month_seq" <= "_u_1"."_col_0"
   GROUP BY
     "my_customers"."c_customer_sk"
 )
@@ -6674,20 +6670,7 @@ FROM   (SELECT *
 GROUP  BY i_item_id
 ORDER  BY total_sales
 LIMIT 100;
-WITH "item_2" AS (
-  SELECT
-    "item"."i_item_sk" AS "i_item_sk",
-    "item"."i_item_id" AS "i_item_id"
-  FROM "item" AS "item"
-), "_u_0" AS (
-  SELECT
-    "item"."i_item_id" AS "i_item_id"
-  FROM "item" AS "item"
-  WHERE
-    "item"."i_color" IN ('firebrick', 'rosy', 'white')
-  GROUP BY
-    "item"."i_item_id"
-), "date_dim_2" AS (
+WITH "date_dim_2" AS (
   SELECT
     "date_dim"."d_date_sk" AS "d_date_sk",
     "date_dim"."d_year" AS "d_year",
@@ -6702,19 +6685,32 @@ WITH "item_2" AS (
   FROM "customer_address" AS "customer_address"
   WHERE
     "customer_address"."ca_gmt_offset" = -6
+), "item_2" AS (
+  SELECT
+    "item"."i_item_sk" AS "i_item_sk",
+    "item"."i_item_id" AS "i_item_id"
+  FROM "item" AS "item"
+), "_u_0" AS (
+  SELECT
+    "item"."i_item_id" AS "i_item_id"
+  FROM "item" AS "item"
+  WHERE
+    "item"."i_color" IN ('firebrick', 'rosy', 'white')
+  GROUP BY
+    "item"."i_item_id"
 ), "ss" AS (
   SELECT
     "item"."i_item_id" AS "i_item_id",
     SUM("store_sales"."ss_ext_sales_price") AS "total_sales"
   FROM "store_sales" AS "store_sales"
-  JOIN "item_2" AS "item"
-    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
-  LEFT JOIN "_u_0" AS "_u_0"
-    ON "item"."i_item_id" = "_u_0"."i_item_id"
   JOIN "date_dim_2" AS "date_dim"
     ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_address_2" AS "customer_address"
     ON "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "item_2" AS "item"
+    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+  LEFT JOIN "_u_0" AS "_u_0"
+    ON "item"."i_item_id" = "_u_0"."i_item_id"
   WHERE
     NOT "_u_0"."i_item_id" IS NULL
   GROUP BY
@@ -6724,14 +6720,14 @@ WITH "item_2" AS (
     "item"."i_item_id" AS "i_item_id",
     SUM("catalog_sales"."cs_ext_sales_price") AS "total_sales"
   FROM "catalog_sales" AS "catalog_sales"
-  JOIN "item_2" AS "item"
-    ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
-  LEFT JOIN "_u_0" AS "_u_1"
-    ON "item"."i_item_id" = "_u_1"."i_item_id"
   JOIN "date_dim_2" AS "date_dim"
     ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_address_2" AS "customer_address"
     ON "catalog_sales"."cs_bill_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "item_2" AS "item"
+    ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
+  LEFT JOIN "_u_0" AS "_u_1"
+    ON "item"."i_item_id" = "_u_1"."i_item_id"
   WHERE
     NOT "_u_1"."i_item_id" IS NULL
   GROUP BY
@@ -6741,14 +6737,14 @@ WITH "item_2" AS (
     "item"."i_item_id" AS "i_item_id",
     SUM("web_sales"."ws_ext_sales_price") AS "total_sales"
   FROM "web_sales" AS "web_sales"
-  JOIN "item_2" AS "item"
-    ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
-  LEFT JOIN "_u_0" AS "_u_2"
-    ON "item"."i_item_id" = "_u_2"."i_item_id"
   JOIN "date_dim_2" AS "date_dim"
     ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_address_2" AS "customer_address"
     ON "web_sales"."ws_bill_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "item_2" AS "item"
+    ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
+  LEFT JOIN "_u_0" AS "_u_2"
+    ON "item"."i_item_id" = "_u_2"."i_item_id"
   WHERE
     NOT "_u_2"."i_item_id" IS NULL
   GROUP BY
@@ -7075,12 +7071,13 @@ WITH "wss" AS (
     "wss"."thu_sales" AS "thu_sales2",
     "wss"."fri_sales" AS "fri_sales2",
     "wss"."sat_sales" AS "sat_sales2"
-  FROM "wss", "store" AS "store", "date_dim" AS "date_dim"
-  WHERE
-    "date_dim"."d_month_seq" <= 1219
+  FROM "wss"
+  JOIN "store" AS "store"
+    ON "wss"."ss_store_sk" = "store"."s_store_sk"
+  JOIN "date_dim" AS "date_dim"
+    ON "date_dim"."d_month_seq" <= 1219
     AND "date_dim"."d_month_seq" >= 1208
     AND "date_dim"."d_week_seq" = "wss"."d_week_seq"
-    AND "wss"."ss_store_sk" = "store"."s_store_sk"
 )
 SELECT
   "store"."s_store_name" AS "s_store_name1",
@@ -7177,20 +7174,7 @@ GROUP  BY i_item_id
 ORDER  BY i_item_id,
           total_sales
 LIMIT 100;
-WITH "item_2" AS (
-  SELECT
-    "item"."i_item_sk" AS "i_item_sk",
-    "item"."i_item_id" AS "i_item_id"
-  FROM "item" AS "item"
-), "_u_0" AS (
-  SELECT
-    "item"."i_item_id" AS "i_item_id"
-  FROM "item" AS "item"
-  WHERE
-    "item"."i_category" IN ('Jewelry')
-  GROUP BY
-    "item"."i_item_id"
-), "date_dim_2" AS (
+WITH "date_dim_2" AS (
   SELECT
     "date_dim"."d_date_sk" AS "d_date_sk",
     "date_dim"."d_year" AS "d_year",
@@ -7205,19 +7189,32 @@ WITH "item_2" AS (
   FROM "customer_address" AS "customer_address"
   WHERE
     "customer_address"."ca_gmt_offset" = -6
+), "item_2" AS (
+  SELECT
+    "item"."i_item_sk" AS "i_item_sk",
+    "item"."i_item_id" AS "i_item_id"
+  FROM "item" AS "item"
+), "_u_0" AS (
+  SELECT
+    "item"."i_item_id" AS "i_item_id"
+  FROM "item" AS "item"
+  WHERE
+    "item"."i_category" IN ('Jewelry')
+  GROUP BY
+    "item"."i_item_id"
 ), "ss" AS (
   SELECT
     "item"."i_item_id" AS "i_item_id",
     SUM("store_sales"."ss_ext_sales_price") AS "total_sales"
   FROM "store_sales" AS "store_sales"
-  JOIN "item_2" AS "item"
-    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
-  LEFT JOIN "_u_0" AS "_u_0"
-    ON "item"."i_item_id" = "_u_0"."i_item_id"
   JOIN "date_dim_2" AS "date_dim"
     ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_address_2" AS "customer_address"
     ON "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "item_2" AS "item"
+    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+  LEFT JOIN "_u_0" AS "_u_0"
+    ON "item"."i_item_id" = "_u_0"."i_item_id"
   WHERE
     NOT "_u_0"."i_item_id" IS NULL
   GROUP BY
@@ -7227,14 +7224,14 @@ WITH "item_2" AS (
     "item"."i_item_id" AS "i_item_id",
     SUM("catalog_sales"."cs_ext_sales_price") AS "total_sales"
   FROM "catalog_sales" AS "catalog_sales"
-  JOIN "item_2" AS "item"
-    ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
-  LEFT JOIN "_u_0" AS "_u_1"
-    ON "item"."i_item_id" = "_u_1"."i_item_id"
   JOIN "date_dim_2" AS "date_dim"
     ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_address_2" AS "customer_address"
     ON "catalog_sales"."cs_bill_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "item_2" AS "item"
+    ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
+  LEFT JOIN "_u_0" AS "_u_1"
+    ON "item"."i_item_id" = "_u_1"."i_item_id"
   WHERE
     NOT "_u_1"."i_item_id" IS NULL
   GROUP BY
@@ -7244,14 +7241,14 @@ WITH "item_2" AS (
     "item"."i_item_id" AS "i_item_id",
     SUM("web_sales"."ws_ext_sales_price") AS "total_sales"
   FROM "web_sales" AS "web_sales"
-  JOIN "item_2" AS "item"
-    ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
-  LEFT JOIN "_u_0" AS "_u_2"
-    ON "item"."i_item_id" = "_u_2"."i_item_id"
   JOIN "date_dim_2" AS "date_dim"
     ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_address_2" AS "customer_address"
     ON "web_sales"."ws_bill_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "item_2" AS "item"
+    ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
+  LEFT JOIN "_u_0" AS "_u_2"
+    ON "item"."i_item_id" = "_u_2"."i_item_id"
   WHERE
     NOT "_u_2"."i_item_id" IS NULL
   GROUP BY
@@ -7395,13 +7392,17 @@ WITH "store_2" AS (
 ), "all_sales" AS (
   SELECT
     SUM("store_sales"."ss_ext_sales_price") AS "total"
-  FROM "store_sales" AS "store_sales", "store_2" AS "store", "date_dim_2" AS "date_dim", "customer_2" AS "customer", "customer_address_2" AS "customer_address", "item_2" AS "item"
-  WHERE
-    "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
-    AND "store_sales"."ss_customer_sk" = "customer"."c_customer_sk"
-    AND "store_sales"."ss_item_sk" = "item"."i_item_sk"
-    AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
-    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+  FROM "store_sales" AS "store_sales"
+  JOIN "store_2" AS "store"
+    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+  JOIN "customer_2" AS "customer"
+    ON "store_sales"."ss_customer_sk" = "customer"."c_customer_sk"
+  JOIN "customer_address_2" AS "customer_address"
+    ON "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
+  JOIN "item_2" AS "item"
+    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
 )
 SELECT
   "promotional_sales"."promotions" AS "promotions",
@@ -9111,17 +9112,17 @@ SELECT
   "customer_demographics"."cd_credit_rating" AS "cd_credit_rating",
   COUNT(*) AS "cnt3"
 FROM "customer" AS "customer"
+JOIN "customer_address" AS "customer_address"
+  ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
+  AND "customer_address"."ca_state" IN ('KS', 'AZ', 'NE')
+JOIN "customer_demographics" AS "customer_demographics"
+  ON "customer_demographics"."cd_demo_sk" = "customer"."c_current_cdemo_sk"
 LEFT JOIN "_u_0" AS "_u_0"
   ON "customer"."c_customer_sk" = "_u_0"."_u_1"
 LEFT JOIN "_u_2" AS "_u_2"
   ON "customer"."c_customer_sk" = "_u_2"."_u_3"
 LEFT JOIN "_u_4" AS "_u_4"
   ON "customer"."c_customer_sk" = "_u_4"."_u_5"
-JOIN "customer_address" AS "customer_address"
-  ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
-  AND "customer_address"."ca_state" IN ('KS', 'AZ', 'NE')
-JOIN "customer_demographics" AS "customer_demographics"
-  ON "customer_demographics"."cd_demo_sk" = "customer"."c_current_cdemo_sk"
 WHERE
   "_u_2"."_u_3" IS NULL AND "_u_4"."_u_5" IS NULL AND NOT "_u_0"."_u_1" IS NULL
 GROUP BY
@@ -9186,7 +9187,7 @@ WITH "store_sales_2" AS (
     "store_sales"."ss_store_sk" AS "ss_store_sk",
     "store_sales"."ss_net_profit" AS "ss_net_profit"
   FROM "store_sales" AS "store_sales"
-), "date_dim_2" AS (
+), "d1" AS (
   SELECT
     "date_dim"."d_date_sk" AS "d_date_sk",
     "date_dim"."d_month_seq" AS "d_month_seq"
@@ -9200,7 +9201,7 @@ WITH "store_sales_2" AS (
   FROM "store_sales_2" AS "store_sales"
   JOIN "store" AS "store"
     ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
-  JOIN "date_dim_2" AS "date_dim"
+  JOIN "d1" AS "date_dim"
     ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
   GROUP BY
     "store"."s_state"
@@ -9220,12 +9221,12 @@ SELECT
   GROUPING("store"."s_state") + GROUPING("store"."s_county") AS "lochierarchy",
   RANK() OVER (PARTITION BY GROUPING("store"."s_state") + GROUPING("store"."s_county"), CASE WHEN GROUPING("store"."s_county") = 0 THEN "store"."s_state" END ORDER BY SUM("store_sales"."ss_net_profit") DESC) AS "rank_within_parent"
 FROM "store_sales_2" AS "store_sales"
+JOIN "d1" AS "d1"
+  ON "d1"."d_date_sk" = "store_sales"."ss_sold_date_sk"
 JOIN "store" AS "store"
   ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
 LEFT JOIN "_u_0" AS "_u_0"
   ON "store"."s_state" = "_u_0"."s_state"
-JOIN "date_dim_2" AS "d1"
-  ON "d1"."d_date_sk" = "store_sales"."ss_sold_date_sk"
 WHERE
   NOT "_u_0"."s_state" IS NULL
 GROUP BY
@@ -9235,7 +9236,7 @@ ROLLUP (
 )
 ORDER BY
   "lochierarchy" DESC,
-  CASE WHEN "lochierarchy" = 0 THEN "store"."s_state" END,
+  CASE WHEN "lochierarchy" = 0 THEN "s_state" END,
   "rank_within_parent"
 LIMIT 100;
 
@@ -9303,27 +9304,24 @@ WITH "date_dim_2" AS (
     "catalog_sales"."cs_item_sk" AS "sold_item_sk",
     "catalog_sales"."cs_sold_time_sk" AS "time_sk"
   FROM "catalog_sales" AS "catalog_sales"
-  CROSS JOIN "date_dim_2" AS "date_dim"
-  WHERE
-    "date_dim"."d_date_sk" = "catalog_sales"."cs_sold_date_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "date_dim"."d_date_sk" = "catalog_sales"."cs_sold_date_sk"
   UNION ALL
   SELECT
     "store_sales"."ss_ext_sales_price" AS "ext_price",
     "store_sales"."ss_item_sk" AS "sold_item_sk",
     "store_sales"."ss_sold_time_sk" AS "time_sk"
   FROM "store_sales" AS "store_sales"
-  CROSS JOIN "date_dim_2" AS "date_dim"
-  WHERE
-    "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
 ), "tmp" AS (
   SELECT
     "web_sales"."ws_ext_sales_price" AS "ext_price",
     "web_sales"."ws_item_sk" AS "sold_item_sk",
     "web_sales"."ws_sold_time_sk" AS "time_sk"
   FROM "web_sales" AS "web_sales"
-  CROSS JOIN "date_dim_2" AS "date_dim"
-  WHERE
-    "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
   UNION ALL
   SELECT
     "cte_4"."ext_price" AS "ext_price",
@@ -10932,13 +10930,13 @@ SELECT
   "customer_address"."ca_location_type" AS "ca_location_type",
   "ctr1"."ctr_total_return" AS "ctr_total_return"
 FROM "customer_total_return" AS "ctr1"
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "ctr1"."ctr_state" = "_u_0"."_u_1"
 JOIN "customer" AS "customer"
   ON "ctr1"."ctr_customer_sk" = "customer"."c_customer_sk"
 JOIN "customer_address" AS "customer_address"
   ON "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
   AND "customer_address"."ca_state" = 'TX'
+LEFT JOIN "_u_0" AS "_u_0"
+  ON "ctr1"."ctr_state" = "_u_0"."_u_1"
 WHERE
   "ctr1"."ctr_total_return" > "_u_0"."_col_0"
 ORDER BY
@@ -11089,7 +11087,12 @@ WHERE  sr_items.item_id = cr_items.item_id
 ORDER  BY sr_items.item_id,
           sr_item_qty
 LIMIT 100;
-WITH "date_dim_2" AS (
+WITH "item_2" AS (
+  SELECT
+    "item"."i_item_sk" AS "i_item_sk",
+    "item"."i_item_id" AS "i_item_id"
+  FROM "item" AS "item"
+), "date_dim_2" AS (
   SELECT
     "date_dim"."d_date_sk" AS "d_date_sk",
     "date_dim"."d_date" AS "d_date"
@@ -11112,22 +11115,17 @@ WITH "date_dim_2" AS (
     NOT "_u_0"."d_week_seq" IS NULL
   GROUP BY
     "date_dim"."d_date"
-), "item_2" AS (
-  SELECT
-    "item"."i_item_sk" AS "i_item_sk",
-    "item"."i_item_id" AS "i_item_id"
-  FROM "item" AS "item"
 ), "sr_items" AS (
   SELECT
     "item"."i_item_id" AS "item_id",
     SUM("store_returns"."sr_return_quantity") AS "sr_item_qty"
   FROM "store_returns" AS "store_returns"
+  JOIN "item_2" AS "item"
+    ON "store_returns"."sr_item_sk" = "item"."i_item_sk"
   JOIN "date_dim_2" AS "date_dim"
     ON "store_returns"."sr_returned_date_sk" = "date_dim"."d_date_sk"
   LEFT JOIN "_u_1" AS "_u_1"
     ON "date_dim"."d_date" = "_u_1"."d_date"
-  JOIN "item_2" AS "item"
-    ON "store_returns"."sr_item_sk" = "item"."i_item_sk"
   WHERE
     NOT "_u_1"."d_date" IS NULL
   GROUP BY
@@ -11147,12 +11145,12 @@ WITH "date_dim_2" AS (
     "item"."i_item_id" AS "item_id",
     SUM("catalog_returns"."cr_return_quantity") AS "cr_item_qty"
   FROM "catalog_returns" AS "catalog_returns"
+  JOIN "item_2" AS "item"
+    ON "catalog_returns"."cr_item_sk" = "item"."i_item_sk"
   JOIN "date_dim_2" AS "date_dim"
     ON "catalog_returns"."cr_returned_date_sk" = "date_dim"."d_date_sk"
   LEFT JOIN "_u_3" AS "_u_3"
     ON "date_dim"."d_date" = "_u_3"."d_date"
-  JOIN "item_2" AS "item"
-    ON "catalog_returns"."cr_item_sk" = "item"."i_item_sk"
   WHERE
     NOT "_u_3"."d_date" IS NULL
   GROUP BY
@@ -11172,12 +11170,12 @@ WITH "date_dim_2" AS (
     "item"."i_item_id" AS "item_id",
     SUM("web_returns"."wr_return_quantity") AS "wr_item_qty"
   FROM "web_returns" AS "web_returns"
+  JOIN "item_2" AS "item"
+    ON "web_returns"."wr_item_sk" = "item"."i_item_sk"
   JOIN "date_dim_2" AS "date_dim"
     ON "web_returns"."wr_returned_date_sk" = "date_dim"."d_date_sk"
   LEFT JOIN "_u_5" AS "_u_5"
     ON "date_dim"."d_date" = "_u_5"."d_date"
-  JOIN "item_2" AS "item"
-    ON "web_returns"."wr_item_sk" = "item"."i_item_sk"
   WHERE
     NOT "_u_5"."d_date" IS NULL
   GROUP BY
@@ -11437,7 +11435,7 @@ ROLLUP (
 )
 ORDER BY
   "lochierarchy" DESC,
-  CASE WHEN "lochierarchy" = 0 THEN "item"."i_category" END,
+  CASE WHEN "lochierarchy" = 0 THEN "i_category" END,
   "rank_within_parent"
 LIMIT 100;
 
@@ -11687,73 +11685,87 @@ WITH "store_sales_2" AS (
 ), "s2" AS (
   SELECT
     COUNT(*) AS "h9_to_9_30"
-  FROM "store_sales_2" AS "store_sales", "household_demographics_2" AS "household_demographics", "time_dim" AS "time_dim", "store_2" AS "store"
-  WHERE
-    "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
-    AND "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
-    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+  FROM "store_sales_2" AS "store_sales"
+  JOIN "household_demographics_2" AS "household_demographics"
+    ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+  JOIN "time_dim" AS "time_dim"
+    ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
     AND "time_dim"."t_hour" = 9
     AND "time_dim"."t_minute" < 30
+  JOIN "store_2" AS "store"
+    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
 ), "s3" AS (
   SELECT
     COUNT(*) AS "h9_30_to_10"
-  FROM "store_sales_2" AS "store_sales", "household_demographics_2" AS "household_demographics", "time_dim" AS "time_dim", "store_2" AS "store"
-  WHERE
-    "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
-    AND "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
-    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+  FROM "store_sales_2" AS "store_sales"
+  JOIN "household_demographics_2" AS "household_demographics"
+    ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+  JOIN "time_dim" AS "time_dim"
+    ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
     AND "time_dim"."t_hour" = 9
     AND "time_dim"."t_minute" >= 30
+  JOIN "store_2" AS "store"
+    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
 ), "s4" AS (
   SELECT
     COUNT(*) AS "h10_to_10_30"
-  FROM "store_sales_2" AS "store_sales", "household_demographics_2" AS "household_demographics", "time_dim" AS "time_dim", "store_2" AS "store"
-  WHERE
-    "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
-    AND "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
-    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+  FROM "store_sales_2" AS "store_sales"
+  JOIN "household_demographics_2" AS "household_demographics"
+    ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+  JOIN "time_dim" AS "time_dim"
+    ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
     AND "time_dim"."t_hour" = 10
     AND "time_dim"."t_minute" < 30
+  JOIN "store_2" AS "store"
+    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
 ), "s5" AS (
   SELECT
     COUNT(*) AS "h10_30_to_11"
-  FROM "store_sales_2" AS "store_sales", "household_demographics_2" AS "household_demographics", "time_dim" AS "time_dim", "store_2" AS "store"
-  WHERE
-    "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
-    AND "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
-    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+  FROM "store_sales_2" AS "store_sales"
+  JOIN "household_demographics_2" AS "household_demographics"
+    ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+  JOIN "time_dim" AS "time_dim"
+    ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
     AND "time_dim"."t_hour" = 10
     AND "time_dim"."t_minute" >= 30
+  JOIN "store_2" AS "store"
+    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
 ), "s6" AS (
   SELECT
     COUNT(*) AS "h11_to_11_30"
-  FROM "store_sales_2" AS "store_sales", "household_demographics_2" AS "household_demographics", "time_dim" AS "time_dim", "store_2" AS "store"
-  WHERE
-    "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
-    AND "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
-    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+  FROM "store_sales_2" AS "store_sales"
+  JOIN "household_demographics_2" AS "household_demographics"
+    ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+  JOIN "time_dim" AS "time_dim"
+    ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
     AND "time_dim"."t_hour" = 11
     AND "time_dim"."t_minute" < 30
+  JOIN "store_2" AS "store"
+    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
 ), "s7" AS (
   SELECT
     COUNT(*) AS "h11_30_to_12"
-  FROM "store_sales_2" AS "store_sales", "household_demographics_2" AS "household_demographics", "time_dim" AS "time_dim", "store_2" AS "store"
-  WHERE
-    "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
-    AND "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
-    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+  FROM "store_sales_2" AS "store_sales"
+  JOIN "household_demographics_2" AS "household_demographics"
+    ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+  JOIN "time_dim" AS "time_dim"
+    ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
     AND "time_dim"."t_hour" = 11
     AND "time_dim"."t_minute" >= 30
+  JOIN "store_2" AS "store"
+    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
 ), "s8" AS (
   SELECT
     COUNT(*) AS "h12_to_12_30"
-  FROM "store_sales_2" AS "store_sales", "household_demographics_2" AS "household_demographics", "time_dim" AS "time_dim", "store_2" AS "store"
-  WHERE
-    "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
-    AND "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
-    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+  FROM "store_sales_2" AS "store_sales"
+  JOIN "household_demographics_2" AS "household_demographics"
+    ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+  JOIN "time_dim" AS "time_dim"
+    ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
     AND "time_dim"."t_hour" = 12
     AND "time_dim"."t_minute" < 30
+  JOIN "store_2" AS "store"
+    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
 )
 SELECT
   "s1"."h8_30_to_9" AS "h8_30_to_9",
@@ -11946,13 +11958,15 @@ WITH "web_sales_2" AS (
 ), "pt" AS (
   SELECT
     COUNT(*) AS "pmc"
-  FROM "web_sales_2" AS "web_sales", "household_demographics_2" AS "household_demographics", "time_dim" AS "time_dim", "web_page_2" AS "web_page"
-  WHERE
-    "time_dim"."t_hour" <= 21
+  FROM "web_sales_2" AS "web_sales"
+  JOIN "household_demographics_2" AS "household_demographics"
+    ON "web_sales"."ws_ship_hdemo_sk" = "household_demographics"."hd_demo_sk"
+  JOIN "time_dim" AS "time_dim"
+    ON "time_dim"."t_hour" <= 21
     AND "time_dim"."t_hour" >= 20
-    AND "web_sales"."ws_ship_hdemo_sk" = "household_demographics"."hd_demo_sk"
     AND "web_sales"."ws_sold_time_sk" = "time_dim"."t_time_sk"
-    AND "web_sales"."ws_web_page_sk" = "web_page"."wp_web_page_sk"
+  JOIN "web_page_2" AS "web_page"
+    ON "web_sales"."ws_web_page_sk" = "web_page"."wp_web_page_sk"
 )
 SELECT
   CAST("at1"."amc" AS DECIMAL(15, 4)) / CAST("pt"."pmc" AS DECIMAL(15, 4)) AS "am_pm_ratio"
@@ -11997,10 +12011,10 @@ GROUP  BY cc_call_center_id,
           cd_education_status
 ORDER  BY Sum(cr_net_loss) DESC;
 SELECT
-  "call_center"."cc_call_center_id" AS Call_Center,
-  "call_center"."cc_name" AS Call_Center_Name,
-  "call_center"."cc_manager" AS Manager,
-  SUM("catalog_returns"."cr_net_loss") AS Returns_Loss
+  "call_center"."cc_call_center_id" AS "call_center",
+  "call_center"."cc_name" AS "call_center_name",
+  "call_center"."cc_manager" AS "manager",
+  SUM("catalog_returns"."cr_net_loss") AS "returns_loss"
 FROM "call_center" AS "call_center"
 JOIN "catalog_returns" AS "catalog_returns"
   ON "catalog_returns"."cr_call_center_sk" = "call_center"."cc_call_center_sk"
@@ -12096,10 +12110,10 @@ SELECT
 FROM "web_sales_2" AS "web_sales"
 JOIN "item" AS "item"
   ON "item"."i_item_sk" = "web_sales"."ws_item_sk" AND "item"."i_manufact_id" = 718
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "_u_0"."_u_1" = "item"."i_item_sk"
 JOIN "date_dim_2" AS "date_dim"
   ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
+LEFT JOIN "_u_0" AS "_u_0"
+  ON "_u_0"."_u_1" = "item"."i_item_sk"
 WHERE
   "web_sales"."ws_ext_discount_amt" > "_u_0"."_col_0"
 ORDER BY
@@ -12208,10 +12222,6 @@ SELECT
   SUM("web_sales"."ws_ext_ship_cost") AS "total shipping cost",
   SUM("web_sales"."ws_net_profit") AS "total net profit"
 FROM "web_sales" AS "web_sales"
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "web_sales"."ws_order_number" = "_u_0"."_u_1"
-LEFT JOIN "_u_3" AS "_u_3"
-  ON "web_sales"."ws_order_number" = "_u_3"."_u_4"
 JOIN "date_dim" AS "date_dim"
   ON "date_dim"."d_date" >= '2000-3-01'
   AND "web_sales"."ws_ship_date_sk" = "date_dim"."d_date_sk"
@@ -12224,6 +12234,10 @@ JOIN "customer_address" AS "customer_address"
 JOIN "web_site" AS "web_site"
   ON "web_sales"."ws_web_site_sk" = "web_site"."web_site_sk"
   AND "web_site"."web_company_name" = 'pri'
+LEFT JOIN "_u_0" AS "_u_0"
+  ON "web_sales"."ws_order_number" = "_u_0"."_u_1"
+LEFT JOIN "_u_3" AS "_u_3"
+  ON "web_sales"."ws_order_number" = "_u_3"."_u_4"
 WHERE
   "_u_3"."_u_4" IS NULL
   AND ARRAY_ANY("_u_0"."_u_2", "_x" -> "web_sales"."ws_warehouse_sk" <> "_x")
@@ -12303,10 +12317,6 @@ SELECT
   SUM("web_sales"."ws_ext_ship_cost") AS "total shipping cost",
   SUM("web_sales"."ws_net_profit") AS "total net profit"
 FROM "web_sales" AS "web_sales"
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "web_sales"."ws_order_number" = "_u_0"."ws_order_number"
-LEFT JOIN "_u_1" AS "_u_1"
-  ON "web_sales"."ws_order_number" = "_u_1"."wr_order_number"
 JOIN "date_dim" AS "date_dim"
   ON "date_dim"."d_date" >= '2000-4-01'
   AND "web_sales"."ws_ship_date_sk" = "date_dim"."d_date_sk"
@@ -12319,6 +12329,10 @@ JOIN "customer_address" AS "customer_address"
 JOIN "web_site" AS "web_site"
   ON "web_sales"."ws_web_site_sk" = "web_site"."web_site_sk"
   AND "web_site"."web_company_name" = 'pri'
+LEFT JOIN "_u_0" AS "_u_0"
+  ON "web_sales"."ws_order_number" = "_u_0"."ws_order_number"
+LEFT JOIN "_u_1" AS "_u_1"
+  ON "web_sales"."ws_order_number" = "_u_1"."wr_order_number"
 WHERE
   NOT "_u_0"."ws_order_number" IS NULL AND NOT "_u_1"."wr_order_number" IS NULL
 ORDER BY
diff --git a/tests/fixtures/optimizer/tpc-h/tpc-h.sql b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
index ae50f92..a25e247 100644
--- a/tests/fixtures/optimizer/tpc-h/tpc-h.sql
+++ b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
@@ -99,19 +99,19 @@ order by
    p_partkey
 limit
    100;
-WITH "partsupp_2" AS (
-  SELECT
-    "partsupp"."ps_partkey" AS "ps_partkey",
-    "partsupp"."ps_suppkey" AS "ps_suppkey",
-    "partsupp"."ps_supplycost" AS "ps_supplycost"
-  FROM "partsupp" AS "partsupp"
-), "region_2" AS (
+WITH "region_2" AS (
   SELECT
     "region"."r_regionkey" AS "r_regionkey",
     "region"."r_name" AS "r_name"
   FROM "region" AS "region"
   WHERE
     "region"."r_name" = 'EUROPE'
+), "partsupp_2" AS (
+  SELECT
+    "partsupp"."ps_partkey" AS "ps_partkey",
+    "partsupp"."ps_suppkey" AS "ps_suppkey",
+    "partsupp"."ps_supplycost" AS "ps_supplycost"
+  FROM "partsupp" AS "partsupp"
 ), "_u_0" AS (
   SELECT
     MIN("partsupp"."ps_supplycost") AS "_col_0",
@@ -136,8 +136,6 @@ SELECT
   "supplier"."s_phone" AS "s_phone",
   "supplier"."s_comment" AS "s_comment"
 FROM "part" AS "part"
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "part"."p_partkey" = "_u_0"."_u_1"
 CROSS JOIN "region_2" AS "region"
 JOIN "nation" AS "nation"
   ON "nation"."n_regionkey" = "region"."r_regionkey"
@@ -146,6 +144,8 @@ JOIN "partsupp_2" AS "partsupp"
 JOIN "supplier" AS "supplier"
   ON "supplier"."s_nationkey" = "nation"."n_nationkey"
   AND "supplier"."s_suppkey" = "partsupp"."ps_suppkey"
+LEFT JOIN "_u_0" AS "_u_0"
+  ON "part"."p_partkey" = "_u_0"."_u_1"
 WHERE
   "part"."p_size" = 15
   AND "part"."p_type" LIKE '%BRASS'
@@ -681,11 +681,11 @@ SELECT
   "partsupp"."ps_partkey" AS "ps_partkey",
   SUM("partsupp"."ps_supplycost" * "partsupp"."ps_availqty") AS "value"
 FROM "partsupp" AS "partsupp"
-CROSS JOIN "_u_0" AS "_u_0"
 JOIN "supplier_2" AS "supplier"
   ON "partsupp"."ps_suppkey" = "supplier"."s_suppkey"
 JOIN "nation_2" AS "nation"
   ON "supplier"."s_nationkey" = "nation"."n_nationkey"
+CROSS JOIN "_u_0" AS "_u_0"
 GROUP BY
   "partsupp"."ps_partkey"
 HAVING
@@ -950,13 +950,13 @@ SELECT
   "part"."p_size" AS "p_size",
   COUNT(DISTINCT "partsupp"."ps_suppkey") AS "supplier_cnt"
 FROM "partsupp" AS "partsupp"
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "partsupp"."ps_suppkey" = "_u_0"."s_suppkey"
 JOIN "part" AS "part"
   ON "part"."p_brand" <> 'Brand#45'
   AND "part"."p_partkey" = "partsupp"."ps_partkey"
   AND "part"."p_size" IN (49, 14, 23, 45, 19, 3, 36, 9)
   AND NOT "part"."p_type" LIKE 'MEDIUM POLISHED%'
+LEFT JOIN "_u_0" AS "_u_0"
+  ON "partsupp"."ps_suppkey" = "_u_0"."s_suppkey"
 WHERE
   "_u_0"."s_suppkey" IS NULL
 GROUP BY
@@ -1066,10 +1066,10 @@ SELECT
 FROM "customer" AS "customer"
 JOIN "orders" AS "orders"
   ON "customer"."c_custkey" = "orders"."o_custkey"
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "orders"."o_orderkey" = "_u_0"."l_orderkey"
 JOIN "lineitem" AS "lineitem"
   ON "orders"."o_orderkey" = "lineitem"."l_orderkey"
+LEFT JOIN "_u_0" AS "_u_0"
+  ON "orders"."o_orderkey" = "_u_0"."l_orderkey"
 WHERE
   NOT "_u_0"."l_orderkey" IS NULL
 GROUP BY
@@ -1260,10 +1260,10 @@ SELECT
   "supplier"."s_name" AS "s_name",
   "supplier"."s_address" AS "s_address"
 FROM "supplier" AS "supplier"
-LEFT JOIN "_u_4" AS "_u_4"
-  ON "supplier"."s_suppkey" = "_u_4"."ps_suppkey"
 JOIN "nation" AS "nation"
   ON "nation"."n_name" = 'CANADA' AND "supplier"."s_nationkey" = "nation"."n_nationkey"
+LEFT JOIN "_u_4" AS "_u_4"
+  ON "supplier"."s_suppkey" = "_u_4"."ps_suppkey"
 WHERE
   NOT "_u_4"."ps_suppkey" IS NULL
 ORDER BY
@@ -1337,15 +1337,15 @@ FROM "supplier" AS "supplier"
 JOIN "lineitem" AS "lineitem"
   ON "lineitem"."l_receiptdate" > "lineitem"."l_commitdate"
   AND "supplier"."s_suppkey" = "lineitem"."l_suppkey"
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "_u_0"."l_orderkey" = "lineitem"."l_orderkey"
-LEFT JOIN "_u_2" AS "_u_2"
-  ON "_u_2"."l_orderkey" = "lineitem"."l_orderkey"
 JOIN "orders" AS "orders"
   ON "orders"."o_orderkey" = "lineitem"."l_orderkey" AND "orders"."o_orderstatus" = 'F'
 JOIN "nation" AS "nation"
   ON "nation"."n_name" = 'SAUDI ARABIA'
   AND "supplier"."s_nationkey" = "nation"."n_nationkey"
+LEFT JOIN "_u_0" AS "_u_0"
+  ON "_u_0"."l_orderkey" = "lineitem"."l_orderkey"
+LEFT JOIN "_u_2" AS "_u_2"
+  ON "_u_2"."l_orderkey" = "lineitem"."l_orderkey"
 WHERE
   (
     "_u_2"."l_orderkey" IS NULL
diff --git a/tests/fixtures/partial.sql b/tests/fixtures/partial.sql
index c6be364..6c578ab 100644
--- a/tests/fixtures/partial.sql
+++ b/tests/fixtures/partial.sql
@@ -2,7 +2,7 @@ SELECT a FROM
 SELECT a FROM x WHERE
 SELECT a +
 a *
-SELECT a FROM x JOIN
+SELECT a FROM x,
 SELECT a FROM x GROUP BY
 WITH a AS (SELECT 1), b AS (SELECT 2)
 SELECT FROM x