summaryrefslogtreecommitdiffstats
path: root/tests/fixtures
diff options
context:
space:
mode:
Diffstat (limited to 'tests/fixtures')
-rw-r--r--tests/fixtures/identity.sql7
-rw-r--r--tests/fixtures/optimizer/eliminate_subqueries.sql12
-rw-r--r--tests/fixtures/optimizer/lower_identities.sql41
-rw-r--r--tests/fixtures/optimizer/optimizer.sql15
-rw-r--r--tests/fixtures/optimizer/simplify.sql9
-rw-r--r--tests/fixtures/optimizer/tpc-h/tpc-h.sql57
-rw-r--r--tests/fixtures/optimizer/unnest_subqueries.sql84
7 files changed, 160 insertions, 65 deletions
diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql
index 75bd25d..06ab96d 100644
--- a/tests/fixtures/identity.sql
+++ b/tests/fixtures/identity.sql
@@ -34,6 +34,7 @@ x >> 1
x >> 1 | 1 & 1 ^ 1
x || y
1 - -1
+- -5
dec.x + y
a.filter
a.b.c
@@ -438,6 +439,7 @@ SELECT student, score FROM tests CROSS JOIN UNNEST(scores) AS t(a, b)
SELECT student, score FROM tests CROSS JOIN UNNEST(scores) WITH ORDINALITY AS t(a, b)
SELECT student, score FROM tests CROSS JOIN UNNEST(x.scores) AS t(score)
SELECT student, score FROM tests CROSS JOIN UNNEST(ARRAY(x.scores)) AS t(score)
+CREATE TABLE foo (id INT PRIMARY KEY ASC)
CREATE TABLE a.b AS SELECT 1
CREATE TABLE a.b AS SELECT a FROM a.c
CREATE TABLE IF NOT EXISTS x AS SELECT a FROM d
@@ -579,6 +581,7 @@ SELECT * FROM (tbl1 JOIN (tbl2 JOIN tbl3) ON bla = foo)
SELECT * FROM (tbl1 JOIN LATERAL (SELECT * FROM bla) AS tbl)
SELECT CAST(x AS INT) /* comment */ FROM foo
SELECT a /* x */, b /* x */
+SELECT a /* x */ /* y */ /* z */, b /* k */ /* m */
SELECT * FROM foo /* x */, bla /* x */
SELECT 1 /* comment */ + 1
SELECT 1 /* c1 */ + 2 /* c2 */
@@ -588,3 +591,7 @@ SELECT x FROM a.b.c /* x */, e.f.g /* x */
SELECT FOO(x /* c */) /* FOO */, b /* b */
SELECT FOO(x /* c1 */ + y /* c2 */ + BLA(5 /* c3 */)) FROM VALUES (1 /* c4 */, "test" /* c5 */) /* c6 */
SELECT a FROM x WHERE a COLLATE 'utf8_general_ci' = 'b'
+SELECT x AS INTO FROM bla
+SELECT * INTO newevent FROM event
+SELECT * INTO TEMPORARY newevent FROM event
+SELECT * INTO UNLOGGED newevent FROM event
diff --git a/tests/fixtures/optimizer/eliminate_subqueries.sql b/tests/fixtures/optimizer/eliminate_subqueries.sql
index f395c0a..c566657 100644
--- a/tests/fixtures/optimizer/eliminate_subqueries.sql
+++ b/tests/fixtures/optimizer/eliminate_subqueries.sql
@@ -77,3 +77,15 @@ WITH x_2 AS (SELECT * FROM x AS x JOIN y AS y ON x.id = y.id) SELECT x.id FROM x
-- Existing duplicate CTE
WITH y AS (SELECT a FROM x) SELECT a FROM (SELECT a FROM x) AS y JOIN y AS z;
WITH y AS (SELECT a FROM x) SELECT a FROM y AS y JOIN y AS z;
+
+-- Nested CTE
+WITH cte1 AS (SELECT a FROM x) SELECT a FROM (WITH cte2 AS (SELECT a FROM cte1) SELECT a FROM cte2);
+WITH cte1 AS (SELECT a FROM x), cte2 AS (SELECT a FROM cte1), cte AS (SELECT a FROM cte2 AS cte2) SELECT a FROM cte AS cte;
+
+-- Nested CTE inside CTE
+WITH cte1 AS (WITH cte2 AS (SELECT a FROM x) SELECT t.a FROM cte2 AS t) SELECT a FROM cte1;
+WITH cte2 AS (SELECT a FROM x), cte1 AS (SELECT t.a FROM cte2 AS t) SELECT a FROM cte1;
+
+-- Duplicate CTE nested in CTE
+WITH cte1 AS (SELECT a FROM x), cte2 AS (WITH cte3 AS (SELECT a FROM x) SELECT a FROM cte3) SELECT a FROM cte2;
+WITH cte1 AS (SELECT a FROM x), cte2 AS (SELECT a FROM cte1 AS cte3) SELECT a FROM cte2;
diff --git a/tests/fixtures/optimizer/lower_identities.sql b/tests/fixtures/optimizer/lower_identities.sql
new file mode 100644
index 0000000..cea346f
--- /dev/null
+++ b/tests/fixtures/optimizer/lower_identities.sql
@@ -0,0 +1,41 @@
+SELECT a FROM x;
+SELECT a FROM x;
+
+SELECT "A" FROM "X";
+SELECT "A" FROM "X";
+
+SELECT a AS A FROM x;
+SELECT a AS A FROM x;
+
+SELECT * FROM x;
+SELECT * FROM x;
+
+SELECT A FROM x;
+SELECT a FROM x;
+
+SELECT a FROM X;
+SELECT a FROM x;
+
+SELECT A AS A FROM (SELECT a AS A FROM x);
+SELECT a AS A FROM (SELECT a AS a FROM x);
+
+SELECT a AS B FROM x ORDER BY B;
+SELECT a AS B FROM x ORDER BY B;
+
+SELECT A FROM x ORDER BY A;
+SELECT a FROM x ORDER BY a;
+
+SELECT A AS B FROM X GROUP BY A HAVING SUM(B) > 0;
+SELECT a AS B FROM x GROUP BY a HAVING SUM(b) > 0;
+
+SELECT A AS B, SUM(B) AS C FROM X GROUP BY A HAVING C > 0;
+SELECT a AS B, SUM(b) AS C FROM x GROUP BY a HAVING C > 0;
+
+SELECT A FROM X UNION SELECT A FROM X;
+SELECT a FROM x UNION SELECT a FROM x;
+
+SELECT A AS A FROM X UNION SELECT A AS A FROM X;
+SELECT a AS A FROM x UNION SELECT a AS A FROM x;
+
+(SELECT A AS A FROM X);
+(SELECT a AS A FROM x);
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index a1e531b..a692c7d 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -276,3 +276,18 @@ SELECT /*+ COALESCE(3),
FROM `x` AS `x`
JOIN `y` AS `y`
ON `x`.`b` = `y`.`b`;
+
+WITH cte1 AS (
+ WITH cte2 AS (
+ SELECT a, b FROM x
+ )
+ SELECT a1
+ FROM (
+ WITH cte3 AS (SELECT 1)
+ SELECT a AS a1, b AS b1 FROM cte2
+ )
+)
+SELECT a1 FROM cte1;
+SELECT
+ "x"."a" AS "a1"
+FROM "x" AS "x";
diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql
index 7207ba2..d9c7779 100644
--- a/tests/fixtures/optimizer/simplify.sql
+++ b/tests/fixtures/optimizer/simplify.sql
@@ -274,6 +274,15 @@ TRUE;
-(-1);
1;
+- -+1;
+1;
+
++-1;
+-1;
+
+++1;
+1;
+
0.06 - 0.01;
0.05;
diff --git a/tests/fixtures/optimizer/tpc-h/tpc-h.sql b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
index 8138b11..4893743 100644
--- a/tests/fixtures/optimizer/tpc-h/tpc-h.sql
+++ b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
@@ -666,11 +666,20 @@ WITH "supplier_2" AS (
FROM "nation" AS "nation"
WHERE
"nation"."n_name" = 'GERMANY'
+), "_u_0" AS (
+ SELECT
+ SUM("partsupp"."ps_supplycost" * "partsupp"."ps_availqty") * 0.0001 AS "_col_0"
+ FROM "partsupp" AS "partsupp"
+ JOIN "supplier_2" AS "supplier"
+ ON "partsupp"."ps_suppkey" = "supplier"."s_suppkey"
+ JOIN "nation_2" AS "nation"
+ ON "supplier"."s_nationkey" = "nation"."n_nationkey"
)
SELECT
"partsupp"."ps_partkey" AS "ps_partkey",
SUM("partsupp"."ps_supplycost" * "partsupp"."ps_availqty") AS "value"
FROM "partsupp" AS "partsupp"
+CROSS JOIN "_u_0" AS "_u_0"
JOIN "supplier_2" AS "supplier"
ON "partsupp"."ps_suppkey" = "supplier"."s_suppkey"
JOIN "nation_2" AS "nation"
@@ -678,15 +687,7 @@ JOIN "nation_2" AS "nation"
GROUP BY
"partsupp"."ps_partkey"
HAVING
- SUM("partsupp"."ps_supplycost" * "partsupp"."ps_availqty") > (
- SELECT
- SUM("partsupp"."ps_supplycost" * "partsupp"."ps_availqty") * 0.0001 AS "_col_0"
- FROM "partsupp" AS "partsupp"
- JOIN "supplier_2" AS "supplier"
- ON "partsupp"."ps_suppkey" = "supplier"."s_suppkey"
- JOIN "nation_2" AS "nation"
- ON "supplier"."s_nationkey" = "nation"."n_nationkey"
- )
+ SUM("partsupp"."ps_supplycost" * "partsupp"."ps_availqty") > MAX("_u_0"."_col_0")
ORDER BY
"value" DESC;
@@ -880,6 +881,10 @@ WITH "revenue" AS (
AND CAST("lineitem"."l_shipdate" AS DATE) >= CAST('1996-01-01' AS DATE)
GROUP BY
"lineitem"."l_suppkey"
+), "_u_0" AS (
+ SELECT
+ MAX("revenue"."total_revenue") AS "_col_0"
+ FROM "revenue"
)
SELECT
"supplier"."s_suppkey" AS "s_suppkey",
@@ -889,12 +894,9 @@ SELECT
"revenue"."total_revenue" AS "total_revenue"
FROM "supplier" AS "supplier"
JOIN "revenue"
- ON "revenue"."total_revenue" = (
- SELECT
- MAX("revenue"."total_revenue") AS "_col_0"
- FROM "revenue"
- )
- AND "supplier"."s_suppkey" = "revenue"."supplier_no"
+ ON "supplier"."s_suppkey" = "revenue"."supplier_no"
+JOIN "_u_0" AS "_u_0"
+ ON "revenue"."total_revenue" = "_u_0"."_col_0"
ORDER BY
"s_suppkey";
@@ -1395,7 +1397,14 @@ order by
cntrycode;
WITH "_u_0" AS (
SELECT
- "orders"."o_custkey" AS "_u_1"
+ AVG("customer"."c_acctbal") AS "_col_0"
+ FROM "customer" AS "customer"
+ WHERE
+ "customer"."c_acctbal" > 0.00
+ AND SUBSTRING("customer"."c_phone", 1, 2) IN ('13', '31', '23', '29', '30', '18', '17')
+), "_u_1" AS (
+ SELECT
+ "orders"."o_custkey" AS "_u_2"
FROM "orders" AS "orders"
GROUP BY
"orders"."o_custkey"
@@ -1405,18 +1414,12 @@ SELECT
COUNT(*) AS "numcust",
SUM("customer"."c_acctbal") AS "totacctbal"
FROM "customer" AS "customer"
-LEFT JOIN "_u_0" AS "_u_0"
- ON "_u_0"."_u_1" = "customer"."c_custkey"
+JOIN "_u_0" AS "_u_0"
+ ON "customer"."c_acctbal" > "_u_0"."_col_0"
+LEFT JOIN "_u_1" AS "_u_1"
+ ON "_u_1"."_u_2" = "customer"."c_custkey"
WHERE
- "_u_0"."_u_1" IS NULL
- AND "customer"."c_acctbal" > (
- SELECT
- AVG("customer"."c_acctbal") AS "_col_0"
- FROM "customer" AS "customer"
- WHERE
- "customer"."c_acctbal" > 0.00
- AND SUBSTRING("customer"."c_phone", 1, 2) IN ('13', '31', '23', '29', '30', '18', '17')
- )
+ "_u_1"."_u_2" IS NULL
AND SUBSTRING("customer"."c_phone", 1, 2) IN ('13', '31', '23', '29', '30', '18', '17')
GROUP BY
SUBSTRING("customer"."c_phone", 1, 2)
diff --git a/tests/fixtures/optimizer/unnest_subqueries.sql b/tests/fixtures/optimizer/unnest_subqueries.sql
index f53121a..dc373a0 100644
--- a/tests/fixtures/optimizer/unnest_subqueries.sql
+++ b/tests/fixtures/optimizer/unnest_subqueries.sql
@@ -1,10 +1,12 @@
+--SELECT x.a > (SELECT SUM(y.a) AS b FROM y) FROM x;
--------------------------------------
-- Unnest Subqueries
--------------------------------------
SELECT *
FROM x AS x
WHERE
- x.a IN (SELECT y.a AS a FROM y)
+ x.a = (SELECT SUM(y.a) AS a FROM y)
+ AND x.a IN (SELECT y.a AS a FROM y)
AND x.a IN (SELECT y.b AS b FROM y)
AND x.a = ANY (SELECT y.a AS a FROM y)
AND x.a = (SELECT SUM(y.b) AS b FROM y WHERE x.a = y.a)
@@ -24,52 +26,57 @@ WHERE
SELECT
*
FROM x AS x
+CROSS JOIN (
+ SELECT
+ SUM(y.a) AS a
+ FROM y
+) AS "_u_0"
LEFT JOIN (
SELECT
y.a AS a
FROM y
GROUP BY
y.a
-) AS "_u_0"
- ON x.a = "_u_0"."a"
+) AS "_u_1"
+ ON x.a = "_u_1"."a"
LEFT JOIN (
SELECT
y.b AS b
FROM y
GROUP BY
y.b
-) AS "_u_1"
- ON x.a = "_u_1"."b"
+) AS "_u_2"
+ ON x.a = "_u_2"."b"
LEFT JOIN (
SELECT
y.a AS a
FROM y
GROUP BY
y.a
-) AS "_u_2"
- ON x.a = "_u_2"."a"
+) AS "_u_3"
+ ON x.a = "_u_3"."a"
LEFT JOIN (
SELECT
SUM(y.b) AS b,
- y.a AS _u_4
+ y.a AS _u_5
FROM y
WHERE
TRUE
GROUP BY
y.a
-) AS "_u_3"
- ON x.a = "_u_3"."_u_4"
+) AS "_u_4"
+ ON x.a = "_u_4"."_u_5"
LEFT JOIN (
SELECT
SUM(y.b) AS b,
- y.a AS _u_6
+ y.a AS _u_7
FROM y
WHERE
TRUE
GROUP BY
y.a
-) AS "_u_5"
- ON x.a = "_u_5"."_u_6"
+) AS "_u_6"
+ ON x.a = "_u_6"."_u_7"
LEFT JOIN (
SELECT
y.a AS a
@@ -78,8 +85,8 @@ LEFT JOIN (
TRUE
GROUP BY
y.a
-) AS "_u_7"
- ON "_u_7".a = x.a
+) AS "_u_8"
+ ON "_u_8".a = x.a
LEFT JOIN (
SELECT
y.a AS a
@@ -88,31 +95,31 @@ LEFT JOIN (
TRUE
GROUP BY
y.a
-) AS "_u_8"
- ON "_u_8".a = x.a
+) AS "_u_9"
+ ON "_u_9".a = x.a
LEFT JOIN (
SELECT
ARRAY_AGG(y.a) AS a,
- y.b AS _u_10
+ y.b AS _u_11
FROM y
WHERE
TRUE
GROUP BY
y.b
-) AS "_u_9"
- ON "_u_9"."_u_10" = x.a
+) AS "_u_10"
+ ON "_u_10"."_u_11" = x.a
LEFT JOIN (
SELECT
SUM(y.a) AS a,
- y.a AS _u_12,
- ARRAY_AGG(y.b) AS _u_13
+ y.a AS _u_13,
+ ARRAY_AGG(y.b) AS _u_14
FROM y
WHERE
TRUE AND TRUE AND TRUE
GROUP BY
y.a
-) AS "_u_11"
- ON "_u_11"."_u_12" = x.a AND "_u_11"."_u_12" = 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
@@ -121,37 +128,38 @@ LEFT JOIN (
TRUE
GROUP BY
y.a
-) AS "_u_14"
- ON x.a = "_u_14".a
+) AS "_u_15"
+ ON x.a = "_u_15".a
WHERE
- NOT "_u_0"."a" IS NULL
- AND NOT "_u_1"."b" IS NULL
- AND NOT "_u_2"."a" IS NULL
+ 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_3".b AND NOT "_u_3"."_u_4" IS NULL
+ x.a = "_u_4".b AND NOT "_u_4"."_u_5" IS NULL
)
AND (
- x.a > "_u_5".b AND NOT "_u_5"."_u_6" IS NULL
+ x.a > "_u_6".b AND NOT "_u_6"."_u_7" IS NULL
)
AND (
- None = "_u_7".a AND NOT "_u_7".a IS NULL
+ None = "_u_8".a AND NOT "_u_8".a IS NULL
)
AND NOT (
- x.a = "_u_8".a AND NOT "_u_8".a IS NULL
+ x.a = "_u_9".a AND NOT "_u_9".a IS NULL
)
AND (
- ARRAY_ANY("_u_9".a, _x -> _x = x.a) AND NOT "_u_9"."_u_10" IS NULL
+ ARRAY_ANY("_u_10".a, _x -> _x = x.a) AND NOT "_u_10"."_u_11" IS NULL
)
AND (
(
(
- x.a < "_u_11".a AND NOT "_u_11"."_u_12" IS NULL
- ) AND NOT "_u_11"."_u_12" 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_11"."_u_13", "_x" -> "_x" <> x.d)
+ AND ARRAY_ANY("_u_12"."_u_14", "_x" -> "_x" <> x.d)
)
AND (
- NOT "_u_14".a IS NULL AND NOT "_u_14".a IS NULL
+ NOT "_u_15".a IS NULL AND NOT "_u_15".a IS NULL
)
AND x.a IN (
SELECT
generated by cgit v1.2.3 (git 2.39.1) at 2024-06-12 08:56:12 +0000