Adding upstream version 20.9.0.upstream/20.9.0

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

10 files changed, 378 insertions, 150 deletions

diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql
index 228f109..13adf7f 100644
--- a/tests/fixtures/identity.sql
+++ b/tests/fixtures/identity.sql
@@ -107,6 +107,7 @@ CURRENT_DATE('UTC')
 CURRENT_DATE AT TIME ZONE 'UTC'
 CURRENT_DATE AT TIME ZONE zone_column
 CURRENT_DATE AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Tokio'
+f1 AT TIME ZONE INTERVAL '-10:00' AS f1
 ARRAY()
 ARRAY(1, 2)
 ARRAY(time, foo)
@@ -137,19 +138,26 @@ x ILIKE '%y%'
 x LIKE '%y%' ESCAPE '\'
 x ILIKE '%y%' ESCAPE '\'
 1 AS escape
-INTERVAL '1' day
+INTERVAL '1' DAY
 INTERVAL '1' MONTH
 INTERVAL '1' YEAR
 INTERVAL '-1' CURRENT_DATE
 INTERVAL '-31' CAST(GETDATE() AS DATE)
 INTERVAL (1 + 3) DAYS
-INTERVAL '1' day * 5
-5 * INTERVAL '1' day
-CASE WHEN TRUE THEN INTERVAL '15' days END
+INTERVAL '1' DAY * 5
+5 * INTERVAL '1' DAY
+CASE WHEN TRUE THEN INTERVAL '15' DAYS END
+CASE WHEN TRUE THEN 1 ELSE interval END
+CASE WHEN TRUE THEN 1 ELSE "INTERVAL" END
+SELECT * WHERE interval IS NULL
+SELECT * WHERE NOT interval IS NULL
+SELECT * WHERE INTERVAL "is" > 1
+SELECT * WHERE INTERVAL x.is > 1
 CAST('45' AS INTERVAL DAYS)
 CAST(x AS UUID)
 FILTER(a, x -> x.a.b.c.d.e.f.g)
 FILTER(a, x -> FOO(x.a.b.c.d.e.f.g) + x.a.b.c.d.e.f.g)
+TIMESTAMP_FROM_PARTS(2019, 1, 10, 2, 3, 4, 123456789, 'America/Los_Angeles')
 TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), 1, DAY)
 DATETIME_DIFF(CURRENT_DATE, 1, DAY)
 QUANTILE(x, 0.5)
@@ -164,7 +172,7 @@ REPLACE(1)
 DATE(x) = DATE(y)
 TIMESTAMP(DATE(x))
 TIMESTAMP_TRUNC(COALESCE(time_field, CURRENT_TIMESTAMP()), DAY)
-COUNT(DISTINCT CASE WHEN DATE_TRUNC(DATE(time_field), isoweek) = DATE_TRUNC(DATE(time_field2), isoweek) THEN report_id ELSE NULL END)
+COUNT(DISTINCT CASE WHEN DATE_TRUNC('ISOWEEK', DATE(time_field)) = DATE_TRUNC('ISOWEEK', DATE(time_field2)) THEN report_id ELSE NULL END)
 COUNT(a, b)
 x[y - 1]
 CASE WHEN SUM(x) > 3 THEN 1 END OVER (PARTITION BY x)
@@ -224,7 +232,7 @@ SELECT DISTINCT ON (x, y + 1) * FROM z
 SELECT DISTINCT ON (x.y) * FROM z
 SELECT DISTINCT FROM_SOMETHING
 SELECT top.x
-SELECT TIMESTAMP(DATE_TRUNC(DATE(time_field), MONTH)) AS time_value FROM "table"
+SELECT TIMESTAMP(DATE_TRUNC('MONTH', DATE(time_field))) AS time_value FROM "table"
 SELECT GREATEST((3 + 1), LEAST(3, 4))
 SELECT TRANSFORM(a, b -> b) AS x
 SELECT AGGREGATE(a, (a, b) -> a + b) AS x
@@ -335,17 +343,13 @@ SELECT a FROM test TABLESAMPLE (BUCKET 1 OUT OF 5)
 SELECT a FROM test TABLESAMPLE (BUCKET 1 OUT OF 5 ON x)
 SELECT a FROM test TABLESAMPLE (BUCKET 1 OUT OF 5 ON RAND())
 SELECT a FROM test TABLESAMPLE (0.1 PERCENT)
-SELECT a FROM test TABLESAMPLE (100)
 SELECT a FROM test TABLESAMPLE (100 ROWS)
-SELECT a FROM test TABLESAMPLE BERNOULLI (50)
-SELECT a FROM test TABLESAMPLE SYSTEM (75)
 SELECT a FROM test PIVOT(SUM(x) FOR y IN ('z', 'q'))
 SELECT 1 FROM a.b.table1 AS t UNPIVOT((c3) FOR c4 IN (a, b))
 SELECT a FROM test PIVOT(SOMEAGG(x, y, z) FOR q IN (1))
 SELECT a FROM test PIVOT(SUM(x) FOR y IN ('z', 'q')) PIVOT(MAX(b) FOR c IN ('d'))
 SELECT a FROM (SELECT a, b FROM test) PIVOT(SUM(x) FOR y IN ('z', 'q'))
 SELECT a FROM test UNPIVOT(x FOR y IN (z, q)) AS x
-SELECT a FROM test PIVOT(SUM(x) FOR y IN ('z', 'q')) AS x TABLESAMPLE (0.1)
 SELECT a FROM test PIVOT(SUM(x) FOR y IN ('z', 'q')) UNPIVOT(x FOR y IN (z, q)) AS x
 SELECT ABS(a) FROM test
 SELECT AVG(a) FROM test
@@ -590,6 +594,7 @@ CREATE TABLE IF NOT EXISTS customer (pk BIGINT NOT NULL GENERATED ALWAYS AS IDEN
 CREATE TABLE customer (pk BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 10 INCREMENT BY 1 MINVALUE -1 MAXVALUE 1 NO CYCLE))
 CREATE TABLE customer (pk BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 10))
 CREATE TABLE customer (pk BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (CYCLE))
+CREATE TABLE customer (period INT NOT NULL)
 CREATE TABLE foo (baz_id INT REFERENCES baz (id) DEFERRABLE)
 CREATE TABLE foo (baz CHAR(4) CHARACTER SET LATIN UPPERCASE NOT CASESPECIFIC COMPRESS 'a')
 CREATE TABLE foo (baz DATE FORMAT 'YYYY/MM/DD' TITLE 'title' INLINE LENGTH 1 COMPRESS ('a', 'b'))
@@ -621,6 +626,9 @@ CREATE FUNCTION a(b INT, c VARCHAR) AS 'SELECT 1'
 CREATE FUNCTION a() LANGUAGE sql
 CREATE FUNCTION a() LANGUAGE sql RETURNS INT
 CREATE FUNCTION a.b(x INT) RETURNS INT AS RETURN x + 1
+CREATE FUNCTION a.b(x TEXT) RETURNS TEXT CONTAINS SQL AS RETURN x
+CREATE FUNCTION a.b(x TEXT) RETURNS TEXT LANGUAGE SQL MODIFIES SQL DATA AS RETURN x
+CREATE FUNCTION a.b(x TEXT) LANGUAGE SQL READS SQL DATA RETURNS TEXT AS RETURN x
 CREATE FUNCTION a.b.c()
 CREATE INDEX abc ON t(a)
 CREATE INDEX "abc" ON t(a)
@@ -635,6 +643,7 @@ CREATE DATABASE x
 CREATE DATABASE IF NOT EXISTS y
 CREATE PROCEDURE IF NOT EXISTS a.b.c() AS 'DECLARE BEGIN; END'
 CREATE OR REPLACE STAGE
+CREATE TABLE T3 AS (SELECT DISTINCT A FROM T1 EXCEPT (SELECT A FROM T2) LIMIT 1)
 DESCRIBE x
 DROP INDEX a.b.c
 DROP FUNCTION a.b.c (INT)
