From 243d9a15edd910136313a7a9fa7d7f24b494e169 Mon Sep 17 00:00:00 2001
From: Daniel Baumann <daniel.baumann@progress-linux.org>
Date: Wed, 8 Mar 2023 08:22:12 +0100
Subject: Adding upstream version 11.3.3.

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

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

diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 3358227..c28c58d 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -62,6 +62,28 @@ class TestSnowflake(Validator):
                 "duckdb": "CASE WHEN bar = 0 THEN 0 ELSE CAST(foo AS DOUBLE) / bar END",
             },
         )
+        self.validate_all(
+            "ZEROIFNULL(foo)",
+            write={
+                "snowflake": "IFF(foo IS NULL, 0, foo)",
+                "sqlite": "CASE WHEN foo IS NULL THEN 0 ELSE foo END",
+                "presto": "IF(foo IS NULL, 0, foo)",
+                "spark": "IF(foo IS NULL, 0, foo)",
+                "hive": "IF(foo IS NULL, 0, foo)",
+                "duckdb": "CASE WHEN foo IS NULL THEN 0 ELSE foo END",
+            },
+        )
+        self.validate_all(
+            "NULLIFZERO(foo)",
+            write={
+                "snowflake": "IFF(foo = 0, NULL, foo)",
+                "sqlite": "CASE WHEN foo = 0 THEN NULL ELSE foo END",
+                "presto": "IF(foo = 0, NULL, foo)",
+                "spark": "IF(foo = 0, NULL, foo)",
+                "hive": "IF(foo = 0, NULL, foo)",
+                "duckdb": "CASE WHEN foo = 0 THEN NULL ELSE foo END",
+            },
+        )
         self.validate_all(
             "CREATE OR REPLACE TEMPORARY TABLE x (y NUMBER IDENTITY(0, 1))",
             write={
@@ -279,12 +301,6 @@ class TestSnowflake(Validator):
                 "snowflake": "SELECT REGEXP_LIKE(a, b)",
             },
         )
-        self.validate_all(
-            "SELECT a FROM test SAMPLE BLOCK (0.5) SEED (42)",
-            write={
-                "snowflake": "SELECT a FROM test TABLESAMPLE BLOCK (0.5) SEED (42)",
-            },
-        )
         self.validate_all(
             "SELECT a FROM test pivot",
             write={
@@ -356,6 +372,51 @@ class TestSnowflake(Validator):
             },
         )
 
+    def test_sample(self):
+        self.validate_identity("SELECT * FROM testtable TABLESAMPLE BERNOULLI (20.3)")
+        self.validate_identity("SELECT * FROM testtable TABLESAMPLE (100)")
+        self.validate_identity(
+            "SELECT i, j FROM table1 AS t1 INNER JOIN table2 AS t2 TABLESAMPLE (50) WHERE t2.j = t1.i"
+        )
+        self.validate_identity(
+            "SELECT * FROM (SELECT * FROM t1 JOIN t2 ON t1.a = t2.c) TABLESAMPLE (1)"
+        )
+        self.validate_identity("SELECT * FROM testtable TABLESAMPLE SYSTEM (3) SEED (82)")
+        self.validate_identity("SELECT * FROM testtable TABLESAMPLE (10 ROWS)")
+
+        self.validate_all(
+            "SELECT * FROM testtable SAMPLE (10)",
+            write={"snowflake": "SELECT * FROM testtable TABLESAMPLE (10)"},
+        )
+        self.validate_all(
+            "SELECT * FROM testtable SAMPLE ROW (0)",
+            write={"snowflake": "SELECT * FROM testtable TABLESAMPLE ROW (0)"},
+        )
+        self.validate_all(
+            "SELECT a FROM test SAMPLE BLOCK (0.5) SEED (42)",
+            write={
+                "snowflake": "SELECT a FROM test TABLESAMPLE BLOCK (0.5) SEED (42)",
+            },
+        )
+        self.validate_all(
+            """
+            SELECT i, j
+                FROM
+                     table1 AS t1 SAMPLE (25)     -- 25% of rows in table1
+                         INNER JOIN
+                     table2 AS t2 SAMPLE (50)     -- 50% of rows in table2
+                WHERE t2.j = t1.i""",
+            write={
+                "snowflake": "SELECT i, j FROM table1 AS t1 TABLESAMPLE (25) /* 25% of rows in table1 */ INNER JOIN table2 AS t2 TABLESAMPLE (50) /* 50% of rows in table2 */ WHERE t2.j = t1.i",
+            },
+        )
+        self.validate_all(
+            "SELECT * FROM testtable SAMPLE BLOCK (0.012) REPEATABLE (99992)",
+            write={
+                "snowflake": "SELECT * FROM testtable TABLESAMPLE BLOCK (0.012) SEED (99992)",
+            },
+        )
+
     def test_timestamps(self):
         self.validate_identity("SELECT EXTRACT(month FROM a)")
 
@@ -415,6 +476,13 @@ class TestSnowflake(Validator):
                 "presto": "SELECT TO_UNIXTIME(CAST(foo AS TIMESTAMP)) * 1000 AS ddate FROM table_name",
             },
         )
+        self.validate_all(
+            "DATEADD(DAY, 5, CAST('2008-12-25' AS DATE))",
+            write={
+                "bigquery": "DATE_ADD(CAST('2008-12-25' AS DATE), INTERVAL 5 DAY)",
+                "snowflake": "DATEADD(DAY, 5, CAST('2008-12-25' AS DATE))",
+            },
+        )
 
     def test_semi_structured_types(self):
         self.validate_identity("SELECT CAST(a AS VARIANT)")
@@ -655,6 +723,14 @@ FROM persons AS p, LATERAL FLATTEN(input => p.c, path => 'contact') AS f, LATERA
             },
         )
 
+        self.validate_all(
+            """SELECT $1 AS "_1" FROM VALUES ('a'), ('b')""",
+            write={
+                "snowflake": """SELECT $1 AS "_1" FROM (VALUES ('a'), ('b'))""",
+                "spark": """SELECT @1 AS `_1` FROM VALUES ('a'), ('b')""",
+            },
+        )
+
     def test_describe_table(self):
         self.validate_all(
             "DESCRIBE TABLE db.table",
-- 
cgit v1.2.3