diff options
Diffstat (limited to 'tests/dialects/test_duckdb.py')
| -rw-r--r-- | tests/dialects/test_duckdb.py | 107 |
1 files changed, 78 insertions, 29 deletions
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py index 9105a49..03dea93 100644 --- a/tests/dialects/test_duckdb.py +++ b/tests/dialects/test_duckdb.py @@ -272,6 +272,14 @@ class TestDuckDB(Validator): "SELECT * FROM x LEFT JOIN UNNEST(y)", "SELECT * FROM x LEFT JOIN UNNEST(y) ON TRUE" ) self.validate_identity( + "SELECT JSON_EXTRACT_STRING(c, '$.k1') = 'v1'", + "SELECT (c ->> '$.k1') = 'v1'", + ) + self.validate_identity( + "SELECT JSON_EXTRACT(c, '$.k1') = 'v1'", + "SELECT (c -> '$.k1') = 'v1'", + ) + self.validate_identity( """SELECT '{"foo": [1, 2, 3]}' -> 'foo' -> 0""", """SELECT '{"foo": [1, 2, 3]}' -> '$.foo' -> '$[0]'""", ) @@ -324,6 +332,8 @@ class TestDuckDB(Validator): self.validate_identity( "SELECT * FROM (PIVOT Cities ON Year USING SUM(Population) GROUP BY Country) AS pivot_alias" ) + self.validate_identity("DATE_SUB('YEAR', col, '2020-01-01')").assert_is(exp.Anonymous) + self.validate_identity("DATESUB('YEAR', col, '2020-01-01')").assert_is(exp.Anonymous) self.validate_all("0b1010", write={"": "0 AS b1010"}) self.validate_all("0x1010", write={"": "0 AS x1010"}) @@ -724,6 +734,36 @@ class TestDuckDB(Validator): """SELECT i FROM GENERATE_SERIES(0, 12) AS _(i) ORDER BY i ASC""", ) + self.validate_identity( + "COPY lineitem FROM 'lineitem.ndjson' WITH (FORMAT JSON, DELIMITER ',', AUTO_DETECT TRUE, COMPRESSION SNAPPY, CODEC ZSTD, FORCE_NOT_NULL(col1, col2))" + ) + self.validate_identity( + "COPY (SELECT 42 AS a, 'hello' AS b) TO 'query.json' WITH (FORMAT JSON, ARRAY TRUE)" + ) + self.validate_identity("COPY lineitem (l_orderkey) TO 'orderkey.tbl' WITH (DELIMITER '|')") + + self.validate_all( + "VARIANCE(a)", + write={ + "duckdb": "VARIANCE(a)", + "clickhouse": "varSamp(a)", + }, + ) + self.validate_all( + "STDDEV(a)", + write={ + "duckdb": "STDDEV(a)", + "clickhouse": "stddevSamp(a)", + }, + ) + self.validate_all( + "DATE_TRUNC('DAY', x)", + write={ + "duckdb": "DATE_TRUNC('DAY', x)", + "clickhouse": "DATE_TRUNC('DAY', x)", + }, + ) + def test_array_index(self): with self.assertLogs(helper_logger) as cm: self.validate_all( @@ -793,7 +833,7 @@ class TestDuckDB(Validator): write={"duckdb": "SELECT (90 * INTERVAL '1' DAY)"}, ) self.validate_all( - "SELECT ((DATE_TRUNC('DAY', CAST(CAST(DATE_TRUNC('DAY', CURRENT_TIMESTAMP) AS DATE) AS TIMESTAMP) + INTERVAL (0 - (DAYOFWEEK(CAST(CAST(DATE_TRUNC('DAY', CURRENT_TIMESTAMP) AS DATE) AS TIMESTAMP)) % 7) - 1 + 7 % 7) DAY) + (7 * INTERVAL (-5) DAY))) AS t1", + "SELECT ((DATE_TRUNC('DAY', CAST(CAST(DATE_TRUNC('DAY', CURRENT_TIMESTAMP) AS DATE) AS TIMESTAMP) + INTERVAL (0 - ((DAYOFWEEK(CAST(CAST(DATE_TRUNC('DAY', CURRENT_TIMESTAMP) AS DATE) AS TIMESTAMP)) % 7) - 1 + 7) % 7) DAY) + (7 * INTERVAL (-5) DAY))) AS t1", read={ "presto": "SELECT ((DATE_ADD('week', -5, DATE_TRUNC('DAY', DATE_ADD('day', (0 - MOD((DAY_OF_WEEK(CAST(CAST(DATE_TRUNC('DAY', NOW()) AS DATE) AS TIMESTAMP)) % 7) - 1 + 7, 7)), CAST(CAST(DATE_TRUNC('DAY', NOW()) AS DATE) AS TIMESTAMP)))))) AS t1", }, @@ -817,6 +857,9 @@ class TestDuckDB(Validator): "duckdb": "EPOCH_MS(x)", "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( @@ -882,11 +925,11 @@ class TestDuckDB(Validator): def test_sample(self): self.validate_identity( "SELECT * FROM tbl USING SAMPLE 5", - "SELECT * FROM tbl USING SAMPLE (5 ROWS)", + "SELECT * FROM tbl USING SAMPLE RESERVOIR (5 ROWS)", ) self.validate_identity( "SELECT * FROM tbl USING SAMPLE 10%", - "SELECT * FROM tbl USING SAMPLE (10 PERCENT)", + "SELECT * FROM tbl USING SAMPLE SYSTEM (10 PERCENT)", ) self.validate_identity( "SELECT * FROM tbl USING SAMPLE 10 PERCENT (bernoulli)", @@ -910,14 +953,13 @@ class TestDuckDB(Validator): ) self.validate_all( - "SELECT * FROM example TABLESAMPLE (3 ROWS) REPEATABLE (82)", + "SELECT * FROM example TABLESAMPLE RESERVOIR (3 ROWS) REPEATABLE (82)", read={ "duckdb": "SELECT * FROM example TABLESAMPLE (3) REPEATABLE (82)", "snowflake": "SELECT * FROM example SAMPLE (3 ROWS) SEED (82)", }, write={ - "duckdb": "SELECT * FROM example TABLESAMPLE (3 ROWS) REPEATABLE (82)", - "snowflake": "SELECT * FROM example TABLESAMPLE (3 ROWS) SEED (82)", + "duckdb": "SELECT * FROM example TABLESAMPLE RESERVOIR (3 ROWS) REPEATABLE (82)", }, ) @@ -936,10 +978,6 @@ class TestDuckDB(Validator): self.validate_identity("CAST(x AS DOUBLE)") self.validate_identity("CAST(x AS DECIMAL(15, 4))") self.validate_identity("CAST(x AS STRUCT(number BIGINT))") - self.validate_identity( - "CAST(ROW(1, ROW(1)) AS STRUCT(number BIGINT, row STRUCT(number BIGINT)))" - ) - self.validate_identity("CAST(x AS INT64)", "CAST(x AS BIGINT)") self.validate_identity("CAST(x AS INT32)", "CAST(x AS INT)") self.validate_identity("CAST(x AS INT16)", "CAST(x AS SMALLINT)") @@ -959,6 +997,32 @@ class TestDuckDB(Validator): self.validate_identity("CAST(x AS BINARY)", "CAST(x AS BLOB)") self.validate_identity("CAST(x AS VARBINARY)", "CAST(x AS BLOB)") self.validate_identity("CAST(x AS LOGICAL)", "CAST(x AS BOOLEAN)") + self.validate_identity( + "CAST(ROW(1, ROW(1)) AS STRUCT(number BIGINT, row STRUCT(number BIGINT)))" + ) + self.validate_identity( + "123::CHARACTER VARYING", + "CAST(123 AS TEXT)", + ) + self.validate_identity( + "CAST([[STRUCT_PACK(a := 1)]] AS STRUCT(a BIGINT)[][])", + "CAST([[{'a': 1}]] AS STRUCT(a BIGINT)[][])", + ) + self.validate_identity( + "CAST([STRUCT_PACK(a := 1)] AS STRUCT(a BIGINT)[])", + "CAST([{'a': 1}] AS STRUCT(a BIGINT)[])", + ) + + self.validate_all( + "CAST(x AS DECIMAL(38, 0))", + read={ + "snowflake": "CAST(x AS NUMBER)", + "duckdb": "CAST(x AS DECIMAL(38, 0))", + }, + write={ + "snowflake": "CAST(x AS DECIMAL(38, 0))", + }, + ) self.validate_all( "CAST(x AS NUMERIC)", write={ @@ -984,12 +1048,6 @@ class TestDuckDB(Validator): }, ) self.validate_all( - "123::CHARACTER VARYING", - write={ - "duckdb": "CAST(123 AS TEXT)", - }, - ) - self.validate_all( "cast([[1]] as int[][])", write={ "duckdb": "CAST([[1]] AS INT[][])", @@ -997,7 +1055,10 @@ class TestDuckDB(Validator): }, ) self.validate_all( - "CAST(x AS DATE) + INTERVAL (7 * -1) DAY", read={"spark": "DATE_SUB(x, 7)"} + "CAST(x AS DATE) + INTERVAL (7 * -1) DAY", + read={ + "spark": "DATE_SUB(x, 7)", + }, ) self.validate_all( "TRY_CAST(1 AS DOUBLE)", @@ -1024,18 +1085,6 @@ class TestDuckDB(Validator): "snowflake": "CAST(COL AS ARRAY(BIGINT))", }, ) - self.validate_all( - "CAST([STRUCT_PACK(a := 1)] AS STRUCT(a BIGINT)[])", - write={ - "duckdb": "CAST([{'a': 1}] AS STRUCT(a BIGINT)[])", - }, - ) - self.validate_all( - "CAST([[STRUCT_PACK(a := 1)]] AS STRUCT(a BIGINT)[][])", - write={ - "duckdb": "CAST([[{'a': 1}]] AS STRUCT(a BIGINT)[][])", - }, - ) def test_bool_or(self): self.validate_all( |