Merging upstream version 10.1.3.

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

6 files changed, 153 insertions, 65 deletions

diff --git a/tests/fixtures/optimizer/eliminate_subqueries.sql b/tests/fixtures/optimizer/eliminate_subqueries.sql
index f395c0a..c566657 100644
--- a/tests/fixtures/optimizer/eliminate_subqueries.sql
+++ b/tests/fixtures/optimizer/eliminate_subqueries.sql
@@ -77,3 +77,15 @@ WITH x_2 AS (SELECT * FROM x AS x JOIN y AS y ON x.id = y.id) SELECT x.id FROM x
 -- Existing duplicate CTE
 WITH y AS (SELECT a FROM x) SELECT a FROM (SELECT a FROM x) AS y JOIN y AS z;
 WITH y AS (SELECT a FROM x) SELECT a FROM y AS y JOIN y AS z;
+
+-- Nested CTE
+WITH cte1 AS (SELECT a FROM x) SELECT a FROM (WITH cte2 AS (SELECT a FROM cte1) SELECT a FROM cte2);
+WITH cte1 AS (SELECT a FROM x), cte2 AS (SELECT a FROM cte1), cte AS (SELECT a FROM cte2 AS cte2) SELECT a FROM cte AS cte;
+
+-- Nested CTE inside CTE
+WITH cte1 AS (WITH cte2 AS (SELECT a FROM x) SELECT t.a FROM cte2 AS t) SELECT a FROM cte1;
+WITH cte2 AS (SELECT a FROM x), cte1 AS (SELECT t.a FROM cte2 AS t) SELECT a FROM cte1;
+
+-- Duplicate CTE nested in CTE
+WITH cte1 AS (SELECT a FROM x), cte2 AS (WITH cte3 AS (SELECT a FROM x) SELECT a FROM cte3) SELECT a FROM cte2;
+WITH cte1 AS (SELECT a FROM x), cte2 AS (SELECT a FROM cte1 AS cte3) SELECT a FROM cte2;
diff --git a/tests/fixtures/optimizer/lower_identities.sql b/tests/fixtures/optimizer/lower_identities.sql
new file mode 100644
index 0000000..cea346f
--- /dev/null
+++ b/tests/fixtures/optimizer/lower_identities.sql
@@ -0,0 +1,41 @@
+SELECT a FROM x;
+SELECT a FROM x;
+
+SELECT "A" FROM "X";
+SELECT "A" FROM "X";
+
+SELECT a AS A FROM x;
+SELECT a AS A FROM x;
+
+SELECT * FROM x;
+SELECT * FROM x;
+
+SELECT A FROM x;
+SELECT a FROM x;
+
+SELECT a FROM X;
+SELECT a FROM x;
+
+SELECT A AS A FROM (SELECT a AS A FROM x);
+SELECT a AS A FROM (SELECT a AS a FROM x);
+
+SELECT a AS B FROM x ORDER BY B;
+SELECT a AS B FROM x ORDER BY B;
+
+SELECT A FROM x ORDER BY A;
+SELECT a FROM x ORDER BY a;
+
+SELECT A AS B FROM X GROUP BY A HAVING SUM(B) > 0;
+SELECT a AS B FROM x GROUP BY a HAVING SUM(b) > 0;
+
+SELECT A AS B, SUM(B) AS C FROM X GROUP BY A HAVING C > 0;
+SELECT a AS B, SUM(b) AS C FROM x GROUP BY a HAVING C > 0;
+
+SELECT A FROM X UNION SELECT A FROM X;
+SELECT a FROM x UNION SELECT a FROM x;
+
+SELECT A AS A FROM X UNION SELECT A AS A FROM X;
+SELECT a AS A FROM x UNION SELECT a AS A FROM x;
+
+(SELECT A AS A FROM X);
+(SELECT a AS A FROM x);
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index a1e531b..a692c7d 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -276,3 +276,18 @@ SELECT /*+ COALESCE(3),
 FROM `x` AS `x`
 JOIN `y` AS `y`
   ON `x`.`b` = `y`.`b`;
+
+WITH cte1 AS (
+  WITH cte2 AS (
+    SELECT a, b FROM x
+  )
+  SELECT a1
+  FROM (
+    WITH cte3 AS (SELECT 1)
+    SELECT a AS a1, b AS b1 FROM cte2
+  )
+)
+SELECT a1 FROM cte1;
+SELECT
+  "x"."a" AS "a1"
+FROM "x" AS "x";
diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql
index 7207ba2..d9c7779 100644
--- a/tests/fixtures/optimizer/simplify.sql
+++ b/tests/fixtures/optimizer/simplify.sql
@@ -274,6 +274,15 @@ TRUE;
 -(-1);
 1;
 
+- -+1;
+1;
+
++-1;
+-1;
+
+++1;
+1;
+
 0.06 - 0.01;
 0.05;
 
diff --git a/tests/fixtures/optimizer/tpc-h/tpc-h.sql b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
index 8138b11..4893743 100644
--- a/tests/fixtures/optimizer/tpc-h/tpc-h.sql
+++ b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
@@ -666,11 +666,20 @@ WITH "supplier_2" AS (
   FROM "nation" AS "nation"
   WHERE
     "nation"."n_name" = 'GERMANY'
+), "_u_0" AS (
+  SELECT
+    SUM("partsupp"."ps_supplycost" * "partsupp"."ps_availqty") * 0.0001 AS "_col_0"
+  FROM "partsupp" AS "partsupp"
+  JOIN "supplier_2" AS "supplier"
+    ON "partsupp"."ps_suppkey" = "supplier"."s_suppkey"
+  JOIN "nation_2" AS "nation"
+    ON "supplier"."s_nationkey" = "nation"."n_nationkey"
 )
 SELECT
   "partsupp"."ps_partkey" AS "ps_partkey",
   SUM("partsupp"."ps_supplycost" * "partsupp"."ps_availqty") AS "value"
 FROM "partsupp" AS "partsupp"
+CROSS JOIN "_u_0" AS "_u_0"
 JOIN "supplier_2" AS "supplier"
   ON "partsupp"."ps_suppkey" = "supplier"."s_suppkey"
 JOIN "nation_2" AS "nation"
@@ -678,15 +687,7 @@ JOIN "nation_2" AS "nation"
 GROUP BY
   "partsupp"."ps_partkey"
 HAVING
-  SUM("partsupp"."ps_supplycost" * "partsupp"."ps_availqty") > (
-    SELECT
-      SUM("partsupp"."ps_supplycost" * "partsupp"."ps_availqty") * 0.0001 AS "_col_0"
-    FROM "partsupp" AS "partsupp"
-    JOIN "supplier_2" AS "supplier"
-      ON "partsupp"."ps_suppkey" = "supplier"."s_suppkey"
-    JOIN "nation_2" AS "nation"
-      ON "supplier"."s_nationkey" = "nation"."n_nationkey"
-  )
+  SUM("partsupp"."ps_supplycost" * "partsupp"."ps_availqty") > MAX("_u_0"."_col_0")
 ORDER BY
   "value" DESC;
 
@@ -880,6 +881,10 @@ WITH "revenue" AS (
     AND CAST("lineitem"."l_shipdate" AS DATE) >= CAST('1996-01-01' AS DATE)
   GROUP BY
     "lineitem"."l_suppkey"
+), "_u_0" AS (
+  SELECT
+    MAX("revenue"."total_revenue") AS "_col_0"
+  FROM "revenue"
 )
 SELECT
   "supplier"."s_suppkey" AS "s_suppkey",
@@ -889,12 +894,9 @@ SELECT
   "revenue"."total_revenue" AS "total_revenue"
 FROM "supplier" AS "supplier"
 JOIN "revenue"
-  ON "revenue"."total_revenue" = (
-    SELECT
-      MAX("revenue"."total_revenue") AS "_col_0"
-    FROM "revenue"
-  )
-  AND "supplier"."s_suppkey" = "revenue"."supplier_no"
+  ON "supplier"."s_suppkey" = "revenue"."supplier_no"
+JOIN "_u_0" AS "_u_0"
+  ON "revenue"."total_revenue" = "_u_0"."_col_0"
 ORDER BY
   "s_suppkey";
 
@@ -1395,7 +1397,14 @@ order by
         cntrycode;
 WITH "_u_0" AS (
   SELECT
-    "orders"."o_custkey" AS "_u_1"
+    AVG("customer"."c_acctbal") AS "_col_0"
+  FROM "customer" AS "customer"
+  WHERE
+    "customer"."c_acctbal" > 0.00
+    AND SUBSTRING("customer"."c_phone", 1, 2) IN ('13', '31', '23', '29', '30', '18', '17')
+), "_u_1" AS (
+  SELECT
+    "orders"."o_custkey" AS "_u_2"
   FROM "orders" AS "orders"
   GROUP BY
     "orders"."o_custkey"
@@ -1405,18 +1414,12 @@ SELECT
   COUNT(*) AS "numcust",
   SUM("customer"."c_acctbal") AS "totacctbal"
 FROM "customer" AS "customer"
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "_u_0"."_u_1" = "customer"."c_custkey"
+JOIN "_u_0" AS "_u_0"
+  ON "customer"."c_acctbal" > "_u_0"."_col_0"
+LEFT JOIN "_u_1" AS "_u_1"
+  ON "_u_1"."_u_2" = "customer"."c_custkey"
 WHERE
-  "_u_0"."_u_1" IS NULL
-  AND "customer"."c_acctbal" > (
-    SELECT
-      AVG("customer"."c_acctbal") AS "_col_0"
-    FROM "customer" AS "customer"
-    WHERE
-      "customer"."c_acctbal" > 0.00
-      AND SUBSTRING("customer"."c_phone", 1, 2) IN ('13', '31', '23', '29', '30', '18', '17')
-  )
+  "_u_1"."_u_2" IS NULL
   AND SUBSTRING("customer"."c_phone", 1, 2) IN ('13', '31', '23', '29', '30', '18', '17')
 GROUP BY
   SUBSTRING("customer"."c_phone", 1, 2)
diff --git a/tests/fixtures/optimizer/unnest_subqueries.sql b/tests/fixtures/optimizer/unnest_subqueries.sql
index f53121a..dc373a0 100644
--- a/tests/fixtures/optimizer/unnest_subqueries.sql
+++ b/tests/fixtures/optimizer/unnest_subqueries.sql
@@ -1,10 +1,12 @@
+--SELECT x.a > (SELECT SUM(y.a) AS b FROM y) FROM x;
 --------------------------------------
 -- Unnest Subqueries
 --------------------------------------
 SELECT *
 FROM x AS x
 WHERE
-  x.a IN (SELECT y.a AS a FROM y)
+  x.a = (SELECT SUM(y.a) AS a FROM y)
+  AND x.a IN (SELECT y.a AS a FROM y)
   AND x.a IN (SELECT y.b AS b FROM y)
   AND x.a = ANY (SELECT y.a AS a FROM y)
   AND x.a = (SELECT SUM(y.b) AS b FROM y WHERE x.a = y.a)
@@ -24,52 +26,57 @@ WHERE
 SELECT
   *
 FROM x AS x
+CROSS JOIN (
+  SELECT
+    SUM(y.a) AS a
+  FROM y
+) AS "_u_0"
 LEFT JOIN (
   SELECT
     y.a AS a
   FROM y
   GROUP BY
     y.a
-) AS "_u_0"
-  ON x.a = "_u_0"."a"
+) AS "_u_1"
+  ON x.a = "_u_1"."a"
 LEFT JOIN (
   SELECT
     y.b AS b
   FROM y
   GROUP BY
     y.b
-) AS "_u_1"
-  ON x.a = "_u_1"."b"
+) AS "_u_2"
+  ON x.a = "_u_2"."b"
 LEFT JOIN (
   SELECT
     y.a AS a
   FROM y
   GROUP BY
     y.a
-) AS "_u_2"
-  ON x.a = "_u_2"."a"
+) AS "_u_3"
+  ON x.a = "_u_3"."a"
 LEFT JOIN (
   SELECT
     SUM(y.b) AS b,
-    y.a AS _u_4
+    y.a AS _u_5
   FROM y
   WHERE
     TRUE
   GROUP BY
     y.a
-) AS "_u_3"
-  ON x.a = "_u_3"."_u_4"
+) AS "_u_4"
+  ON x.a = "_u_4"."_u_5"
 LEFT JOIN (
   SELECT
     SUM(y.b) AS b,
-    y.a AS _u_6
+    y.a AS _u_7
   FROM y
   WHERE
     TRUE
   GROUP BY
     y.a
-) AS "_u_5"
-  ON x.a = "_u_5"."_u_6"
+) AS "_u_6"
+  ON x.a = "_u_6"."_u_7"
 LEFT JOIN (
   SELECT
     y.a AS a
@@ -78,8 +85,8 @@ LEFT JOIN (
     TRUE
   GROUP BY
     y.a
-) AS "_u_7"
-  ON "_u_7".a = x.a
+) AS "_u_8"
+  ON "_u_8".a = x.a
 LEFT JOIN (
   SELECT
     y.a AS a
@@ -88,31 +95,31 @@ LEFT JOIN (
     TRUE
   GROUP BY
     y.a
-) AS "_u_8"
-  ON "_u_8".a = x.a
+) AS "_u_9"
+  ON "_u_9".a = x.a
 LEFT JOIN (
   SELECT
     ARRAY_AGG(y.a) AS a,
-    y.b AS _u_10
+    y.b AS _u_11
   FROM y
   WHERE
     TRUE
   GROUP BY
     y.b
-) AS "_u_9"
-  ON "_u_9"."_u_10" = x.a
+) AS "_u_10"
+  ON "_u_10"."_u_11" = x.a
 LEFT JOIN (
   SELECT
     SUM(y.a) AS a,
-    y.a AS _u_12,
-    ARRAY_AGG(y.b) AS _u_13
+    y.a AS _u_13,
+    ARRAY_AGG(y.b) AS _u_14
   FROM y
   WHERE
     TRUE AND TRUE AND TRUE
   GROUP BY
     y.a
-) AS "_u_11"
-  ON "_u_11"."_u_12" = x.a AND "_u_11"."_u_12" = x.b
+) AS "_u_12"
+  ON "_u_12"."_u_13" = x.a AND "_u_12"."_u_13" = x.b
 LEFT JOIN (
   SELECT
     y.a AS a
@@ -121,37 +128,38 @@ LEFT JOIN (
     TRUE
   GROUP BY
     y.a
-) AS "_u_14"
-  ON x.a = "_u_14".a
+) AS "_u_15"
+  ON x.a = "_u_15".a
 WHERE
-  NOT "_u_0"."a" IS NULL
-  AND NOT "_u_1"."b" IS NULL
-  AND NOT "_u_2"."a" IS NULL
+  x.a = "_u_0".a
+  AND NOT "_u_1"."a" IS NULL
+  AND NOT "_u_2"."b" IS NULL
+  AND NOT "_u_3"."a" IS NULL
   AND (
-    x.a = "_u_3".b AND NOT "_u_3"."_u_4" IS NULL
+    x.a = "_u_4".b AND NOT "_u_4"."_u_5" IS NULL
   )
   AND (
-    x.a > "_u_5".b AND NOT "_u_5"."_u_6" IS NULL
+    x.a > "_u_6".b AND NOT "_u_6"."_u_7" IS NULL
   )
   AND (
-    None = "_u_7".a AND NOT "_u_7".a IS NULL
+    None = "_u_8".a AND NOT "_u_8".a IS NULL
   )
   AND NOT (
-    x.a = "_u_8".a AND NOT "_u_8".a IS NULL
+    x.a = "_u_9".a AND NOT "_u_9".a IS NULL
   )
   AND (
-    ARRAY_ANY("_u_9".a, _x -> _x = x.a) AND NOT "_u_9"."_u_10" IS NULL
+    ARRAY_ANY("_u_10".a, _x -> _x = x.a) AND NOT "_u_10"."_u_11" IS NULL
   )
   AND (
     (
       (
-        x.a < "_u_11".a AND NOT "_u_11"."_u_12" IS NULL
-      ) AND NOT "_u_11"."_u_12" IS NULL
+        x.a < "_u_12".a AND NOT "_u_12"."_u_13" IS NULL
+      ) AND NOT "_u_12"."_u_13" IS NULL
     )
-    AND ARRAY_ANY("_u_11"."_u_13", "_x" -> "_x" <> x.d)
+    AND ARRAY_ANY("_u_12"."_u_14", "_x" -> "_x" <> x.d)
   )
   AND (
-    NOT "_u_14".a IS NULL AND NOT "_u_14".a IS NULL
+    NOT "_u_15".a IS NULL AND NOT "_u_15".a IS NULL
   )
   AND x.a IN (
     SELECT