diff options
Diffstat (limited to 'tests/dialects/test_snowflake.py')
| -rw-r--r-- | tests/dialects/test_snowflake.py | 88 |
1 files changed, 82 insertions, 6 deletions
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 @@ -63,6 +63,28 @@ class TestSnowflake(Validator): }, ) 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={ "snowflake": "CREATE OR REPLACE TEMPORARY TABLE x (y DECIMAL AUTOINCREMENT START 0 INCREMENT 1)", @@ -280,12 +302,6 @@ class TestSnowflake(Validator): }, ) 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={ "snowflake": "SELECT a FROM test AS pivot", @@ -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", |