From ebec59cc5cb6c6856705bf82ced7fe8d9f75b0d0 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Tue, 7 Mar 2023 19:09:31 +0100 Subject: Merging upstream version 11.3.0. Signed-off-by: Daniel Baumann --- 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