diff options
Diffstat (limited to 'tests/fixtures/optimizer')
| -rw-r--r-- | tests/fixtures/optimizer/canonicalize.sql | 8 | ||||
| -rw-r--r-- | tests/fixtures/optimizer/optimizer.sql | 78 | ||||
| -rw-r--r-- | tests/fixtures/optimizer/pushdown_projections.sql | 20 | ||||
| -rw-r--r-- | tests/fixtures/optimizer/qualify_columns.sql | 24 | ||||
| -rw-r--r-- | tests/fixtures/optimizer/qualify_columns__with_invisible.sql | 8 | ||||
| -rw-r--r-- | tests/fixtures/optimizer/unnest_subqueries.sql | 56 |
6 files changed, 136 insertions, 58 deletions
diff --git a/tests/fixtures/optimizer/canonicalize.sql b/tests/fixtures/optimizer/canonicalize.sql index 8880881..8c7cd45 100644 --- a/tests/fixtures/optimizer/canonicalize.sql +++ b/tests/fixtures/optimizer/canonicalize.sql @@ -1,11 +1,11 @@ SELECT w.d + w.e AS c FROM w AS w; -SELECT CONCAT(w.d, w.e) AS c FROM w AS w; +SELECT CONCAT("w"."d", "w"."e") AS "c" FROM "w" AS "w"; SELECT CAST(w.d AS DATE) > w.e AS a FROM w AS w; -SELECT CAST(w.d AS DATE) > CAST(w.e AS DATE) AS a FROM w AS w; +SELECT CAST("w"."d" AS DATE) > CAST("w"."e" AS DATE) AS "a" FROM "w" AS "w"; SELECT CAST(1 AS VARCHAR) AS a FROM w AS w; -SELECT CAST(1 AS VARCHAR) AS a FROM w AS w; +SELECT CAST(1 AS VARCHAR) AS "a" FROM "w" AS "w"; SELECT CAST(1 + 3.2 AS DOUBLE) AS a FROM w AS w; -SELECT 1 + 3.2 AS a FROM w AS w; +SELECT 1 + 3.2 AS "a" FROM "w" AS "w"; diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql index a692c7d..b502d81 100644 --- a/tests/fixtures/optimizer/optimizer.sql +++ b/tests/fixtures/optimizer/optimizer.sql @@ -291,3 +291,81 @@ SELECT a1 FROM cte1; SELECT "x"."a" AS "a1" FROM "x" AS "x"; + +# title: recursive cte +WITH RECURSIVE cte1 AS ( + SELECT * + FROM ( + SELECT 1 AS a, 2 AS b + ) base + CROSS JOIN (SELECT 3 c) y + UNION ALL + SELECT * + FROM cte1 + WHERE a < 1 +) +SELECT * +FROM cte1; +WITH RECURSIVE "base" AS ( + SELECT + 1 AS "a", + 2 AS "b" +), "y" AS ( + SELECT + 3 AS "c" +), "cte1" AS ( + SELECT + "base"."a" AS "a", + "base"."b" AS "b", + "y"."c" AS "c" + FROM "base" AS "base" + CROSS JOIN "y" AS "y" + UNION ALL + SELECT + "cte1"."a" AS "a", + "cte1"."b" AS "b", + "cte1"."c" AS "c" + FROM "cte1" + WHERE + "cte1"."a" < 1 +) +SELECT + "cte1"."a" AS "a", + "cte1"."b" AS "b", + "cte1"."c" AS "c" +FROM "cte1"; + +# title: right join should not push down to from +SELECT x.a, y.b +FROM x +RIGHT JOIN y +ON x.a = y.b +WHERE x.b = 1; +SELECT + "x"."a" AS "a", + "y"."b" AS "b" +FROM "x" AS "x" +RIGHT JOIN "y" AS "y" + ON "x"."a" = "y"."b" +WHERE + "x"."b" = 1; + +# title: right join can push down to itself +SELECT x.a, y.b +FROM x +RIGHT JOIN y +ON x.a = y.b +WHERE y.b = 1; +WITH "y_2" AS ( + SELECT + "y"."b" AS "b" + FROM "y" AS "y" + WHERE + "y"."b" = 1 +) +SELECT + "x"."a" AS "a", + "y"."b" AS "b" +FROM "x" AS "x" +RIGHT JOIN "y_2" AS "y" + ON "x"."a" = "y"."b"; diff --git a/tests/fixtures/optimizer/pushdown_projections.sql b/tests/fixtures/optimizer/pushdown_projections.sql index ba4bf45..2a21f65 100644 --- a/tests/fixtures/optimizer/pushdown_projections.sql +++ b/tests/fixtures/optimizer/pushdown_projections.sql @@ -1,32 +1,32 @@ SELECT a FROM (SELECT * FROM x); -SELECT "_q_0".a AS a FROM (SELECT x.a AS a FROM x AS x) AS "_q_0"; +SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0; SELECT 1 FROM (SELECT * FROM x) WHERE b = 2; -SELECT 1 AS "_col_0" FROM (SELECT x.b AS b FROM x AS x) AS "_q_0" WHERE "_q_0".b = 2; +SELECT 1 AS _col_0 FROM (SELECT x.b AS b FROM x AS x) AS _q_0 WHERE _q_0.b = 2; SELECT (SELECT c FROM y WHERE q.b = y.b) FROM (SELECT * FROM x) AS q; -SELECT (SELECT y.c AS c FROM y AS y WHERE q.b = y.b) AS "_col_0" FROM (SELECT x.b AS b FROM x AS x) AS q; +SELECT (SELECT y.c AS c FROM y AS y WHERE q.b = y.b) AS _col_0 FROM (SELECT x.b AS b FROM x AS x) AS q; SELECT a FROM x JOIN (SELECT b, c FROM y) AS z ON x.b = z.b; SELECT x.a AS a FROM x AS x JOIN (SELECT y.b AS b FROM y AS y) AS z ON x.b = z.b; SELECT x1.a FROM (SELECT * FROM x) AS x1, (SELECT * FROM x) AS x2; -SELECT x1.a AS a FROM (SELECT x.a AS a FROM x AS x) AS x1, (SELECT 1 AS "_" FROM x AS x) AS x2; +SELECT x1.a AS a FROM (SELECT x.a AS a FROM x AS x) AS x1, (SELECT 1 AS _ FROM x AS x) AS x2; SELECT x1.a FROM (SELECT * FROM x) AS x1, (SELECT * FROM x) AS x2; -SELECT x1.a AS a FROM (SELECT x.a AS a FROM x AS x) AS x1, (SELECT 1 AS "_" FROM x AS x) AS x2; +SELECT x1.a AS a FROM (SELECT x.a AS a FROM x AS x) AS x1, (SELECT 1 AS _ FROM x AS x) AS x2; SELECT a FROM (SELECT DISTINCT a, b FROM x); -SELECT "_q_0".a AS a FROM (SELECT DISTINCT x.a AS a, x.b AS b FROM x AS x) AS "_q_0"; +SELECT _q_0.a AS a FROM (SELECT DISTINCT x.a AS a, x.b AS b FROM x AS x) AS _q_0; SELECT a FROM (SELECT a, b FROM x UNION ALL SELECT a, b FROM x); -SELECT "_q_0".a AS a FROM (SELECT x.a AS a FROM x AS x UNION ALL SELECT x.a AS a FROM x AS x) AS "_q_0"; +SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x UNION ALL SELECT x.a AS a FROM x AS x) AS _q_0; WITH t1 AS (SELECT x.a AS a, x.b AS b FROM x UNION ALL SELECT z.b AS b, z.c AS c FROM z) SELECT a, b FROM t1; WITH t1 AS (SELECT x.a AS a, x.b AS b FROM x AS x UNION ALL SELECT z.b AS b, z.c AS c FROM z AS z) SELECT t1.a AS a, t1.b AS b FROM t1; SELECT a FROM (SELECT a, b FROM x UNION SELECT a, b FROM x); -SELECT "_q_0".a AS a FROM (SELECT x.a AS a, x.b AS b FROM x AS x UNION SELECT x.a AS a, x.b AS b FROM x AS x) AS "_q_0"; +SELECT _q_0.a AS a FROM (SELECT x.a AS a, x.b AS b FROM x AS x UNION SELECT x.a AS a, x.b AS b FROM x AS x) AS _q_0; WITH y AS (SELECT * FROM x) SELECT a FROM y; WITH y AS (SELECT x.a AS a FROM x AS x) SELECT y.a AS a FROM y; @@ -38,10 +38,10 @@ WITH z AS (SELECT * FROM x) SELECT a FROM z UNION SELECT a FROM z; WITH z AS (SELECT x.a AS a FROM x AS x) SELECT z.a AS a FROM z UNION SELECT z.a AS a FROM z; SELECT b FROM (SELECT a, SUM(b) AS b FROM x GROUP BY a); -SELECT "_q_0".b AS b FROM (SELECT SUM(x.b) AS b FROM x AS x GROUP BY x.a) AS "_q_0"; +SELECT _q_0.b AS b FROM (SELECT SUM(x.b) AS b FROM x AS x GROUP BY x.a) AS _q_0; SELECT b FROM (SELECT a, SUM(b) AS b FROM x ORDER BY a); -SELECT "_q_0".b AS b FROM (SELECT x.a AS a, SUM(x.b) AS b FROM x AS x ORDER BY a) AS "_q_0"; +SELECT _q_0.b AS b FROM (SELECT x.a AS a, SUM(x.b) AS b FROM x AS x ORDER BY a) AS _q_0; SELECT x FROM (VALUES(1, 2)) AS q(x, y); SELECT q.x AS x FROM (VALUES (1, 2)) AS q(x, y); diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql index 1176078..9c5a0be 100644 --- a/tests/fixtures/optimizer/qualify_columns.sql +++ b/tests/fixtures/optimizer/qualify_columns.sql @@ -21,15 +21,15 @@ SELECT x.a AS b FROM x AS x; # execute: false SELECT 1, 2 FROM x; -SELECT 1 AS "_col_0", 2 AS "_col_1" FROM x AS x; +SELECT 1 AS _col_0, 2 AS _col_1 FROM x AS x; # execute: false SELECT a + b FROM x; -SELECT x.a + x.b AS "_col_0" FROM x AS x; +SELECT x.a + x.b AS _col_0 FROM x AS x; # execute: false SELECT a, SUM(b) FROM x WHERE a > 1 AND b > 1 GROUP BY a; -SELECT x.a AS a, SUM(x.b) AS "_col_1" FROM x AS x WHERE x.a > 1 AND x.b > 1 GROUP BY x.a; +SELECT x.a AS a, SUM(x.b) AS _col_1 FROM x AS x WHERE x.a > 1 AND x.b > 1 GROUP BY x.a; SELECT SUM(a) AS c FROM x HAVING SUM(a) > 3; SELECT SUM(x.a) AS c FROM x AS x HAVING SUM(x.a) > 3; @@ -59,7 +59,7 @@ SELECT SUM(x.a) AS c, SUM(x.b) AS d FROM x AS x ORDER BY SUM(x.a), SUM(x.b); # execute: false SELECT SUM(a), SUM(b) AS c FROM x ORDER BY 1, 2; -SELECT SUM(x.a) AS "_col_0", SUM(x.b) AS c FROM x AS x ORDER BY SUM(x.a), SUM(x.b); +SELECT SUM(x.a) AS _col_0, SUM(x.b) AS c FROM x AS x ORDER BY SUM(x.a), SUM(x.b); SELECT a AS j, b FROM x GROUP BY j, b; SELECT x.a AS j, x.b AS b FROM x AS x GROUP BY x.a, x.b; @@ -72,7 +72,7 @@ SELECT x.a AS a, x.b AS b FROM x AS x ORDER BY x.a, x.b; # execute: false SELECT DATE(a), DATE(b) AS c FROM x GROUP BY 1, 2; -SELECT DATE(x.a) AS "_col_0", DATE(x.b) AS c FROM x AS x GROUP BY DATE(x.a), DATE(x.b); +SELECT DATE(x.a) AS _col_0, DATE(x.b) AS c FROM x AS x GROUP BY DATE(x.a), DATE(x.b); SELECT SUM(x.a) AS c FROM x JOIN y ON x.b = y.b GROUP BY c; SELECT SUM(x.a) AS c FROM x AS x JOIN y AS y ON x.b = y.b GROUP BY y.c; @@ -130,10 +130,10 @@ SELECT a FROM (SELECT a FROM x AS x) y; SELECT y.a AS a FROM (SELECT x.a AS a FROM x AS x) AS y; SELECT a FROM (SELECT a AS a FROM x); -SELECT "_q_0".a AS a FROM (SELECT x.a AS a FROM x AS x) AS "_q_0"; +SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0; SELECT a FROM (SELECT a FROM (SELECT a FROM x)); -SELECT "_q_1".a AS a FROM (SELECT "_q_0".a AS a FROM (SELECT x.a AS a FROM x AS x) AS "_q_0") AS "_q_1"; +SELECT _q_1.a AS a FROM (SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0) AS _q_1; SELECT x.a FROM x AS x JOIN (SELECT * FROM x) AS y ON x.a = y.a; SELECT x.a AS a FROM x AS x JOIN (SELECT x.a AS a, x.b AS b FROM x AS x) AS y ON x.a = y.a; @@ -157,7 +157,7 @@ SELECT a FROM x UNION SELECT a FROM x UNION SELECT a FROM x; SELECT x.a AS a FROM x AS x UNION SELECT x.a AS a FROM x AS x UNION SELECT x.a AS a FROM x AS x; SELECT a FROM (SELECT a FROM x UNION SELECT a FROM x); -SELECT "_q_0".a AS a FROM (SELECT x.a AS a FROM x AS x UNION SELECT x.a AS a FROM x AS x) AS "_q_0"; +SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x UNION SELECT x.a AS a FROM x AS x) AS _q_0; -------------------------------------- -- Subqueries @@ -167,10 +167,10 @@ SELECT x.a AS a FROM x AS x WHERE x.b IN (SELECT y.c AS c FROM y AS y); # execute: false SELECT (SELECT c FROM y) FROM x; -SELECT (SELECT y.c AS c FROM y AS y) AS "_col_0" FROM x AS x; +SELECT (SELECT y.c AS c FROM y AS y) AS _col_0 FROM x AS x; SELECT a FROM (SELECT a FROM x) WHERE a IN (SELECT b FROM (SELECT b FROM y)); -SELECT "_q_1".a AS a FROM (SELECT x.a AS a FROM x AS x) AS "_q_1" WHERE "_q_1".a IN (SELECT "_q_0".b AS b FROM (SELECT y.b AS b FROM y AS y) AS "_q_0"); +SELECT _q_1.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_1 WHERE _q_1.a IN (SELECT _q_0.b AS b FROM (SELECT y.b AS b FROM y AS y) AS _q_0); -------------------------------------- -- Correlated subqueries @@ -215,10 +215,10 @@ SELECT x.*, y.* FROM x JOIN y ON x.b = y.b; SELECT x.a AS a, x.b AS b, y.b AS b, y.c AS c FROM x AS x JOIN y AS y ON x.b = y.b; SELECT a FROM (SELECT * FROM x); -SELECT "_q_0".a AS a FROM (SELECT x.a AS a, x.b AS b FROM x AS x) AS "_q_0"; +SELECT _q_0.a AS a FROM (SELECT x.a AS a, x.b AS b FROM x AS x) AS _q_0; SELECT * FROM (SELECT a FROM x); -SELECT "_q_0".a AS a FROM (SELECT x.a AS a FROM x AS x) AS "_q_0"; +SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0; -------------------------------------- -- CTEs diff --git a/tests/fixtures/optimizer/qualify_columns__with_invisible.sql b/tests/fixtures/optimizer/qualify_columns__with_invisible.sql index ee46c23..05253f3 100644 --- a/tests/fixtures/optimizer/qualify_columns__with_invisible.sql +++ b/tests/fixtures/optimizer/qualify_columns__with_invisible.sql @@ -11,10 +11,10 @@ SELECT x.b AS b FROM x AS x; -- Derived tables -------------------------------------- SELECT x.a FROM x AS x JOIN (SELECT * FROM x); -SELECT x.a AS a FROM x AS x JOIN (SELECT x.a AS a FROM x AS x) AS "_q_0"; +SELECT x.a AS a FROM x AS x JOIN (SELECT x.a AS a FROM x AS x) AS _q_0; SELECT x.b FROM x AS x JOIN (SELECT b FROM x); -SELECT x.b AS b FROM x AS x JOIN (SELECT x.b AS b FROM x AS x) AS "_q_0"; +SELECT x.b AS b FROM x AS x JOIN (SELECT x.b AS b FROM x AS x) AS _q_0; -------------------------------------- -- Expand * @@ -29,7 +29,7 @@ SELECT * FROM y JOIN z ON y.c = z.c; SELECT y.b AS b, z.b AS b FROM y AS y JOIN z AS z ON y.c = z.c; SELECT a FROM (SELECT * FROM x); -SELECT "_q_0".a AS a FROM (SELECT x.a AS a FROM x AS x) AS "_q_0"; +SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0; SELECT * FROM (SELECT a FROM x); -SELECT "_q_0".a AS a FROM (SELECT x.a AS a FROM x AS x) AS "_q_0"; +SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0; diff --git a/tests/fixtures/optimizer/unnest_subqueries.sql b/tests/fixtures/optimizer/unnest_subqueries.sql index dc373a0..a444945 100644 --- a/tests/fixtures/optimizer/unnest_subqueries.sql +++ b/tests/fixtures/optimizer/unnest_subqueries.sql @@ -30,14 +30,14 @@ CROSS JOIN ( SELECT SUM(y.a) AS a FROM y -) AS "_u_0" +) AS _u_0 LEFT JOIN ( SELECT y.a AS a FROM y GROUP BY y.a -) AS "_u_1" +) AS _u_1 ON x.a = "_u_1"."a" LEFT JOIN ( SELECT @@ -45,7 +45,7 @@ LEFT JOIN ( FROM y GROUP BY y.b -) AS "_u_2" +) AS _u_2 ON x.a = "_u_2"."b" LEFT JOIN ( SELECT @@ -53,7 +53,7 @@ LEFT JOIN ( FROM y GROUP BY y.a -) AS "_u_3" +) AS _u_3 ON x.a = "_u_3"."a" LEFT JOIN ( SELECT @@ -64,8 +64,8 @@ LEFT JOIN ( TRUE GROUP BY y.a -) AS "_u_4" - ON x.a = "_u_4"."_u_5" +) AS _u_4 + ON x.a = _u_4._u_5 LEFT JOIN ( SELECT SUM(y.b) AS b, @@ -75,8 +75,8 @@ LEFT JOIN ( TRUE GROUP BY y.a -) AS "_u_6" - ON x.a = "_u_6"."_u_7" +) AS _u_6 + ON x.a = _u_6._u_7 LEFT JOIN ( SELECT y.a AS a @@ -85,8 +85,8 @@ LEFT JOIN ( TRUE GROUP BY y.a -) AS "_u_8" - ON "_u_8".a = x.a +) AS _u_8 + ON _u_8.a = x.a LEFT JOIN ( SELECT y.a AS a @@ -95,8 +95,8 @@ LEFT JOIN ( TRUE GROUP BY y.a -) AS "_u_9" - ON "_u_9".a = x.a +) AS _u_9 + ON _u_9.a = x.a LEFT JOIN ( SELECT ARRAY_AGG(y.a) AS a, @@ -106,8 +106,8 @@ LEFT JOIN ( TRUE GROUP BY y.b -) AS "_u_10" - ON "_u_10"."_u_11" = x.a +) AS _u_10 + ON _u_10._u_11 = x.a LEFT JOIN ( SELECT SUM(y.a) AS a, @@ -118,8 +118,8 @@ LEFT JOIN ( TRUE AND TRUE AND TRUE GROUP BY y.a -) AS "_u_12" - ON "_u_12"."_u_13" = x.a AND "_u_12"."_u_13" = x.b +) AS _u_12 + ON _u_12._u_13 = x.a AND _u_12._u_13 = x.b LEFT JOIN ( SELECT y.a AS a @@ -128,38 +128,38 @@ LEFT JOIN ( TRUE GROUP BY y.a -) AS "_u_15" - ON x.a = "_u_15".a +) AS _u_15 + ON x.a = _u_15.a WHERE - x.a = "_u_0".a + x.a = _u_0.a AND NOT "_u_1"."a" IS NULL AND NOT "_u_2"."b" IS NULL AND NOT "_u_3"."a" IS NULL AND ( - x.a = "_u_4".b AND NOT "_u_4"."_u_5" IS NULL + x.a = _u_4.b AND NOT _u_4._u_5 IS NULL ) AND ( - x.a > "_u_6".b AND NOT "_u_6"."_u_7" IS NULL + x.a > _u_6.b AND NOT _u_6._u_7 IS NULL ) AND ( - None = "_u_8".a AND NOT "_u_8".a IS NULL + None = _u_8.a AND NOT _u_8.a IS NULL ) AND NOT ( - x.a = "_u_9".a AND NOT "_u_9".a IS NULL + x.a = _u_9.a AND NOT _u_9.a IS NULL ) AND ( - ARRAY_ANY("_u_10".a, _x -> _x = x.a) AND NOT "_u_10"."_u_11" IS NULL + ARRAY_ANY(_u_10.a, _x -> _x = x.a) AND NOT _u_10._u_11 IS NULL ) AND ( ( ( - x.a < "_u_12".a AND NOT "_u_12"."_u_13" IS NULL - ) AND NOT "_u_12"."_u_13" IS NULL + x.a < _u_12.a AND NOT _u_12._u_13 IS NULL + ) AND NOT _u_12._u_13 IS NULL ) - AND ARRAY_ANY("_u_12"."_u_14", "_x" -> "_x" <> x.d) + AND ARRAY_ANY(_u_12._u_14, "_x" -> _x <> x.d) ) AND ( - NOT "_u_15".a IS NULL AND NOT "_u_15".a IS NULL + NOT _u_15.a IS NULL AND NOT _u_15.a IS NULL ) AND x.a IN ( SELECT |