diff options
author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2022-10-15 13:52:53 +0000 |
---|---|---|
committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2022-10-15 13:52:53 +0000 |
commit | 97d3673ec2d668050912aa6aea1816885ca6c5ab (patch) | |
tree | f391e30e039a3d22368e9696e171f759e104c765 /tests/fixtures/optimizer/eliminate_joins.sql | |
parent | Adding upstream version 6.3.1. (diff) | |
download | sqlglot-97d3673ec2d668050912aa6aea1816885ca6c5ab.tar.xz sqlglot-97d3673ec2d668050912aa6aea1816885ca6c5ab.zip |
Adding upstream version 7.1.3.upstream/7.1.3
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to '')
-rw-r--r-- | tests/fixtures/optimizer/eliminate_joins.sql | 317 |
1 files changed, 317 insertions, 0 deletions
diff --git a/tests/fixtures/optimizer/eliminate_joins.sql b/tests/fixtures/optimizer/eliminate_joins.sql new file mode 100644 index 0000000..ac3d92d --- /dev/null +++ b/tests/fixtures/optimizer/eliminate_joins.sql @@ -0,0 +1,317 @@ +# title: Remove left join on distinct derived table +SELECT + x.a +FROM x +LEFT JOIN ( + SELECT DISTINCT + y.b + FROM y +) AS y + ON x.b = y.b; +SELECT + x.a +FROM x; + +# title: Remove left join on grouped derived table +SELECT + x.a +FROM x +LEFT JOIN ( + SELECT + y.b, + SUM(y.c) + FROM y + GROUP BY y.b +) AS y + ON x.b = y.b; +SELECT + x.a +FROM x; + +# title: Remove left join on aggregate derived table +SELECT + x.a +FROM x +LEFT JOIN ( + SELECT + SUM(y.b) AS b + FROM y +) AS y + ON x.b = y.b; +SELECT + x.a +FROM x; + +# title: Noop - not all distinct columns in condition +SELECT + x.a +FROM x +LEFT JOIN ( + SELECT DISTINCT + y.b, + y.c + FROM y +) AS y + ON x.b = y.b; +SELECT + x.a +FROM x +LEFT JOIN ( + SELECT DISTINCT + y.b, + y.c + FROM y +) AS y + ON x.b = y.b; + +# title: Noop - not all grouped columns in condition +SELECT + x.a +FROM x +LEFT JOIN ( + SELECT + y.b, + y.c + FROM y + GROUP BY + y.b, + y.c +) AS y + ON x.b = y.b; +SELECT + x.a +FROM x +LEFT JOIN ( + SELECT + y.b, + y.c + FROM y + GROUP BY + y.b, + y.c +) AS y + ON x.b = y.b; + +# title: Noop - not left join +SELECT + x.a +FROM x +JOIN ( + SELECT DISTINCT + y.b + FROM y +) AS y + ON x.b = y.b; +SELECT + x.a +FROM x +JOIN ( + SELECT DISTINCT + y.b + FROM y +) AS y + ON x.b = y.b; + +# title: Noop - unqualified columns +SELECT + a +FROM x +LEFT JOIN ( + SELECT DISTINCT + y.b + FROM y +) AS y + ON x.b = y.b; +SELECT + a +FROM x +LEFT JOIN ( + SELECT DISTINCT + y.b + FROM y +) AS y + ON x.b = y.b; + +# title: Noop - cross join +SELECT + a +FROM x +CROSS JOIN ( + SELECT DISTINCT + y.b + FROM y +) AS y; +SELECT + a +FROM x +CROSS JOIN ( + SELECT DISTINCT + y.b + FROM y +) AS y; + +# title: Noop - column is used +SELECT + x.a, + y.b +FROM x +LEFT JOIN ( + SELECT DISTINCT + y.b + FROM y +) AS y + ON x.b = y.b; +SELECT + x.a, + y.b +FROM x +LEFT JOIN ( + SELECT DISTINCT + y.b + FROM y +) AS y + ON x.b = y.b; + +# title: Multiple group by columns +SELECT + x.a +FROM x +LEFT JOIN ( + SELECT + y.b AS b, + y.c + 1 AS d, + COUNT(1) + FROM y + GROUP BY y.b, y.c + 1 +) AS y + ON x.b = y.b + AND 1 = y.d; +SELECT + x.a +FROM x; + +# title: Chained left joins +SELECT + x.a +FROM x +LEFT JOIN ( + SELECT + y.b AS b + FROM y + GROUP BY y.b +) AS y + ON x.b = y.b +LEFT JOIN ( + SELECT + y.b AS c + FROM y + GROUP BY y.b +) AS z + ON y.b = z.c; +SELECT + x.a +FROM x; + +# title: CTE +WITH z AS ( + SELECT DISTINCT + y.b + FROM y +) +SELECT + x.a +FROM x +LEFT JOIN z + ON x.b = z.b; +WITH z AS ( + SELECT DISTINCT + y.b + FROM y +) +SELECT + x.a +FROM x; + +# title: Noop - Not all grouped expressions are in outputs +SELECT + x.a +FROM x +LEFT JOIN ( + SELECT + y.b + FROM y + GROUP BY + y.b, + y.c +) AS y + ON x.b = y.b; +SELECT + x.a +FROM x +LEFT JOIN ( + SELECT + y.b + FROM y + GROUP BY + y.b, + y.c +) AS y + ON x.b = y.b; + +# title: Cross join on aggregate derived table +SELECT + x.a +FROM x +CROSS JOIN ( + SELECT + SUM(y.b) AS b + FROM y +) AS y; +SELECT + x.a +FROM x; + +# title: Cross join on derived table with LIMIT 1 +SELECT + x.a +FROM x +CROSS JOIN ( + SELECT + y.b AS b + FROM y + LIMIT 1 +) AS y; +SELECT + x.a +FROM x; + +# title: Cross join on derived table with no FROM clause +SELECT + x.a +FROM x +CROSS JOIN ( + SELECT + 1 AS b, + 2 AS c +) AS y; +SELECT + x.a +FROM x; + +# title: Noop - cross join on non-aggregate subquery +SELECT + x.a +FROM x +CROSS JOIN ( + SELECT + y.b + FROM y +) AS y; +SELECT + x.a +FROM x +CROSS JOIN ( + SELECT + y.b + FROM y +) AS y; |