7 files changed, 165 insertions, 10 deletions
diff --git a/tests/fixtures/optimizer/eliminate_subqueries.sql b/tests/fixtures/optimizer/eliminate_subqueries.sql
index 4c79c22..8542c3e 100644
--- a/tests/fixtures/optimizer/eliminate_subqueries.sql
+++ b/tests/fixtures/optimizer/eliminate_subqueries.sql
@@ -93,3 +93,7 @@ WITH cte2 AS (SELECT a FROM x), cte1 AS (SELECT t.a FROM cte2 AS t) SELECT a FRO
 -- Duplicate CTE nested in CTE
 WITH cte1 AS (SELECT a FROM x), cte2 AS (WITH cte3 AS (SELECT a FROM x) SELECT a FROM cte3) SELECT a FROM cte2;
 WITH cte1 AS (SELECT a FROM x), cte2 AS (SELECT a FROM cte1 AS cte3) SELECT a FROM cte2;
+
+-- Wrapped subquery joined with table
+SELECT * FROM ((SELECT c FROM t1) JOIN t2);
+WITH cte AS (SELECT c FROM t1) SELECT * FROM (cte AS cte, t2);
diff --git a/tests/fixtures/optimizer/merge_subqueries.sql b/tests/fixtures/optimizer/merge_subqueries.sql
index fb69ea7..b89e2fb 100644
--- a/tests/fixtures/optimizer/merge_subqueries.sql
+++ b/tests/fixtures/optimizer/merge_subqueries.sql
@@ -372,3 +372,27 @@ 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;
+
+# title: Outer scope selects from wrapped table with a join (unknown schema)
+# execute: false
+WITH _q_0 AS (SELECT t1.c AS c FROM t1 AS t1) SELECT * FROM (_q_0 AS _q_0 CROSS JOIN t2 AS t2);
+WITH _q_0 AS (SELECT t1.c AS c FROM t1 AS t1) SELECT * FROM (_q_0 AS _q_0 CROSS JOIN t2 AS t2);
+
+# title: Outer scope selects single column from wrapped table with a join
+WITH _q_0 AS (
+  SELECT
+    x.a AS a
+  FROM x AS x
+), y_2 AS (
+  SELECT
+    y.b AS b
+  FROM y AS y
+)
+SELECT
+  y.b AS b
+FROM (
+  _q_0 AS _q_0
+    JOIN y_2 AS y
+      ON _q_0.a = y.b
+);
+SELECT y.b AS b FROM (x AS x JOIN y AS y ON x.a = y.b);
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index 4b58ea5..14f5cfe 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -806,3 +806,87 @@ LEFT JOIN (
       ON "y"."c" = "z"."c"
 )
   ON "x"."b" = "y"."b";
+
+# title: select * from wrapped subquery
+# execute: false
+SELECT * FROM ((SELECT * FROM tbl));
+WITH "_q_0" AS (
+  SELECT
+    *
+  FROM "tbl" AS "tbl"
+)
+SELECT
+  *
+FROM (
+  "_q_0" AS "_q_0"
+);
+
+# title: select * from wrapped subquery joined to a table (known schema)
+SELECT * FROM ((SELECT * FROM x) INNER JOIN y ON a = c);
+SELECT
+  "x"."a" AS "a",
+  "x"."b" AS "b",
+  "y"."b" AS "b",
+  "y"."c" AS "c"
+FROM (
+  "x" AS "x"
+    JOIN "y" AS "y"
+      ON "x"."a" = "y"."c"
+);
+
+# title: select * from wrapped subquery joined to a table (unknown schema)
+# execute: false
+SELECT * FROM ((SELECT c FROM t1) JOIN t2);
+WITH "_q_0" AS (
+  SELECT
+    "t1"."c" AS "c"
+  FROM "t1" AS "t1"
+)
+SELECT
+  *
+FROM (
+  "_q_0" AS "_q_0"
+    CROSS JOIN "t2" AS "t2"
+);
+
+# title: select specific columns from wrapped subquery joined to a table
+SELECT b FROM ((SELECT a FROM x) INNER JOIN y ON a = b);
+SELECT
+  "y"."b" AS "b"
+FROM (
+  "x" AS "x"
+    JOIN "y" AS "y"
+      ON "x"."a" = "y"."b"
+);
+
+# title: select * from wrapped join of subqueries (unknown schema)
+# execute: false
+SELECT * FROM ((SELECT * FROM t1) JOIN (SELECT * FROM t2));
+WITH "_q_0" AS (
+  SELECT
+    *
+  FROM "t1" AS "t1"
+), "_q_1" AS (
+  SELECT
+    *
+  FROM "t2" AS "t2"
+)
+SELECT
+  *
+FROM (
+  "_q_0" AS "_q_0"
+    CROSS JOIN "_q_1" AS "_q_1"
+);
+
+# title: select * from wrapped join of subqueries (known schema)
+SELECT * FROM ((SELECT * FROM x) INNER JOIN (SELECT * FROM y) ON a = c);
+SELECT
+  "x"."a" AS "a",
+  "x"."b" AS "b",
+  "y"."b" AS "b",
+  "y"."c" AS "c"
+FROM (
+  "x" AS "x"
+    JOIN "y" AS "y"
+      ON "x"."a" = "y"."c"
+);
diff --git a/tests/fixtures/optimizer/pushdown_projections.sql b/tests/fixtures/optimizer/pushdown_projections.sql
index 0821339..86dea8c 100644
--- a/tests/fixtures/optimizer/pushdown_projections.sql
+++ b/tests/fixtures/optimizer/pushdown_projections.sql
@@ -16,9 +16,6 @@ SELECT x.a AS a FROM x AS x JOIN (SELECT y.b AS b FROM y AS y) AS z ON x.b = z.b
 SELECT x1.a FROM (SELECT * FROM x) AS x1, (SELECT * FROM x) AS x2;
 SELECT x1.a AS a FROM (SELECT x.a AS a FROM x AS x) AS x1, (SELECT 1 AS _ FROM x AS x) AS x2;
 
