summaryrefslogtreecommitdiffstats
path: root/tests/fixtures/optimizer
diff options
context:
space:
mode:
Diffstat (limited to 'tests/fixtures/optimizer')
-rw-r--r--tests/fixtures/optimizer/canonicalize.sql8
-rw-r--r--tests/fixtures/optimizer/optimizer.sql78
-rw-r--r--tests/fixtures/optimizer/pushdown_projections.sql20
-rw-r--r--tests/fixtures/optimizer/qualify_columns.sql24
-rw-r--r--tests/fixtures/optimizer/qualify_columns__with_invisible.sql8
-rw-r--r--tests/fixtures/optimizer/unnest_subqueries.sql56
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