1 files changed, 131 insertions, 9 deletions
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index eb6761a..ab4f769 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -1,3 +1,5 @@
+# title: lateral
+# execute: false
 SELECT a, m FROM z LATERAL VIEW EXPLODE([1, 2]) q AS m;
 SELECT
   "z"."a" AS "a",
@@ -6,11 +8,13 @@ FROM "z" AS "z"
 LATERAL VIEW
 EXPLODE(ARRAY(1, 2)) q AS "m";
 
+# title: unnest
 SELECT x FROM UNNEST([1, 2]) AS q(x, y);
 SELECT
   "q"."x" AS "x"
 FROM UNNEST(ARRAY(1, 2)) AS "q"("x", "y");
 
+# title: Union in CTE
 WITH cte AS (
     (
         SELECT
@@ -21,7 +25,7 @@ WITH cte AS (
     UNION ALL
     (
         SELECT
-            a
+            b AS a
         FROM
             y
     )
@@ -39,7 +43,7 @@ WITH "cte" AS (
   UNION ALL
   (
     SELECT
-      "y"."a" AS "a"
+      "y"."b" AS "a"
     FROM "y" AS "y"
   )
 )
@@ -47,6 +51,7 @@ SELECT
   "cte"."a" AS "a"
 FROM "cte";
 
+# title: Chained CTEs
 WITH cte1 AS (
     SELECT a
     FROM x
@@ -74,30 +79,31 @@ SELECT
   "cte1"."a" + 1 AS "a"
 FROM "cte1";
 
-SELECT a, SUM(b)
+# title: Correlated subquery
+SELECT a, SUM(b) AS sum_b
 FROM (
     SELECT x.a, y.b
     FROM x, y
-    WHERE (SELECT max(b) FROM y WHERE x.a = y.a) >= 0 AND x.a = y.a
+    WHERE (SELECT max(b) FROM y WHERE x.b = y.b) >= 0 AND x.b = y.b
 ) d
 WHERE (TRUE AND TRUE OR 'a' = 'b') AND a > 1
 GROUP BY a;
 WITH "_u_0" AS (
   SELECT
     MAX("y"."b") AS "_col_0",
-    "y"."a" AS "_u_1"
+    "y"."b" AS "_u_1"
   FROM "y" AS "y"
   GROUP BY
-    "y"."a"
+    "y"."b"
 )
 SELECT
   "x"."a" AS "a",
-  SUM("y"."b") AS "_col_1"
+  SUM("y"."b") AS "sum_b"
 FROM "x" AS "x"
 LEFT JOIN "_u_0" AS "_u_0"
-  ON "x"."a" = "_u_0"."_u_1"
+  ON "x"."b" = "_u_0"."_u_1"
 JOIN "y" AS "y"
-  ON "x"."a" = "y"."a"
+  ON "x"."b" = "y"."b"
 WHERE
   "_u_0"."_col_0" >= 0
   AND "x"."a" > 1
@@ -105,6 +111,7 @@ WHERE
 GROUP BY
   "x"."a";
 
+# title: Root subquery
 (SELECT a FROM x) LIMIT 1;
 (
   SELECT
@@ -113,6 +120,7 @@ GROUP BY
 )
 LIMIT 1;
 
+# title: Root subquery is union
 (SELECT b FROM x UNION SELECT b FROM y) LIMIT 1;
 (
   SELECT
@@ -125,6 +133,7 @@ LIMIT 1;
 )
 LIMIT 1;
 
+# title: broadcast
 # dialect: spark
 SELECT /*+ BROADCAST(y) */ x.b FROM x JOIN y ON x.b = y.b;
 SELECT /*+ BROADCAST(`y`) */
@@ -133,11 +142,14 @@ FROM `x` AS `x`
 JOIN `y` AS `y`
   ON `x`.`b` = `y`.`b`;
 
+# title: aggregate
+# execute: false
 SELECT AGGREGATE(ARRAY(x.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";
 
+# title: values
 SELECT cola, colb FROM (VALUES (1, 'test'), (2, 'test2')) AS tab(cola, colb);
 SELECT
   "tab"."cola" AS "cola",
@@ -146,6 +158,7 @@ FROM (VALUES
   (1, 'test'),
   (2, 'test2')) AS "tab"("cola", "colb");
 
+# title: spark values
 # dialect: spark
 SELECT cola, colb FROM (VALUES (1, 'test'), (2, 'test2')) AS tab(cola, colb);
 SELECT
@@ -154,3 +167,112 @@ SELECT
 FROM VALUES
   (1, 'test'),
   (2, 'test2') AS `tab`(`cola`, `colb`);
+
+# title: complex CTE dependencies
+WITH m AS (
+  SELECT a, b FROM (VALUES (1, 2)) AS a1(a, b)
+), n AS (
+  SELECT a, b FROM m WHERE m.a = 1
+), o AS (
+  SELECT a, b FROM m WHERE m.a = 2
+) SELECT
+    n.a,
+    n.b,
+    o.b
+FROM n
+FULL OUTER JOIN o ON n.a = o.a
+CROSS JOIN n AS n2
+WHERE o.b > 0 AND n.a = n2.a;
+WITH "m" AS (
+  SELECT
+    "a1"."a" AS "a",
+    "a1"."b" AS "b"
+  FROM (VALUES
+    (1, 2)) AS "a1"("a", "b")
+), "n" AS (
+  SELECT
+    "m"."a" AS "a",
+    "m"."b" AS "b"
+  FROM "m"
+  WHERE
+    "m"."a" = 1
+), "o" AS (
+  SELECT
+    "m"."a" AS "a",
+    "m"."b" AS "b"
+  FROM "m"
+  WHERE
+    "m"."a" = 2
+)
+SELECT
+  "n"."a" AS "a",
+  "n"."b" AS "b",
+  "o"."b" AS "b"
+FROM "n"
+FULL JOIN "o"
+  ON "n"."a" = "o"."a"
+JOIN "n" AS "n2"
+  ON "n"."a" = "n2"."a"
+WHERE
+  "o"."b" > 0;
+
+# title: Broadcast hint
+# dialect: spark
+WITH m AS (
+  SELECT
+    x.a,
+    x.b
+  FROM x
+), n AS (
+  SELECT
+    y.b,
+    y.c
+  FROM y
+), joined as (
+  SELECT /*+ BROADCAST(n) */
+    m.a,
+    n.c
+  FROM m JOIN n ON m.b = n.b
+)
+SELECT
+  joined.a,
+  joined.c
+FROM joined;
+SELECT /*+ BROADCAST(`y`) */
+  `x`.`a` AS `a`,
+  `y`.`c` AS `c`
+FROM `x` AS `x`
+JOIN `y` AS `y`
+  ON `x`.`b` = `y`.`b`;
+
+# title: Mix Table and Column Hints
+# dialect: spark
+WITH m AS (
+  SELECT
+    x.a,
+    x.b
+  FROM x
+), n AS (
+  SELECT
+    y.b,
+    y.c
+  FROM y
+), joined as (
+  SELECT /*+ BROADCAST(m), MERGE(m, n) */
+    m.a,
+    n.c
+  FROM m JOIN n ON m.b = n.b
+)
+SELECT
+  /*+ COALESCE(3) */
+  joined.a,
+  joined.c
+FROM joined;
+SELECT /*+ COALESCE(3),
+  BROADCAST(`x`),
+  MERGE(`x`, `y`) */
+  `x`.`a` AS `a`,
+  `y`.`c` AS `c`
+FROM `x` AS `x`
+JOIN `y` AS `y`
+  ON `x`.`b` = `y`.`b`;