summaryrefslogtreecommitdiffstats
path: root/tests/fixtures/optimizer/optimizer.sql
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2023-06-02 23:59:11 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2023-06-02 23:59:11 +0000
commitcaea5267cb8e1fea3702adbdf6f68fd37d13b3b7 (patch)
treef06f1da1ab3b6906beca1c3c7222d28ff00766ac /tests/fixtures/optimizer/optimizer.sql
parentAdding upstream version 12.2.0. (diff)
downloadsqlglot-caea5267cb8e1fea3702adbdf6f68fd37d13b3b7.tar.xz
sqlglot-caea5267cb8e1fea3702adbdf6f68fd37d13b3b7.zip
Adding upstream version 15.0.0.upstream/15.0.0
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'tests/fixtures/optimizer/optimizer.sql')
-rw-r--r--tests/fixtures/optimizer/optimizer.sql121
1 files changed, 119 insertions, 2 deletions
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index 9e7880c..e0567d7 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -101,10 +101,10 @@ SELECT
"x"."a" AS "a",
SUM("y"."b") AS "sum_b"
FROM "x" AS "x"
-LEFT JOIN "_u_0" AS "_u_0"
- ON "x"."b" = "_u_0"."_u_1"
JOIN "y" AS "y"
ON "x"."b" = "y"."b"
+LEFT JOIN "_u_0" AS "_u_0"
+ ON "x"."b" = "_u_0"."_u_1"
WHERE
"_u_0"."_col_0" >= 0 AND "x"."a" > 1
GROUP BY
@@ -502,3 +502,120 @@ WHERE
"unioned"."source_system" = 'bamboohr' OR "unioned"."source_system" = 'workday'
QUALIFY
ROW_NUMBER() OVER (PARTITION BY "unioned"."unique_filter_key" ORDER BY "unioned"."sort_order" DESC, 1) = 1;
+
+# title: pivoted source with explicit selections
+# execute: false
+SELECT * FROM (SELECT a, b, c FROM sc.tb) PIVOT (SUM(c) FOR b IN ('x','y','z'));
+SELECT
+ "_q_1"."a" AS "a",
+ "_q_1"."x" AS "x",
+ "_q_1"."y" AS "y",
+ "_q_1"."z" AS "z"
+FROM (
+ SELECT
+ "tb"."a" AS "a",
+ "tb"."b" AS "b",
+ "tb"."c" AS "c"
+ FROM "sc"."tb" AS "tb"
+) AS "_q_0" PIVOT(SUM("_q_0"."c") FOR "_q_0"."b" IN ('x', 'y', 'z')) AS "_q_1";
+
+# title: pivoted source with implicit selections
+# execute: false
+SELECT * FROM (SELECT * FROM u) PIVOT (SUM(f) FOR h IN ('x', 'y'));
+SELECT
+ "_q_1"."g" AS "g",
+ "_q_1"."x" AS "x",
+ "_q_1"."y" AS "y"
+FROM (
+ SELECT
+ "u"."f" AS "f",
+ "u"."g" AS "g",
+ "u"."h" AS "h"
+ FROM "u" AS "u"
+) AS "_q_0" PIVOT(SUM("_q_0"."f") FOR "_q_0"."h" IN ('x', 'y')) AS "_q_1";
+
+# title: selecting explicit qualified columns from pivoted source with explicit selections
+# execute: false
+SELECT piv.x, piv.y FROM (SELECT f, h FROM u) PIVOT (SUM(f) FOR h IN ('x', 'y')) AS piv;
+SELECT
+ "piv"."x" AS "x",
+ "piv"."y" AS "y"
+FROM (
+ SELECT
+ "u"."f" AS "f",
+ "u"."h" AS "h"
+ FROM "u" AS "u"
+) AS "_q_0" PIVOT(SUM("_q_0"."f") FOR "_q_0"."h" IN ('x', 'y')) AS "piv";
+
+# title: selecting explicit unqualified columns from pivoted source with implicit selections
+# execute: false
+SELECT x, y FROM u PIVOT (SUM(f) FOR h IN ('x', 'y'));
+SELECT
+ "_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";
+
+# title: selecting all columns from a pivoted CTE source, using alias for the aggregation and generating bigquery
+# execute: false
+# dialect: bigquery
+WITH u_cte(f, g, h) AS (SELECT * FROM u) SELECT * FROM u_cte PIVOT(SUM(f) AS sum FOR h IN ('x', 'y'));
+WITH `u_cte` AS (
+ SELECT
+ `u`.`f` AS `f`,
+ `u`.`g` AS `g`,
+ `u`.`h` AS `h`
+ FROM `u` AS `u`
+)
+SELECT
+ `_q_0`.`g` AS `g`,
+ `_q_0`.`sum_x` AS `sum_x`,
+ `_q_0`.`sum_y` AS `sum_y`
+FROM `u_cte` AS `u_cte` PIVOT(SUM(`u_cte`.`f`) AS `sum` FOR `u_cte`.`h` IN ('x', 'y')) AS `_q_0`;
+
+# title: selecting all columns from a pivoted source and generating snowflake
+# execute: false
+# dialect: snowflake
+SELECT * FROM u PIVOT (SUM(f) FOR h IN ('x', 'y'));
+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"
+;
+
+# 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`)
+# execute: false
+# dialect: spark
+SELECT * FROM u PIVOT (SUM(f) FOR h IN ('x', 'y'));
+SELECT
+ `_q_0`.`g` AS `g`,
+ `_q_0`.`x` AS `x`,
+ `_q_0`.`y` AS `y`
+FROM (
+ SELECT
+ *
+ FROM `u` AS `u` PIVOT(SUM(`u`.`f`) FOR `h` IN ('x', 'y'))
+) AS `_q_0`;
+
+# title: quoting is maintained
+# dialect: snowflake
+with cte1("id", foo) as (select 1, 2) select "id" from cte1;
+WITH "CTE1" AS (
+ SELECT
+ 1 AS "id"
+)
+SELECT
+ "CTE1"."id" AS "id"
+FROM "CTE1";
+
+# title: ensures proper quoting happens after all optimizations
+# execute: false
+SELECT "foO".x FROM (SELECT 1 AS x) AS "foO";
+WITH "foO" AS (
+ SELECT
+ 1 AS "x"
+)
+SELECT
+ "foO"."x" AS "x"
+FROM "foO" AS "foO";