summaryrefslogtreecommitdiffstats
path: root/tests/fixtures/optimizer
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2023-03-07 18:09:27 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2023-03-07 18:09:27 +0000
commite4c72fd7c8018d9dfc6043e7304275e2fffac89f (patch)
treefb9c5556844d46d0e59cc31f129d03502f06f571 /tests/fixtures/optimizer
parentAdding upstream version 11.2.3. (diff)
downloadsqlglot-e4c72fd7c8018d9dfc6043e7304275e2fffac89f.tar.xz
sqlglot-e4c72fd7c8018d9dfc6043e7304275e2fffac89f.zip
Adding upstream version 11.3.0.upstream/11.3.0
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'tests/fixtures/optimizer')
-rw-r--r--tests/fixtures/optimizer/optimizer.sql45
1 files changed, 45 insertions, 0 deletions
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;