@@ -741,6 +750,7 @@ SELECT (WITH x AS (SELECT 1 AS y) SELECT * FROM x) AS z
 SELECT ((SELECT 1) + 1)
 SELECT * FROM project.dataset.INFORMATION_SCHEMA.TABLES
 SELECT CAST(x AS INT) /* comment */ FROM foo
+SELECT c /* c1 */ AS alias /* c2 */
 SELECT a /* x */, b /* x */
 SELECT a /* x */ /* y */ /* z */, b /* k */ /* m */
 SELECT * FROM foo /* x */, bla /* x */
@@ -756,9 +766,6 @@ INSERT INTO foo SELECT * FROM bar /* comment */
 /* c */ WITH x AS (SELECT 1) SELECT * FROM x
 SELECT a FROM x WHERE a COLLATE 'utf8_general_ci' = 'b'
 SELECT x AS INTO FROM bla
-SELECT * INTO newevent FROM event
-SELECT * INTO TEMPORARY newevent FROM event
-SELECT * INTO UNLOGGED newevent FROM event
 ALTER TABLE integers ADD COLUMN k INT
 ALTER TABLE integers ADD COLUMN k INT FIRST
 ALTER TABLE integers ADD COLUMN k INT AFTER m
diff --git a/tests/fixtures/optimizer/canonicalize.sql b/tests/fixtures/optimizer/canonicalize.sql
index 302acb9..4db3764 100644
--- a/tests/fixtures/optimizer/canonicalize.sql
+++ b/tests/fixtures/optimizer/canonicalize.sql
@@ -16,8 +16,8 @@ SELECT '1' + 1 AS "col";
 SELECT '1' + '1' AS "col";
 SELECT CONCAT('1', '1') AS "col";
 
-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";
 
 SELECT CAST('2022-01-01' AS DATE) IS NULL AS "a";
 SELECT CAST('2022-01-01' AS DATE) IS NULL AS "a";
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index 4ae9e90..ea29e3e 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -626,8 +626,7 @@ 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"
-;
+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`)
@@ -644,6 +643,110 @@ FROM (
   FROM `u` AS `u` PIVOT(SUM(`u`.`f`) FOR `h` IN ('x', 'y'))
 ) AS `_q_0`;
 
+# title: selecting all columns from a pivoted source, pivot has column aliases
+# execute: false
+# dialect: snowflake
+WITH source AS (
+  SELECT
+    id,
+    key,
+    value,
+    timestamp_1,
+    timestamp_2
+  FROM DB_NAME.SCHEMA_NAME.TABLE_NAME
+),
+enriched AS (
+  SELECT * FROM source
+  PIVOT(MAX(value) FOR key IN ('a', 'b', 'c'))
+  AS final (id, timestamp_1, timestamp_2, col_1, col_2, col_3)
+)
+SELECT id, timestamp_1 FROM enriched;
+WITH "SOURCE" AS (
+  SELECT
+    "TABLE_NAME"."ID" AS "ID",
+    "TABLE_NAME"."KEY" AS "KEY",
+    "TABLE_NAME"."VALUE" AS "VALUE",
+    "TABLE_NAME"."TIMESTAMP_1" AS "TIMESTAMP_1",
+    "TABLE_NAME"."TIMESTAMP_2" AS "TIMESTAMP_2"
+  FROM "DB_NAME"."SCHEMA_NAME"."TABLE_NAME" AS "TABLE_NAME"
+)
+SELECT
+  "FINAL"."ID" AS "ID",
+  "FINAL"."TIMESTAMP_1" AS "TIMESTAMP_1"
+FROM "SOURCE" AS "SOURCE" PIVOT(MAX("SOURCE"."VALUE") FOR "SOURCE"."KEY" IN ('a', 'b', 'c')) AS "FINAL"("ID", "TIMESTAMP_1", "TIMESTAMP_2", "COL_1", "COL_2", "COL_3");
+
+# title: unpivoted table source with a single value column, unpivot columns can't be qualified
+# execute: false
+# dialect: snowflake
+SELECT * FROM m_sales AS m_sales(empid, dept, jan, feb) UNPIVOT(sales FOR month IN (jan, feb)) ORDER BY empid;
+SELECT
+  "_q_0"."EMPID" AS "EMPID",
+  "_q_0"."DEPT" AS "DEPT",
+  "_q_0"."MONTH" AS "MONTH",
+  "_q_0"."SALES" AS "SALES"
+FROM "M_SALES" AS "M_SALES"("EMPID", "DEPT", "JAN", "FEB") UNPIVOT("SALES" FOR "MONTH" IN ("JAN", "FEB")) AS "_q_0"
+ORDER BY
+  "_q_0"."EMPID";
+
+# title: unpivoted table source, unpivot has column aliases
+# execute: false
+SELECT * FROM (SELECT * FROM m_sales) AS m_sales(empid, dept, jan, feb) UNPIVOT(sales FOR month IN (jan, feb)) AS unpiv(a, b, c, d);
+SELECT
+  "unpiv"."a" AS "a",
+  "unpiv"."b" AS "b",
+  "unpiv"."c" AS "c",
+  "unpiv"."d" AS "d"
+FROM (
+  SELECT
+    "m_sales"."empid" AS "empid",
+    "m_sales"."dept" AS "dept",
+    "m_sales"."jan" AS "jan",
+    "m_sales"."feb" AS "feb"
+  FROM "m_sales" AS "m_sales"
+) AS "m_sales" UNPIVOT("sales" FOR "month" IN ("m_sales"."jan", "m_sales"."feb")) AS "unpiv"("a", "b", "c", "d");
+
+# title: unpivoted derived table source with a single value column
+# execute: false
+# dialect: snowflake
+SELECT * FROM (SELECT * FROM m_sales) AS m_sales(empid, dept, jan, feb) UNPIVOT(sales FOR month IN (jan, feb)) ORDER BY empid;
+SELECT
+  "_q_0"."EMPID" AS "EMPID",
+  "_q_0"."DEPT" AS "DEPT",
+  "_q_0"."MONTH" AS "MONTH",
+  "_q_0"."SALES" AS "SALES"
+FROM (
+  SELECT
+    "M_SALES"."EMPID" AS "EMPID",
+    "M_SALES"."DEPT" AS "DEPT",
+    "M_SALES"."JAN" AS "JAN",
+    "M_SALES"."FEB" AS "FEB"
+  FROM "M_SALES" AS "M_SALES"
+) AS "M_SALES" UNPIVOT("SALES" FOR "MONTH" IN ("JAN", "FEB")) AS "_q_0"
+ORDER BY
+  "_q_0"."EMPID";
+
+# title: unpivoted table source with a single value column, unpivot columns can be qualified
+# execute: false
+# dialect: bigquery
+# note: the named columns aren't supported by BQ but we add them here to avoid defining a schema
+SELECT * FROM produce AS produce(product, q1, q2, q3, q4) UNPIVOT(sales FOR quarter IN (q1, q2, q3, q4));
+SELECT
+  `_q_0`.`product` AS `product`,
+  `_q_0`.`quarter` AS `quarter`,
+  `_q_0`.`sales` AS `sales`
+FROM `produce` AS `produce` UNPIVOT(`sales` FOR `quarter` IN (`produce`.`q1`, `produce`.`q2`, `produce`.`q3`, `produce`.`q4`)) AS `_q_0`;
+
+# title: unpivoted table source with multiple value columns
+# execute: false
+# dialect: bigquery
+SELECT * FROM produce AS produce(product, q1, q2, q3, q4) UNPIVOT((first_half_sales, second_half_sales) FOR semesters IN ((Q1, Q2) AS 'semester_1', (Q3, Q4) AS 'semester_2'));
+SELECT
+  `_q_0`.`product` AS `product`,
+  `_q_0`.`semesters` AS `semesters`,
+  `_q_0`.`first_half_sales` AS `first_half_sales`,
+  `_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
 # dialect: snowflake
 with cte1("id", foo) as (select 1, 2) select "id" from cte1;
