Adding upstream version 10.1.3.upstream/10.1.3

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

7 files changed, 160 insertions, 65 deletions

diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql
index 75bd25d..06ab96d 100644
--- a/tests/fixtures/identity.sql
+++ b/tests/fixtures/identity.sql
@@ -34,6 +34,7 @@ x >> 1
 x >> 1 | 1 & 1 ^ 1
 x || y
 1 - -1
+- -5
 dec.x + y
 a.filter
 a.b.c
@@ -438,6 +439,7 @@ SELECT student, score FROM tests CROSS JOIN UNNEST(scores) AS t(a, b)
 SELECT student, score FROM tests CROSS JOIN UNNEST(scores) WITH ORDINALITY AS t(a, b)
 SELECT student, score FROM tests CROSS JOIN UNNEST(x.scores) AS t(score)
 SELECT student, score FROM tests CROSS JOIN UNNEST(ARRAY(x.scores)) AS t(score)
+CREATE TABLE foo (id INT PRIMARY KEY ASC)
 CREATE TABLE a.b AS SELECT 1
 CREATE TABLE a.b AS SELECT a FROM a.c
 CREATE TABLE IF NOT EXISTS x AS SELECT a FROM d
@@ -579,6 +581,7 @@ SELECT * FROM (tbl1 JOIN (tbl2 JOIN tbl3) ON bla = foo)
 SELECT * FROM (tbl1 JOIN LATERAL (SELECT * FROM bla) AS tbl)
 SELECT CAST(x AS INT) /* comment */ FROM foo
 SELECT a /* x */, b /* x */
+SELECT a /* x */ /* y */ /* z */, b /* k */ /* m */
 SELECT * FROM foo /* x */, bla /* x */
 SELECT 1 /* comment */ + 1
 SELECT 1 /* c1 */ + 2 /* c2 */
@@ -588,3 +591,7 @@ SELECT x FROM a.b.c /* x */, e.f.g /* x */
 SELECT FOO(x /* c */) /* FOO */, b /* b */
 SELECT FOO(x /* c1 */ + y /* c2 */ + BLA(5 /* c3 */)) FROM VALUES (1 /* c4 */, "test" /* c5 */) /* c6 */
 SELECT a FROM x WHERE a COLLATE 'utf8_general_ci' = 'b'
+SELECT x AS INTO FROM bla
+SELECT * INTO newevent FROM event
+SELECT * INTO TEMPORARY newevent FROM event
+SELECT * INTO UNLOGGED newevent FROM event
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