5 files changed, 118 insertions, 19 deletions
diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql
index 5a4871d..b3f546b 100644
--- a/tests/fixtures/identity.sql
+++ b/tests/fixtures/identity.sql
@@ -17,6 +17,7 @@ SUM(CASE WHEN x > 1 THEN 1 ELSE 0 END) / y
 '\x'
 "x"
 ""
+"""x"""
 N'abc'
 x
 x % 1
@@ -101,6 +102,8 @@ SPLIT(SPLIT(referrer, 'utm_source=')[OFFSET(1)], "&")[OFFSET(0)]
 x[ORDINAL(1)][SAFE_OFFSET(2)]
 x GLOB '??-*'
 x GLOB y
+LIKE(x, 'z')
+ILIKE(x, 'z')
 x LIKE SUBSTR('abc', 1, 1)
 x LIKE y
 x LIKE a.y
@@ -113,7 +116,7 @@ INTERVAL '1' day
 INTERVAL '1' MONTH
 INTERVAL '1 day'
 INTERVAL 2 months
-INTERVAL 1 + 3 DAYS
+INTERVAL (1 + 3) DAYS
 CAST('45' AS INTERVAL DAYS)
 TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), 1, DAY)
 DATETIME_DIFF(CURRENT_DATE, 1, DAY)
@@ -221,6 +224,7 @@ SELECT JSON_EXTRACT(x, '$.name')
 SELECT JSON_EXTRACT_SCALAR(x, '$.name')
 SELECT x LIKE '%x%' FROM test
 SELECT * FROM test LIMIT 100
+SELECT * FROM test LIMIT 1 + 1
 SELECT * FROM test LIMIT 100 OFFSET 200
 SELECT * FROM test FETCH FIRST ROWS ONLY
 SELECT * FROM test FETCH FIRST 1 ROWS ONLY
@@ -393,6 +397,7 @@ SELECT * EXCEPT (a, b) REPLACE (a AS b, b AS C)
 SELECT * EXCEPT (a, b) REPLACE (a AS b, b AS C) FROM y
 SELECT a.* EXCEPT (a, b), b.* REPLACE (a AS b, b AS C)
 SELECT a.* EXCEPT (a, b), b.* REPLACE (a AS b, b AS C) FROM x
+SELECT A.* EXCEPT (A.COL_1) FROM TABLE_1 AS A
 SELECT zoo, animals FROM (VALUES ('oakland', ARRAY('a', 'b')), ('sf', ARRAY('b', 'c'))) AS t(zoo, animals)
 SELECT zoo, animals FROM UNNEST(ARRAY(STRUCT('oakland' AS zoo, ARRAY('a', 'b') AS animals), STRUCT('sf' AS zoo, ARRAY('b', 'c') AS animals))) AS t(zoo, animals)
 WITH a AS (SELECT 1) SELECT 1 UNION ALL SELECT 2
@@ -558,6 +563,7 @@ CREATE FUNCTION f AS 'g'
 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.c()
 CREATE INDEX abc ON t (a)
 CREATE INDEX abc ON t (a, b, b)
@@ -585,6 +591,7 @@ INSERT OVERWRITE TABLE a.b PARTITION(ds) SELECT x FROM y
 INSERT OVERWRITE TABLE a.b PARTITION(ds = 'YYYY-MM-DD') SELECT x FROM y
 INSERT OVERWRITE TABLE a.b PARTITION(ds, hour) SELECT x FROM y
 INSERT OVERWRITE TABLE a.b PARTITION(ds = 'YYYY-MM-DD', hour = 'hh') SELECT x FROM y
+ALTER SESSION SET STATEMENT_TIMEOUT_IN_SECONDS=3
 ALTER AGGREGATE bla(foo) OWNER TO CURRENT_USER
 ALTER RULE foo ON bla RENAME TO baz
 ALTER ROLE CURRENT_USER WITH REPLICATION
@@ -721,3 +728,6 @@ ALTER TABLE a ADD PRIMARY KEY (x, y) NOT ENFORCED
 ALTER TABLE a ADD FOREIGN KEY (x, y) REFERENCES bla
 SELECT end FROM a
 SELECT id FROM b.a AS a QUALIFY ROW_NUMBER() OVER (PARTITION BY br ORDER BY sadf DESC) = 1
+SELECT LEFT.FOO FROM BLA AS LEFT
+SELECT RIGHT.FOO FROM BLA AS RIGHT
+SELECT LEFT FROM LEFT LEFT JOIN RIGHT RIGHT JOIN LEFT
diff --git a/tests/fixtures/optimizer/expand_laterals.sql b/tests/fixtures/optimizer/expand_laterals.sql
new file mode 100644
index 0000000..09bbd0f
--- /dev/null
+++ b/tests/fixtures/optimizer/expand_laterals.sql
@@ -0,0 +1,40 @@
+# 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/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index 664b3c7..9c14ec1 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -1,14 +1,20 @@
 # title: lateral
 # execute: false
 SELECT a, m FROM z LATERAL VIEW EXPLODE([1, 2]) q AS m;
+WITH "z_2" AS (
+  SELECT
+    "z"."a" AS "a"
+  FROM "z" AS "z"
+)
 SELECT
   "z"."a" AS "a",
   "q"."m" AS "m"
-FROM "z" AS "z"
+FROM "z_2" AS "z"
 LATERAL VIEW
 EXPLODE(ARRAY(1, 2)) q AS "m";
 
 # title: unnest
