1 files changed, 31 insertions, 4 deletions
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";