Adding upstream version 23.16.0.upstream/23.16.0

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

2 files changed, 74 insertions, 268 deletions

diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
index a357b07..5b004fa 100644
--- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
+++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
@@ -1409,31 +1409,11 @@ WITH "_u_0" AS (
     "store_sales"."ss_quantity" <= 80 AND "store_sales"."ss_quantity" >= 61
 )
 SELECT
-  CASE
-    WHEN MAX("_u_0"."_col_0") > 3672
-    THEN MAX("_u_1"."_col_0")
-    ELSE MAX("_u_2"."_col_0")
-  END AS "bucket1",
-  CASE
-    WHEN MAX("_u_3"."_col_0") > 3392
-    THEN MAX("_u_4"."_col_0")
-    ELSE MAX("_u_5"."_col_0")
-  END AS "bucket2",
-  CASE
-    WHEN MAX("_u_6"."_col_0") > 32784
-    THEN MAX("_u_7"."_col_0")
-    ELSE MAX("_u_8"."_col_0")
-  END AS "bucket3",
-  CASE
-    WHEN MAX("_u_9"."_col_0") > 26032
-    THEN MAX("_u_10"."_col_0")
-    ELSE MAX("_u_11"."_col_0")
-  END AS "bucket4",
-  CASE
-    WHEN MAX("_u_12"."_col_0") > 23982
-    THEN MAX("_u_13"."_col_0")
-    ELSE MAX("_u_14"."_col_0")
-  END AS "bucket5"
+  CASE WHEN "_u_0"."_col_0" > 3672 THEN "_u_1"."_col_0" ELSE "_u_2"."_col_0" END AS "bucket1",
+  CASE WHEN "_u_3"."_col_0" > 3392 THEN "_u_4"."_col_0" ELSE "_u_5"."_col_0" END AS "bucket2",
+  CASE WHEN "_u_6"."_col_0" > 32784 THEN "_u_7"."_col_0" ELSE "_u_8"."_col_0" END AS "bucket3",
+  CASE WHEN "_u_9"."_col_0" > 26032 THEN "_u_10"."_col_0" ELSE "_u_11"."_col_0" END AS "bucket4",
+  CASE WHEN "_u_12"."_col_0" > 23982 THEN "_u_13"."_col_0" ELSE "_u_14"."_col_0" END AS "bucket5"
 FROM "reason" AS "reason"
 CROSS JOIN "_u_0" AS "_u_0"
 CROSS JOIN "_u_1" AS "_u_1"
