diff options
Diffstat (limited to 'tests/dialects/test_snowflake.py')
| -rw-r--r-- | tests/dialects/test_snowflake.py | 68 |
1 files changed, 67 insertions, 1 deletions
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py index a934c78..3358227 100644 --- a/tests/dialects/test_snowflake.py +++ b/tests/dialects/test_snowflake.py @@ -10,11 +10,59 @@ class TestSnowflake(Validator): self.validate_identity("SELECT REGEXP_LIKE(a, b, c)") self.validate_identity("PUT file:///dir/tmp.csv @%table") self.validate_identity("CREATE TABLE foo (bar FLOAT AUTOINCREMENT START 0 INCREMENT 1)") + self.validate_identity("ALTER TABLE IF EXISTS foo SET TAG a = 'a', b = 'b', c = 'c'") + self.validate_identity("ALTER TABLE foo UNSET TAG a, b, c") self.validate_identity( 'COPY INTO NEW_TABLE ("foo", "bar") FROM (SELECT $1, $2, $3, $4 FROM @%old_table)' ) + self.validate_identity("COMMENT IF EXISTS ON TABLE foo IS 'bar'") self.validate_all( + "1 / 2", + read={ + "bigquery": "1 / 2", + "clickhouse": "1 / 2", + "databricks": "1 / 2", + "hive": "1 / 2", + "mysql": "1 / 2", + "oracle": "1 / 2", + "snowflake": "1 / 2", + "spark": "1 / 2", + "starrocks": "1 / 2", + }, + write={ + "bigquery": "1 / 2", + "clickhouse": "1 / 2", + "databricks": "1 / 2", + "hive": "1 / 2", + "mysql": "1 / 2", + "oracle": "1 / 2", + "snowflake": "1 / 2", + "spark": "1 / 2", + "starrocks": "1 / 2", + "drill": "CAST(1 AS DOUBLE) / 2", + "duckdb": "CAST(1 AS DOUBLE) / 2", + "postgres": "CAST(1 AS DOUBLE PRECISION) / 2", + "presto": "CAST(1 AS DOUBLE) / 2", + "redshift": "CAST(1 AS DOUBLE PRECISION) / 2", + "sqlite": "CAST(1 AS REAL) / 2", + "teradata": "CAST(1 AS DOUBLE) / 2", + "trino": "CAST(1 AS DOUBLE) / 2", + "tsql": "CAST(1 AS DOUBLE) / 2", + }, + ) + self.validate_all( + "DIV0(foo, bar)", + write={ + "snowflake": "IFF(bar = 0, 0, foo / bar)", + "sqlite": "CASE WHEN bar = 0 THEN 0 ELSE CAST(foo AS REAL) / bar END", + "presto": "IF(bar = 0, 0, CAST(foo AS DOUBLE) / bar)", + "spark": "IF(bar = 0, 0, foo / bar)", + "hive": "IF(bar = 0, 0, foo / bar)", + "duckdb": "CASE WHEN bar = 0 THEN 0 ELSE CAST(foo AS DOUBLE) / bar END", + }, + ) + self.validate_all( "CREATE OR REPLACE TEMPORARY TABLE x (y NUMBER IDENTITY(0, 1))", write={ "snowflake": "CREATE OR REPLACE TEMPORARY TABLE x (y DECIMAL AUTOINCREMENT START 0 INCREMENT 1)", @@ -63,9 +111,13 @@ class TestSnowflake(Validator): }, ) self.validate_all( - "SELECT * EXCLUDE a, b RENAME (c AS d, E as F) FROM xxx", + "SELECT * EXCLUDE (a, b) RENAME (c AS d, E AS F) FROM xxx", + read={ + "duckdb": "SELECT * EXCLUDE (a, b) REPLACE (c AS d, E AS F) FROM xxx", + }, write={ "snowflake": "SELECT * EXCLUDE (a, b) RENAME (c AS d, E AS F) FROM xxx", + "duckdb": "SELECT * EXCLUDE (a, b) REPLACE (c AS d, E AS F) FROM xxx", }, ) self.validate_all( @@ -171,6 +223,20 @@ class TestSnowflake(Validator): }, ) self.validate_all( + "ARRAY_TO_STRING(x, '')", + write={ + "spark": "ARRAY_JOIN(x, '')", + "snowflake": "ARRAY_TO_STRING(x, '')", + }, + ) + self.validate_all( + "TO_ARRAY(x)", + write={ + "spark": "ARRAY(x)", + "snowflake": "[x]", + }, + ) + self.validate_all( "SELECT * FROM a INTERSECT ALL SELECT * FROM b", write={ "snowflake": UnsupportedError, |