diff options
Diffstat (limited to 'tests/fixtures/optimizer/eliminate_joins.sql')
-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; |