From ebec59cc5cb6c6856705bf82ced7fe8d9f75b0d0 Mon Sep 17 00:00:00 2001
From: Daniel Baumann <daniel.baumann@progress-linux.org>
Date: Tue, 7 Mar 2023 19:09:31 +0100
Subject: Merging upstream version 11.3.0.

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

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

diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index 6ccf24e..a14e325 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -385,3 +385,48 @@ SELECT
   "x"."a" + 1 AS "b",
   "x"."b" + 1 AS "c"
 FROM "x" AS "x";
+
+# title: left join doesnt push down predicate to join in merge subqueries
+# execute: false
+SELECT
+  main_query.id,
+  main_query.score
+FROM (
+  SELECT
+    alias_1.id,
+    score
+  FROM (
+    SELECT
+      company_table.score AS score,
+      id
+    FROM company_table
+  ) AS alias_1
+  JOIN (
+    SELECT
+      id
+    FROM (
+      SELECT
+        company_table_2.id,
+        CASE WHEN unlocked.company_id IS NULL THEN 0 ELSE 1 END AS is_exported
+      FROM company_table AS company_table_2
+      LEFT JOIN unlocked AS unlocked
+        ON company_table_2.id = unlocked.company_id
+    )
+    WHERE
+      NOT id IS NULL AND is_exported = FALSE
+  ) AS alias_2
+    ON (
+      alias_1.id = alias_2.id
+    )
+) AS main_query;
+SELECT
+  "company_table"."id" AS "id",
+  "company_table"."score" AS "score"
+FROM "company_table" AS "company_table"
+JOIN "company_table" AS "company_table_2"
+  ON "company_table"."id" = "company_table_2"."id"
+LEFT JOIN "unlocked" AS "unlocked"
+  ON "company_table_2"."id" = "unlocked"."company_id"
+WHERE
+  CASE WHEN "unlocked"."company_id" IS NULL THEN 0 ELSE 1 END = FALSE
+  AND NOT "company_table_2"."id" IS NULL;
-- 
cgit v1.2.3