-SELECT x1.a FROM (SELECT * FROM x) AS x1, (SELECT * FROM x) AS x2;
-SELECT x1.a AS a FROM (SELECT x.a AS a FROM x AS x) AS x1, (SELECT 1 AS _ FROM x AS x) AS x2;
-
 SELECT a FROM (SELECT DISTINCT a, b FROM x);
 SELECT _q_0.a AS a FROM (SELECT DISTINCT x.a AS a, x.b AS b FROM x AS x) AS _q_0;
 
@@ -77,9 +74,6 @@ SELECT _q_0.a AS a FROM (SELECT aa.a AS a FROM aa AS aa UNION ALL SELECT bb.a AS
 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;
 
@@ -91,3 +85,6 @@ SELECT _q_1.a AS a FROM (SELECT _q_0.a AS a FROM (SELECT aa.a AS a FROM aa AS aa
 
 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;
+
+SELECT * FROM ((SELECT c FROM t1) JOIN t2);
+SELECT * FROM ((SELECT t1.c AS c FROM t1 AS t1) AS _q_0, t2 AS t2);
diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql
index 487a831..90505ac 100644
--- a/tests/fixtures/optimizer/qualify_columns.sql
+++ b/tests/fixtures/optimizer/qualify_columns.sql
@@ -307,6 +307,11 @@ SELECT x.b AS b, y.b AS b, y.c AS c FROM x AS x, y AS y;
 SELECT * EXCEPT(a) FROM x;
 SELECT x.b AS b FROM x AS x;
 
+# execute: false
+# note: this query would fail in the engine level because there are 0 selected columns
+SELECT * EXCEPT (a, b) FROM x;
+SELECT * EXCEPT (x.a, x.b) FROM x AS x;
+
 --------------------------------------
 -- Using
 --------------------------------------
@@ -410,8 +415,7 @@ SELECT _q_0.c AS c FROM (SELECT 1 AS a) AS x LATERAL VIEW EXPLODE(x.a) _q_0 AS c
 --------------------------------------
 -- Window functions
 --------------------------------------
-
--- ORDER BY in window function
+# title: 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;
 
@@ -429,7 +433,6 @@ SELECT x.a AS a, x.b AS b FROM x AS x QUALIFY COUNT(x.a) OVER (PARTITION BY x.b)
 --------------------------------------
 -- Expand laterals
 --------------------------------------
-
 # execute: false
 select 2 AS d, d + 1 FROM x WHERE d = 2 GROUP BY d;
 SELECT 2 AS d, 2 + 1 AS _col_1 FROM x AS x WHERE 2 = 2 GROUP BY 1;
@@ -457,3 +460,38 @@ FROM (
   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;
+
+--------------------------------------
+-- Wrapped tables / join constructs
+--------------------------------------
+# execute: false
+SELECT * FROM ((tbl));
+SELECT * FROM ((tbl AS tbl));
+
+SELECT a, c FROM (x LEFT JOIN y ON a = c);
+SELECT x.a AS a, y.c AS c FROM (x AS x LEFT JOIN y AS y ON x.a = y.c);
+
+# execute: false
+SELECT * FROM ((a CROSS JOIN ((b CROSS JOIN c) CROSS JOIN (d CROSS JOIN e))));
+SELECT * FROM ((a AS a CROSS JOIN ((b AS b CROSS JOIN c AS c) CROSS JOIN (d AS d CROSS JOIN e AS e))));
+
+# execute: false
+SELECT * FROM ((SELECT * FROM tbl));
+SELECT * FROM ((SELECT * FROM tbl AS tbl) AS _q_0);
+
+# execute: false
+SELECT * FROM ((SELECT c FROM t1) JOIN t2);
+SELECT * FROM ((SELECT t1.c AS c FROM t1 AS t1) AS _q_0, t2 AS t2);
+
+# execute: false
+SELECT * FROM ((SELECT * FROM x) INNER JOIN y ON a = c);
+SELECT y.b AS b, y.c AS c, _q_0.a AS a, _q_0.b AS b FROM ((SELECT x.a AS a, x.b AS b FROM x AS x) AS _q_0 INNER JOIN y AS y ON _q_0.a = y.c);
+
+SELECT x.a, y.b, z.c FROM x LEFT JOIN (y INNER JOIN z ON y.c = z.c) ON x.b = y.b;
+SELECT x.a AS a, y.b AS b, z.c AS c FROM x AS x LEFT JOIN (y AS y INNER JOIN z AS z ON y.c = z.c) ON x.b = y.b;
+
+SELECT * FROM ((SELECT * FROM x) INNER JOIN (SELECT * FROM y) ON a = c);
+SELECT _q_0.a AS a, _q_0.b AS b, _q_1.b AS b, _q_1.c AS c FROM ((SELECT x.a AS a, x.b AS b FROM x AS x) AS _q_0 INNER JOIN (SELECT y.b AS b, y.c AS c FROM y AS y) AS _q_1 ON _q_0.a = _q_1.c);
+
+SELECT b FROM ((SELECT a FROM x) INNER JOIN y ON a = b);
+SELECT y.b AS b FROM ((SELECT x.a AS a FROM x AS x) AS _q_0 INNER JOIN y AS y ON _q_0.a = y.b);
diff --git a/tests/fixtures/optimizer/qualify_columns__invalid.sql b/tests/fixtures/optimizer/qualify_columns__invalid.sql
index d2d4959..f3d8b6a 100644
--- a/tests/fixtures/optimizer/qualify_columns__invalid.sql
+++ b/tests/fixtures/optimizer/qualify_columns__invalid.sql
@@ -10,4 +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);
+SELECT a FROM (SELECT * FROM x CROSS JOIN y);
diff --git a/tests/fixtures/optimizer/qualify_tables.sql b/tests/fixtures/optimizer/qualify_tables.sql
index cd749a0..f43ac01 100644
--- a/tests/fixtures/optimizer/qualify_tables.sql
+++ b/tests/fixtures/optimizer/qualify_tables.sql
@@ -101,3 +101,11 @@ SELECT * FROM (SELECT * FROM c.db.tbl1 AS tbl1 JOIN c.db.tbl2 AS tbl2 ON id1 = i
 # title: join construct within join construct
 SELECT * FROM (tbl1 AS tbl1 JOIN (tbl2 AS tbl2 JOIN tbl3 AS tbl3 ON id2 = id3) AS _q_0 ON id1 = id3) AS _q_1;
 SELECT * FROM (SELECT * FROM c.db.tbl1 AS tbl1 JOIN (SELECT * FROM c.db.tbl2 AS tbl2 JOIN c.db.tbl3 AS tbl3 ON id2 = id3) AS _q_0 ON id1 = id3) AS _q_1;
+
+# title: wrapped subquery without alias
+SELECT * FROM ((SELECT * FROM t));
+SELECT * FROM ((SELECT * FROM c.db.t AS t) AS _q_0);
+
+# title: wrapped subquery without alias joined with a table
+SELECT * FROM ((SELECT * FROM t1) INNER JOIN t2 ON a = b);
+SELECT * FROM ((SELECT * FROM c.db.t1 AS t1) AS _q_0 INNER JOIN c.db.t2 AS t2 ON a = b);