summaryrefslogtreecommitdiffstats
path: root/tests/dialects/test_dialect.py
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-18 05:35:50 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-18 05:35:50 +0000
commit8f1b330983bddb35e2ec61a5667a84318bad88ef (patch)
tree32b74e964839c34014d31ec828fc6521323b9edc /tests/dialects/test_dialect.py
parentAdding upstream version 23.13.7. (diff)
downloadsqlglot-8f1b330983bddb35e2ec61a5667a84318bad88ef.tar.xz
sqlglot-8f1b330983bddb35e2ec61a5667a84318bad88ef.zip
Adding upstream version 23.16.0.upstream/23.16.0
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'tests/dialects/test_dialect.py')
-rw-r--r--tests/dialects/test_dialect.py104
1 files changed, 102 insertions, 2 deletions
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,