+# execute: false
 SELECT x FROM UNNEST([1, 2]) AS q(x, y);
 SELECT
   "q"."x" AS "x"
@@ -369,3 +375,18 @@ SELECT
 FROM "x" AS "x"
 RIGHT JOIN "y_2" AS "y"
   ON "x"."a" = "y"."b";
+
+
+# title: lateral column alias reference
+SELECT x.a + 1 AS c, c + 1 AS d FROM x;
+SELECT
+  "x"."a" + 1 AS "c",
+  "x"."a" + 2 AS "d"
+FROM "x" AS "x";
+
+# title: column reference takes priority over lateral column alias reference
+SELECT x.a + 1 AS b, b + 1 AS c FROM x;
+SELECT
+  "x"."a" + 1 AS "b",
+  "x"."b" + 1 AS "c"
+FROM "x" AS "x";
diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql
index ee041e2..141f028 100644
--- a/tests/fixtures/optimizer/qualify_columns.sql
+++ b/tests/fixtures/optimizer/qualify_columns.sql
@@ -104,14 +104,6 @@ SELECT x.a AS a FROM x AS x ORDER BY x.b;
 SELECT SUM(a) AS a FROM x ORDER BY SUM(a);
 SELECT SUM(x.a) AS a FROM x AS x ORDER BY SUM(x.a);
 
-# 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;
-
-# 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;
-
 # execute: false
 SELECT AGGREGATE(ARRAY(a, x.b), 0, (x, acc) -> x + acc + a) AS sum_agg FROM x;
 SELECT AGGREGATE(ARRAY(x.a, x.b), 0, (x, acc) -> x + acc + x.a) AS sum_agg FROM x AS x;
@@ -199,15 +191,6 @@ SELECT x.a AS a FROM x AS x WHERE x.b IN (SELECT x.b AS b FROM y AS x);
 SELECT a FROM x AS i WHERE b IN (SELECT b FROM y AS j WHERE j.b IN (SELECT c FROM y AS k WHERE k.b = j.b));
 SELECT i.a AS a FROM x AS i WHERE i.b IN (SELECT j.b AS b FROM y AS j WHERE j.b IN (SELECT k.c AS c FROM y AS k WHERE k.b = j.b));
 
-# execute: false
-# dialect: bigquery
-SELECT aa FROM x, UNNEST(a) AS aa;
-SELECT aa AS aa FROM x AS x, UNNEST(x.a) AS aa;
-
-# execute: false
-SELECT aa FROM x, UNNEST(a) AS t(aa);
-SELECT t.aa AS aa FROM x AS x, UNNEST(x.a) AS t(aa);
-
 --------------------------------------
 -- Expand *
 --------------------------------------
@@ -302,3 +285,43 @@ SELECT COALESCE(x.b, y.b, z.b) AS b FROM x AS x JOIN y AS y ON x.b = y.b JOIN z
 # dialect: spark
 SELECT /*+ BROADCAST(y) */ x.b FROM x JOIN y ON x.b = y.b;
 SELECT /*+ BROADCAST(y) */ x.b AS b FROM x AS x JOIN y AS y ON x.b = y.b;
+
+--------------------------------------
+-- UDTF
+--------------------------------------
+# execute: false
+SELECT c FROM x LATERAL VIEW EXPLODE (a) AS c;
+SELECT _q_0.c AS c FROM x AS x LATERAL VIEW EXPLODE(x.a) _q_0 AS c;
+
+# execute: false
+SELECT c FROM xx LATERAL VIEW EXPLODE (a) AS c;
+SELECT _q_0.c AS c FROM xx AS xx LATERAL VIEW EXPLODE(xx.a) _q_0 AS c;
+
+# execute: false
+SELECT c FROM x LATERAL VIEW EXPLODE (a) t AS c;
+SELECT t.c AS c FROM x AS x LATERAL VIEW EXPLODE(x.a) t AS c;
+
+# execute: false
+SELECT aa FROM x, UNNEST(a) AS t(aa);
+SELECT t.aa AS aa FROM x AS x, UNNEST(x.a) AS t(aa);
+
+# execute: false
+# dialect: bigquery
+SELECT aa FROM x, UNNEST(a) AS aa;
+SELECT aa AS aa FROM x AS x, UNNEST(x.a) AS aa;
+
+--------------------------------------
+-- Window functions
+--------------------------------------
+
+-- ORDER BY in window function
+SELECT a + 1 AS a, ROW_NUMBER() OVER (PARTITION BY b ORDER BY a) AS row_num FROM x;
+SELECT x.a + 1 AS a, ROW_NUMBER() OVER (PARTITION BY x.b ORDER BY x.a) AS row_num FROM x AS x;
+
+# 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;
+
+# 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;
diff --git a/tests/fixtures/pretty.sql b/tests/fixtures/pretty.sql
index a240597..c67ba5d 100644
--- a/tests/fixtures/pretty.sql
+++ b/tests/fixtures/pretty.sql
@@ -342,3 +342,8 @@ SELECT
   basket_index
 FROM table_data
 CROSS JOIN UNNEST(fruit_basket) AS fruit WITH OFFSET AS basket_index;
+SELECT A.* EXCEPT A.COL_1, A.COL_2 FROM TABLE_1 A;
+SELECT
+  A.*
+  EXCEPT (A.COL_1, A.COL_2)
+FROM TABLE_1 AS A;