1 files changed, 131 insertions, 2 deletions
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index 4ae9e90..ea29e3e 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -626,8 +626,7 @@ SELECT
   "_q_0"."G" AS "G",
   "_q_0"."'x'" AS "'x'",
   "_q_0"."'y'" AS "'y'"
-FROM "U" AS "U" PIVOT(SUM("U"."F") FOR "U"."H" IN ('x', 'y')) AS "_q_0"
-;
+FROM "U" AS "U" PIVOT(SUM("U"."F") FOR "U"."H" IN ('x', 'y')) AS "_q_0";
 
 # title: selecting all columns from a pivoted source and generating spark
 # note: spark doesn't allow pivot aliases or qualified columns for the pivot's "field" (`h`)
@@ -644,6 +643,110 @@ FROM (
   FROM `u` AS `u` PIVOT(SUM(`u`.`f`) FOR `h` IN ('x', 'y'))
 ) AS `_q_0`;
 
+# title: selecting all columns from a pivoted source, pivot has column aliases
+# execute: false
+# dialect: snowflake
+WITH source AS (
+  SELECT
+    id,
+    key,
+    value,
+    timestamp_1,
+    timestamp_2
+  FROM DB_NAME.SCHEMA_NAME.TABLE_NAME
+),
+enriched AS (
+  SELECT * FROM source
+  PIVOT(MAX(value) FOR key IN ('a', 'b', 'c'))
+  AS final (id, timestamp_1, timestamp_2, col_1, col_2, col_3)
+)
+SELECT id, timestamp_1 FROM enriched;
+WITH "SOURCE" AS (
+  SELECT
+    "TABLE_NAME"."ID" AS "ID",
+    "TABLE_NAME"."KEY" AS "KEY",
+    "TABLE_NAME"."VALUE" AS "VALUE",
+    "TABLE_NAME"."TIMESTAMP_1" AS "TIMESTAMP_1",
+    "TABLE_NAME"."TIMESTAMP_2" AS "TIMESTAMP_2"
+  FROM "DB_NAME"."SCHEMA_NAME"."TABLE_NAME" AS "TABLE_NAME"
+)
+SELECT
+  "FINAL"."ID" AS "ID",
+  "FINAL"."TIMESTAMP_1" AS "TIMESTAMP_1"
+FROM "SOURCE" AS "SOURCE" PIVOT(MAX("SOURCE"."VALUE") FOR "SOURCE"."KEY" IN ('a', 'b', 'c')) AS "FINAL"("ID", "TIMESTAMP_1", "TIMESTAMP_2", "COL_1", "COL_2", "COL_3");
+
+# title: unpivoted table source with a single value column, unpivot columns can't be qualified
+# execute: false
+# dialect: snowflake
+SELECT * FROM m_sales AS m_sales(empid, dept, jan, feb) UNPIVOT(sales FOR month IN (jan, feb)) ORDER BY empid;
+SELECT
+  "_q_0"."EMPID" AS "EMPID",
+  "_q_0"."DEPT" AS "DEPT",
+  "_q_0"."MONTH" AS "MONTH",
+  "_q_0"."SALES" AS "SALES"
+FROM "M_SALES" AS "M_SALES"("EMPID", "DEPT", "JAN", "FEB") UNPIVOT("SALES" FOR "MONTH" IN ("JAN", "FEB")) AS "_q_0"
+ORDER BY
+  "_q_0"."EMPID";
+
+# title: unpivoted table source, unpivot has column aliases
+# execute: false
+SELECT * FROM (SELECT * FROM m_sales) AS m_sales(empid, dept, jan, feb) UNPIVOT(sales FOR month IN (jan, feb)) AS unpiv(a, b, c, d);
+SELECT
+  "unpiv"."a" AS "a",
+  "unpiv"."b" AS "b",
+  "unpiv"."c" AS "c",
+  "unpiv"."d" AS "d"
+FROM (
+  SELECT
+    "m_sales"."empid" AS "empid",
+    "m_sales"."dept" AS "dept",
+    "m_sales"."jan" AS "jan",
+    "m_sales"."feb" AS "feb"
+  FROM "m_sales" AS "m_sales"
+) AS "m_sales" UNPIVOT("sales" FOR "month" IN ("m_sales"."jan", "m_sales"."feb")) AS "unpiv"("a", "b", "c", "d");
+
+# title: unpivoted derived table source with a single value column
+# execute: false
+# dialect: snowflake
+SELECT * FROM (SELECT * FROM m_sales) AS m_sales(empid, dept, jan, feb) UNPIVOT(sales FOR month IN (jan, feb)) ORDER BY empid;
+SELECT
+  "_q_0"."EMPID" AS "EMPID",
+  "_q_0"."DEPT" AS "DEPT",
+  "_q_0"."MONTH" AS "MONTH",
+  "_q_0"."SALES" AS "SALES"
+FROM (
+  SELECT
+    "M_SALES"."EMPID" AS "EMPID",
+    "M_SALES"."DEPT" AS "DEPT",
+    "M_SALES"."JAN" AS "JAN",
+    "M_SALES"."FEB" AS "FEB"
+  FROM "M_SALES" AS "M_SALES"
+) AS "M_SALES" UNPIVOT("SALES" FOR "MONTH" IN ("JAN", "FEB")) AS "_q_0"
+ORDER BY
+  "_q_0"."EMPID";
+
+# title: unpivoted table source with a single value column, unpivot columns can be qualified
+# execute: false
+# dialect: bigquery
+# note: the named columns aren't supported by BQ but we add them here to avoid defining a schema
+SELECT * FROM produce AS produce(product, q1, q2, q3, q4) UNPIVOT(sales FOR quarter IN (q1, q2, q3, q4));
+SELECT
+  `_q_0`.`product` AS `product`,
+  `_q_0`.`quarter` AS `quarter`,
+  `_q_0`.`sales` AS `sales`
+FROM `produce` AS `produce` UNPIVOT(`sales` FOR `quarter` IN (`produce`.`q1`, `produce`.`q2`, `produce`.`q3`, `produce`.`q4`)) AS `_q_0`;
+
+# title: unpivoted table source with multiple value columns
+# execute: false
+# dialect: bigquery
+SELECT * FROM produce AS produce(product, q1, q2, q3, q4) UNPIVOT((first_half_sales, second_half_sales) FOR semesters IN ((Q1, Q2) AS 'semester_1', (Q3, Q4) AS 'semester_2'));
+SELECT
+  `_q_0`.`product` AS `product`,
+  `_q_0`.`semesters` AS `semesters`,
+  `_q_0`.`first_half_sales` AS `first_half_sales`,
+  `_q_0`.`second_half_sales` AS `second_half_sales`
+FROM `produce` AS `produce` UNPIVOT((`first_half_sales`, `second_half_sales`) FOR `semesters` IN ((`produce`.`q1`, `produce`.`q2`) AS 'semester_1', (`produce`.`q3`, `produce`.`q4`) AS 'semester_2')) AS `_q_0`;
+
 # title: quoting is maintained
 # dialect: snowflake
 with cte1("id", foo) as (select 1, 2) select "id" from cte1;
@@ -1144,3 +1247,29 @@ WITH "x" AS (
   LIMIT 10
 )
 LIMIT 10;
+
+# title: avoid producing DAG cycle when pushing down predicate to join
+# execute: false
+SELECT
+  a.company,
+  b.num
+FROM route AS a(num, company, pos, stop)
+JOIN route AS b(num, company, pos, stop) ON (a.num = b.num)
+JOIN stops AS c(id, name) ON (c.id = b.stop)
+JOIN stops AS d(id, name) ON (d.id = c.id)
+WHERE
+  c.name = 'Craiglockhart'
+  OR d.name = 'Tollcross';
+SELECT
+  "a"."company" AS "company",
+  "b"."num" AS "num"
+FROM "route" AS "a"("num", "company", "pos", "stop")
+JOIN "route" AS "b"("num", "company", "pos", "stop")
+  ON "a"."num" = "b"."num"
+JOIN "stops" AS "c"("id", "name")
+  ON "b"."stop" = "c"."id"
+JOIN "stops" AS "d"("id", "name")
+  ON "c"."id" = "d"."id"
+  AND (
+    "c"."name" = 'Craiglockhart' OR "d"."name" = 'Tollcross'
+  );