6 files changed, 64 insertions, 11 deletions
diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql
index b3f546b..7b9ae6d 100644
--- a/tests/fixtures/identity.sql
+++ b/tests/fixtures/identity.sql
@@ -16,6 +16,18 @@ SUM(CASE WHEN x > 1 THEN 1 ELSE 0 END) / y
 'x'
 '\x'
 "x"
+'\z'
+'\\z'
+'\\\z'
+'\\\\z'
+'\\\\\z'
+'\\\\\\z'
+'\n'
+'\\n'
+'\\\n'
+'\\\\n'
+'\\\\\n'
+'\\\\\\n'
 ""
 """x"""
 N'abc'
@@ -502,7 +514,7 @@ CREATE TABLE z (a INT(11) DEFAULT NULL COMMENT '客户id')
 CREATE TABLE z (a INT(11) NOT NULL DEFAULT 1)
 CREATE TABLE z (a INT(11) NOT NULL DEFAULT -1)
 CREATE TABLE z (a INT(11) NOT NULL COLLATE utf8_bin AUTO_INCREMENT)
-CREATE TABLE z (a INT, PRIMARY KEY(a))
+CREATE TABLE z (a INT, PRIMARY KEY (a))
 CREATE TABLE z WITH (FORMAT='parquet') AS SELECT 1
 CREATE TABLE z WITH (FORMAT='ORC', x='2') AS SELECT 1
 CREATE TABLE z WITH (TABLE_FORMAT='iceberg', FORMAT='parquet') AS SELECT 1
@@ -530,9 +542,13 @@ 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 ALWAYS AS IDENTITY)
 CREATE TABLE IF NOT EXISTS customer (pk BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (INCREMENT BY 1))
-CREATE TABLE customer (pk BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 10 INCREMENT BY 1))
+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 foo (baz_id INT REFERENCES baz(id) DEFERRABLE)
+CREATE TABLE foo (baz CHAR(4) CHARACTER SET LATIN UPPERCASE NOT CASESPECIFIC)
+CREATE TABLE foo (baz DATE FORMAT 'YYYY/MM/DD' TITLE 'title')
+CREATE TABLE t (title TEXT)
 CREATE TABLE a, FALLBACK, LOG, JOURNAL, CHECKSUM=DEFAULT, DEFAULT MERGEBLOCKRATIO, BLOCKCOMPRESSION=MANUAL (a INT)
 CREATE TABLE a, NO FALLBACK PROTECTION, NO LOG, NO JOURNAL, CHECKSUM=ON, NO MERGEBLOCKRATIO, BLOCKCOMPRESSION=ALWAYS (a INT)
 CREATE TABLE a, WITH JOURNAL TABLE=x.y.z, CHECKSUM=OFF, MERGEBLOCKRATIO=1, DATABLOCKSIZE=10 KBYTES (a INT)
@@ -556,6 +572,7 @@ CREATE TEMPORARY VIEW x AS WITH y AS (SELECT 1) SELECT * FROM y
 CREATE MATERIALIZED VIEW x.y.z AS SELECT a FROM b
 CREATE VIEW z (a, b)
 CREATE VIEW z (a, b COMMENT 'b', c COMMENT 'c')
+CREATE VIEW z AS LOCKING ROW FOR ACCESS SELECT a FROM b
 CREATE TEMPORARY FUNCTION f
 CREATE TEMPORARY FUNCTION f AS 'g'
 CREATE FUNCTION f
