From 341eb1a6bdf0dd5b015e5140d3b068c6fd3f4d87 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Thu, 7 Sep 2023 13:39:43 +0200 Subject: Adding upstream version 18.2.0. Signed-off-by: Daniel Baumann --- tests/fixtures/identity.sql | 15 +++++---- tests/fixtures/optimizer/eliminate_subqueries.sql | 4 +++ tests/fixtures/optimizer/optimize_joins.sql | 12 ++++++-- tests/fixtures/optimizer/optimizer.sql | 37 +++++++++++++++++++++++ tests/fixtures/optimizer/pushdown_predicates.sql | 8 +++++ tests/fixtures/optimizer/simplify.sql | 15 +++++++++ 6 files changed, 82 insertions(+), 9 deletions(-) (limited to 'tests/fixtures') diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql index b21d65d..0953fee 100644 --- a/tests/fixtures/identity.sql +++ b/tests/fixtures/identity.sql @@ -139,6 +139,7 @@ x ILIKE '%y%' ESCAPE '\' 1 AS escape INTERVAL '1' day INTERVAL '1' MONTH +INTERVAL '1' YEAR INTERVAL '-1' CURRENT_DATE INTERVAL '-31' CAST(GETDATE() AS DATE) INTERVAL (1 + 3) DAYS @@ -620,13 +621,13 @@ CREATE FUNCTION a() LANGUAGE sql CREATE FUNCTION a() LANGUAGE sql RETURNS INT CREATE FUNCTION a.b(x INT) RETURNS INT AS RETURN x + 1 CREATE FUNCTION a.b.c() -CREATE INDEX abc ON t (a) -CREATE INDEX "abc" ON t (a) -CREATE INDEX abc ON t (a, b, b) -CREATE INDEX abc ON t (a NULLS LAST) +CREATE INDEX abc ON t(a) +CREATE INDEX "abc" ON t(a) +CREATE INDEX abc ON t(a, b, b) +CREATE INDEX abc ON t(a NULLS LAST) CREATE INDEX pointloc ON points USING GIST(BOX(location, location)) -CREATE UNIQUE INDEX abc ON t (a, b, b) -CREATE UNIQUE INDEX IF NOT EXISTS my_idx ON tbl (a, b) +CREATE UNIQUE INDEX abc ON t(a, b, b) +CREATE UNIQUE INDEX IF NOT EXISTS my_idx ON tbl(a, b) CREATE SCHEMA x CREATE SCHEMA IF NOT EXISTS y CREATE DATABASE x @@ -836,6 +837,7 @@ JSON_OBJECT('x': NULL, 'y': 1 WITH UNIQUE KEYS) JSON_OBJECT('x': NULL, 'y': 1 ABSENT ON NULL WITH UNIQUE KEYS) JSON_OBJECT('x': 1 RETURNING VARCHAR(100)) JSON_OBJECT('x': 1 RETURNING VARBINARY FORMAT JSON ENCODING UTF8) +PRIOR AS x SELECT if.x SELECT NEXT VALUE FOR db.schema.sequence_name SELECT NEXT VALUE FOR db.schema.sequence_name OVER (ORDER BY foo), col @@ -855,3 +857,4 @@ SELECT * FROM (tbl1 CROSS JOIN (SELECT * FROM tbl2) AS t1) /* comment1 */ DELETE FROM x /* comment2 */ WHERE y > 1 /* comment */ CREATE TABLE foo AS SELECT 1 SELECT next, transform, if +SELECT "any", "case", "if", "next" diff --git a/tests/fixtures/optimizer/eliminate_subqueries.sql b/tests/fixtures/optimizer/eliminate_subqueries.sql index 8542c3e..e18d2a4 100644 --- a/tests/fixtures/optimizer/eliminate_subqueries.sql +++ b/tests/fixtures/optimizer/eliminate_subqueries.sql @@ -97,3 +97,7 @@ WITH cte1 AS (SELECT a FROM x), cte2 AS (SELECT a FROM cte1 AS cte3) SELECT a FR -- Wrapped subquery joined with table SELECT * FROM ((SELECT c FROM t1) JOIN t2); WITH cte AS (SELECT c FROM t1) SELECT * FROM (cte AS cte, t2); + +-- Wrapped subquery with redundant parentheses +SELECT * FROM (((SELECT * FROM tbl))); +WITH cte AS (SELECT * FROM tbl) SELECT * FROM cte AS cte; diff --git a/tests/fixtures/optimizer/optimize_joins.sql b/tests/fixtures/optimizer/optimize_joins.sql index b8e39c3..6d5bed2 100644 --- a/tests/fixtures/optimizer/optimize_joins.sql +++ b/tests/fixtures/optimizer/optimize_joins.sql @@ -14,7 +14,7 @@ SELECT * FROM x INNER JOIN z ON x.id = z.id; SELECT * FROM x JOIN z ON x.id = z.id; SELECT * FROM x LEFT OUTER JOIN z; -SELECT * FROM x LEFT JOIN z; +SELECT * FROM x LEFT JOIN z ON TRUE; SELECT * FROM x CROSS JOIN z; SELECT * FROM x CROSS JOIN z; @@ -22,11 +22,17 @@ SELECT * FROM x CROSS JOIN z; SELECT * FROM x JOIN z; SELECT * FROM x CROSS JOIN z; +SELECT * FROM x FULL JOIN z; +SELECT * FROM x FULL JOIN z ON TRUE; + SELECT * FROM x NATURAL JOIN z; -SELECT * FROM x NATURAL JOIN z; +SELECT * FROM x NATURAL JOIN z ON TRUE; SELECT * FROM x RIGHT JOIN z; -SELECT * FROM x RIGHT JOIN z; +SELECT * FROM x RIGHT JOIN z ON TRUE; SELECT * FROM x JOIN z USING (id); SELECT * FROM x JOIN z USING (id); + +SELECT * FROM x CROSS JOIN z ON TRUE; +SELECT * FROM x CROSS JOIN z; diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql index b318a92..18ee804 100644 --- a/tests/fixtures/optimizer/optimizer.sql +++ b/tests/fixtures/optimizer/optimizer.sql @@ -950,3 +950,40 @@ JOIN "n" AS "foo"("a") SELECT CONCAT('a', 'b') || CONCAT(CONCAT('c', 'd'), CONCAT('e', 'f')) + ('g' || 'h' || 'i'); SELECT 'abcdefghi' AS "_col_0"; + +# title: complex query with derived tables and redundant parentheses +# execute: false +# dialect: snowflake +SELECT + ("SUBQUERY_0"."KEY") AS "SUBQUERY_1_COL_0" +FROM + ( + SELECT + * + FROM + ((( + SELECT + * + FROM + ( + SELECT + event_name AS key, + insert_ts + FROM + ( + SELECT + insert_ts, + event_name + FROM + sales + WHERE + insert_ts > '2023-08-07 21:03:35.590 -0700' + ) + ) + ))) AS "SF_CONNECTOR_QUERY_ALIAS" + ) AS "SUBQUERY_0"; +SELECT + "SALES"."EVENT_NAME" AS "SUBQUERY_1_COL_0" +FROM "SALES" AS "SALES" +WHERE + "SALES"."INSERT_TS" > '2023-08-07 21:03:35.590 -0700'; diff --git a/tests/fixtures/optimizer/pushdown_predicates.sql b/tests/fixtures/optimizer/pushdown_predicates.sql index 79ce353..cfa69fb 100644 --- a/tests/fixtures/optimizer/pushdown_predicates.sql +++ b/tests/fixtures/optimizer/pushdown_predicates.sql @@ -36,3 +36,11 @@ WITH t1 AS (SELECT x.a, x.b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) A WITH m AS (SELECT a, b FROM (VALUES (1, 2)) AS a1(a, b)), n AS (SELECT a, b FROM m WHERE m.a = 1), o AS (SELECT a, b FROM m WHERE m.a = 2) SELECT n.a, n.b, n.a, o.b FROM n FULL OUTER JOIN o ON n.a = o.a; WITH m AS (SELECT a, b FROM (VALUES (1, 2)) AS a1(a, b)), n AS (SELECT a, b FROM m WHERE m.a = 1), o AS (SELECT a, b FROM m WHERE m.a = 2) SELECT n.a, n.b, n.a, o.b FROM n FULL OUTER JOIN o ON n.a = o.a; + +-- Pushdown predicate to HAVING (CNF) +SELECT x.cnt AS cnt FROM (SELECT COUNT(1) AS cnt FROM x AS x) AS x WHERE x.cnt > 0; +SELECT x.cnt AS cnt FROM (SELECT COUNT(1) AS cnt FROM x AS x HAVING COUNT(1) > 0) AS x WHERE TRUE; + +-- Pushdown predicate to HAVING (DNF) +SELECT x.cnt AS cnt FROM (SELECT COUNT(1) AS cnt, COUNT(x.a) AS cnt_a, COUNT(x.b) AS cnt_b FROM x AS x) AS x WHERE (x.cnt_a > 0 AND x.cnt_b > 0) OR x.cnt > 0; +SELECT x.cnt AS cnt FROM (SELECT COUNT(1) AS cnt, COUNT(x.a) AS cnt_a, COUNT(x.b) AS cnt_b FROM x AS x HAVING COUNT(1) > 0 OR (COUNT(x.a) > 0 AND COUNT(x.b) > 0)) AS x WHERE x.cnt > 0 OR (x.cnt_a > 0 AND x.cnt_b > 0); \ No newline at end of file diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql index 3ed02cd..66fb19c 100644 --- a/tests/fixtures/optimizer/simplify.sql +++ b/tests/fixtures/optimizer/simplify.sql @@ -264,6 +264,9 @@ TRUE; (FALSE); FALSE; +((TRUE)); +TRUE; + (FALSE OR TRUE); TRUE; @@ -288,6 +291,9 @@ x = y AND z; x * (1 - y); x * (1 - y); +(((x % 20) = 0) = TRUE); +((x % 20) = 0) = TRUE; + -------------------------------------- -- Literals -------------------------------------- @@ -612,6 +618,9 @@ TRUE; x = 2018 OR x <> 2018; x <> 2018 OR x = 2018; +t0.x = t1.x AND t0.y < t1.y AND t0.y <= t1.y; +t0.x = t1.x AND t0.y < t1.y AND t0.y <= t1.y; + -------------------------------------- -- Coalesce -------------------------------------- @@ -645,6 +654,12 @@ x = 1 OR x IS NULL; COALESCE(x, 1) IS NULL; FALSE; +COALESCE(ROW() OVER (), 1) = 1; +ROW() OVER () = 1 OR ROW() OVER () IS NULL; + +a AND b AND COALESCE(ROW() OVER (), 1) = 1; +a AND b AND (ROW() OVER () = 1 OR ROW() OVER () IS NULL); + -------------------------------------- -- CONCAT -------------------------------------- -- cgit v1.2.3