@@ -1144,3 +1247,29 @@ WITH "x" AS (
   LIMIT 10
 )
 LIMIT 10;
+
+# title: avoid producing DAG cycle when pushing down predicate to join
+# execute: false
+SELECT
+  a.company,
+  b.num
+FROM route AS a(num, company, pos, stop)
+JOIN route AS b(num, company, pos, stop) ON (a.num = b.num)
+JOIN stops AS c(id, name) ON (c.id = b.stop)
+JOIN stops AS d(id, name) ON (d.id = c.id)
+WHERE
+  c.name = 'Craiglockhart'
+  OR d.name = 'Tollcross';
+SELECT
+  "a"."company" AS "company",
+  "b"."num" AS "num"
+FROM "route" AS "a"("num", "company", "pos", "stop")
+JOIN "route" AS "b"("num", "company", "pos", "stop")
+  ON "a"."num" = "b"."num"
+JOIN "stops" AS "c"("id", "name")
+  ON "b"."stop" = "c"."id"
+JOIN "stops" AS "d"("id", "name")
+  ON "c"."id" = "d"."id"
+  AND (
+    "c"."name" = 'Craiglockhart' OR "d"."name" = 'Tollcross'
+  );
diff --git a/tests/fixtures/optimizer/pushdown_cte_alias_columns.sql b/tests/fixtures/optimizer/pushdown_cte_alias_columns.sql
new file mode 100644
index 0000000..e25ab19
--- /dev/null
+++ b/tests/fixtures/optimizer/pushdown_cte_alias_columns.sql
@@ -0,0 +1,20 @@
+WITH y(c) AS (SELECT SUM(a) FROM (SELECT 1 a) AS x HAVING c > 0) SELECT c FROM y;
+WITH y(c) AS (SELECT SUM(a) AS c FROM (SELECT 1 AS a) AS x HAVING c > 0) SELECT c FROM y;
+
+WITH y(c) AS (SELECT SUM(a) as d FROM (SELECT 1 a) AS x HAVING c > 0) SELECT c FROM y;
+WITH y(c) AS (SELECT SUM(a) AS c FROM (SELECT 1 AS a) AS x HAVING c > 0) SELECT c FROM y;
+
+WITH x(c) AS (SELECT SUM(1) a HAVING c > 0 LIMIT 1) SELECT * FROM x;
+WITH x(c) AS (SELECT SUM(1) AS c HAVING c > 0 LIMIT 1) SELECT * FROM x;
+
+-- Invalid statement in Snowflake but checking more complex structures
+WITH x(c) AS ((SELECT 1 a) HAVING c > 0) SELECT * FROM x;
+WITH x(c) AS ((SELECT 1 AS a) HAVING c > 0) SELECT * FROM x;
+
+-- Invalid statement in Snowflake but checking more complex structures
+WITH x(c) AS ((SELECT SUM(1) a) HAVING c > 0 LIMIT 1) SELECT * FROM x;
+WITH x(c) AS ((SELECT SUM(1) AS a) HAVING c > 0 LIMIT 1) SELECT * FROM x;
+
+-- Invalid statement in Snowflake but checking that we don't fail
+WITH x(c) AS (SELECT SUM(a) FROM x HAVING c > 0 UNION ALL SELECT SUM(a) FROM y HAVING c > 0) SELECT * FROM x;
+WITH x(c) AS (SELECT SUM(a) FROM x HAVING c > 0 UNION ALL SELECT SUM(a) FROM y HAVING c > 0) SELECT * FROM x;
diff --git a/tests/fixtures/optimizer/pushdown_predicates.sql b/tests/fixtures/optimizer/pushdown_predicates.sql
index 43f9842..81b9acd 100644
--- a/tests/fixtures/optimizer/pushdown_predicates.sql
+++ b/tests/fixtures/optimizer/pushdown_predicates.sql
@@ -11,7 +11,7 @@ SELECT x.a FROM (SELECT * FROM x) AS x JOIN y WHERE (x.a = y.a AND x.a = 1 AND x
 SELECT x.a FROM (SELECT * FROM x) AS x JOIN y ON x.a = y.a WHERE TRUE;
 
 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.b;
-SELECT x.a FROM (SELECT * FROM x) AS x JOIN y ON x.a = y.a OR x.a = y.b WHERE (x.a = 1 AND x.a = y.a AND x.b = 1) OR x.a = y.b;
+SELECT x.a FROM (SELECT * FROM x) AS x JOIN y ON (x.a = 1 AND x.a = y.a AND x.b = 1) OR x.a = y.b WHERE (x.a = 1 AND x.a = y.a AND x.b = 1) OR x.a = y.b;
 
 SELECT x.a FROM (SELECT x.a AS a, x.b * 1 AS c FROM x) AS x WHERE x.c = 1;
 SELECT x.a FROM (SELECT x.a AS a, x.b * 1 AS c FROM x WHERE x.b * 1 = 1) AS x WHERE TRUE;
diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql
index 43127a9..5641ed4 100644
--- a/tests/fixtures/optimizer/qualify_columns.sql
+++ b/tests/fixtures/optimizer/qualify_columns.sql
@@ -153,7 +153,7 @@ SELECT AGGREGATE(ARRAY(x.a, x.b), 0, (x, acc) -> x + acc + x.a) AS sum_agg FROM
 # dialect: starrocks
 # execute: false
 SELECT DATE_TRUNC('week', a) AS a FROM x;
-SELECT DATE_TRUNC('week', x.a) AS a FROM x AS x;
+SELECT DATE_TRUNC('WEEK', x.a) AS a FROM x AS x;
 
 # dialect: bigquery
 # execute: false
@@ -276,6 +276,17 @@ SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0;
 SELECT * FROM x GROUP BY 1, 2;
 SELECT x.a AS a, x.b AS b FROM x AS x GROUP BY x.a, x.b;
 
+SELECT * FROM (SELECT * FROM x) AS s(a, b);
+SELECT s.a AS a, s.b AS b FROM (SELECT x.a AS a, x.b AS b FROM x AS x) AS s;
+
+# execute: false
+SELECT * FROM (SELECT * FROM t) AS s(a, b);
+SELECT s.a AS a, s.b AS b FROM (SELECT t.a AS a, t.b AS b FROM t AS t) AS s;
+
+# execute: false
+SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2) AS s(b);
+SELECT s.b AS b FROM (SELECT t1.b AS b FROM t1 AS t1 UNION ALL SELECT t2.b AS b FROM t2 AS t2) AS s;
+
 --------------------------------------
 -- CTEs
 --------------------------------------
@@ -430,6 +441,16 @@ SELECT t.aa AS aa FROM x AS x, UNNEST(x.a) AS t(aa);
 SELECT aa FROM x, UNNEST(a) AS aa;
 SELECT aa AS aa FROM x AS x, UNNEST(x.a) AS aa;
 
+# dialect: bigquery
+# execute: false
+select * from unnest ([1, 2]) as x with offset;
+SELECT x AS x, offset AS offset FROM UNNEST([1, 2]) AS x WITH OFFSET AS offset;
+
+# dialect: bigquery
+# execute: false
+select * from unnest ([1, 2]) as x with offset as y;
+SELECT x AS x, y AS y FROM UNNEST([1, 2]) AS x WITH OFFSET AS y;
+
 # dialect: presto
 SELECT x.a, i.b FROM x CROSS JOIN UNNEST(SPLIT(b, ',')) AS i(b);
 SELECT x.a AS a, i.b AS b FROM x AS x CROSS JOIN UNNEST(SPLIT(x.b, ',')) AS i(b);
diff --git a/tests/fixtures/optimizer/qualify_tables.sql b/tests/fixtures/optimizer/qualify_tables.sql
index 248d7e9..0ebfcd3 100644
--- a/tests/fixtures/optimizer/qualify_tables.sql
+++ b/tests/fixtures/optimizer/qualify_tables.sql
@@ -14,6 +14,11 @@ SELECT 1 FROM x.y.z AS z;
 SELECT 1 FROM x.y.z AS z;
 SELECT 1 FROM x.y.z AS z;
 
