summaryrefslogtreecommitdiffstats
path: root/tests/dialects/test_snowflake.py
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2023-03-07 18:09:27 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2023-03-07 18:09:27 +0000
commite4c72fd7c8018d9dfc6043e7304275e2fffac89f (patch)
treefb9c5556844d46d0e59cc31f129d03502f06f571 /tests/dialects/test_snowflake.py
parentAdding upstream version 11.2.3. (diff)
downloadsqlglot-e4c72fd7c8018d9dfc6043e7304275e2fffac89f.tar.xz
sqlglot-e4c72fd7c8018d9dfc6043e7304275e2fffac89f.zip
Adding upstream version 11.3.0.upstream/11.3.0
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'tests/dialects/test_snowflake.py')
-rw-r--r--tests/dialects/test_snowflake.py68
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,