7 files changed, 107 insertions, 32 deletions
diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql
index e6a6e6b..beb5703 100644
--- a/tests/fixtures/identity.sql
+++ b/tests/fixtures/identity.sql
@@ -89,6 +89,7 @@ POSEXPLODE("x") AS ("a", "b")
 POSEXPLODE("x") AS ("a", "b", "c")
 STR_POSITION(x, 'a')
 STR_POSITION(x, 'a', 3)
+LEVENSHTEIN('gumbo', 'gambol', 2, 1, 1)
 SPLIT(SPLIT(referrer, 'utm_source=')[OFFSET(1)], "&")[OFFSET(0)]
 x[ORDINAL(1)][SAFE_OFFSET(2)]
 x LIKE SUBSTR('abc', 1, 1)
@@ -425,6 +426,7 @@ SELECT SUM(x) OVER (PARTITION BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT
 SELECT SUM(x) OVER (PARTITION BY a RANGE BETWEEN 1 AND 3)
 SELECT SUM(x) OVER (PARTITION BY a RANGE BETWEEN 1 FOLLOWING AND 3)
 SELECT SUM(x) OVER (PARTITION BY a RANGE BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)
+SELECT AVG(x) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t
 SELECT LISTAGG(x) WITHIN GROUP (ORDER BY x) AS y
 SELECT LISTAGG(x) WITHIN GROUP (ORDER BY x DESC)
 SELECT SUM(x) FILTER(WHERE x > 1)
@@ -450,14 +452,24 @@ SELECT student, score FROM tests CROSS JOIN UNNEST(scores) AS t(a, b)
 SELECT student, score FROM tests CROSS JOIN UNNEST(scores) WITH ORDINALITY AS t(a, b)
 SELECT student, score FROM tests CROSS JOIN UNNEST(x.scores) AS t(score)
 SELECT student, score FROM tests CROSS JOIN UNNEST(ARRAY(x.scores)) AS t(score)
+SELECT * FROM t WITH (TABLOCK, INDEX(myindex))
+SELECT * FROM t WITH (NOWAIT)
+CREATE TABLE foo AS (SELECT 1) UNION ALL (SELECT 2)
 CREATE TABLE foo (id INT PRIMARY KEY ASC)
 CREATE TABLE a.b AS SELECT 1
+CREATE TABLE a.b AS SELECT 1 WITH DATA AND STATISTICS
+CREATE TABLE a.b AS SELECT 1 WITH NO DATA AND NO STATISTICS
+CREATE TABLE a.b AS (SELECT 1) NO PRIMARY INDEX
+CREATE TABLE a.b AS (SELECT 1) UNIQUE PRIMARY INDEX index1 (a) UNIQUE INDEX index2 (b)
+CREATE TABLE a.b AS (SELECT 1) PRIMARY AMP INDEX index1 (a) UNIQUE INDEX index2 (b)
 CREATE TABLE a.b AS SELECT a FROM a.c
 CREATE TABLE IF NOT EXISTS x AS SELECT a FROM d
 CREATE TEMPORARY TABLE x AS SELECT a FROM d
 CREATE TEMPORARY TABLE IF NOT EXISTS x AS SELECT a FROM d
 CREATE VIEW x AS SELECT a FROM b
 CREATE VIEW IF NOT EXISTS x AS SELECT a FROM b
+CREATE VIEW z (a, b COMMENT 'b', c COMMENT 'c') AS SELECT a, b, c FROM d
+CREATE VIEW IF NOT EXISTS z (a, b COMMENT 'b', c COMMENT 'c') AS SELECT a, b, c FROM d
 CREATE OR REPLACE VIEW x AS SELECT *
 CREATE OR REPLACE TEMPORARY VIEW x AS SELECT *
 CREATE TEMPORARY VIEW x AS SELECT a FROM d
@@ -490,6 +502,8 @@ CREATE TABLE z (a INT UNIQUE AUTO_INCREMENT)
 CREATE TABLE z (a INT REFERENCES parent(b, c))
 CREATE TABLE z (a INT PRIMARY KEY, b INT REFERENCES foo(id))
 CREATE TABLE z (a INT, FOREIGN KEY (a) REFERENCES parent(b, c))
+CREATE VIEW z (a, b)
+CREATE VIEW z (a, b COMMENT 'b', c COMMENT 'c')
 CREATE TEMPORARY FUNCTION f
 CREATE TEMPORARY FUNCTION f AS 'g'
 CREATE FUNCTION f
@@ -559,6 +573,7 @@ INSERT INTO x.z IF EXISTS SELECT * FROM y
 INSERT INTO x VALUES (1, 'a', 2.0)
 INSERT INTO x VALUES (1, 'a', 2.0), (1, 'a', 3.0), (X(), y[1], z.x)
 INSERT INTO y (a, b, c) SELECT a, b, c FROM x
+INSERT INTO y (SELECT 1) UNION (SELECT 2)
 INSERT OVERWRITE TABLE x IF EXISTS SELECT * FROM y
 INSERT OVERWRITE TABLE a.b IF EXISTS SELECT * FROM y
 INSERT OVERWRITE DIRECTORY 'x' SELECT 1
@@ -627,3 +642,4 @@ ALTER TABLE integers ALTER COLUMN i SET DEFAULT 10
 ALTER TABLE integers ALTER COLUMN i DROP DEFAULT
 ALTER TABLE mydataset.mytable DROP COLUMN A, DROP COLUMN IF EXISTS B
 ALTER TABLE mydataset.mytable ADD COLUMN A TEXT, ADD COLUMN IF NOT EXISTS B INT
+SELECT div.a FROM test_table AS div
diff --git a/tests/fixtures/optimizer/merge_subqueries.sql b/tests/fixtures/optimizer/merge_subqueries.sql
index 4a3ad4b..4c06e42 100644
--- a/tests/fixtures/optimizer/merge_subqueries.sql
+++ b/tests/fixtures/optimizer/merge_subqueries.sql
@@ -311,3 +311,42 @@ FROM
   ON
     t1.cola = t2.cola;
 SELECT /*+ BROADCAST(a2) */ a1.cola AS cola, a2.cola AS cola FROM VALUES (1) AS a1(cola) JOIN VALUES (1) AS a2(cola) ON a1.cola = a2.cola;
+
+# title: Nested subquery selects from same table as another subquery
+WITH i AS (
+  SELECT
+    x.a AS a
+  FROM x AS x
+), j AS (
+  SELECT
+    x.a,
+    x.b
+  FROM x AS x
+), k AS (
+  SELECT
+    j.a,
+    j.b
+  FROM j AS j
+)
+SELECT
+  i.a,
+  k.b
+FROM i AS i
+LEFT JOIN k AS k
+ON  i.a = k.a;
+SELECT x.a AS a, x_2.b AS b FROM x AS x LEFT JOIN x AS x_2 ON x.a = x_2.a;
+
+# title: Outer select joins on inner select join
+WITH i AS (
+  SELECT
+    x.a AS a
+  FROM y AS y
+  JOIN x AS x
+    ON y.b = x.b
+)
+SELECT
+  x.a AS a
+FROM x AS x
+LEFT JOIN i AS i
+  ON x.a = i.a;
+WITH i AS (SELECT x.a AS a FROM y AS y JOIN x AS x ON y.b = x.b) SELECT x.a AS a FROM x AS x LEFT JOIN i AS i ON x.a = i.a;
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index b502d81..664b3c7 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -105,7 +105,7 @@ LEFT JOIN "_u_0" AS "_u_0"
 JOIN "y" AS "y"
   ON "x"."b" = "y"."b"
 WHERE
-  "_u_0"."_col_0" >= 0 AND "x"."a" > 1 AND NOT "_u_0"."_u_1" IS NULL
+  "_u_0"."_col_0" >= 0 AND "x"."a" > 1
 GROUP BY
   "x"."a";
 
diff --git a/tests/fixtures/optimizer/pushdown_projections.sql b/tests/fixtures/optimizer/pushdown_projections.sql
index 2a21f65..b9f6c3f 100644
--- a/tests/fixtures/optimizer/pushdown_projections.sql
+++ b/tests/fixtures/optimizer/pushdown_projections.sql
@@ -54,3 +54,6 @@ WITH t1 AS (SELECT q.cola AS cola FROM UNNEST(ARRAY(STRUCT(1 AS cola, 'test' AS
 
 SELECT x FROM VALUES(1, 2) AS q(x, y);
 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;
diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql
index cf4195d..4e9e70c 100644
--- a/tests/fixtures/optimizer/simplify.sql
+++ b/tests/fixtures/optimizer/simplify.sql
@@ -375,6 +375,18 @@ 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(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);
+
+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;
+
 --------------------------------------
 -- Comparisons
 --------------------------------------
diff --git a/tests/fixtures/optimizer/tpc-h/tpc-h.sql b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
index 9c1f138..272fb26 100644
--- a/tests/fixtures/optimizer/tpc-h/tpc-h.sql
+++ b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
@@ -150,7 +150,6 @@ WHERE
   "part"."p_size" = 15
   AND "part"."p_type" LIKE '%BRASS'
   AND "partsupp"."ps_supplycost" = "_u_0"."_col_0"
-  AND NOT "_u_0"."_u_1" IS NULL
 ORDER BY
   "s_acctbal" DESC,
   "n_name",
@@ -1008,7 +1007,7 @@ JOIN "part" AS "part"
 LEFT JOIN "_u_0" AS "_u_0"
   ON "_u_0"."_u_1" = "part"."p_partkey"
 WHERE
-  "lineitem"."l_quantity" < "_u_0"."_col_0" AND NOT "_u_0"."_u_1" IS NULL;
+  "lineitem"."l_quantity" < "_u_0"."_col_0";
 
 --------------------------------------
 -- TPC-H 18
@@ -1253,10 +1252,7 @@ WITH "_u_0" AS (
   LEFT JOIN "_u_3" AS "_u_3"
     ON "partsupp"."ps_partkey" = "_u_3"."p_partkey"
   WHERE
-    "partsupp"."ps_availqty" > "_u_0"."_col_0"
-    AND NOT "_u_0"."_u_1" IS NULL
-    AND NOT "_u_0"."_u_2" IS NULL
-    AND NOT "_u_3"."p_partkey" IS NULL
+    "partsupp"."ps_availqty" > "_u_0"."_col_0" AND NOT "_u_3"."p_partkey" IS NULL
   GROUP BY
     "partsupp"."ps_suppkey"
 )
diff --git a/tests/fixtures/optimizer/unnest_subqueries.sql b/tests/fixtures/optimizer/unnest_subqueries.sql
index a444945..9d760e0 100644
--- a/tests/fixtures/optimizer/unnest_subqueries.sql
+++ b/tests/fixtures/optimizer/unnest_subqueries.sql
@@ -22,6 +22,8 @@ WHERE
   AND x.a > ANY (SELECT y.a FROM y)
   AND x.a = (SELECT SUM(y.c) AS c FROM y WHERE y.a = x.a LIMIT 10)
   AND x.a = (SELECT SUM(y.c) AS c FROM y WHERE y.a = x.a OFFSET 10)
+  AND x.a > ALL (SELECT y.c FROM y WHERE y.a = x.a)
+  AND x.a > (SELECT COUNT(*) as d FROM y WHERE y.a = x.a)
 ;
 SELECT
   *
@@ -130,37 +132,42 @@ LEFT JOIN (
     y.a
 ) AS _u_15
   ON x.a = _u_15.a
+LEFT JOIN (
+  SELECT
+    ARRAY_AGG(c),
+    y.a AS _u_20
+  FROM y
+  WHERE
+    TRUE
+  GROUP BY
+    y.a
+) AS _u_19
+  ON _u_19._u_20 = x.a
+LEFT JOIN (
+  SELECT
+    COUNT(*) AS d,
+    y.a AS _u_22
+  FROM y
+  WHERE
+    TRUE
+  GROUP BY
+    y.a
+) AS _u_21
+  ON _u_21._u_22 = x.a
 WHERE
   x.a = _u_0.a
   AND NOT "_u_1"."a" IS NULL
   AND NOT "_u_2"."b" IS NULL
   AND NOT "_u_3"."a" IS NULL
+  AND x.a = _u_4.b
+  AND x.a > _u_6.b
+  AND x.a = _u_8.a
+  AND NOT x.a = _u_9.a
+  AND ARRAY_ANY(_u_10.a, _x -> _x = x.a)
   AND (
-    x.a = _u_4.b AND NOT _u_4._u_5 IS NULL
-  )
-  AND (
-    x.a > _u_6.b AND NOT _u_6._u_7 IS NULL
-  )
-  AND (
-    None = _u_8.a AND NOT _u_8.a IS NULL
-  )
-  AND NOT (
-    x.a = _u_9.a AND NOT _u_9.a IS NULL
-  )
-  AND (
-    ARRAY_ANY(_u_10.a, _x -> _x = x.a) AND NOT _u_10._u_11 IS NULL
-  )
-  AND (
-    (
-      (
-        x.a < _u_12.a AND NOT _u_12._u_13 IS NULL
-      ) AND NOT _u_12._u_13 IS NULL
-    )
-    AND ARRAY_ANY(_u_12._u_14, "_x" -> _x <> x.d)
-  )
-  AND (
-    NOT _u_15.a IS NULL AND NOT _u_15.a IS NULL
+    x.a < _u_12.a AND ARRAY_ANY(_u_12._u_14, "_x" -> _x <> x.d)
   )
+  AND NOT _u_15.a IS NULL
   AND x.a IN (
     SELECT
       y.a AS a
@@ -199,4 +206,6 @@ WHERE
     WHERE
       y.a = x.a
     OFFSET 10
-  );
+  )
+  AND ARRAY_ALL(_u_19."", _x -> _x = x.a)
+  AND x.a > COALESCE(_u_21.d, 0);