@@ -731,3 +748,5 @@ SELECT id FROM b.a AS a QUALIFY ROW_NUMBER() OVER (PARTITION BY br ORDER BY sadf
 SELECT LEFT.FOO FROM BLA AS LEFT
 SELECT RIGHT.FOO FROM BLA AS RIGHT
 SELECT LEFT FROM LEFT LEFT JOIN RIGHT RIGHT JOIN LEFT
+SELECT * FROM x WHERE name ILIKE ANY XXX('a', 'b')
+SELECT * FROM x WHERE name LIKE ANY XXX('a', 'b')
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index 9c14ec1..6ccf24e 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -1,15 +1,10 @@
 # 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_2" AS "z"
+FROM "z" AS "z"
 LATERAL VIEW
 EXPLODE(ARRAY(1, 2)) q AS "m";
 
diff --git a/tests/fixtures/optimizer/pushdown_projections.sql b/tests/fixtures/optimizer/pushdown_projections.sql
index 03ecf16..107e92f 100644
--- a/tests/fixtures/optimizer/pushdown_projections.sql
+++ b/tests/fixtures/optimizer/pushdown_projections.sql
@@ -57,3 +57,30 @@ 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;
+
+--------------------------------------
+-- 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;
+
+SELECT a FROM (SELECT * FROM aa UNION ALL SELECT * FROM bb UNION ALL SELECT * from cc);
+SELECT _q_0.a AS a FROM (SELECT aa.a AS a FROM aa AS aa UNION ALL SELECT bb.a AS a FROM bb AS bb UNION ALL SELECT cc.a AS a FROM cc AS cc) AS _q_0;
+
+SELECT a FROM (SELECT a FROM aa UNION ALL SELECT * FROM bb UNION ALL SELECT * from cc);
+SELECT _q_0.a AS a FROM (SELECT aa.a AS a FROM aa AS aa UNION ALL SELECT bb.a AS a FROM bb AS bb UNION ALL SELECT cc.a AS a FROM cc AS cc) AS _q_0;
+
+SELECT a FROM (SELECT * FROM aa UNION ALL SELECT * FROM bb UNION ALL SELECT * from cc);
+SELECT _q_0.a AS a FROM (SELECT aa.a AS a FROM aa AS aa UNION ALL SELECT bb.a AS a FROM bb AS bb UNION ALL SELECT cc.a AS a FROM cc AS cc) AS _q_0;
+
+SELECT a FROM (SELECT * FROM aa CROSS JOIN bb);
+SELECT _q_0.a AS a FROM (SELECT a AS a FROM aa AS aa CROSS JOIN bb AS bb) AS _q_0;
+
+SELECT a FROM (SELECT aa.* FROM aa);
+SELECT _q_0.a AS a FROM (SELECT aa.a AS a FROM aa AS aa) AS _q_0;
+
+SELECT a FROM (SELECT * FROM (SELECT * FROM aa));
+SELECT _q_1.a AS a FROM (SELECT _q_0.a AS a FROM (SELECT aa.a AS a FROM aa AS aa) AS _q_0) AS _q_1;
+
+with cte1 as (SELECT cola, colb FROM tb UNION ALL SELECT colc, cold FROM tb2) SELECT cola FROM cte1;
+WITH cte1 AS (SELECT tb.cola AS cola FROM tb AS tb UNION ALL SELECT tb2.colc AS colc FROM tb2 AS tb2) SELECT cte1.cola AS cola FROM cte1;
diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql
index 141f028..46c576a 100644
--- a/tests/fixtures/optimizer/qualify_columns.sql
+++ b/tests/fixtures/optimizer/qualify_columns.sql
@@ -215,6 +215,9 @@ SELECT _q_0.a AS a FROM (SELECT x.a AS a, x.b AS b FROM x AS x) AS _q_0;
 SELECT * FROM (SELECT a FROM x);
 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;
+
 --------------------------------------
 -- CTEs
 --------------------------------------
@@ -310,6 +313,15 @@ 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;
 
+# execute: false
+# 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);
+
+# execute: false
+SELECT c FROM (SELECT 1 a) AS x LATERAL VIEW EXPLODE(a) AS c;
+SELECT _q_0.c AS c FROM (SELECT 1 AS a) AS x LATERAL VIEW EXPLODE(x.a) _q_0 AS c;
+
 --------------------------------------
 -- Window functions
 --------------------------------------
diff --git a/tests/fixtures/optimizer/qualify_columns__invalid.sql b/tests/fixtures/optimizer/qualify_columns__invalid.sql
index 2a3ccfb..f0f9f87 100644
--- a/tests/fixtures/optimizer/qualify_columns__invalid.sql
+++ b/tests/fixtures/optimizer/qualify_columns__invalid.sql
@@ -1,4 +1,3 @@
-SELECT * FROM zz;
 SELECT z.a FROM x;
 SELECT z.* FROM x;
 SELECT x FROM x;
@@ -11,3 +10,4 @@ SELECT b FROM x AS a CROSS JOIN y AS b CROSS JOIN y AS c;
 SELECT x.a FROM x JOIN y USING (a);
 SELECT a, SUM(b) FROM x GROUP BY 3;
 SELECT p FROM (SELECT x from xx) y CROSS JOIN yy CROSS JOIN zz
+select a from (select * from x cross join y);
diff --git a/tests/fixtures/pretty.sql b/tests/fixtures/pretty.sql
index c67ba5d..a06af88 100644
--- a/tests/fixtures/pretty.sql
+++ b/tests/fixtures/pretty.sql
@@ -40,7 +40,7 @@ WITH cte1 AS (
         FROM (SELECT 1) AS x, y, (SELECT 2) z
         UNION ALL
         SELECT MAX(COALESCE(x AND y, a and b and c, d and e)), FOO(CASE WHEN a and b THEN c and d ELSE 3 END)
-        GROUP BY x, GROUPING SETS (a, (b, c)) CUBE(y, z)
+        GROUP BY x, GROUPING SETS (a, (b, c)), CUBE(y, z)
     ) x
 )
 SELECT a, b c FROM (
@@ -95,7 +95,7 @@ WITH cte1 AS (
       MAX(COALESCE(x AND y, a AND b AND c, d AND e)),
       FOO(CASE WHEN a AND b THEN c AND d ELSE 3 END)
     GROUP BY
-      x
+      x,
     GROUPING SETS (
       a,
       (b, c)