From d3bb537b2b73788ba06bf4158f473ecc5bb556cc Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Tue, 17 Jan 2023 11:32:16 +0100 Subject: Merging upstream version 10.5.2. Signed-off-by: Daniel Baumann --- tests/fixtures/optimizer/merge_subqueries.sql | 39 +++++++++++++++ tests/fixtures/optimizer/optimizer.sql | 2 +- tests/fixtures/optimizer/pushdown_projections.sql | 3 ++ tests/fixtures/optimizer/simplify.sql | 12 +++++ tests/fixtures/optimizer/tpc-h/tpc-h.sql | 8 +-- tests/fixtures/optimizer/unnest_subqueries.sql | 59 +++++++++++++---------- 6 files changed, 91 insertions(+), 32 deletions(-) (limited to 'tests/fixtures/optimizer') diff --git a/tests/fixtures/optimizer/merge_subqueries.sql b/tests/fixtures/optimizer/merge_subqueries.sql index 4a3ad4b..4c06e42 100644 --- a/tests/fixtures/optimizer/merge_subqueries.sql +++ b/tests/fixtures/optimizer/merge_subqueries.sql @@ -311,3 +311,42 @@ FROM ON t1.cola = t2.cola; SELECT /*+ BROADCAST(a2) */ a1.cola AS cola, a2.cola AS cola FROM VALUES (1) AS a1(cola) JOIN VALUES (1) AS a2(cola) ON a1.cola = a2.cola; + +# title: Nested subquery selects from same table as another subquery +WITH i AS ( + SELECT + x.a AS a + FROM x AS x +), j AS ( + SELECT + x.a, + x.b + FROM x AS x +), k AS ( + SELECT + j.a, + j.b + FROM j AS j +) +SELECT + i.a, + k.b +FROM i AS i +LEFT JOIN k AS k +ON i.a = k.a; +SELECT x.a AS a, x_2.b AS b FROM x AS x LEFT JOIN x AS x_2 ON x.a = x_2.a; + +# title: Outer select joins on inner select join +WITH i AS ( + SELECT + x.a AS a + FROM y AS y + JOIN x AS x + ON y.b = x.b +) +SELECT + x.a AS a +FROM x AS x +LEFT JOIN i AS i + ON x.a = i.a; +WITH i AS (SELECT x.a AS a FROM y AS y JOIN x AS x ON y.b = x.b) SELECT x.a AS a FROM x AS x LEFT JOIN i AS i ON x.a = i.a; diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql index b502d81..664b3c7 100644 --- a/tests/fixtures/optimizer/optimizer.sql +++ b/tests/fixtures/optimizer/optimizer.sql @@ -105,7 +105,7 @@ LEFT JOIN "_u_0" AS "_u_0" JOIN "y" AS "y" ON "x"."b" = "y"."b" WHERE - "_u_0"."_col_0" >= 0 AND "x"."a" > 1 AND NOT "_u_0"."_u_1" IS NULL + "_u_0"."_col_0" >= 0 AND "x"."a" > 1 GROUP BY "x"."a"; diff --git a/tests/fixtures/optimizer/pushdown_projections.sql b/tests/fixtures/optimizer/pushdown_projections.sql index 2a21f65..b9f6c3f 100644 --- a/tests/fixtures/optimizer/pushdown_projections.sql +++ b/tests/fixtures/optimizer/pushdown_projections.sql @@ -54,3 +54,6 @@ WITH t1 AS (SELECT q.cola AS cola FROM UNNEST(ARRAY(STRUCT(1 AS cola, 'test' AS SELECT x FROM VALUES(1, 2) AS q(x, y); SELECT q.x AS x FROM (VALUES (1, 2)) AS q(x, y); + +SELECT i.a FROM x AS i LEFT JOIN (SELECT a, b FROM (SELECT a, b FROM x)) AS j ON i.a = j.a; +SELECT i.a AS a FROM x AS i LEFT JOIN (SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0) AS j ON i.a = j.a; diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql index cf4195d..4e9e70c 100644 --- a/tests/fixtures/optimizer/simplify.sql +++ b/tests/fixtures/optimizer/simplify.sql @@ -375,6 +375,18 @@ CAST('1998-12-01' AS DATE) - INTERVAL '90' foo; date '1998-12-01' + interval '90' foo; CAST('1998-12-01' AS DATE) + INTERVAL '90' foo; +CAST(x AS DATE) + interval '1' week; +CAST(x AS DATE) + INTERVAL '1' week; + +CAST('2008-11-11' AS DATETIME) + INTERVAL '5' MONTH; +CAST('2009-04-11 00:00:00' AS DATETIME); + +datetime '1998-12-01' - interval '90' day; +CAST('1998-09-02 00:00:00' AS DATETIME); + +CAST(x AS DATETIME) + interval '1' week; +CAST(x AS DATETIME) + INTERVAL '1' week; + -------------------------------------- -- Comparisons -------------------------------------- diff --git a/tests/fixtures/optimizer/tpc-h/tpc-h.sql b/tests/fixtures/optimizer/tpc-h/tpc-h.sql index 9c1f138..272fb26 100644 --- a/tests/fixtures/optimizer/tpc-h/tpc-h.sql +++ b/tests/fixtures/optimizer/tpc-h/tpc-h.sql @@ -150,7 +150,6 @@ WHERE "part"."p_size" = 15 AND "part"."p_type" LIKE '%BRASS' AND "partsupp"."ps_supplycost" = "_u_0"."_col_0" - AND NOT "_u_0"."_u_1" IS NULL ORDER BY "s_acctbal" DESC, "n_name", @@ -1008,7 +1007,7 @@ JOIN "part" AS "part" LEFT JOIN "_u_0" AS "_u_0" ON "_u_0"."_u_1" = "part"."p_partkey" WHERE - "lineitem"."l_quantity" < "_u_0"."_col_0" AND NOT "_u_0"."_u_1" IS NULL; + "lineitem"."l_quantity" < "_u_0"."_col_0"; -------------------------------------- -- TPC-H 18 @@ -1253,10 +1252,7 @@ WITH "_u_0" AS ( LEFT JOIN "_u_3" AS "_u_3" ON "partsupp"."ps_partkey" = "_u_3"."p_partkey" WHERE - "partsupp"."ps_availqty" > "_u_0"."_col_0" - AND NOT "_u_0"."_u_1" IS NULL - AND NOT "_u_0"."_u_2" IS NULL - AND NOT "_u_3"."p_partkey" IS NULL + "partsupp"."ps_availqty" > "_u_0"."_col_0" AND NOT "_u_3"."p_partkey" IS NULL GROUP BY "partsupp"."ps_suppkey" ) diff --git a/tests/fixtures/optimizer/unnest_subqueries.sql b/tests/fixtures/optimizer/unnest_subqueries.sql index a444945..9d760e0 100644 --- a/tests/fixtures/optimizer/unnest_subqueries.sql +++ b/tests/fixtures/optimizer/unnest_subqueries.sql @@ -22,6 +22,8 @@ WHERE AND x.a > ANY (SELECT y.a FROM y) AND x.a = (SELECT SUM(y.c) AS c FROM y WHERE y.a = x.a LIMIT 10) AND x.a = (SELECT SUM(y.c) AS c FROM y WHERE y.a = x.a OFFSET 10) + AND x.a > ALL (SELECT y.c FROM y WHERE y.a = x.a) + AND x.a > (SELECT COUNT(*) as d FROM y WHERE y.a = x.a) ; SELECT * @@ -130,37 +132,42 @@ LEFT JOIN ( y.a ) AS _u_15 ON x.a = _u_15.a +LEFT JOIN ( + SELECT + ARRAY_AGG(c), + y.a AS _u_20 + FROM y + WHERE + TRUE + GROUP BY + y.a +) AS _u_19 + ON _u_19._u_20 = x.a +LEFT JOIN ( + SELECT + COUNT(*) AS d, + y.a AS _u_22 + FROM y + WHERE + TRUE + GROUP BY + y.a +) AS _u_21 + ON _u_21._u_22 = x.a WHERE 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 x.a > _u_6.b + AND x.a = _u_8.a + AND NOT x.a = _u_9.a + AND ARRAY_ANY(_u_10.a, _x -> _x = x.a) AND ( - 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 - ) - AND ( - 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 - ) - AND ( - 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 - ) - 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 + x.a < _u_12.a AND ARRAY_ANY(_u_12._u_14, "_x" -> _x <> x.d) ) + AND NOT _u_15.a IS NULL AND x.a IN ( SELECT y.a AS a @@ -199,4 +206,6 @@ WHERE WHERE y.a = x.a OFFSET 10 - ); + ) + AND ARRAY_ALL(_u_19."", _x -> _x = x.a) + AND x.a > COALESCE(_u_21.d, 0); -- cgit v1.2.3