diff options
Diffstat (limited to 'tests/dialects/test_dialect.py')
| -rw-r--r-- | tests/dialects/test_dialect.py | 38 |
1 files changed, 28 insertions, 10 deletions
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py index 9888a5d..aaeb7b0 100644 --- a/tests/dialects/test_dialect.py +++ b/tests/dialects/test_dialect.py @@ -155,6 +155,7 @@ class TestDialect(Validator): "clickhouse": "CAST(a AS String)", "drill": "CAST(a AS VARCHAR)", "duckdb": "CAST(a AS TEXT)", + "materialize": "CAST(a AS TEXT)", "mysql": "CAST(a AS CHAR)", "hive": "CAST(a AS STRING)", "oracle": "CAST(a AS CLOB)", @@ -175,6 +176,7 @@ class TestDialect(Validator): "clickhouse": "CAST(a AS BINARY(4))", "drill": "CAST(a AS VARBINARY(4))", "duckdb": "CAST(a AS BLOB(4))", + "materialize": "CAST(a AS BYTEA(4))", "mysql": "CAST(a AS BINARY(4))", "hive": "CAST(a AS BINARY(4))", "oracle": "CAST(a AS BLOB(4))", @@ -193,6 +195,7 @@ class TestDialect(Validator): "bigquery": "CAST(a AS BYTES)", "clickhouse": "CAST(a AS String)", "duckdb": "CAST(a AS BLOB(4))", + "materialize": "CAST(a AS BYTEA(4))", "mysql": "CAST(a AS VARBINARY(4))", "hive": "CAST(a AS BINARY(4))", "oracle": "CAST(a AS BLOB(4))", @@ -236,6 +239,7 @@ class TestDialect(Validator): "bigquery": "CAST(a AS STRING)", "drill": "CAST(a AS VARCHAR)", "duckdb": "CAST(a AS TEXT)", + "materialize": "CAST(a AS TEXT)", "mysql": "CAST(a AS CHAR)", "hive": "CAST(a AS STRING)", "oracle": "CAST(a AS CLOB)", @@ -255,6 +259,7 @@ class TestDialect(Validator): "bigquery": "CAST(a AS STRING)", "drill": "CAST(a AS VARCHAR)", "duckdb": "CAST(a AS TEXT)", + "materialize": "CAST(a AS VARCHAR)", "mysql": "CAST(a AS CHAR)", "hive": "CAST(a AS STRING)", "oracle": "CAST(a AS VARCHAR2)", @@ -274,6 +279,7 @@ class TestDialect(Validator): "bigquery": "CAST(a AS STRING)", "drill": "CAST(a AS VARCHAR(3))", "duckdb": "CAST(a AS TEXT(3))", + "materialize": "CAST(a AS VARCHAR(3))", "mysql": "CAST(a AS CHAR(3))", "hive": "CAST(a AS VARCHAR(3))", "oracle": "CAST(a AS VARCHAR2(3))", @@ -293,6 +299,7 @@ class TestDialect(Validator): "bigquery": "CAST(a AS INT64)", "drill": "CAST(a AS INTEGER)", "duckdb": "CAST(a AS SMALLINT)", + "materialize": "CAST(a AS SMALLINT)", "mysql": "CAST(a AS SIGNED)", "hive": "CAST(a AS SMALLINT)", "oracle": "CAST(a AS NUMBER)", @@ -328,6 +335,7 @@ class TestDialect(Validator): "clickhouse": "CAST(a AS Float64)", "drill": "CAST(a AS DOUBLE)", "duckdb": "CAST(a AS DOUBLE)", + "materialize": "CAST(a AS DOUBLE PRECISION)", "mysql": "CAST(a AS DOUBLE)", "hive": "CAST(a AS DOUBLE)", "oracle": "CAST(a AS DOUBLE PRECISION)", @@ -599,6 +607,7 @@ class TestDialect(Validator): "drill": "TO_TIMESTAMP(x, 'yy')", "duckdb": "STRPTIME(x, '%y')", "hive": "CAST(FROM_UNIXTIME(UNIX_TIMESTAMP(x, 'yy')) AS TIMESTAMP)", + "materialize": "TO_TIMESTAMP(x, 'YY')", "presto": "DATE_PARSE(x, '%y')", "oracle": "TO_TIMESTAMP(x, 'YY')", "postgres": "TO_TIMESTAMP(x, 'YY')", @@ -655,6 +664,7 @@ class TestDialect(Validator): "drill": "TO_CHAR(x, 'yyyy-MM-dd')", "duckdb": "STRFTIME(x, '%Y-%m-%d')", "hive": "DATE_FORMAT(x, 'yyyy-MM-dd')", + "materialize": "TO_CHAR(x, 'YYYY-MM-DD')", "oracle": "TO_CHAR(x, 'YYYY-MM-DD')", "postgres": "TO_CHAR(x, 'YYYY-MM-DD')", "presto": "DATE_FORMAT(x, '%Y-%m-%d')", @@ -698,6 +708,7 @@ class TestDialect(Validator): "bigquery": "CAST(x AS DATE)", "duckdb": "CAST(x AS DATE)", "hive": "TO_DATE(x)", + "materialize": "CAST(x AS DATE)", "postgres": "CAST(x AS DATE)", "presto": "CAST(CAST(x AS TIMESTAMP) AS DATE)", "snowflake": "TO_DATE(x)", @@ -730,6 +741,7 @@ class TestDialect(Validator): "duckdb": "TO_TIMESTAMP(x)", "hive": "FROM_UNIXTIME(x)", "oracle": "TO_DATE('1970-01-01', 'YYYY-MM-DD') + (x / 86400)", + "materialize": "TO_TIMESTAMP(x)", "postgres": "TO_TIMESTAMP(x)", "presto": "FROM_UNIXTIME(x)", "starrocks": "FROM_UNIXTIME(x)", @@ -790,6 +802,7 @@ class TestDialect(Validator): "drill": "DATE_ADD(x, INTERVAL 1 DAY)", "duckdb": "x + INTERVAL 1 DAY", "hive": "DATE_ADD(x, 1)", + "materialize": "x + INTERVAL '1 DAY'", "mysql": "DATE_ADD(x, INTERVAL 1 DAY)", "postgres": "x + INTERVAL '1 DAY'", "presto": "DATE_ADD('DAY', 1, x)", @@ -826,6 +839,7 @@ class TestDialect(Validator): "duckdb": "DATE_TRUNC('DAY', x)", "mysql": "DATE(x)", "presto": "DATE_TRUNC('DAY', x)", + "materialize": "DATE_TRUNC('DAY', x)", "postgres": "DATE_TRUNC('DAY', x)", "snowflake": "DATE_TRUNC('DAY', x)", "starrocks": "DATE_TRUNC('DAY', x)", @@ -838,6 +852,7 @@ class TestDialect(Validator): read={ "bigquery": "TIMESTAMP_TRUNC(x, day)", "duckdb": "DATE_TRUNC('day', x)", + "materialize": "DATE_TRUNC('day', x)", "presto": "DATE_TRUNC('day', x)", "postgres": "DATE_TRUNC('day', x)", "snowflake": "DATE_TRUNC('day', x)", @@ -899,6 +914,7 @@ class TestDialect(Validator): }, write={ "bigquery": "DATE_TRUNC(x, YEAR)", + "materialize": "DATE_TRUNC('YEAR', x)", "mysql": "STR_TO_DATE(CONCAT(YEAR(x), ' 1 1'), '%Y %c %e')", "postgres": "DATE_TRUNC('YEAR', x)", "snowflake": "DATE_TRUNC('YEAR', x)", @@ -911,6 +927,7 @@ class TestDialect(Validator): "TIMESTAMP_TRUNC(x, YEAR)", read={ "bigquery": "TIMESTAMP_TRUNC(x, year)", + "materialize": "DATE_TRUNC('YEAR', x)", "postgres": "DATE_TRUNC(year, x)", "spark": "DATE_TRUNC('year', x)", "snowflake": "DATE_TRUNC(year, x)", @@ -1024,6 +1041,7 @@ class TestDialect(Validator): write={ "": "TIMESTAMP_TRUNC(x, DAY, 'UTC')", "duckdb": "DATE_TRUNC('DAY', x)", + "materialize": "DATE_TRUNC('DAY', x, 'UTC')", "presto": "DATE_TRUNC('DAY', x)", "postgres": "DATE_TRUNC('DAY', x, 'UTC')", "snowflake": "DATE_TRUNC('DAY', x)", @@ -1485,21 +1503,21 @@ class TestDialect(Validator): "snowflake": "x ILIKE '%y'", }, write={ - "bigquery": "LOWER(x) LIKE '%y'", + "bigquery": "LOWER(x) LIKE LOWER('%y')", "clickhouse": "x ILIKE '%y'", "drill": "x `ILIKE` '%y'", "duckdb": "x ILIKE '%y'", - "hive": "LOWER(x) LIKE '%y'", - "mysql": "LOWER(x) LIKE '%y'", - "oracle": "LOWER(x) LIKE '%y'", + "hive": "LOWER(x) LIKE LOWER('%y')", + "mysql": "LOWER(x) LIKE LOWER('%y')", + "oracle": "LOWER(x) LIKE LOWER('%y')", "postgres": "x ILIKE '%y'", - "presto": "LOWER(x) LIKE '%y'", + "presto": "LOWER(x) LIKE LOWER('%y')", "snowflake": "x ILIKE '%y'", "spark": "x ILIKE '%y'", - "sqlite": "LOWER(x) LIKE '%y'", - "starrocks": "LOWER(x) LIKE '%y'", - "trino": "LOWER(x) LIKE '%y'", - "doris": "LOWER(x) LIKE '%y'", + "sqlite": "LOWER(x) LIKE LOWER('%y')", + "starrocks": "LOWER(x) LIKE LOWER('%y')", + "trino": "LOWER(x) LIKE LOWER('%y')", + "doris": "LOWER(x) LIKE LOWER('%y')", }, ) self.validate_all( @@ -2530,7 +2548,7 @@ FROM subquery2""", def test_reserved_keywords(self): order = exp.select("*").from_("order") - for dialect in ("presto", "redshift"): + for dialect in ("duckdb", "presto", "redshift"): dialect = Dialect.get_or_raise(dialect) self.assertEqual( order.sql(dialect=dialect), |