+# title: redshift unnest syntax, z.a should be a column, not a table
+# dialect: redshift
+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
 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;
@@ -26,6 +31,10 @@ 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;
 
+# title: pivoted table, pivot has alias
+SELECT * FROM x PIVOT (SUM(a) FOR b IN ('a', 'b')) AS piv;
+SELECT * FROM c.db.x AS x PIVOT(SUM(a) FOR b IN ('a', 'b')) AS piv;
+
 # title: wrapped table without alias
 SELECT * FROM (tbl);
 SELECT * FROM (c.db.tbl AS tbl);
diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql
index d3b03fb..a80be17 100644
--- a/tests/fixtures/optimizer/simplify.sql
+++ b/tests/fixtures/optimizer/simplify.sql
@@ -427,7 +427,7 @@ FALSE;
 TRUE;
 
 date '1998-12-01' - interval x day;
-CAST('1998-12-01' AS DATE) - INTERVAL x day;
+CAST('1998-12-01' AS DATE) - INTERVAL x DAY;
 
 date '1998-12-01' - interval '90' day;
 CAST('1998-09-02' AS DATE);
@@ -442,13 +442,13 @@ interval '1' year + date '1998-01-01' + 3 * 7 * 4;
 CAST('1999-01-01' AS DATE) + 84;
 
 date '1998-12-01' - interval '90' foo;
-CAST('1998-12-01' AS DATE) - INTERVAL '90' foo;
+CAST('1998-12-01' AS DATE) - INTERVAL '90' FOO;
 
 date '1998-12-01' + interval '90' foo;
-CAST('1998-12-01' AS DATE) + INTERVAL '90' foo;
+CAST('1998-12-01' AS DATE) + INTERVAL '90' FOO;
 
 CAST(x AS DATE) + interval '1' week;
-CAST(x AS DATE) + INTERVAL '1' week;
+CAST(x AS DATE) + INTERVAL '1' WEEK;
 
 CAST('2008-11-11' AS DATETIME) + INTERVAL '5' MONTH;
 CAST('2009-04-11 00:00:00' AS DATETIME);
@@ -456,8 +456,8 @@ CAST('2009-04-11 00:00:00' AS DATETIME);
 datetime '1998-12-01' - interval '90' day;
 CAST('1998-09-02 00:00:00' AS DATETIME);
 
-CAST(x AS DATETIME) + interval '1' week;
-CAST(x AS DATETIME) + INTERVAL '1' week;
+CAST(x AS DATETIME) + interval '1' WEEK;
+CAST(x AS DATETIME) + INTERVAL '1' WEEK;
 
 TS_OR_DS_TO_DATE('1998-12-01 00:00:01') - interval '90' day;
 CAST('1998-09-02' AS DATE);
@@ -844,14 +844,14 @@ x < CAST('2022-01-01' AS DATE) AND x >= CAST('2021-01-01' AS DATE);
 
 -- Always false, except for nulls
 DATE_TRUNC('quarter', x) = CAST('2021-01-02' AS DATE);
-DATE_TRUNC('quarter', x) = CAST('2021-01-02' AS DATE);
+DATE_TRUNC('QUARTER', x) = CAST('2021-01-02' AS DATE);
 
 DATE_TRUNC('year', x) <> CAST('2021-01-01' AS DATE);
 FALSE;
 
 -- Always true, except for nulls
 DATE_TRUNC('year', x) <> CAST('2021-01-02' AS DATE);
-DATE_TRUNC('year', x) <> CAST('2021-01-02' AS DATE);
+DATE_TRUNC('YEAR', x) <> CAST('2021-01-02' AS DATE);
 
 DATE_TRUNC('year', x) <= CAST('2021-01-01' AS DATE);
 x < CAST('2022-01-01' AS DATE);
@@ -884,11 +884,11 @@ DATE_TRUNC('year', x) > TS_OR_DS_TO_DATE(TS_OR_DS_TO_DATE('2021-01-02'));
 x >= CAST('2022-01-01' AS DATE);
 
 DATE_TRUNC('year', x) > TS_OR_DS_TO_DATE(TS_OR_DS_TO_DATE('2021-01-02', '%Y'));
-DATE_TRUNC('year', x) > TS_OR_DS_TO_DATE(TS_OR_DS_TO_DATE('2021-01-02', '%Y'));
+DATE_TRUNC('YEAR', x) > CAST(STR_TO_TIME('2021-01-02', '%Y') AS DATE);
 
 -- right is not a date
 DATE_TRUNC('year', x) <> '2021-01-02';
-DATE_TRUNC('year', x) <> '2021-01-02';
+DATE_TRUNC('YEAR', x) <> '2021-01-02';
 
 DATE_TRUNC('year', x) IN (CAST('2021-01-01' AS DATE), CAST('2023-01-01' AS DATE));
 (x < CAST('2022-01-01' AS DATE) AND x >= CAST('2021-01-01' AS DATE)) OR (x < CAST('2024-01-01' AS DATE) AND x >= CAST('2023-01-01' AS DATE));
