From 41f1f5740d2140bfd3b2a282ca1087a4b576679a Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Mon, 15 Apr 2024 07:02:18 +0200 Subject: Merging upstream version 23.10.0. Signed-off-by: Daniel Baumann --- tests/fixtures/optimizer/annotate_types.sql | 57 ++++++++++++++++++++++++++++ tests/fixtures/optimizer/optimizer.sql | 42 ++++++++++++++++++++ tests/fixtures/optimizer/qualify_columns.sql | 10 +++++ tests/fixtures/optimizer/qualify_tables.sql | 7 ++++ 4 files changed, 116 insertions(+) create mode 100644 tests/fixtures/optimizer/annotate_types.sql (limited to 'tests/fixtures') diff --git a/tests/fixtures/optimizer/annotate_types.sql b/tests/fixtures/optimizer/annotate_types.sql new file mode 100644 index 0000000..e781765 --- /dev/null +++ b/tests/fixtures/optimizer/annotate_types.sql @@ -0,0 +1,57 @@ +5; +INT; + +5.3; +DOUBLE; + +'bla'; +VARCHAR; + +True; +bool; + +false; +bool; + +null; +null; +CASE WHEN x THEN NULL ELSE 1 END; +INT; + +CASE WHEN x THEN 1 ELSE NULL END; +INT; + +IF(true, 1, null); +INT; + +IF(true, null, 1); +INT; + +STRUCT(1 AS col); +STRUCT; + +STRUCT(1 AS col, 2.5 AS row); +STRUCT; + +STRUCT(1); +STRUCT; + +STRUCT(1 AS col, 2.5 AS row, struct(3.5 AS inner_col, 4 AS inner_row) AS nested_struct); +STRUCT>; + +STRUCT(1 AS col, 2.5, ARRAY[1, 2, 3] AS nested_array, 'foo'); +STRUCT, VARCHAR>; + +STRUCT(1, 2.5, 'bar'); +STRUCT; + +STRUCT(1 AS "CaseSensitive"); +STRUCT<"CaseSensitive" INT>; + +# dialect: duckdb +STRUCT_PACK(a := 1, b := 2.5); +STRUCT; + +# dialect: presto +ROW(1, 2.5, 'foo'); +STRUCT; diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql index cc72e6d..37ef4fd 100644 --- a/tests/fixtures/optimizer/optimizer.sql +++ b/tests/fixtures/optimizer/optimizer.sql @@ -1388,3 +1388,45 @@ WHERE ORDER BY COUNT(DISTINCT `cs1`.`cs_order_number`) LIMIT 100; + +# execute: false +SELECT + * +FROM event +WHERE priority = 'High' AND tagname IN ( + SELECT + tag_input AS tagname + FROM cascade + WHERE tag_input = 'XXX' OR tag_output = 'XXX' + UNION + SELECT + tag_output AS tagname + FROM cascade + WHERE tag_input = 'XXX' OR tag_output = 'XXX' +); +WITH "_u_0" AS ( + SELECT + "cascade"."tag_input" AS "tagname" + FROM "cascade" AS "cascade" + WHERE + "cascade"."tag_input" = 'XXX' OR "cascade"."tag_output" = 'XXX' + UNION + SELECT + "cascade"."tag_output" AS "tagname" + FROM "cascade" AS "cascade" + WHERE + "cascade"."tag_input" = 'XXX' OR "cascade"."tag_output" = 'XXX' +), "_u_1" AS ( + SELECT + "cascade"."tag_input" AS "tagname" + FROM "_u_0" AS "_u_0" + GROUP BY + "cascade"."tag_input" +) +SELECT + * +FROM "event" AS "event" +LEFT JOIN "_u_1" AS "_u_1" + ON "_u_1"."tagname" = "event"."tagname" +WHERE + "event"."priority" = 'High' AND NOT "_u_1"."tagname" IS NULL; diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql index 289145b..8baf961 100644 --- a/tests/fixtures/optimizer/qualify_columns.sql +++ b/tests/fixtures/optimizer/qualify_columns.sql @@ -492,6 +492,11 @@ SELECT x AS x, offset AS offset FROM UNNEST([1, 2]) AS x WITH OFFSET AS offset; select * from unnest ([1, 2]) as x with offset as y; SELECT x AS x, y AS y FROM UNNEST([1, 2]) AS x WITH OFFSET AS y; +# dialect: bigquery +# execute: false +select x, a, x.a from unnest([STRUCT(1 AS a)]) as x; +SELECT x AS x, a AS a, x.a AS a FROM UNNEST([STRUCT(1 AS a)]) AS x; + # dialect: presto SELECT x.a, i.b FROM x CROSS JOIN UNNEST(SPLIT(CAST(b AS VARCHAR), ',')) AS i(b); SELECT x.a AS a, i.b AS b FROM x AS x CROSS JOIN UNNEST(SPLIT(CAST(x.b AS VARCHAR), ',')) AS i(b); @@ -508,6 +513,11 @@ SELECT t.c1 AS c1, t.c2 AS c2, t.c3 AS c3 FROM FOO(bar) AS t(c1, c2, c3); SELECT c1, c3 FROM foo(bar) AS t(c1, c2, c3); SELECT t.c1 AS c1, t.c3 AS c3 FROM FOO(bar) AS t(c1, c2, c3); +# dialect: redshift +# execute: false +SELECT c.f::VARCHAR(MAX) AS f, e AS e FROM a.b AS c, c.d AS e; +SELECT CAST(c.f AS VARCHAR(MAX)) AS f, e AS e FROM a.b AS c, c.d AS e; + -------------------------------------- -- Window functions -------------------------------------- diff --git a/tests/fixtures/optimizer/qualify_tables.sql b/tests/fixtures/optimizer/qualify_tables.sql index f651a87..104400e 100644 --- a/tests/fixtures/optimizer/qualify_tables.sql +++ b/tests/fixtures/optimizer/qualify_tables.sql @@ -166,3 +166,10 @@ WITH cte AS (SELECT b FROM c.db.y AS y) INSERT INTO c.db.s SELECT * FROM cte AS # title: qualify wrapped query (SELECT x FROM t); (SELECT x FROM c.db.t AS t); + +# title: replace columns with db/catalog refs +SELECT db1.a.id, db2.a.id FROM db1.a JOIN db2.a ON db1.a.id = db2.a.id; +SELECT a.id, a_2.id FROM c.db1.a AS a JOIN c.db2.a AS a_2 ON a.id = a_2.id; + +SELECT cat.db1.a.id, db2.a.id FROM cat.db1.a JOIN db2.a ON cat.db1.a.id = db2.a.id; +SELECT a.id, a_2.id FROM cat.db1.a AS a JOIN c.db2.a AS a_2 ON a.id = a_2.id; -- cgit v1.2.3