From 244a05de60c9417daab9528b51788c3d2a00dc5f Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Tue, 17 Jan 2023 11:32:12 +0100 Subject: Adding upstream version 10.5.2. Signed-off-by: Daniel Baumann --- tests/fixtures/identity.sql | 16 ++++++ 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 +++++++++++++---------- 7 files changed, 107 insertions(+), 32 deletions(-) (limited to 'tests/fixtures') diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql index e6a6e6b..beb5703 100644 --- a/tests/fixtures/identity.sql +++ b/tests/fixtures/identity.sql @@ -89,6 +89,7 @@ POSEXPLODE("x") AS ("a", "b") POSEXPLODE("x") AS ("a", "b", "c") STR_POSITION(x, 'a') STR_POSITION(x, 'a', 3) +LEVENSHTEIN('gumbo', 'gambol', 2, 1, 1) SPLIT(SPLIT(referrer, 'utm_source=')[OFFSET(1)], "&")[OFFSET(0)] x[ORDINAL(1)][SAFE_OFFSET(2)] x LIKE SUBSTR('abc', 1, 1) @@ -425,6 +426,7 @@ SELECT SUM(x) OVER (PARTITION BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT SELECT SUM(x) OVER (PARTITION BY a RANGE BETWEEN 1 AND 3) SELECT SUM(x) OVER (PARTITION BY a RANGE BETWEEN 1 FOLLOWING AND 3) SELECT SUM(x) OVER (PARTITION BY a RANGE BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) +SELECT AVG(x) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t SELECT LISTAGG(x) WITHIN GROUP (ORDER BY x) AS y SELECT LISTAGG(x) WITHIN GROUP (ORDER BY x DESC) SELECT SUM(x) FILTER(WHERE x > 1) @@ -450,14 +452,24 @@ 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) +SELECT * FROM t WITH (TABLOCK, INDEX(myindex)) +SELECT * FROM t WITH (NOWAIT) +CREATE TABLE foo AS (SELECT 1) UNION ALL (SELECT 2) CREATE TABLE foo (id INT PRIMARY KEY ASC) CREATE TABLE a.b AS SELECT 1 +CREATE TABLE a.b AS SELECT 1 WITH DATA AND STATISTICS +CREATE TABLE a.b AS SELECT 1 WITH NO DATA AND NO STATISTICS +CREATE TABLE a.b AS (SELECT 1) NO PRIMARY INDEX +CREATE TABLE a.b AS (SELECT 1) UNIQUE PRIMARY INDEX index1 (a) UNIQUE INDEX index2 (b) +CREATE TABLE a.b AS (SELECT 1) PRIMARY AMP INDEX index1 (a) UNIQUE INDEX index2 (b) CREATE TABLE a.b AS SELECT a FROM a.c CREATE TABLE IF NOT EXISTS x AS SELECT a FROM d CREATE TEMPORARY TABLE x AS SELECT a FROM d CREATE TEMPORARY TABLE IF NOT EXISTS x AS SELECT a FROM d CREATE VIEW x AS SELECT a FROM b CREATE VIEW IF NOT EXISTS x AS SELECT a FROM b +CREATE VIEW z (a, b COMMENT 'b', c COMMENT 'c') AS SELECT a, b, c FROM d +CREATE VIEW IF NOT EXISTS z (a, b COMMENT 'b', c COMMENT 'c') AS SELECT a, b, c FROM d CREATE OR REPLACE VIEW x AS SELECT * CREATE OR REPLACE TEMPORARY VIEW x AS SELECT * CREATE TEMPORARY VIEW x AS SELECT a FROM d @@ -490,6 +502,8 @@ CREATE TABLE z (a INT UNIQUE AUTO_INCREMENT) CREATE TABLE z (a INT REFERENCES parent(b, c)) CREATE TABLE z (a INT PRIMARY KEY, b INT REFERENCES foo(id)) CREATE TABLE z (a INT, FOREIGN KEY (a) REFERENCES parent(b, c)) +CREATE VIEW z (a, b) +CREATE VIEW z (a, b COMMENT 'b', c COMMENT 'c') CREATE TEMPORARY FUNCTION f CREATE TEMPORARY FUNCTION f AS 'g' CREATE FUNCTION f @@ -559,6 +573,7 @@ INSERT INTO x.z IF EXISTS SELECT * FROM y INSERT INTO x VALUES (1, 'a', 2.0) INSERT INTO x VALUES (1, 'a', 2.0), (1, 'a', 3.0), (X(), y[1], z.x) INSERT INTO y (a, b, c) SELECT a, b, c FROM x +INSERT INTO y (SELECT 1) UNION (SELECT 2) INSERT OVERWRITE TABLE x IF EXISTS SELECT * FROM y INSERT OVERWRITE TABLE a.b IF EXISTS SELECT * FROM y INSERT OVERWRITE DIRECTORY 'x' SELECT 1 @@ -627,3 +642,4 @@ ALTER TABLE integers ALTER COLUMN i SET DEFAULT 10 ALTER TABLE integers ALTER COLUMN i DROP DEFAULT ALTER TABLE mydataset.mytable DROP COLUMN A, DROP COLUMN IF EXISTS B ALTER TABLE mydataset.mytable ADD COLUMN A TEXT, ADD COLUMN IF NOT EXISTS B INT +SELECT div.a FROM test_table AS div 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