summaryrefslogtreecommitdiffstats
path: root/tests/dialects/test_duckdb.py
diff options
context:
space:
mode:
Diffstat (limited to 'tests/dialects/test_duckdb.py')
-rw-r--r--tests/dialects/test_duckdb.py92
1 files changed, 57 insertions, 35 deletions
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index 15af086..3fb9930 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -8,8 +8,6 @@ class TestDuckDB(Validator):
dialect = "duckdb"
def test_duckdb(self):
- self.validate_identity("x::int[3]", "CAST(x AS INT[3])")
-
with self.assertRaises(ParseError):
parse_one("1 //", read="duckdb")
@@ -35,31 +33,6 @@ class TestDuckDB(Validator):
},
)
self.validate_all(
- "SELECT CAST('2020-01-01 12:05:01' AS TIMESTAMP)",
- read={
- "duckdb": "SELECT CAST('2020-01-01 12:05:01' AS TIMESTAMP)",
- "snowflake": "SELECT CAST('2020-01-01 12:05:01' AS TIMESTAMPNTZ)",
- },
- )
- self.validate_all(
- "SELECT CAST('2020-01-01' AS DATE) + INTERVAL (day_offset) DAY FROM t",
- read={
- "duckdb": "SELECT CAST('2020-01-01' AS DATE) + INTERVAL (day_offset) DAY FROM t",
- "mysql": "SELECT DATE '2020-01-01' + INTERVAL day_offset DAY FROM t",
- },
- )
- self.validate_all(
- "SELECT CAST('09:05:03' AS TIME) + INTERVAL 2 HOUR",
- read={
- "bigquery": "SELECT TIME_ADD(CAST('09:05:03' AS TIME), INTERVAL 2 HOUR)",
- "snowflake": "SELECT TIMEADD(HOUR, 2, TO_TIME('09:05:03'))",
- },
- write={
- "duckdb": "SELECT CAST('09:05:03' AS TIME) + INTERVAL '2' HOUR",
- "snowflake": "SELECT CAST('09:05:03' AS TIME) + INTERVAL '2 HOUR'",
- },
- )
- self.validate_all(
'STRUCT_PACK("a b" := 1)',
write={
"duckdb": "{'a b': 1}",
@@ -112,7 +85,9 @@ class TestDuckDB(Validator):
self.validate_all(
"CREATE TEMPORARY FUNCTION f1(a, b) AS (a + b)",
- read={"bigquery": "CREATE TEMP FUNCTION f1(a INT64, b INT64) AS (a + b)"},
+ read={
+ "bigquery": "CREATE TEMP FUNCTION f1(a INT64, b INT64) AS (a + b)",
+ },
)
self.validate_identity("SELECT 1 WHERE x > $1")
self.validate_identity("SELECT 1 WHERE x > $name")
@@ -128,13 +103,17 @@ class TestDuckDB(Validator):
)
self.validate_all(
- "{'a': 1, 'b': '2'}", write={"presto": "CAST(ROW(1, '2') AS ROW(a INTEGER, b VARCHAR))"}
+ "{'a': 1, 'b': '2'}",
+ write={
+ "presto": "CAST(ROW(1, '2') AS ROW(a INTEGER, b VARCHAR))",
+ },
)
self.validate_all(
"struct_pack(a := 1, b := 2)",
- write={"presto": "CAST(ROW(1, 2) AS ROW(a INTEGER, b INTEGER))"},
+ write={
+ "presto": "CAST(ROW(1, 2) AS ROW(a INTEGER, b INTEGER))",
+ },
)
-
self.validate_all(
"struct_pack(a := 1, b := x)",
write={
@@ -818,6 +797,9 @@ class TestDuckDB(Validator):
)
self.validate_identity("SELECT LENGTH(foo)")
+ self.validate_identity("SELECT ARRAY[1, 2, 3]", "SELECT [1, 2, 3]")
+
+ self.validate_identity("SELECT * FROM (DESCRIBE t)")
def test_array_index(self):
with self.assertLogs(helper_logger) as cm:
@@ -909,12 +891,12 @@ class TestDuckDB(Validator):
"EPOCH_MS(x)",
write={
"bigquery": "TIMESTAMP_MILLIS(x)",
+ "clickhouse": "fromUnixTimestamp64Milli(CAST(x AS Nullable(Int64)))",
"duckdb": "EPOCH_MS(x)",
+ "mysql": "FROM_UNIXTIME(x / POWER(10, 3))",
+ "postgres": "TO_TIMESTAMP(CAST(x AS DOUBLE PRECISION) / 10 ^ 3)",
"presto": "FROM_UNIXTIME(CAST(x AS DOUBLE) / POW(10, 3))",
"spark": "TIMESTAMP_MILLIS(x)",
- "clickhouse": "fromUnixTimestamp64Milli(CAST(x AS Int64))",
- "postgres": "TO_TIMESTAMP(CAST(x AS DOUBLE PRECISION) / 10 ^ 3)",
- "mysql": "FROM_UNIXTIME(x / POWER(10, 3))",
},
)
self.validate_all(
@@ -958,7 +940,7 @@ class TestDuckDB(Validator):
self.validate_all(
"STRPTIME(x, '%-m/%-d/%y %-I:%M %p')",
write={
- "bigquery": "PARSE_TIMESTAMP('%-m/%-d/%y %-I:%M %p', x)",
+ "bigquery": "PARSE_TIMESTAMP('%-m/%e/%y %-I:%M %p', x)",
"duckdb": "STRPTIME(x, '%-m/%-d/%y %-I:%M %p')",
"presto": "DATE_PARSE(x, '%c/%e/%y %l:%i %p')",
"hive": "CAST(FROM_UNIXTIME(UNIX_TIMESTAMP(x, 'M/d/yy h:mm a')) AS TIMESTAMP)",
@@ -1023,6 +1005,7 @@ class TestDuckDB(Validator):
self.validate_identity("ARRAY((SELECT id FROM t))")
def test_cast(self):
+ self.validate_identity("x::int[3]", "CAST(x AS INT[3])")
self.validate_identity("CAST(x AS REAL)")
self.validate_identity("CAST(x AS UINTEGER)")
self.validate_identity("CAST(x AS UBIGINT)")
@@ -1076,6 +1059,39 @@ class TestDuckDB(Validator):
"STRUCT_PACK(a := 'b')::STRUCT(a TEXT)",
"CAST(ROW('b') AS STRUCT(a TEXT))",
)
+
+ self.validate_all(
+ "CAST(x AS TIME)",
+ read={
+ "duckdb": "CAST(x AS TIME)",
+ "presto": "CAST(x AS TIME(6))",
+ },
+ )
+ self.validate_all(
+ "SELECT CAST('2020-01-01 12:05:01' AS TIMESTAMP)",
+ read={
+ "duckdb": "SELECT CAST('2020-01-01 12:05:01' AS TIMESTAMP)",
+ "snowflake": "SELECT CAST('2020-01-01 12:05:01' AS TIMESTAMPNTZ)",
+ },
+ )
+ self.validate_all(
+ "SELECT CAST('2020-01-01' AS DATE) + INTERVAL (day_offset) DAY FROM t",
+ read={
+ "duckdb": "SELECT CAST('2020-01-01' AS DATE) + INTERVAL (day_offset) DAY FROM t",
+ "mysql": "SELECT DATE '2020-01-01' + INTERVAL day_offset DAY FROM t",
+ },
+ )
+ self.validate_all(
+ "SELECT CAST('09:05:03' AS TIME) + INTERVAL 2 HOUR",
+ read={
+ "bigquery": "SELECT TIME_ADD(CAST('09:05:03' AS TIME), INTERVAL 2 HOUR)",
+ "snowflake": "SELECT TIMEADD(HOUR, 2, TO_TIME('09:05:03'))",
+ },
+ write={
+ "duckdb": "SELECT CAST('09:05:03' AS TIME) + INTERVAL '2' HOUR",
+ "snowflake": "SELECT CAST('09:05:03' AS TIME) + INTERVAL '2 HOUR'",
+ },
+ )
self.validate_all(
"CAST(x AS VARCHAR(5))",
write={
@@ -1156,6 +1172,12 @@ class TestDuckDB(Validator):
},
)
+ self.validate_identity("SELECT x::INT[3][3]", "SELECT CAST(x AS INT[3][3])")
+ self.validate_identity(
+ """SELECT ARRAY[[[1]]]::INT[1][1][1]""",
+ """SELECT CAST([[[1]]] AS INT[1][1][1])""",
+ )
+
def test_encode_decode(self):
self.validate_all(
"ENCODE(x)",
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-10 02:40:05 +0000