From 358a09296d7198a4cc142f1976de8f3eb3318e58 Mon Sep 17 00:00:00 2001
From: Daniel Baumann <daniel.baumann@progress-linux.org>
Date: Fri, 16 Jun 2023 11:41:15 +0200
Subject: Adding upstream version 16.2.1.

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

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

diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index e0567d7..0cb1a58 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -101,10 +101,10 @@ SELECT
   "x"."a" AS "a",
   SUM("y"."b") AS "sum_b"
 FROM "x" AS "x"
-JOIN "y" AS "y"
-  ON "x"."b" = "y"."b"
 LEFT JOIN "_u_0" AS "_u_0"
   ON "x"."b" = "_u_0"."_u_1"
+JOIN "y" AS "y"
+  ON "x"."b" = "y"."b"
 WHERE
   "_u_0"."_col_0" >= 0 AND "x"."a" > 1
 GROUP BY
@@ -210,10 +210,10 @@ SELECT
   "n"."b" AS "b",
   "o"."b" AS "b"
 FROM "n"
-FULL JOIN "o"
-  ON "n"."a" = "o"."a"
 JOIN "n" AS "n2"
   ON "n"."a" = "n2"."a"
+FULL JOIN "o"
+  ON "n"."a" = "o"."a"
 WHERE
   "o"."b" > 0;
 
@@ -619,3 +619,30 @@ WITH "foO" AS (
 SELECT
   "foO"."x" AS "x"
 FROM "foO" AS "foO";
+
+# title: lateral subquery
+# execute: false
+# dialect: postgres
+SELECT u.user_id, l.log_date
+FROM   users u
+CROSS JOIN LATERAL (
+    SELECT l.log_date
+    FROM   logs l
+    WHERE  l.user_id = u.user_id AND l.log_date <= 100
+    ORDER  BY l.log_date DESC NULLS LAST
+    LIMIT  1
+) l;
+SELECT
+  "u"."user_id" AS "user_id",
+  "l"."log_date" AS "log_date"
+FROM "users" AS "u"
+CROSS JOIN LATERAL (
+  SELECT
+    "l"."log_date"
+  FROM "logs" AS "l"
+  WHERE
+    "l"."log_date" <= 100 AND "l"."user_id" = "u"."user_id"
+  ORDER BY
+    "l"."log_date" DESC NULLS LAST
+  LIMIT 1
+) AS "l";
-- 
cgit v1.2.3