diff options
author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2023-03-07 18:09:27 +0000 |
---|---|---|
committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2023-03-07 18:09:27 +0000 |
commit | e4c72fd7c8018d9dfc6043e7304275e2fffac89f (patch) | |
tree | fb9c5556844d46d0e59cc31f129d03502f06f571 /tests/fixtures/optimizer | |
parent | Adding upstream version 11.2.3. (diff) | |
download | sqlglot-upstream/11.3.0.tar.xz sqlglot-upstream/11.3.0.zip |
Adding upstream version 11.3.0.upstream/11.3.0
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to '')
-rw-r--r-- | tests/fixtures/optimizer/optimizer.sql | 45 |
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; |