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/dialects/test_bigquery.py | 29 +++++++++++++++ tests/dialects/test_clickhouse.py | 43 ++++++++++++++++++++++ tests/dialects/test_dialect.py | 15 ++++++++ tests/dialects/test_duckdb.py | 19 +++++++++- tests/dialects/test_hive.py | 5 +++ tests/dialects/test_mysql.py | 1 + tests/dialects/test_presto.py | 2 +- tests/dialects/test_prql.py | 8 ++++ tests/dialects/test_snowflake.py | 23 +++++++++--- tests/dialects/test_spark.py | 4 +- tests/dialects/test_tsql.py | 11 +++++- 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 +- tests/test_expressions.py | 4 +- tests/test_optimizer.py | 11 ++++++ tests/test_parser.py | 5 ++- tests/test_transforms.py | 20 ++++++++++ tests/test_transpile.py | 26 +++++++++++++ 23 files changed, 247 insertions(+), 37 deletions(-) (limited to 'tests') diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py index 300d492..301cd57 100644 --- a/tests/dialects/test_bigquery.py +++ b/tests/dialects/test_bigquery.py @@ -20,6 +20,27 @@ class TestBigQuery(Validator): maxDiff = None def test_bigquery(self): + self.validate_identity( + "[a, a(1, 2,3,4444444444444444, tttttaoeunthaoentuhaoentuheoantu, toheuntaoheutnahoeunteoahuntaoeh), b(3, 4,5), c, d, tttttttttttttttteeeeeeeeeeeeeett, 12312312312]", + """[ + a, + a( + 1, + 2, + 3, + 4444444444444444, + tttttaoeunthaoentuhaoentuheoantu, + toheuntaoheutnahoeunteoahuntaoeh + ), + b(3, 4, 5), + c, + d, + tttttttttttttttteeeeeeeeeeeeeett, + 12312312312 +]""", + pretty=True, + ) + self.validate_all( "SELECT STRUCT(1, 2, 3), STRUCT(), STRUCT('abc'), STRUCT(1, t.str_col), STRUCT(1 as a, 'abc' AS b), STRUCT(str_col AS abc)", write={ @@ -40,6 +61,10 @@ class TestBigQuery(Validator): "duckdb": "STRPTIME(x, '%Y-%m-%dT%H:%M:%S.%f%z')", }, ) + self.validate_identity( + "PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E*S%z', x)", + "PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E*S%z', x)", + ) table = parse_one("x-0._y.z", dialect="bigquery", into=exp.Table) self.assertEqual(table.catalog, "x-0") @@ -57,9 +82,13 @@ class TestBigQuery(Validator): self.assertEqual(exp.to_table("`x.y.z`", dialect="bigquery").sql("bigquery"), "`x.y.z`") self.assertEqual(exp.to_table("`x`.`y`", dialect="bigquery").sql("bigquery"), "`x`.`y`") + column = self.validate_identity("SELECT `db.t`.`c` FROM `db.t`").selects[0] + self.assertEqual(len(column.parts), 3) + select_with_quoted_udf = self.validate_identity("SELECT `p.d.UdF`(data) FROM `p.d.t`") self.assertEqual(select_with_quoted_udf.selects[0].name, "p.d.UdF") + self.validate_identity("assert.true(1 = 1)") self.validate_identity("SELECT ARRAY_TO_STRING(list, '--') AS text") self.validate_identity("SELECT jsondoc['some_key']") self.validate_identity("SELECT `p.d.UdF`(data).* FROM `p.d.t`") diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py index df3caaf..af552d1 100644 --- a/tests/dialects/test_clickhouse.py +++ b/tests/dialects/test_clickhouse.py @@ -93,6 +93,9 @@ class TestClickhouse(Validator): self.validate_identity("""SELECT JSONExtractString('{"x": {"y": 1}}', 'x', 'y')""") self.validate_identity("SELECT * FROM table LIMIT 1 BY a, b") self.validate_identity("SELECT * FROM table LIMIT 2 OFFSET 1 BY a, b") + self.validate_identity( + "SELECT id, quantileGK(100, 0.95)(reading) OVER (PARTITION BY id ORDER BY id RANGE BETWEEN 30000 PRECEDING AND CURRENT ROW) AS window FROM table" + ) self.validate_identity( "SELECT $1$foo$1$", @@ -409,6 +412,19 @@ class TestClickhouse(Validator): self.validate_identity("SELECT FORMAT") self.validate_identity("1 AS FORMAT").assert_is(exp.Alias) + self.validate_identity("SELECT DATE_FORMAT(NOW(), '%Y-%m-%d', '%T')") + self.validate_all( + "SELECT DATE_FORMAT(NOW(), '%Y-%m-%d')", + read={ + "clickhouse": "SELECT formatDateTime(NOW(), '%Y-%m-%d')", + "mysql": "SELECT DATE_FORMAT(NOW(), '%Y-%m-%d')", + }, + write={ + "clickhouse": "SELECT DATE_FORMAT(NOW(), '%Y-%m-%d')", + "mysql": "SELECT DATE_FORMAT(NOW(), '%Y-%m-%d')", + }, + ) + def test_cte(self): self.validate_identity("WITH 'x' AS foo SELECT foo") self.validate_identity("WITH ['c'] AS field_names SELECT field_names") @@ -813,3 +829,30 @@ LIFETIME(MIN 0 MAX 0)""", self.validate_identity( "CREATE TABLE t1 (a String EPHEMERAL, b String EPHEMERAL func(), c String MATERIALIZED func(), d String ALIAS func()) ENGINE=TinyLog()" ) + + def test_agg_functions(self): + def extract_agg_func(query): + return parse_one(query, read="clickhouse").selects[0].this + + self.assertIsInstance( + extract_agg_func("select quantileGK(100, 0.95) OVER (PARTITION BY id) FROM table"), + exp.AnonymousAggFunc, + ) + self.assertIsInstance( + extract_agg_func( + "select quantileGK(100, 0.95)(reading) OVER (PARTITION BY id) FROM table" + ), + exp.ParameterizedAgg, + ) + self.assertIsInstance( + extract_agg_func("select quantileGKIf(100, 0.95) OVER (PARTITION BY id) FROM table"), + exp.CombinedAggFunc, + ) + self.assertIsInstance( + extract_agg_func( + "select quantileGKIf(100, 0.95)(reading) OVER (PARTITION BY id) FROM table" + ), + exp.CombinedParameterizedAgg, + ) + + parse_one("foobar(x)").assert_is(exp.Anonymous) diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py index 691beb9..ea38521 100644 --- a/tests/dialects/test_dialect.py +++ b/tests/dialects/test_dialect.py @@ -2426,3 +2426,18 @@ FROM c""", """CREATE TEMPORARY SEQUENCE seq START WITH = 1 INCREMENT BY = 2""", """CREATE TEMPORARY SEQUENCE seq START WITH 1 INCREMENT BY 2""", ) + + def test_reserved_keywords(self): + order = exp.select("*").from_("order") + + for dialect in ("presto", "redshift"): + dialect = Dialect.get_or_raise(dialect) + self.assertEqual( + order.sql(dialect=dialect), + f"SELECT * FROM {dialect.IDENTIFIER_START}order{dialect.IDENTIFIER_END}", + ) + + self.validate_identity( + """SELECT partition.d FROM t PARTITION (d)""", + """SELECT partition.d FROM t AS PARTITION(d)""", + ) diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py index 0b13a70..9105a49 100644 --- a/tests/dialects/test_duckdb.py +++ b/tests/dialects/test_duckdb.py @@ -652,8 +652,14 @@ class TestDuckDB(Validator): "SELECT CAST('2020-05-06' AS DATE) + INTERVAL 5 DAY", read={"bigquery": "SELECT DATE_ADD(CAST('2020-05-06' AS DATE), INTERVAL 5 DAY)"}, ) - self.validate_identity("SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY y DESC) FROM t") - self.validate_identity("SELECT PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY y DESC) FROM t") + self.validate_identity( + "SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY y DESC) FROM t", + "SELECT QUANTILE_CONT(y, 0.25 ORDER BY y DESC) FROM t", + ) + self.validate_identity( + "SELECT PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY y DESC) FROM t", + "SELECT QUANTILE_DISC(y, 0.25 ORDER BY y DESC) FROM t", + ) self.validate_all( "SELECT QUANTILE_CONT(x, q) FROM t", write={ @@ -1075,6 +1081,15 @@ class TestDuckDB(Validator): write={ "snowflake": "ALTER TABLE db.t1 RENAME TO db.t2", "duckdb": "ALTER TABLE db.t1 RENAME TO t2", + "tsql": "EXEC sp_rename 'db.t1', 't2'", + }, + ) + self.validate_all( + 'ALTER TABLE "db"."t1" RENAME TO "db"."t2"', + write={ + "snowflake": 'ALTER TABLE "db"."t1" RENAME TO "db"."t2"', + "duckdb": 'ALTER TABLE "db"."t1" RENAME TO "t2"', + "tsql": "EXEC sp_rename '[db].[t1]', 't2'", }, ) diff --git a/tests/dialects/test_hive.py b/tests/dialects/test_hive.py index 33294ee..9215f05 100644 --- a/tests/dialects/test_hive.py +++ b/tests/dialects/test_hive.py @@ -428,6 +428,9 @@ class TestHive(Validator): self.validate_identity( "INSERT OVERWRITE TABLE zipcodes PARTITION(state = 0) VALUES (896, 'US', 'TAMPA', 33607)" ) + self.validate_identity( + "INSERT OVERWRITE DIRECTORY 'x' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' COLLECTION ITEMS TERMINATED BY ',' MAP KEYS TERMINATED BY ':' LINES TERMINATED BY '' STORED AS TEXTFILE SELECT * FROM `a`.`b`" + ) self.validate_identity( "SELECT a, b, SUM(c) FROM tabl AS t GROUP BY a, b, GROUPING SETS ((a, b), a)" ) @@ -524,9 +527,11 @@ class TestHive(Validator): self.validate_all( "APPROX_COUNT_DISTINCT(a)", write={ + "bigquery": "APPROX_COUNT_DISTINCT(a)", "duckdb": "APPROX_COUNT_DISTINCT(a)", "presto": "APPROX_DISTINCT(a)", "hive": "APPROX_COUNT_DISTINCT(a)", + "snowflake": "APPROX_COUNT_DISTINCT(a)", "spark": "APPROX_COUNT_DISTINCT(a)", }, ) diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py index 6558c97..e8af5c6 100644 --- a/tests/dialects/test_mysql.py +++ b/tests/dialects/test_mysql.py @@ -223,6 +223,7 @@ class TestMySQL(Validator): self.validate_identity("CHAR(0)") self.validate_identity("CHAR(77, 121, 83, 81, '76')") self.validate_identity("CHAR(77, 77.3, '77.3' USING utf8mb4)") + self.validate_identity("SELECT * FROM t1 PARTITION(p0)") def test_types(self): self.validate_identity("CAST(x AS MEDIUMINT) + CAST(y AS YEAR(4))") diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py index e1d8c06..4bafc08 100644 --- a/tests/dialects/test_presto.py +++ b/tests/dialects/test_presto.py @@ -871,7 +871,7 @@ class TestPresto(Validator): "SELECT ARRAY_SORT(x, (left, right) -> -1)", write={ "duckdb": "SELECT ARRAY_SORT(x)", - "presto": "SELECT ARRAY_SORT(x, (left, right) -> -1)", + "presto": 'SELECT ARRAY_SORT(x, ("left", "right") -> -1)', "hive": "SELECT SORT_ARRAY(x)", "spark": "SELECT ARRAY_SORT(x, (left, right) -> -1)", }, diff --git a/tests/dialects/test_prql.py b/tests/dialects/test_prql.py index 69e2e28..1a0eec2 100644 --- a/tests/dialects/test_prql.py +++ b/tests/dialects/test_prql.py @@ -58,3 +58,11 @@ class TestPRQL(Validator): self.validate_identity( "from x intersect y", "SELECT * FROM x INTERSECT ALL SELECT * FROM y" ) + self.validate_identity( + "from x filter a == null filter null != b", + "SELECT * FROM x WHERE a IS NULL AND NOT b IS NULL", + ) + self.validate_identity( + "from x filter (a > 1 || null != b || c != null)", + "SELECT * FROM x WHERE (a > 1 OR NOT b IS NULL OR NOT c IS NULL)", + ) diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py index b652541..1cbf68c 100644 --- a/tests/dialects/test_snowflake.py +++ b/tests/dialects/test_snowflake.py @@ -456,7 +456,7 @@ WHERE }, write={ "": f"SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x NULLS LAST){suffix}", - "duckdb": f"SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x){suffix}", + "duckdb": f"SELECT QUANTILE_CONT(x, 0.5 ORDER BY x){suffix}", "postgres": f"SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x){suffix}", "snowflake": f"SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x){suffix}", }, @@ -821,6 +821,13 @@ WHERE "snowflake": "CASE WHEN x = a OR (x IS NULL AND a IS NULL) THEN b WHEN x = c OR (x IS NULL AND c IS NULL) THEN d ELSE e END", }, ) + self.validate_all( + "SELECT LISTAGG(col1, ', ') WITHIN GROUP (ORDER BY col2) FROM t", + write={ + "duckdb": "SELECT GROUP_CONCAT(col1, ', ' ORDER BY col2) FROM t", + "snowflake": "SELECT LISTAGG(col1, ', ') WITHIN GROUP (ORDER BY col2) FROM t", + }, + ) def test_null_treatment(self): self.validate_all( @@ -1038,10 +1045,16 @@ WHERE "SELECT CAST('2019-02-28' AS DATE) + INTERVAL '1 day, 1 year'", ) - self.validate_identity("DATE(x)").assert_is(exp.Anonymous) - self.validate_identity("TO_DATE(x)").assert_is(exp.Anonymous) - self.validate_identity("TRY_TO_DATE(x)").assert_is(exp.Anonymous) + self.validate_identity("TO_DATE(x)").assert_is(exp.TsOrDsToDate) + self.validate_identity("TRY_TO_DATE(x)").assert_is(exp.TsOrDsToDate) + self.validate_all( + "DATE(x)", + write={ + "duckdb": "CAST(x AS DATE)", + "snowflake": "TO_DATE(x)", + }, + ) self.validate_all( "TO_DATE(x, 'MM-DD-YYYY')", write={ @@ -1490,7 +1503,7 @@ FROM persons AS p, LATERAL FLATTEN(input => p.c, path => 'contact') AS _flattene write={ "bigquery": "REGEXP_EXTRACT(subject, pattern, pos, occ)", "hive": "REGEXP_EXTRACT(subject, pattern, group)", - "presto": "REGEXP_EXTRACT(subject, pattern, group)", + "presto": 'REGEXP_EXTRACT(subject, pattern, "group")', "snowflake": "REGEXP_SUBSTR(subject, pattern, pos, occ, params, group)", "spark": "REGEXP_EXTRACT(subject, pattern, group)", }, diff --git a/tests/dialects/test_spark.py b/tests/dialects/test_spark.py index d2285e0..7534573 100644 --- a/tests/dialects/test_spark.py +++ b/tests/dialects/test_spark.py @@ -234,7 +234,7 @@ TBLPROPERTIES ( self.validate_identity("first_value(col, true)", "FIRST_VALUE(col) IGNORE NULLS") self.validate_identity("last(col, true)", "LAST(col) IGNORE NULLS") self.validate_identity("last_value(col, true)", "LAST_VALUE(col) IGNORE NULLS") - self.validate_identity("DESCRIBE EXTENDED db.table") + self.validate_identity("DESCRIBE EXTENDED db.tbl") self.validate_identity("SELECT * FROM test TABLESAMPLE (50 PERCENT)") self.validate_identity("SELECT * FROM test TABLESAMPLE (5 ROWS)") self.validate_identity("SELECT * FROM test TABLESAMPLE (BUCKET 4 OUT OF 10)") @@ -566,7 +566,7 @@ TBLPROPERTIES ( "ARRAY_SORT(x, (left, right) -> -1)", write={ "duckdb": "ARRAY_SORT(x)", - "presto": "ARRAY_SORT(x, (left, right) -> -1)", + "presto": 'ARRAY_SORT(x, ("left", "right") -> -1)', "hive": "SORT_ARRAY(x)", "spark": "ARRAY_SORT(x, (left, right) -> -1)", }, diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py index aefd857..4a475f6 100644 --- a/tests/dialects/test_tsql.py +++ b/tests/dialects/test_tsql.py @@ -455,7 +455,6 @@ class TestTSQL(Validator): self.validate_identity("CAST(x AS UNIQUEIDENTIFIER)") self.validate_identity("CAST(x AS MONEY)") self.validate_identity("CAST(x AS SMALLMONEY)") - self.validate_identity("CAST(x AS ROWVERSION)") self.validate_identity("CAST(x AS IMAGE)") self.validate_identity("CAST(x AS SQL_VARIANT)") self.validate_identity("CAST(x AS BIT)") @@ -475,6 +474,16 @@ class TestTSQL(Validator): "hive": "CAST(x AS TIMESTAMP)", }, ) + self.validate_all( + "CAST(x AS ROWVERSION)", + read={ + "tsql": "CAST(x AS TIMESTAMP)", + }, + write={ + "tsql": "CAST(x AS ROWVERSION)", + "hive": "CAST(x AS BINARY)", + }, + ) def test__types_ints(self): self.validate_all( 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 ) diff --git a/tests/test_expressions.py b/tests/test_expressions.py index 85560b8..81cfb86 100644 --- a/tests/test_expressions.py +++ b/tests/test_expressions.py @@ -871,6 +871,8 @@ FROM foo""", FROM foo""", ) + self.assertEqual(parse_one('max(x) as "a b" -- comment').comments, [" comment"]) + def test_to_interval(self): self.assertEqual(exp.to_interval("1day").sql(), "INTERVAL '1' DAY") self.assertEqual(exp.to_interval(" 5 months").sql(), "INTERVAL '5' MONTHS") @@ -1104,6 +1106,6 @@ FROM foo""", parse_one("x").assert_is(exp.Column) with self.assertRaisesRegex( - AssertionError, "x is not \." + AssertionError, "x is not \\." ): parse_one("x").assert_is(exp.Identifier) diff --git a/tests/test_optimizer.py b/tests/test_optimizer.py index c0b362c..758b60c 100644 --- a/tests/test_optimizer.py +++ b/tests/test_optimizer.py @@ -228,6 +228,17 @@ class TestOptimizer(unittest.TestCase): @patch("sqlglot.generator.logger") def test_qualify_columns(self, logger): + self.assertEqual( + optimizer.qualify.qualify( + parse_one( + "SELECT `my_db.my_table`.`my_column` FROM `my_db.my_table`", + read="bigquery", + ), + dialect="bigquery", + ).sql(dialect="bigquery"), + "SELECT `my_table`.`my_column` AS `my_column` FROM `my_db.my_table` AS `my_table`", + ) + self.assertEqual( optimizer.qualify_columns.qualify_columns( parse_one( diff --git a/tests/test_parser.py b/tests/test_parser.py index 791d352..6bcdb64 100644 --- a/tests/test_parser.py +++ b/tests/test_parser.py @@ -87,6 +87,9 @@ class TestParser(unittest.TestCase): self.assertIsNotNone(parse_one("date").find(exp.Column)) + def test_tuple(self): + parse_one("(a,)").assert_is(exp.Tuple) + def test_structs(self): cast = parse_one("cast(x as struct)") self.assertIsInstance(cast.to.expressions[0], exp.DataType) @@ -439,7 +442,7 @@ class TestParser(unittest.TestCase): self.assertEqual(expression.comments, ["comment2"]) self.assertEqual(expression.this.comments, ["comment3"]) - self.assertEqual(expression.args.get("with").comments, ["comment1.1", "comment1.2"]) + self.assertEqual(expression.args["with"].comments, ["comment1.1", "comment1.2"]) def test_type_literals(self): self.assertEqual(parse_one("int 1"), parse_one("CAST(1 AS INT)")) diff --git a/tests/test_transforms.py b/tests/test_transforms.py index 8f14ae4..73d6705 100644 --- a/tests/test_transforms.py +++ b/tests/test_transforms.py @@ -111,6 +111,26 @@ class TestTransforms(unittest.TestCase): "SELECT x FROM y QUALIFY ROW_NUMBER() OVER (PARTITION BY p)", "SELECT x FROM (SELECT x, ROW_NUMBER() OVER (PARTITION BY p) AS _w, p FROM y) AS _t WHERE _w", ) + self.validate( + eliminate_qualify, + "SELECT x AS z FROM y QUALIFY ROW_NUMBER() OVER (PARTITION BY z)", + "SELECT z FROM (SELECT x AS z, ROW_NUMBER() OVER (PARTITION BY x) AS _w, x FROM y) AS _t WHERE _w", + ) + self.validate( + eliminate_qualify, + "SELECT SOME_UDF(x) AS z FROM y QUALIFY ROW_NUMBER() OVER (PARTITION BY x ORDER BY z)", + "SELECT z FROM (SELECT SOME_UDF(x) AS z, ROW_NUMBER() OVER (PARTITION BY x ORDER BY SOME_UDF(x)) AS _w, x FROM y) AS _t WHERE _w", + ) + self.validate( + eliminate_qualify, + "SELECT x, t, x || t AS z FROM y QUALIFY ROW_NUMBER() OVER (PARTITION BY x ORDER BY z DESC)", + "SELECT x, t, z FROM (SELECT x, t, x || t AS z, ROW_NUMBER() OVER (PARTITION BY x ORDER BY x || t DESC) AS _w FROM y) AS _t WHERE _w", + ) + self.validate( + eliminate_qualify, + "SELECT y.x AS x, y.t AS z FROM y QUALIFY ROW_NUMBER() OVER (PARTITION BY x ORDER BY x DESC, z)", + "SELECT x, z FROM (SELECT y.x AS x, y.t AS z, ROW_NUMBER() OVER (PARTITION BY y.x ORDER BY y.x DESC, y.t) AS _w, y.t FROM y) AS _t WHERE _w", + ) def test_remove_precision_parameterized_types(self): self.validate( diff --git a/tests/test_transpile.py b/tests/test_transpile.py index 95fba30..22085b3 100644 --- a/tests/test_transpile.py +++ b/tests/test_transpile.py @@ -123,6 +123,10 @@ class TestTranspile(unittest.TestCase): "SELECT * FROM t1\n/*x*/\nUNION ALL SELECT * FROM t2", "SELECT * FROM t1 /* x */ UNION ALL SELECT * FROM t2", ) + self.validate( + "/* comment */ SELECT * FROM a UNION SELECT * FROM b", + "/* comment */ SELECT * FROM a UNION SELECT * FROM b", + ) self.validate( "SELECT * FROM t1\n/*x*/\nINTERSECT ALL SELECT * FROM t2", "SELECT * FROM t1 /* x */ INTERSECT ALL SELECT * FROM t2", @@ -524,6 +528,28 @@ INNER JOIN y USING (id)""", pretty=True, ) + self.validate( + """with x as ( + SELECT * + /* +NOTE: LEFT JOIN because blah blah blah + */ + FROM a +) +select * from x""", + """WITH x AS ( + SELECT + * + /* +NOTE: LEFT JOIN because blah blah blah + */ + FROM a +) +SELECT + * +FROM x""", + pretty=True, + ) def test_types(self): self.validate("INT 1", "CAST(1 AS INT)") -- cgit v1.2.3