From ebec59cc5cb6c6856705bf82ced7fe8d9f75b0d0 Mon Sep 17 00:00:00 2001
From: Daniel Baumann <daniel.baumann@progress-linux.org>
Date: Tue, 7 Mar 2023 19:09:31 +0100
Subject: Merging upstream version 11.3.0.

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
---
 tests/dialects/test_snowflake.py | 68 +++++++++++++++++++++++++++++++++++++++-
 1 file changed, 67 insertions(+), 1 deletion(-)

(limited to 'tests/dialects/test_snowflake.py')

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,10 +10,58 @@ 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={
@@ -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(
@@ -170,6 +222,20 @@ class TestSnowflake(Validator):
                 "snowflake": "SELECT ARRAY_AGG(DISTINCT a)",
             },
         )
+        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={
-- 
cgit v1.2.3