Adding upstream version 20.1.0.upstream/20.1.0

Signed-off-by: Daniel Baumann <mail@daniel-baumann.ch>

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`;