From 8f1b330983bddb35e2ec61a5667a84318bad88ef Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 18 May 2024 07:35:50 +0200 Subject: Adding upstream version 23.16.0. Signed-off-by: Daniel Baumann --- tests/dialects/test_dialect.py | 104 ++++++++++++++++++++++++++++++++++++++++- 1 file changed, 102 insertions(+), 2 deletions(-) (limited to 'tests/dialects/test_dialect.py') diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py index dda0eb2..77306dc 100644 --- a/tests/dialects/test_dialect.py +++ b/tests/dialects/test_dialect.py @@ -1019,6 +1019,19 @@ class TestDialect(Validator): }, ) + self.validate_all( + "TIMESTAMP_TRUNC(x, DAY, 'UTC')", + write={ + "": "TIMESTAMP_TRUNC(x, DAY, 'UTC')", + "duckdb": "DATE_TRUNC('DAY', x)", + "presto": "DATE_TRUNC('DAY', x)", + "postgres": "DATE_TRUNC('DAY', x, 'UTC')", + "snowflake": "DATE_TRUNC('DAY', x)", + "databricks": "DATE_TRUNC('DAY', x)", + "clickhouse": "DATE_TRUNC('DAY', x, 'UTC')", + }, + ) + for unit in ("DAY", "MONTH", "YEAR"): self.validate_all( f"{unit}(x)", @@ -1681,6 +1694,26 @@ class TestDialect(Validator): "tsql": "CAST(a AS FLOAT) / b", }, ) + self.validate_all( + "MOD(8 - 1 + 7, 7)", + write={ + "": "(8 - 1 + 7) % 7", + "hive": "(8 - 1 + 7) % 7", + "presto": "(8 - 1 + 7) % 7", + "snowflake": "(8 - 1 + 7) % 7", + "bigquery": "MOD(8 - 1 + 7, 7)", + }, + ) + self.validate_all( + "MOD(a, b + 1)", + write={ + "": "a % (b + 1)", + "hive": "a % (b + 1)", + "presto": "a % (b + 1)", + "snowflake": "a % (b + 1)", + "bigquery": "MOD(a, b + 1)", + }, + ) def test_typeddiv(self): typed_div = exp.Div(this=exp.column("a"), expression=exp.column("b"), typed=True) @@ -2186,6 +2219,8 @@ SELECT ) def test_cast_to_user_defined_type(self): + self.validate_identity("CAST(x AS some_udt(1234))") + self.validate_all( "CAST(x AS some_udt)", write={ @@ -2214,6 +2249,18 @@ SELECT "tsql": "SELECT * FROM (SELECT *, COUNT(*) OVER () AS _w FROM t) AS _t WHERE _w > 1", }, ) + self.validate_all( + 'SELECT "user id", some_id, 1 as other_id, 2 as "2 nd id" FROM t QUALIFY COUNT(*) OVER () > 1', + write={ + "duckdb": 'SELECT "user id", some_id, 1 AS other_id, 2 AS "2 nd id" FROM t QUALIFY COUNT(*) OVER () > 1', + "snowflake": 'SELECT "user id", some_id, 1 AS other_id, 2 AS "2 nd id" FROM t QUALIFY COUNT(*) OVER () > 1', + "clickhouse": 'SELECT "user id", some_id, other_id, "2 nd id" FROM (SELECT "user id", some_id, 1 AS other_id, 2 AS "2 nd id", COUNT(*) OVER () AS _w FROM t) AS _t WHERE _w > 1', + "mysql": "SELECT `user id`, some_id, other_id, `2 nd id` FROM (SELECT `user id`, some_id, 1 AS other_id, 2 AS `2 nd id`, COUNT(*) OVER () AS _w FROM t) AS _t WHERE _w > 1", + "oracle": 'SELECT "user id", some_id, other_id, "2 nd id" FROM (SELECT "user id", some_id, 1 AS other_id, 2 AS "2 nd id", COUNT(*) OVER () AS _w FROM t) _t WHERE _w > 1', + "postgres": 'SELECT "user id", some_id, other_id, "2 nd id" FROM (SELECT "user id", some_id, 1 AS other_id, 2 AS "2 nd id", COUNT(*) OVER () AS _w FROM t) AS _t WHERE _w > 1', + "tsql": "SELECT [user id], some_id, other_id, [2 nd id] FROM (SELECT [user id] AS [user id], some_id AS some_id, 1 AS other_id, 2 AS [2 nd id], COUNT(*) OVER () AS _w FROM t) AS _t WHERE _w > 1", + }, + ) def test_nested_ctes(self): self.validate_all( @@ -2249,7 +2296,7 @@ SELECT "WITH t1(x) AS (SELECT 1) SELECT * FROM (WITH t2(y) AS (SELECT 2) SELECT y FROM t2) AS subq", write={ "duckdb": "WITH t1(x) AS (SELECT 1) SELECT * FROM (WITH t2(y) AS (SELECT 2) SELECT y FROM t2) AS subq", - "tsql": "WITH t2(y) AS (SELECT 2), t1(x) AS (SELECT 1) SELECT * FROM (SELECT y AS y FROM t2) AS subq", + "tsql": "WITH t1(x) AS (SELECT 1), t2(y) AS (SELECT 2) SELECT * FROM (SELECT y AS y FROM t2) AS subq", }, ) self.validate_all( @@ -2273,6 +2320,59 @@ FROM c""", "hive": "WITH a1 AS (SELECT 1), a2 AS (SELECT 2), b AS (SELECT * FROM a1, a2), c AS (SELECT * FROM b) SELECT * FROM c", }, ) + self.validate_all( + """ +WITH subquery1 AS ( + WITH tmp AS ( + SELECT + * + FROM table0 + ) + SELECT + * + FROM tmp +), subquery2 AS ( + WITH tmp2 AS ( + SELECT + * + FROM table1 + WHERE + a IN subquery1 + ) + SELECT + * + FROM tmp2 +) +SELECT + * +FROM subquery2 +""", + write={ + "hive": """WITH tmp AS ( + SELECT + * + FROM table0 +), subquery1 AS ( + SELECT + * + FROM tmp +), tmp2 AS ( + SELECT + * + FROM table1 + WHERE + a IN subquery1 +), subquery2 AS ( + SELECT + * + FROM tmp2 +) +SELECT + * +FROM subquery2""", + }, + pretty=True, + ) def test_unsupported_null_ordering(self): # We'll transpile a portable query from the following dialects to MySQL / T-SQL, which @@ -2372,7 +2472,7 @@ FROM c""", "hive": UnsupportedError, "mysql": UnsupportedError, "oracle": UnsupportedError, - "postgres": "(ARRAY_LENGTH(arr, 1) = 0 OR ARRAY_LENGTH(ARRAY(SELECT x FROM UNNEST(arr) AS _t(x) WHERE pred), 1) <> 0)", + "postgres": "(ARRAY_LENGTH(arr, 1) = 0 OR ARRAY_LENGTH(ARRAY(SELECT x FROM UNNEST(arr) AS _t0(x) WHERE pred), 1) <> 0)", "presto": "ANY_MATCH(arr, x -> pred)", "redshift": UnsupportedError, "snowflake": UnsupportedError, -- cgit v1.2.3