@@ -906,7 +906,7 @@ x < CAST('2022-01-01 00:00:00' AS DATETIME) AND x >= CAST('2021-01-01 00:00:00'
 
 -- right side is not a date literal
 DATE_TRUNC('day', x) = CAST(y AS DATE);
-CAST(y AS DATE) = DATE_TRUNC('day', x);
+CAST(y AS DATE) = DATE_TRUNC('DAY', x);
 
 -- nested cast
 DATE_TRUNC('day', x) = CAST(CAST('2021-01-01 01:02:03' AS DATETIME) AS DATE);
@@ -973,7 +973,7 @@ DATE_ADD(DATE_ADD(DATE_TRUNC('week', DATE_SUB(x, 1, DAY)), 1, DAY), 1, YEAR) < C
 x < CAST('2020-01-14' AS DATE);
 
 x - INTERVAL '1' day = CAST(y AS DATE);
-CAST(y AS DATE) = x - INTERVAL '1' day;
+CAST(y AS DATE) = x - INTERVAL '1' DAY;
 
 --------------------------------------
 -- Constant Propagation
@@ -1094,3 +1094,33 @@ CASE WHEN x = y THEN z END;
 
 CASE x1 + x2 WHEN x3 THEN x4 WHEN x5 + x6 THEN x7 ELSE x8 END;
 CASE WHEN x3 = (x1 + x2) THEN x4 WHEN (x1 + x2) = (x5 + x6) THEN x7 ELSE x8 END;
+
+--------------------------------------
+-- Simplify STARTSWITH
+--------------------------------------
+STARTS_WITH('foo', 'f');
+TRUE;
+
+STARTS_WITH('foo', 'g');
+FALSE;
+
+STARTS_WITH('', 'f');
+FALSE;
+
+STARTS_WITH('', '');
+TRUE;
+
+STARTS_WITH('foo', '');
+TRUE;
+
+STARTS_WITH(NULL, y);
+STARTS_WITH(NULL, y);
+
+STARTS_WITH(x, y);
+STARTS_WITH(x, y);
+
+STARTS_WITH('x', y);
+STARTS_WITH('x', y);
+
+STARTS_WITH(x, 'y');
+STARTS_WITH(x, 'y');
diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
index f0f584f..d38c3cc 100644
--- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
+++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
@@ -527,10 +527,11 @@ SELECT
   "t_s_secyear"."customer_last_name" AS "customer_last_name",
   "t_s_secyear"."customer_preferred_cust_flag" AS "customer_preferred_cust_flag"
 FROM "year_total" AS "t_s_firstyear"
-JOIN "year_total" AS "t_c_secyear"
-  ON "t_c_secyear"."customer_id" = "t_s_firstyear"."customer_id"
-  AND "t_c_secyear"."dyear" = 2002
-  AND "t_c_secyear"."sale_type" = 'c'
+JOIN "year_total" AS "t_c_firstyear"
+  ON "t_c_firstyear"."customer_id" = "t_s_firstyear"."customer_id"
+  AND "t_c_firstyear"."dyear" = 2001
+  AND "t_c_firstyear"."sale_type" = 'c'
+  AND "t_c_firstyear"."year_total" > 0
 JOIN "year_total" AS "t_s_secyear"
   ON "t_s_firstyear"."customer_id" = "t_s_secyear"."customer_id"
   AND "t_s_secyear"."dyear" = 2002
@@ -540,15 +541,10 @@ JOIN "year_total" AS "t_w_firstyear"
   AND "t_w_firstyear"."dyear" = 2001
   AND "t_w_firstyear"."sale_type" = 'w'
   AND "t_w_firstyear"."year_total" > 0
-JOIN "year_total" AS "t_w_secyear"
-  ON "t_s_firstyear"."customer_id" = "t_w_secyear"."customer_id"
-  AND "t_w_secyear"."dyear" = 2002
-  AND "t_w_secyear"."sale_type" = 'w'
-JOIN "year_total" AS "t_c_firstyear"
-  ON "t_c_firstyear"."customer_id" = "t_s_firstyear"."customer_id"
-  AND "t_c_firstyear"."dyear" = 2001
-  AND "t_c_firstyear"."sale_type" = 'c'
-  AND "t_c_firstyear"."year_total" > 0
+JOIN "year_total" AS "t_c_secyear"
+  ON "t_c_secyear"."customer_id" = "t_s_firstyear"."customer_id"
+  AND "t_c_secyear"."dyear" = 2002
+  AND "t_c_secyear"."sale_type" = 'c'
   AND CASE
     WHEN "t_c_firstyear"."year_total" > 0
     THEN "t_c_secyear"."year_total" / "t_c_firstyear"."year_total"
@@ -558,6 +554,10 @@ JOIN "year_total" AS "t_c_firstyear"
     THEN "t_s_secyear"."year_total" / "t_s_firstyear"."year_total"
     ELSE NULL
   END
+JOIN "year_total" AS "t_w_secyear"
+  ON "t_s_firstyear"."customer_id" = "t_w_secyear"."customer_id"
+  AND "t_w_secyear"."dyear" = 2002
+  AND "t_w_secyear"."sale_type" = 'w'
   AND CASE
     WHEN "t_c_firstyear"."year_total" > 0
     THEN "t_c_secyear"."year_total" / "t_c_firstyear"."year_total"
@@ -1787,6 +1787,10 @@ SELECT
   "t_s_secyear"."customer_last_name" AS "customer_last_name",
   "t_s_secyear"."customer_birth_country" AS "customer_birth_country"
 FROM "year_total" AS "t_s_firstyear"
+JOIN "year_total" AS "t_s_secyear"
+  ON "t_s_firstyear"."customer_id" = "t_s_secyear"."customer_id"
+  AND "t_s_secyear"."dyear" = 2002
+  AND "t_s_secyear"."sale_type" = 's'
 JOIN "year_total" AS "t_w_firstyear"
   ON "t_s_firstyear"."customer_id" = "t_w_firstyear"."customer_id"
   AND "t_w_firstyear"."dyear" = 2001
@@ -1796,10 +1800,6 @@ JOIN "year_total" AS "t_w_secyear"
   ON "t_s_firstyear"."customer_id" = "t_w_secyear"."customer_id"
   AND "t_w_secyear"."dyear" = 2002
   AND "t_w_secyear"."sale_type" = 'w'
-JOIN "year_total" AS "t_s_secyear"
-  ON "t_s_firstyear"."customer_id" = "t_s_secyear"."customer_id"
-  AND "t_s_secyear"."dyear" = 2002
-  AND "t_s_secyear"."sale_type" = 's'
   AND CASE
     WHEN "t_s_firstyear"."year_total" > 0
     THEN "t_s_secyear"."year_total" / "t_s_firstyear"."year_total"
@@ -1934,7 +1934,7 @@ SELECT
   AVG("store_sales"."ss_ext_wholesale_cost") AS "_col_2",
   SUM("store_sales"."ss_ext_wholesale_cost") AS "_col_3"
 FROM "store_sales" AS "store_sales"
-CROSS JOIN "customer_demographics" AS "customer_demographics"
+CROSS JOIN "household_demographics" AS "household_demographics"
 JOIN "customer_address" AS "customer_address"
   ON (
     "customer_address"."ca_address_sk" = "store_sales"."ss_addr_sk"
@@ -1957,10 +1957,7 @@ JOIN "customer_address" AS "customer_address"
     AND "store_sales"."ss_net_profit" <= 300
     AND "store_sales"."ss_net_profit" >= 150
   )
-JOIN "date_dim" AS "date_dim"
-  ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
-  AND "date_dim"."d_year" = 2001
-JOIN "household_demographics" AS "household_demographics"
+JOIN "customer_demographics" AS "customer_demographics"
   ON (
     "customer_demographics"."cd_demo_sk" = "store_sales"."ss_cdemo_sk"
     AND "customer_demographics"."cd_education_status" = 'Advanced Degree'
@@ -1988,6 +1985,9 @@ JOIN "household_demographics" AS "household_demographics"
     AND "store_sales"."ss_sales_price" <= 200.00
     AND "store_sales"."ss_sales_price" >= 150.00
   )
+JOIN "date_dim" AS "date_dim"
+  ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+  AND "date_dim"."d_year" = 2001
 JOIN "store" AS "store"
   ON "store"."s_store_sk" = "store_sales"."ss_store_sk";
 
@@ -2343,16 +2343,18 @@ SELECT
   "customer_address"."ca_zip" AS "ca_zip",
   SUM("catalog_sales"."cs_sales_price") AS "_col_1"
 FROM "catalog_sales" AS "catalog_sales"
-JOIN "customer_address" AS "customer_address"
-  ON "catalog_sales"."cs_sales_price" > 500
-  OR "customer_address"."ca_state" IN ('CA', 'WA', 'GA')
-  OR SUBSTR("customer_address"."ca_zip", 1, 5) IN ('85669', '86197', '88274', '83405', '86475', '85392', '85460', '80348', '81792')
+JOIN "customer" AS "customer"
+  ON "catalog_sales"."cs_bill_customer_sk" = "customer"."c_customer_sk"
 JOIN "date_dim" AS "date_dim"
   ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   AND "date_dim"."d_qoy" = 1
   AND "date_dim"."d_year" = 1998
-JOIN "customer" AS "customer"
-  ON "catalog_sales"."cs_bill_customer_sk" = "customer"."c_customer_sk"
+JOIN "customer_address" AS "customer_address"
+  ON (
+    "catalog_sales"."cs_sales_price" > 500
+    OR "customer_address"."ca_state" IN ('CA', 'WA', 'GA')
+    OR SUBSTR("customer_address"."ca_zip", 1, 5) IN ('85669', '86197', '88274', '83405', '86475', '85392', '85460', '80348', '81792')
+  )
   AND "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
 GROUP BY
   "customer_address"."ca_zip"
@@ -2428,7 +2430,7 @@ JOIN "date_dim" AS "date_dim"
   ON "cs1"."cs_ship_date_sk" = "date_dim"."d_date_sk"
   AND "date_dim"."d_date" >= '2002-3-01'
   AND (
-    CAST('2002-3-01' AS DATE) + INTERVAL '60' day
+    CAST('2002-3-01' AS DATE) + INTERVAL '60' DAY
   ) >= CAST("date_dim"."d_date" AS DATE)
 WHERE
   "_u_3"."_u_4" IS NULL
@@ -2669,15 +2671,15 @@ SELECT
 FROM "date_dim" AS "date_dim"
 JOIN "store_sales" AS "store_sales"
   ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+JOIN "customer" AS "customer"
+  ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk"
 JOIN "item" AS "item"
   ON "item"."i_item_sk" = "store_sales"."ss_item_sk" AND "item"."i_manager_id" = 38
-JOIN "store" AS "store"
-  ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
 JOIN "customer_address" AS "customer_address"
-  ON SUBSTR("customer_address"."ca_zip", 1, 5) <> SUBSTR("store"."s_zip", 1, 5)
-JOIN "customer" AS "customer"
   ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
-  AND "customer"."c_customer_sk" = "store_sales"."ss_customer_sk"
+JOIN "store" AS "store"
+  ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
+  AND SUBSTR("customer_address"."ca_zip", 1, 5) <> SUBSTR("store"."s_zip", 1, 5)
 WHERE
   "date_dim"."d_moy" = 12 AND "date_dim"."d_year" = 1998
 GROUP BY
@@ -3109,6 +3111,8 @@ WITH "ssales" AS (
     "item"."i_color" AS "i_color",
     SUM("store_sales"."ss_net_profit") AS "netpaid"
   FROM "store_sales" AS "store_sales"
+  JOIN "customer" AS "customer"
+    ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk"
   JOIN "item" AS "item"
     ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
   JOIN "store" AS "store"
@@ -3117,10 +3121,8 @@ WITH "ssales" AS (
     ON "store_returns"."sr_item_sk" = "store_sales"."ss_item_sk"
     AND "store_returns"."sr_ticket_number" = "store_sales"."ss_ticket_number"
   JOIN "customer_address" AS "customer_address"
-    ON "customer_address"."ca_zip" = "store"."s_zip"
-  JOIN "customer" AS "customer"
     ON "customer"."c_birth_country" = UPPER("customer_address"."ca_country")
-    AND "customer"."c_customer_sk" = "store_sales"."ss_customer_sk"
+    AND "customer_address"."ca_zip" = "store"."s_zip"
   GROUP BY
     "customer"."c_last_name",
     "customer"."c_first_name",
@@ -3850,6 +3852,8 @@ JOIN "ss" AS "ss2"
   ON "ss1"."ca_county" = "ss2"."ca_county" AND "ss2"."d_qoy" = 2 AND "ss2"."d_year" = 2001
 JOIN "ws" AS "ws1"
   ON "ss1"."ca_county" = "ws1"."ca_county" AND "ws1"."d_qoy" = 1 AND "ws1"."d_year" = 2001
+JOIN "ss" AS "ss3"
+  ON "ss2"."ca_county" = "ss3"."ca_county" AND "ss3"."d_qoy" = 3 AND "ss3"."d_year" = 2001
 JOIN "ws" AS "ws2"
   ON "ws1"."ca_county" = "ws2"."ca_county"
   AND "ws2"."d_qoy" = 2
@@ -3864,11 +3868,9 @@ JOIN "ws" AS "ws2"
     ELSE NULL
   END
 JOIN "ws" AS "ws3"
-  ON "ws1"."ca_county" = "ws3"."ca_county" AND "ws3"."d_qoy" = 3 AND "ws3"."d_year" = 2001
-JOIN "ss" AS "ss3"
-  ON "ss2"."ca_county" = "ss3"."ca_county"
-  AND "ss3"."d_qoy" = 3
-  AND "ss3"."d_year" = 2001
+  ON "ws1"."ca_county" = "ws3"."ca_county"
+  AND "ws3"."d_qoy" = 3
+  AND "ws3"."d_year" = 2001
   AND CASE
     WHEN "ss2"."store_sales" > 0
     THEN "ss3"."store_sales" / "ss2"."store_sales"
@@ -7013,22 +7015,22 @@ SELECT
     "ss_items"."ss_item_rev" + "cs_items"."cs_item_rev" + "ws_items"."ws_item_rev"
   ) / 3 AS "average"
 FROM "ss_items"
-JOIN "ws_items"
-  ON "ss_items"."item_id" = "ws_items"."item_id"
-  AND "ss_items"."ss_item_rev" <= 1.1 * "ws_items"."ws_item_rev"
-  AND "ss_items"."ss_item_rev" >= 0.9 * "ws_items"."ws_item_rev"
-  AND "ws_items"."ws_item_rev" <= 1.1 * "ss_items"."ss_item_rev"
-  AND "ws_items"."ws_item_rev" >= 0.9 * "ss_items"."ss_item_rev"
 JOIN "cs_items"
   ON "cs_items"."cs_item_rev" <= 1.1 * "ss_items"."ss_item_rev"
-  AND "cs_items"."cs_item_rev" <= 1.1 * "ws_items"."ws_item_rev"
   AND "cs_items"."cs_item_rev" >= 0.9 * "ss_items"."ss_item_rev"
-  AND "cs_items"."cs_item_rev" >= 0.9 * "ws_items"."ws_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"
+  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"
+  AND "ss_items"."ss_item_rev" <= 1.1 * "ws_items"."ws_item_rev"
+  AND "ss_items"."ss_item_rev" >= 0.9 * "ws_items"."ws_item_rev"
   AND "ws_items"."ws_item_rev" <= 1.1 * "cs_items"."cs_item_rev"
+  AND "ws_items"."ws_item_rev" <= 1.1 * "ss_items"."ss_item_rev"
   AND "ws_items"."ws_item_rev" >= 0.9 * "cs_items"."cs_item_rev"
+  AND "ws_items"."ws_item_rev" >= 0.9 * "ss_items"."ss_item_rev"
 ORDER BY
   "item_id",
   "ss_item_rev"
@@ -7915,17 +7917,18 @@ WITH "cs_ui" AS (
     SUM("store_sales"."ss_list_price") AS "s2",
     SUM("store_sales"."ss_coupon_amt") AS "s3"
   FROM "store_sales" AS "store_sales"
-  CROSS JOIN "income_band" AS "ib2"
   JOIN "customer_address" AS "ad1"
     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"
     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"
   JOIN "date_dim" AS "d1"
     ON "d1"."d_date_sk" = "store_sales"."ss_sold_date_sk"
   JOIN "household_demographics" AS "hd1"
     ON "hd1"."hd_demo_sk" = "store_sales"."ss_hdemo_sk"
-  JOIN "household_demographics" AS "hd2"
-    ON "hd2"."hd_income_band_sk" = "ib2"."ib_income_band_sk"
   JOIN "item" AS "item"
     ON "item"."i_color" IN ('cyan', 'peach', 'blush', 'frosted', 'powder', 'orange')
     AND "item"."i_current_price" <= 68
@@ -7938,22 +7941,21 @@ WITH "cs_ui" AS (
   JOIN "store_returns" AS "store_returns"
     ON "store_returns"."sr_item_sk" = "store_sales"."ss_item_sk"
     AND "store_returns"."sr_ticket_number" = "store_sales"."ss_ticket_number"
-  JOIN "customer" AS "customer"
-    ON "customer"."c_current_hdemo_sk" = "hd2"."hd_demo_sk"
-    AND "customer"."c_customer_sk" = "store_sales"."ss_customer_sk"
-  JOIN "income_band" AS "ib1"
-    ON "hd1"."hd_income_band_sk" = "ib1"."ib_income_band_sk"
   JOIN "customer_address" AS "ad2"
     ON "ad2"."ca_address_sk" = "customer"."c_current_addr_sk"
   JOIN "customer_demographics" AS "cd2"
-    ON "cd2"."cd_demo_sk" = "customer"."c_current_cdemo_sk"
+    ON "cd1"."cd_marital_status" <> "cd2"."cd_marital_status"
+    AND "cd2"."cd_demo_sk" = "customer"."c_current_cdemo_sk"
   JOIN "date_dim" AS "d2"
     ON "customer"."c_first_sales_date_sk" = "d2"."d_date_sk"
   JOIN "date_dim" AS "d3"
     ON "customer"."c_first_shipto_date_sk" = "d3"."d_date_sk"
-  JOIN "customer_demographics" AS "cd1"
-    ON "cd1"."cd_demo_sk" = "store_sales"."ss_cdemo_sk"
-    AND "cd1"."cd_marital_status" <> "cd2"."cd_marital_status"
+  JOIN "household_demographics" AS "hd2"
+    ON "customer"."c_current_hdemo_sk" = "hd2"."hd_demo_sk"
+  JOIN "income_band" AS "ib1"
+    ON "hd1"."hd_income_band_sk" = "ib1"."ib_income_band_sk"
+  JOIN "income_band" AS "ib2"
+    ON "hd2"."hd_income_band_sk" = "ib2"."ib_income_band_sk"
   GROUP BY
     "item"."i_product_name",
     "item"."i_item_sk",
@@ -9523,8 +9525,8 @@ LEFT JOIN "catalog_returns" AS "catalog_returns"
 JOIN "customer_demographics" AS "customer_demographics"
   ON "catalog_sales"."cs_bill_cdemo_sk" = "customer_demographics"."cd_demo_sk"
   AND "customer_demographics"."cd_marital_status" = 'M'
-JOIN "date_dim" AS "d3"
-  ON "catalog_sales"."cs_ship_date_sk" = "d3"."d_date_sk"
+JOIN "date_dim" AS "d1"
+  ON "catalog_sales"."cs_sold_date_sk" = "d1"."d_date_sk" AND "d1"."d_year" = 2002
 JOIN "household_demographics" AS "household_demographics"
   ON "catalog_sales"."cs_bill_hdemo_sk" = "household_demographics"."hd_demo_sk"
   AND "household_demographics"."hd_buy_potential" = '501-1000'
@@ -9536,14 +9538,13 @@ JOIN "item" AS "item"
 LEFT JOIN "promotion" AS "promotion"
   ON "catalog_sales"."cs_promo_sk" = "promotion"."p_promo_sk"
 JOIN "date_dim" AS "d2"
-  ON "d2"."d_date_sk" = "inventory"."inv_date_sk"
+  ON "d1"."d_week_seq" = "d2"."d_week_seq"
+  AND "d2"."d_date_sk" = "inventory"."inv_date_sk"
+JOIN "date_dim" AS "d3"
+  ON "catalog_sales"."cs_ship_date_sk" = "d3"."d_date_sk"
+  AND "d3"."d_date" > "d1"."d_date" + INTERVAL '5' DAY
 JOIN "warehouse" AS "warehouse"
   ON "inventory"."inv_warehouse_sk" = "warehouse"."w_warehouse_sk"
-JOIN "date_dim" AS "d1"
-  ON "catalog_sales"."cs_sold_date_sk" = "d1"."d_date_sk"
-  AND "d1"."d_week_seq" = "d2"."d_week_seq"
-  AND "d1"."d_year" = 2002
-  AND "d3"."d_date" > "d1"."d_date" + INTERVAL '5' day
 GROUP BY
   "item"."i_item_desc",
   "warehouse"."w_warehouse_name",
@@ -9769,6 +9770,10 @@ SELECT
   "t_s_secyear"."customer_first_name" AS "customer_first_name",
   "t_s_secyear"."customer_last_name" AS "customer_last_name"
 FROM "year_total" AS "t_s_firstyear"
+JOIN "year_total" AS "t_s_secyear"
+  ON "t_s_firstyear"."customer_id" = "t_s_secyear"."customer_id"
+  AND "t_s_secyear"."sale_type" = 's'
+  AND "t_s_secyear"."year1" = 2000
 JOIN "year_total" AS "t_w_firstyear"
   ON "t_s_firstyear"."customer_id" = "t_w_firstyear"."customer_id"
   AND "t_w_firstyear"."sale_type" = 'w'
@@ -9778,10 +9783,6 @@ JOIN "year_total" AS "t_w_secyear"
   ON "t_s_firstyear"."customer_id" = "t_w_secyear"."customer_id"
   AND "t_w_secyear"."sale_type" = 'w'
   AND "t_w_secyear"."year1" = 2000
-JOIN "year_total" AS "t_s_secyear"
-  ON "t_s_firstyear"."customer_id" = "t_s_secyear"."customer_id"
-  AND "t_s_secyear"."sale_type" = 's'
-  AND "t_s_secyear"."year1" = 2000
   AND CASE
     WHEN "t_s_firstyear"."year_total" > 0
     THEN "t_s_secyear"."year_total" / "t_s_firstyear"."year_total"
@@ -11369,8 +11370,8 @@ JOIN "web_page" AS "web_page"
 JOIN "web_returns" AS "web_returns"
   ON "web_returns"."wr_item_sk" = "web_sales"."ws_item_sk"
   AND "web_returns"."wr_order_number" = "web_sales"."ws_order_number"
-JOIN "customer_demographics" AS "cd2"
-  ON "cd2"."cd_demo_sk" = "web_returns"."wr_returning_cdemo_sk"
+JOIN "customer_demographics" AS "cd1"
+  ON "cd1"."cd_demo_sk" = "web_returns"."wr_refunded_cdemo_sk"
 JOIN "customer_address" AS "customer_address"
   ON "customer_address"."ca_address_sk" = "web_returns"."wr_refunded_addr_sk"
   AND (
@@ -11395,8 +11396,8 @@ JOIN "customer_address" AS "customer_address"
   )
 JOIN "reason" AS "reason"
   ON "reason"."r_reason_sk" = "web_returns"."wr_reason_sk"
-JOIN "customer_demographics" AS "cd1"
-  ON "cd1"."cd_demo_sk" = "web_returns"."wr_refunded_cdemo_sk"
+JOIN "customer_demographics" AS "cd2"
+  ON "cd2"."cd_demo_sk" = "web_returns"."wr_returning_cdemo_sk"
   AND (
     (
       "cd1"."cd_education_status" = "cd2"."cd_education_status"
@@ -12032,13 +12033,14 @@ SELECT
   "call_center"."cc_manager" AS "manager",
   SUM("catalog_returns"."cr_net_loss") AS "returns_loss"
 FROM "call_center" AS "call_center"
-JOIN "household_demographics" AS "household_demographics"
-  ON "household_demographics"."hd_buy_potential" LIKE 'Unknown%'
-JOIN "customer" AS "customer"
-  ON "customer"."c_current_hdemo_sk" = "household_demographics"."hd_demo_sk"
 JOIN "catalog_returns" AS "catalog_returns"
   ON "call_center"."cc_call_center_sk" = "catalog_returns"."cr_call_center_sk"
-  AND "catalog_returns"."cr_returning_customer_sk" = "customer"."c_customer_sk"
+JOIN "customer" AS "customer"
+  ON "catalog_returns"."cr_returning_customer_sk" = "customer"."c_customer_sk"
+JOIN "date_dim" AS "date_dim"
+  ON "catalog_returns"."cr_returned_date_sk" = "date_dim"."d_date_sk"
+  AND "date_dim"."d_moy" = 12
+  AND "date_dim"."d_year" = 1999
 JOIN "customer_address" AS "customer_address"
   ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
   AND "customer_address"."ca_gmt_offset" = -7
@@ -12060,10 +12062,9 @@ JOIN "customer_demographics" AS "customer_demographics"
     "customer_demographics"."cd_marital_status" = 'M'
     OR "customer_demographics"."cd_marital_status" = 'W'
   )
-JOIN "date_dim" AS "date_dim"
-  ON "catalog_returns"."cr_returned_date_sk" = "date_dim"."d_date_sk"
-  AND "date_dim"."d_moy" = 12
-  AND "date_dim"."d_year" = 1999
+JOIN "household_demographics" AS "household_demographics"
+  ON "customer"."c_current_hdemo_sk" = "household_demographics"."hd_demo_sk"
+  AND "household_demographics"."hd_buy_potential" LIKE 'Unknown%'
 GROUP BY
   "call_center"."cc_call_center_id",
   "call_center"."cc_name",
@@ -12249,7 +12250,7 @@ JOIN "date_dim" AS "date_dim"
   ON "date_dim"."d_date" >= '2000-3-01'
   AND "date_dim"."d_date_sk" = "ws1"."ws_ship_date_sk"
   AND (
-    CAST('2000-3-01' AS DATE) + INTERVAL '60' day
+    CAST('2000-3-01' AS DATE) + INTERVAL '60' DAY
   ) >= CAST("date_dim"."d_date" AS DATE)
 JOIN "web_site" AS "web_site"
   ON "web_site"."web_company_name" = 'pri'
@@ -12339,7 +12340,7 @@ JOIN "date_dim" AS "date_dim"
   ON "date_dim"."d_date" >= '2000-4-01'
   AND "date_dim"."d_date_sk" = "ws1"."ws_ship_date_sk"
   AND (
-    CAST('2000-4-01' AS DATE) + INTERVAL '60' day
+    CAST('2000-4-01' AS DATE) + INTERVAL '60' DAY
   ) >= CAST("date_dim"."d_date" AS DATE)
 JOIN "web_site" AS "web_site"
   ON "web_site"."web_company_name" = 'pri'
diff --git a/tests/fixtures/optimizer/tpc-h/tpc-h.sql b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
index 660b565..36f096c 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 "region_2" AS (
+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 (
   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,16 +136,16 @@ SELECT
   "supplier"."s_phone" AS "s_phone",
   "supplier"."s_comment" AS "s_comment"
 FROM "part" AS "part"
-CROSS JOIN "region_2" AS "region"
 LEFT JOIN "_u_0" AS "_u_0"
   ON "_u_0"."_u_1" = "part"."p_partkey"
-JOIN "nation" AS "nation"
-  ON "nation"."n_regionkey" = "region"."r_regionkey"
 JOIN "partsupp_2" AS "partsupp"
   ON "part"."p_partkey" = "partsupp"."ps_partkey"
 JOIN "supplier" AS "supplier"
+  ON "partsupp"."ps_suppkey" = "supplier"."s_suppkey"
+JOIN "nation" AS "nation"
   ON "nation"."n_nationkey" = "supplier"."s_nationkey"
-  AND "partsupp"."ps_suppkey" = "supplier"."s_suppkey"
+JOIN "region_2" AS "region"
+  ON "nation"."n_regionkey" = "region"."r_regionkey"
 WHERE
   "_u_0"."_col_0" = "partsupp"."ps_supplycost"
   AND "part"."p_size" = 15
@@ -294,10 +294,10 @@ JOIN "orders" AS "orders"
   ON "customer"."c_custkey" = "orders"."o_custkey"
   AND CAST("orders"."o_orderdate" AS DATE) < CAST('1995-01-01' AS DATE)
   AND CAST("orders"."o_orderdate" AS DATE) >= CAST('1994-01-01' AS DATE)
-JOIN "supplier" AS "supplier"
-  ON "customer"."c_nationkey" = "supplier"."s_nationkey"
 JOIN "lineitem" AS "lineitem"
   ON "lineitem"."l_orderkey" = "orders"."o_orderkey"
+JOIN "supplier" AS "supplier"
+  ON "customer"."c_nationkey" = "supplier"."s_nationkey"
   AND "lineitem"."l_suppkey" = "supplier"."s_suppkey"
 JOIN "nation" AS "nation"
   ON "nation"."n_nationkey" = "supplier"."s_nationkey"
@@ -389,8 +389,13 @@ JOIN "nation" AS "n1"
     "n1"."n_name" = 'FRANCE' OR "n1"."n_name" = 'GERMANY'
   )
   AND "n1"."n_nationkey" = "supplier"."s_nationkey"
+JOIN "orders" AS "orders"
+  ON "lineitem"."l_orderkey" = "orders"."o_orderkey"
+JOIN "customer" AS "customer"
+  ON "customer"."c_custkey" = "orders"."o_custkey"
 JOIN "nation" AS "n2"
-  ON (
+  ON "customer"."c_nationkey" = "n2"."n_nationkey"
+  AND (
     "n1"."n_name" = 'FRANCE' OR "n2"."n_name" = 'FRANCE'
   )
   AND (
@@ -399,11 +404,6 @@ JOIN "nation" AS "n2"
   AND (
     "n2"."n_name" = 'FRANCE' OR "n2"."n_name" = 'GERMANY'
   )
-JOIN "customer" AS "customer"
-  ON "customer"."c_nationkey" = "n2"."n_nationkey"
-JOIN "orders" AS "orders"
-  ON "customer"."c_custkey" = "orders"."o_custkey"
-  AND "lineitem"."l_orderkey" = "orders"."o_orderkey"
 GROUP BY
   "n1"."n_name",
   "n2"."n_name",
@@ -467,23 +467,22 @@ SELECT
     1 - "lineitem"."l_discount"
   )) AS "mkt_share"
 FROM "part" AS "part"
-JOIN "region" AS "region"
-  ON "region"."r_name" = 'AMERICA'
 JOIN "lineitem" AS "lineitem"
   ON "lineitem"."l_partkey" = "part"."p_partkey"
-JOIN "nation" AS "n1"
-  ON "n1"."n_regionkey" = "region"."r_regionkey"
-JOIN "customer" AS "customer"
-  ON "customer"."c_nationkey" = "n1"."n_nationkey"
+JOIN "orders" AS "orders"
+  ON "lineitem"."l_orderkey" = "orders"."o_orderkey"
+  AND CAST("orders"."o_orderdate" AS DATE) <= CAST('1996-12-31' AS DATE)
+  AND CAST("orders"."o_orderdate" AS DATE) >= CAST('1995-01-01' AS DATE)
 JOIN "supplier" AS "supplier"
   ON "lineitem"."l_suppkey" = "supplier"."s_suppkey"
+JOIN "customer" AS "customer"
+  ON "customer"."c_custkey" = "orders"."o_custkey"
 JOIN "nation" AS "n2"
   ON "n2"."n_nationkey" = "supplier"."s_nationkey"
-JOIN "orders" AS "orders"
-  ON "customer"."c_custkey" = "orders"."o_custkey"
-  AND "lineitem"."l_orderkey" = "orders"."o_orderkey"
-  AND CAST("orders"."o_orderdate" AS DATE) <= CAST('1996-12-31' AS DATE)
-  AND CAST("orders"."o_orderdate" AS DATE) >= CAST('1995-01-01' AS DATE)
+JOIN "nation" AS "n1"
+  ON "customer"."c_nationkey" = "n1"."n_nationkey"
+JOIN "region" AS "region"
+  ON "n1"."n_regionkey" = "region"."r_regionkey" AND "region"."r_name" = 'AMERICA'
 WHERE
   "part"."p_type" = 'ECONOMY ANODIZED STEEL'
 GROUP BY
@@ -1126,6 +1125,10 @@ FROM "lineitem" AS "lineitem"
 JOIN "part" AS "part"
   ON (
     "lineitem"."l_partkey" = "part"."p_partkey"
+    AND "lineitem"."l_quantity" <= 11
+    AND "lineitem"."l_quantity" >= 1
+    AND "lineitem"."l_shipinstruct" = 'DELIVER IN PERSON'
+    AND "lineitem"."l_shipmode" IN ('AIR', 'AIR REG')
     AND "part"."p_brand" = 'Brand#12'
     AND "part"."p_container" IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
     AND "part"."p_size" <= 5
@@ -1133,6 +1136,10 @@ JOIN "part" AS "part"
   )
   OR (
     "lineitem"."l_partkey" = "part"."p_partkey"
+    AND "lineitem"."l_quantity" <= 20
+    AND "lineitem"."l_quantity" >= 10
+    AND "lineitem"."l_shipinstruct" = 'DELIVER IN PERSON'
+    AND "lineitem"."l_shipmode" IN ('AIR', 'AIR REG')
     AND "part"."p_brand" = 'Brand#23'
     AND "part"."p_container" IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
     AND "part"."p_size" <= 10
@@ -1140,6 +1147,10 @@ JOIN "part" AS "part"
   )
   OR (
     "lineitem"."l_partkey" = "part"."p_partkey"
+    AND "lineitem"."l_quantity" <= 30
+    AND "lineitem"."l_quantity" >= 20
+    AND "lineitem"."l_shipinstruct" = 'DELIVER IN PERSON'
+    AND "lineitem"."l_shipmode" IN ('AIR', 'AIR REG')
     AND "part"."p_brand" = 'Brand#34'
     AND "part"."p_container" IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
     AND "part"."p_size" <= 15