summaryrefslogtreecommitdiffstats
path: root/tests/fixtures/optimizer/optimizer.sql
diff options
context:
space:
mode:
authorDaniel Baumann <mail@daniel-baumann.ch>2023-12-10 10:45:55 +0000
committerDaniel Baumann <mail@daniel-baumann.ch>2023-12-10 10:45:55 +0000
commit02df6cdb000c8dbf739abda2af321a4f90d1b059 (patch)
tree2fc1daf848082ff67a11e60025cac260e3c318b2 /tests/fixtures/optimizer/optimizer.sql
parentAdding upstream version 19.0.1. (diff)
downloadsqlglot-02df6cdb000c8dbf739abda2af321a4f90d1b059.tar.xz
sqlglot-02df6cdb000c8dbf739abda2af321a4f90d1b059.zip
Adding upstream version 20.1.0.upstream/20.1.0
Signed-off-by: Daniel Baumann <mail@daniel-baumann.ch>
Diffstat (limited to 'tests/fixtures/optimizer/optimizer.sql')
-rw-r--r--tests/fixtures/optimizer/optimizer.sql41
1 files changed, 41 insertions, 0 deletions
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index a9d6584..f81d54a 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -548,6 +548,23 @@ FROM (
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 explicit selections where one of them is excluded & selected at the same time
+# note: we need to respect the exclude when selecting * from pivoted source and not include the computed column twice
+# execute: false
+SELECT * EXCEPT (x), CAST(x AS TEXT) AS x 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"."y" AS "y",
+ "_q_1"."z" AS "z",
+ CAST("_q_1"."x" AS TEXT) AS "x"
+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'));
@@ -1074,3 +1091,27 @@ SELECT
`_q_0`.`fruitstruct`.`$id` AS `$id`,
`_q_0`.`fruitstruct`.`value` AS `value`
FROM `_q_0` AS `_q_0`;
+
+# title: mysql is case-sensitive by default
+# dialect: mysql
+# execute: false
+WITH T AS (SELECT 1 AS CoL) SELECT * FROM `T`;
+WITH `T` AS (
+ SELECT
+ 1 AS `CoL`
+)
+SELECT
+ `T`.`CoL` AS `CoL`
+FROM `T`;
+
+# title: override mysql's settings so it normalizes to lowercase
+# dialect: mysql, normalization_strategy = lowercase
+# execute: false
+WITH T AS (SELECT 1 AS `CoL`) SELECT * FROM T;
+WITH `t` AS (
+ SELECT
+ 1 AS `CoL`
+)
+SELECT
+ `t`.`CoL` AS `CoL`
+FROM `t`;