From 1a60bbae98d3b530924a6807a55f8250de19ea86 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Fri, 2 Dec 2022 10:16:29 +0100 Subject: Adding upstream version 10.1.3. Signed-off-by: Daniel Baumann --- tests/fixtures/identity.sql | 7 ++ tests/fixtures/optimizer/eliminate_subqueries.sql | 12 ++++ tests/fixtures/optimizer/lower_identities.sql | 41 +++++++++++ tests/fixtures/optimizer/optimizer.sql | 15 ++++ tests/fixtures/optimizer/simplify.sql | 9 +++ tests/fixtures/optimizer/tpc-h/tpc-h.sql | 57 +++++++-------- tests/fixtures/optimizer/unnest_subqueries.sql | 84 +++++++++++++---------- 7 files changed, 160 insertions(+), 65 deletions(-) create mode 100644 tests/fixtures/optimizer/lower_identities.sql (limited to 'tests/fixtures') 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 -- cgit v1.2.3