From cf49728f719975144a958f23ba5f3336fb81ae55 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 27 Apr 2024 04:50:25 +0200 Subject: Merging upstream version 23.12.1. Signed-off-by: Daniel Baumann --- tests/fixtures/identity.sql | 5 ++++- tests/fixtures/optimizer/merge_subqueries.sql | 16 ++++++++-------- tests/fixtures/optimizer/normalize_identifiers.sql | 4 ++-- tests/fixtures/optimizer/qualify_columns.sql | 9 +++++++-- tests/fixtures/optimizer/simplify.sql | 5 ++++- tests/fixtures/optimizer/tpc-ds/tpc-ds.sql | 15 +++++++-------- tests/fixtures/pretty.sql | 4 ++-- 7 files changed, 34 insertions(+), 24 deletions(-) (limited to 'tests/fixtures') diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql index 5453a78..d51a978 100644 --- a/tests/fixtures/identity.sql +++ b/tests/fixtures/identity.sql @@ -659,6 +659,7 @@ INSERT OVERWRITE TABLE a.b PARTITION(ds) SELECT x FROM y INSERT OVERWRITE TABLE a.b PARTITION(ds = 'YYYY-MM-DD') SELECT x FROM y INSERT OVERWRITE TABLE a.b PARTITION(ds, hour) SELECT x FROM y INSERT OVERWRITE TABLE a.b PARTITION(ds = 'YYYY-MM-DD', hour = 'hh') SELECT x FROM y +INSERT INTO a.b PARTITION(DAY = '2024-04-14') (col1, col2) SELECT x FROM y DELETE FROM x WHERE y > 1 DELETE FROM y DELETE FROM event USING sales WHERE event.eventid = sales.eventid @@ -690,6 +691,8 @@ INSERT INTO y (a, b, c) SELECT a, b, c FROM x INSERT INTO y (SELECT 1) UNION (SELECT 2) INSERT INTO result_table (WITH test AS (SELECT * FROM source_table) SELECT * FROM test) INSERT INTO "tests_user" ("username", "first_name", "last_name") VALUES ('fiara', 'Fiara', 'Ironhide') RETURNING "tests_user"."id" +INSERT INTO t1 (tc1 /* tc1 */, tc2 /* tc2 */) SELECT c1 /* sc1 */, c2 /* sc2 */ FROM t +INSERT INTO t1 ("tc1" /* tc1 */, "tc2" /* tc2 */) SELECT "c1" /* sc1 */, "c2" /* sc2 */ FROM t 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 @@ -864,4 +867,4 @@ SELECT only TRUNCATE(a, b) SELECT enum SELECT unlogged -SELECT name +SELECT name \ No newline at end of file diff --git a/tests/fixtures/optimizer/merge_subqueries.sql b/tests/fixtures/optimizer/merge_subqueries.sql index f953539..ce5a435 100644 --- a/tests/fixtures/optimizer/merge_subqueries.sql +++ b/tests/fixtures/optimizer/merge_subqueries.sql @@ -86,20 +86,20 @@ SELECT * FROM (SELECT * FROM (SELECT * FROM x)) ORDER BY a LIMIT 1; SELECT x.a AS a, x.b AS b FROM x AS x ORDER BY x.a LIMIT 1; # title: CTE -WITH x AS (SELECT a, b FROM x) SELECT a, b FROM x; -SELECT x.a AS a, x.b AS b FROM x AS x; +WITH x AS (SELECT a, b FROM main.x) SELECT a, b FROM x; +SELECT x.a AS a, x.b AS b FROM main.x AS x; # title: CTE with outer table alias WITH y AS (SELECT a, b FROM x) SELECT a, b FROM y AS z; SELECT x.a AS a, x.b AS b FROM x AS x; # title: Nested CTE -WITH x2 AS (SELECT a FROM x), x3 AS (SELECT a FROM x2) SELECT a FROM x3; -SELECT x.a AS a FROM x AS x; +WITH x2 AS (SELECT a FROM main.x), x3 AS (SELECT a FROM x2) SELECT a FROM x3; +SELECT x.a AS a FROM main.x AS x; # title: CTE WHERE clause is merged -WITH x AS (SELECT a, b FROM x WHERE a > 1) SELECT a, SUM(b) AS b FROM x GROUP BY a; -SELECT x.a AS a, SUM(x.b) AS b FROM x AS x WHERE x.a > 1 GROUP BY x.a; +WITH x AS (SELECT a, b FROM main.x WHERE a > 1) SELECT a, SUM(b) AS b FROM x GROUP BY a; +SELECT x.a AS a, SUM(x.b) AS b FROM main.x AS x WHERE x.a > 1 GROUP BY x.a; # title: CTE Outer query has join WITH x2 AS (SELECT a, b FROM x WHERE a > 1) SELECT a, c FROM x2 AS x JOIN y ON x.b = y.b; @@ -110,8 +110,8 @@ WITH y AS (SELECT a, b FROM x AS q) SELECT a, b FROM y AS z; SELECT q.a AS a, q.b AS b FROM x AS q; # title: Nested CTE -SELECT * FROM (WITH x AS (SELECT a, b FROM x) SELECT a, b FROM x); -SELECT x.a AS a, x.b AS b FROM x AS x; +SELECT * FROM (WITH x AS (SELECT a, b FROM main.x) SELECT a, b FROM x); +SELECT x.a AS a, x.b AS b FROM main.x AS x; # title: Inner select is an expression SELECT a FROM (SELECT a FROM (SELECT COALESCE(a) AS a FROM x LEFT JOIN y ON x.a = y.b) AS x) AS x; diff --git a/tests/fixtures/optimizer/normalize_identifiers.sql b/tests/fixtures/optimizer/normalize_identifiers.sql index 7ddcc6d..b210fa7 100644 --- a/tests/fixtures/optimizer/normalize_identifiers.sql +++ b/tests/fixtures/optimizer/normalize_identifiers.sql @@ -68,8 +68,8 @@ SELECT a /* sqlglot.meta case_sensitive */, b FROM table /* sqlglot.meta case_se SELECT a /* sqlglot.meta case_sensitive */, B FROM table /* sqlglot.meta case_sensitive */; # dialect: redshift -SELECT COALESCE(json_val.a /* sqlglot.meta case_sensitive */, json_val.A /* sqlglot.meta case_sensitive */) FROM table; -SELECT COALESCE(json_val.a /* sqlglot.meta case_sensitive */, json_val.A /* sqlglot.meta case_sensitive */) FROM table; +SELECT COALESCE(json_val.a /* sqlglot.meta case_sensitive */, json_val.A /* sqlglot.meta case_sensitive */) FROM tbl; +SELECT COALESCE(json_val.a /* sqlglot.meta case_sensitive */, json_val.A /* sqlglot.meta case_sensitive */) FROM tbl; SELECT @X; SELECT @X; diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql index 8baf961..b020a27 100644 --- a/tests/fixtures/optimizer/qualify_columns.sql +++ b/tests/fixtures/optimizer/qualify_columns.sql @@ -494,8 +494,13 @@ 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; +select x, a, x.a from unnest([STRUCT(1 AS a)]) as x CROSS JOIN m; +SELECT x AS x, a AS a, x.a AS a FROM UNNEST([STRUCT(1 AS a)]) AS x CROSS JOIN m AS m; + +# dialect: bigquery +# execute: false +WITH cte AS (SELECT [STRUCT(1 AS a)] AS x) select a, x, m.a from cte, UNNEST(x) AS m CROSS JOIN n; +WITH cte AS (SELECT [STRUCT(1 AS a)] AS x) SELECT a AS a, cte.x AS x, m.a AS a FROM cte AS cte, UNNEST(cte.x) AS m CROSS JOIN n AS n; # dialect: presto SELECT x.a, i.b FROM x CROSS JOIN UNNEST(SPLIT(CAST(b AS VARCHAR), ',')) AS i(b); diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql index a10942d..6af51bf 100644 --- a/tests/fixtures/optimizer/simplify.sql +++ b/tests/fixtures/optimizer/simplify.sql @@ -304,6 +304,9 @@ x * (1 - y); (((x % 20) = 0) = TRUE); ((x % 20) = 0) = TRUE; +ANY(t.value); +ANY(t.value); + -------------------------------------- -- Literals -------------------------------------- @@ -866,7 +869,7 @@ CONCAT_WS(sep, 'a', 'b'); CONCAT_WS(sep, 'a', 'b'); 'a' || 'b' || x; -CONCAT('ab', x); +'ab' || x; CONCAT(a, b) IN (SELECT * FROM foo WHERE cond); CONCAT(a, b) IN (SELECT * FROM foo WHERE cond); diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql index 35fbb70..a357b07 100644 --- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql +++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql @@ -822,7 +822,7 @@ WITH "salesreturns" AS ( ), "x" AS ( SELECT 'store channel' AS "channel", - CONCAT('store', "ssr"."s_store_id") AS "id", + 'store' || "ssr"."s_store_id" AS "id", "ssr"."sales" AS "sales", "ssr"."returns1" AS "returns1", "ssr"."profit" - "ssr"."profit_loss" AS "profit" @@ -830,7 +830,7 @@ WITH "salesreturns" AS ( UNION ALL SELECT 'catalog channel' AS "channel", - CONCAT('catalog_page', "csr"."cp_catalog_page_id") AS "id", + 'catalog_page' || "csr"."cp_catalog_page_id" AS "id", "csr"."sales" AS "sales", "csr"."returns1" AS "returns1", "csr"."profit" - "csr"."profit_loss" AS "profit" @@ -838,7 +838,7 @@ WITH "salesreturns" AS ( UNION ALL SELECT 'web channel' AS "channel", - CONCAT('web_site', "wsr"."web_site_id") AS "id", + 'web_site' || "wsr"."web_site_id" AS "id", "wsr"."sales" AS "sales", "wsr"."returns1" AS "returns1", "wsr"."profit" - "wsr"."profit_loss" AS "profit" @@ -10927,7 +10927,7 @@ WITH "date_dim_2" AS ( ), "x" AS ( SELECT 'store channel' AS "channel", - CONCAT('store', "ssr"."store_id") AS "id", + 'store' || "ssr"."store_id" AS "id", "ssr"."sales" AS "sales", "ssr"."returns1" AS "returns1", "ssr"."profit" AS "profit" @@ -10935,7 +10935,7 @@ WITH "date_dim_2" AS ( UNION ALL SELECT 'catalog channel' AS "channel", - CONCAT('catalog_page', "csr"."catalog_page_id") AS "id", + 'catalog_page' || "csr"."catalog_page_id" AS "id", "csr"."sales" AS "sales", "csr"."returns1" AS "returns1", "csr"."profit" AS "profit" @@ -10943,7 +10943,7 @@ WITH "date_dim_2" AS ( UNION ALL SELECT 'web channel' AS "channel", - CONCAT('web_site', "wsr"."web_site_id") AS "id", + 'web_site' || "wsr"."web_site_id" AS "id", "wsr"."sales" AS "sales", "wsr"."returns1" AS "returns1", "wsr"."profit" AS "profit" @@ -11368,7 +11368,7 @@ ORDER BY c_customer_id LIMIT 100; SELECT "customer"."c_customer_id" AS "customer_id", - CONCAT("customer"."c_last_name", ', ', "customer"."c_first_name") AS "customername" + "customer"."c_last_name" || ', ' || "customer"."c_first_name" AS "customername" FROM "customer" AS "customer" JOIN "customer_address" AS "customer_address" ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk" @@ -12743,4 +12743,3 @@ ORDER BY "sm_type", "cc_name" LIMIT 100; - diff --git a/tests/fixtures/pretty.sql b/tests/fixtures/pretty.sql index fac08be..62ba01c 100644 --- a/tests/fixtures/pretty.sql +++ b/tests/fixtures/pretty.sql @@ -336,8 +336,8 @@ SELECT * /* multi comment */; SELECT * /* multi - line - comment */; + line + comment */; WITH table_data AS ( SELECT 'bob' AS name, ARRAY['banana', 'apple', 'orange'] AS fruit_basket ) -- cgit v1.2.3