diff --git a/tests/fixtures/optimizer/unnest_subqueries.sql b/tests/fixtures/optimizer/unnest_subqueries.sql
index 45e462b..a5a35b1 100644
--- a/tests/fixtures/optimizer/unnest_subqueries.sql
+++ b/tests/fixtures/optimizer/unnest_subqueries.sql
@@ -1,243 +1,69 @@
---SELECT x.a > (SELECT SUM(y.a) AS b FROM y) FROM x;
---------------------------------------
--- Unnest Subqueries
---------------------------------------
-SELECT *
-FROM x AS x
-WHERE
-  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)
-  AND x.a > (SELECT SUM(y.b) AS b FROM y WHERE x.a = y.a)
-  AND x.a <> ANY (SELECT y.a AS a FROM y WHERE y.a = x.a)
-  AND x.a NOT IN (SELECT y.a AS a FROM y WHERE y.a = x.a)
-  AND x.a IN (SELECT y.a AS a FROM y WHERE y.b = x.a)
-  AND x.a < (SELECT SUM(y.a) AS a FROM y WHERE y.a = x.a and y.a = x.b and y.b <> x.d)
-  AND EXISTS (SELECT y.a AS a, y.b AS b FROM y WHERE x.a = y.a)
-  AND x.a IN (SELECT y.a AS a FROM y LIMIT 10)
-  AND x.a IN (SELECT y.a AS a FROM y OFFSET 10)
-  AND x.a IN (SELECT y.a AS a, y.b AS b FROM y)
-  AND x.a > ANY (SELECT y.a FROM y)
-  AND x.a = (SELECT SUM(y.c) AS c FROM y WHERE y.a = x.a LIMIT 10)
-  AND x.a = (SELECT SUM(y.c) AS c FROM y WHERE y.a = x.a OFFSET 10)
-  AND x.a > ALL (SELECT y.c FROM y WHERE y.a = x.a)
-  AND x.a > (SELECT COUNT(*) as d FROM y WHERE y.a = x.a)
-  AND x.a = SUM(SELECT 1)  -- invalid statement left alone
-  AND x.a IN (SELECT max(y.b) AS b FROM y GROUP BY y.a)
-;
-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_1
-  ON x.a = _u_1.a
-LEFT JOIN (
-  SELECT
-    y.b AS b
-  FROM y
-  GROUP BY
-    y.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_3
-  ON x.a = _u_3.a
-LEFT JOIN (
-  SELECT
-    SUM(y.b) AS b,
-    y.a AS _u_5
-  FROM y
-  WHERE
-    TRUE
-  GROUP BY
-    y.a
-) AS _u_4
-  ON x.a = _u_4._u_5
-LEFT JOIN (
-  SELECT
-    SUM(y.b) AS b,
-    y.a AS _u_7
-  FROM y
-  WHERE
-    TRUE
-  GROUP BY
-    y.a
-) AS _u_6
-  ON x.a = _u_6._u_7
-LEFT JOIN (
-  SELECT
-    y.a AS a
-  FROM y
-  WHERE
-    TRUE
-  GROUP BY
-    y.a
-) AS _u_8
-  ON _u_8.a = x.a
-LEFT JOIN (
-  SELECT
-    y.a AS a
-  FROM y
-  WHERE
-    TRUE
-  GROUP BY
-    y.a
-) AS _u_9
-  ON _u_9.a = x.a
-LEFT JOIN (
-  SELECT
-    ARRAY_AGG(y.a) AS a,
-    y.b AS _u_11
-  FROM y
-  WHERE
-    TRUE
-  GROUP BY
-    y.b
-) AS _u_10
-  ON _u_10._u_11 = x.a
-LEFT JOIN (
-  SELECT
-    SUM(y.a) AS a,
-    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_12
-  ON _u_12._u_13 = x.a AND _u_12._u_13 = x.b
-LEFT JOIN (
-  SELECT
-    y.a AS a
-  FROM y
-  WHERE
-    TRUE
-  GROUP BY
-    y.a
-) AS _u_15
-  ON x.a = _u_15.a
-LEFT JOIN (
-  SELECT
-    ARRAY_AGG(c),
-    y.a AS _u_20
-  FROM y
-  WHERE
-    TRUE
-  GROUP BY
-    y.a
-) AS _u_19
-  ON _u_19._u_20 = x.a
-LEFT JOIN (
-  SELECT
-    COUNT(*) AS d,
-    y.a AS _u_22
-  FROM y
-  WHERE
-    TRUE
-  GROUP BY
-    y.a
-) AS _u_21
-  ON _u_21._u_22 = x.a
-LEFT JOIN (
-  SELECT
-    _q.b
-  FROM (
-    SELECT
-      MAX(y.b) AS b
-    FROM y
-    GROUP BY
-      y.a
-  ) AS _q
-  GROUP BY
-    _q.b
-) AS _u_24
-  ON x.a = _u_24.b
-WHERE
-  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_4.b
-  AND x.a > _u_6.b
-  AND x.a = _u_8.a
-  AND NOT x.a = _u_9.a
-  AND ARRAY_ANY(_u_10.a, _x -> _x = x.a)
-  AND (
-    x.a < _u_12.a AND ARRAY_ANY(_u_12._u_14, _x -> _x <> x.d)
-  )
-  AND NOT _u_15.a IS NULL
-  AND x.a IN (
-    SELECT
-      y.a AS a
-    FROM y
-    LIMIT 10
-  )
-  AND x.a IN (
-    SELECT
-      y.a AS a
-    FROM y
-    OFFSET 10
-  )
-  AND x.a IN (
-    SELECT
-      y.a AS a,
-      y.b AS b
-    FROM y
-  )
-  AND x.a > ANY (
-    SELECT
-      y.a
-    FROM y
-  )
-  AND x.a = (
-    SELECT
-      SUM(y.c) AS c
-    FROM y
-    WHERE
-      y.a = x.a
-    LIMIT 10
-  )
-  AND x.a = (
-    SELECT
-      SUM(y.c) AS c
-    FROM y
-    WHERE
-      y.a = x.a
-    OFFSET 10
-  )
-  AND ARRAY_ALL(_u_19."", _x -> _x = x.a)
-  AND x.a > COALESCE(_u_21.d, 0)
-  AND x.a = SUM(SELECT
-    1) /* invalid statement left alone */
-  AND NOT _u_24.b IS NULL
-;
-SELECT
-  CAST((
-    SELECT
-      x.a AS a
-    FROM x
-  ) AS TEXT) AS a;
-SELECT
-  CAST((
-    SELECT
-      x.a AS a
-    FROM x
-  ) AS TEXT) AS a;
+SELECT * FROM x WHERE x.a = (SELECT SUM(y.a) AS a FROM y);
+SELECT * FROM x CROSS JOIN (SELECT SUM(y.a) AS a FROM y) AS _u_0 WHERE x.a = _u_0.a;
+
+SELECT * FROM x WHERE x.a IN (SELECT y.a AS a FROM y);
+SELECT * FROM x LEFT JOIN (SELECT y.a AS a FROM y GROUP BY y.a) AS _u_0 ON x.a = _u_0.a WHERE NOT _u_0.a IS NULL;
+
+SELECT * FROM x WHERE x.a IN (SELECT y.b AS b FROM y);
+SELECT * FROM x LEFT JOIN (SELECT y.b AS b FROM y GROUP BY y.b) AS _u_0 ON x.a = _u_0.b WHERE NOT _u_0.b IS NULL;
+
+SELECT * FROM x WHERE x.a = ANY (SELECT y.a AS a FROM y);
+SELECT * FROM x LEFT JOIN (SELECT y.a AS a FROM y GROUP BY y.a) AS _u_0 ON x.a = _u_0.a WHERE NOT _u_0.a IS NULL;
+
+SELECT * FROM x WHERE x.a = (SELECT SUM(y.b) AS b FROM y WHERE x.a = y.a);
+SELECT * FROM x LEFT JOIN (SELECT SUM(y.b) AS b, y.a AS _u_1 FROM y WHERE TRUE GROUP BY y.a) AS _u_0 ON x.a = _u_0._u_1 WHERE x.a = _u_0.b;
+
+SELECT * FROM x WHERE x.a > (SELECT SUM(y.b) AS b FROM y WHERE x.a = y.a);
+SELECT * FROM x LEFT JOIN (SELECT SUM(y.b) AS b, y.a AS _u_1 FROM y WHERE TRUE GROUP BY y.a) AS _u_0 ON x.a = _u_0._u_1 WHERE x.a > _u_0.b;
+
+SELECT * FROM x WHERE x.a <> ANY (SELECT y.a AS a FROM y WHERE y.a = x.a);
+SELECT * FROM x LEFT JOIN (SELECT y.a AS a FROM y WHERE TRUE GROUP BY y.a) AS _u_0 ON _u_0.a = x.a WHERE x.a <> _u_0.a;
+
+SELECT * FROM x WHERE x.a NOT IN (SELECT y.a AS a FROM y WHERE y.a = x.a);
+SELECT * FROM x LEFT JOIN (SELECT y.a AS a FROM y WHERE TRUE GROUP BY y.a) AS _u_0 ON _u_0.a = x.a WHERE NOT x.a = _u_0.a;
+
+SELECT * FROM x WHERE x.a IN (SELECT y.a AS a FROM y WHERE y.b = x.a);
+SELECT * FROM x LEFT JOIN (SELECT ARRAY_AGG(y.a) AS a, y.b AS _u_1 FROM y WHERE TRUE GROUP BY y.b) AS _u_0 ON _u_0._u_1 = x.a WHERE ARRAY_ANY(_u_0.a, _x -> _x = x.a);
+
+SELECT * FROM x WHERE x.a < (SELECT SUM(y.a) AS a FROM y WHERE y.a = x.a and y.a = x.b and y.b <> x.d);
+SELECT * FROM x LEFT JOIN (SELECT SUM(y.a) AS a, y.a AS _u_1, ARRAY_AGG(y.b) AS _u_2 FROM y WHERE TRUE AND TRUE AND TRUE GROUP BY y.a) AS _u_0 ON _u_0._u_1 = x.a AND _u_0._u_1 = x.b WHERE (x.a < _u_0.a AND ARRAY_ANY(_u_0._u_2, _x -> _x <> x.d));
+
+SELECT * FROM x WHERE EXISTS (SELECT y.a AS a, y.b AS b FROM y WHERE x.a = y.a);
+SELECT * FROM x LEFT JOIN (SELECT y.a AS a FROM y WHERE TRUE GROUP BY y.a) AS _u_0 ON x.a = _u_0.a WHERE NOT _u_0.a IS NULL;
+
+SELECT * FROM x WHERE x.a IN (SELECT y.a AS a FROM y LIMIT 10);
+SELECT * FROM x WHERE x.a IN (SELECT y.a AS a FROM y LIMIT 10);
+
+SELECT * FROM x.a WHERE x.a IN (SELECT y.a AS a FROM y OFFSET 10);
+SELECT * FROM x.a WHERE x.a IN (SELECT y.a AS a FROM y OFFSET 10);
+
+SELECT * FROM x.a WHERE x.a IN (SELECT y.a AS a, y.b AS b FROM y);
+SELECT * FROM x.a WHERE x.a IN (SELECT y.a AS a, y.b AS b FROM y);
+
+SELECT * FROM x.a WHERE x.a > ANY (SELECT y.a FROM y);
+SELECT * FROM x.a WHERE x.a > ANY (SELECT y.a FROM y);
+
+SELECT * FROM x WHERE x.a = (SELECT SUM(y.c) AS c FROM y WHERE y.a = x.a LIMIT 10);
+SELECT * FROM x WHERE x.a = (SELECT SUM(y.c) AS c FROM y WHERE y.a = x.a LIMIT 10);
+
+SELECT * FROM x WHERE x.a = (SELECT SUM(y.c) AS c FROM y WHERE y.a = x.a OFFSET 10);
+SELECT * FROM x WHERE x.a = (SELECT SUM(y.c) AS c FROM y WHERE y.a = x.a OFFSET 10);
+
+SELECT * FROM x WHERE x.a > ALL (SELECT y.c AS c FROM y WHERE y.a = x.a);
+SELECT * FROM x LEFT JOIN (SELECT ARRAY_AGG(y.c) AS c, y.a AS _u_1 FROM y WHERE TRUE GROUP BY y.a) AS _u_0 ON _u_0._u_1 = x.a WHERE ARRAY_ALL(_u_0.c, _x -> x.a > _x);
+
+SELECT * FROM x WHERE x.a > (SELECT COUNT(*) as d FROM y WHERE y.a = x.a);
+SELECT * FROM x LEFT JOIN (SELECT COUNT(*) AS d, y.a AS _u_1 FROM y WHERE TRUE GROUP BY y.a) AS _u_0 ON _u_0._u_1 = x.a WHERE x.a > COALESCE(_u_0.d, 0);
+
+# title: invalid statement left alone
+SELECT * FROM x WHERE x.a = SUM(SELECT 1);
+SELECT * FROM x WHERE x.a = SUM(SELECT 1);
+
+SELECT * FROM x WHERE x.a IN (SELECT max(y.b) AS b FROM y GROUP BY y.a);
+SELECT * FROM x LEFT JOIN (SELECT _q.b AS b FROM (SELECT MAX(y.b) AS b FROM y GROUP BY y.a) AS _q GROUP BY _q.b) AS _u_0 ON x.a = _u_0.b WHERE NOT _u_0.b IS NULL;
+
+SELECT x.a > (SELECT SUM(y.a) AS b FROM y) FROM x;
+SELECT x.a > _u_0.b FROM x CROSS JOIN (SELECT SUM(y.a) AS b FROM y) AS _u_0;
+
+SELECT (SELECT MAX(t2.c1) AS c1 FROM t2 WHERE t2.c2 = t1.c2 AND t2.c3 <= TRUNC(t1.c3)) AS c FROM t1;
+SELECT _u_0.c1 AS c FROM t1 LEFT JOIN (SELECT MAX(t2.c1) AS c1, t2.c2 AS _u_1, MAX(t2.c3) AS _u_2 FROM t2 WHERE TRUE AND TRUE GROUP BY t2.c2) AS _u_0 ON _u_0._u_1 = t1.c2 WHERE _u_0._u_2 <= TRUNC(t1.c3);