From 1a60bbae98d3b530924a6807a55f8250de19ea86 Mon Sep 17 00:00:00 2001
From: Daniel Baumann <daniel.baumann@progress-linux.org>
Date: Fri, 2 Dec 2022 10:16:29 +0100
Subject: Adding upstream version 10.1.3.

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
---
 tests/fixtures/optimizer/unnest_subqueries.sql | 84 ++++++++++++++------------
 1 file changed, 46 insertions(+), 38 deletions(-)

(limited to 'tests/fixtures/optimizer/unnest_subqueries.sql')

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
-- 
cgit v1.2.3