From 42a1548cecf48d18233f56e3385cf9c89abcb9c2 Mon Sep 17 00:00:00 2001
From: Daniel Baumann <daniel.baumann@progress-linux.org>
Date: Sun, 3 Mar 2024 15:11:07 +0100
Subject: Merging upstream version 22.2.0.

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

(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 3caeef6..45e462b 100644
--- a/tests/fixtures/optimizer/unnest_subqueries.sql
+++ b/tests/fixtures/optimizer/unnest_subqueries.sql
@@ -25,6 +25,7 @@ WHERE
   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
   *
@@ -155,6 +156,20 @@ LEFT JOIN (
     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
@@ -212,6 +227,7 @@ WHERE
   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((
-- 
cgit v1.2.3

