diff options
Diffstat (limited to 'tests/dialects/test_snowflake.py')
| -rw-r--r-- | tests/dialects/test_snowflake.py | 18 |
1 files changed, 15 insertions, 3 deletions
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py index 7a821f6..321dd73 100644 --- a/tests/dialects/test_snowflake.py +++ b/tests/dialects/test_snowflake.py @@ -85,6 +85,10 @@ WHERE "SELECT a FROM test PIVOT(SUM(x) FOR y IN ('z', 'q')) AS x TABLESAMPLE (0.1)" ) self.validate_identity( + "value:values::string", + "CAST(GET_PATH(value, 'values') AS TEXT)", + ) + self.validate_identity( """SELECT GET_PATH(PARSE_JSON('{"y": [{"z": 1}]}'), 'y[0]:z')""", """SELECT GET_PATH(PARSE_JSON('{"y": [{"z": 1}]}'), 'y[0].z')""", ) @@ -462,7 +466,7 @@ WHERE "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", + "sqlite": "IIF(bar = 0, 0, CAST(foo AS REAL) / bar)", "presto": "IF(bar = 0, 0, CAST(foo AS DOUBLE) / bar)", "spark": "IF(bar = 0, 0, foo / bar)", "hive": "IF(bar = 0, 0, foo / bar)", @@ -473,7 +477,7 @@ WHERE "ZEROIFNULL(foo)", write={ "snowflake": "IFF(foo IS NULL, 0, foo)", - "sqlite": "CASE WHEN foo IS NULL THEN 0 ELSE foo END", + "sqlite": "IIF(foo IS NULL, 0, foo)", "presto": "IF(foo IS NULL, 0, foo)", "spark": "IF(foo IS NULL, 0, foo)", "hive": "IF(foo IS NULL, 0, foo)", @@ -484,7 +488,7 @@ WHERE "NULLIFZERO(foo)", write={ "snowflake": "IFF(foo = 0, NULL, foo)", - "sqlite": "CASE WHEN foo = 0 THEN NULL ELSE foo END", + "sqlite": "IIF(foo = 0, NULL, foo)", "presto": "IF(foo = 0, NULL, foo)", "spark": "IF(foo = 0, NULL, foo)", "hive": "IF(foo = 0, NULL, foo)", @@ -1513,6 +1517,10 @@ MATCH_RECOGNIZE ( self.validate_identity("SHOW COLUMNS IN VIEW") self.validate_identity("SHOW COLUMNS LIKE '_foo%' IN VIEW dt_test") + self.validate_identity("SHOW USERS") + self.validate_identity("SHOW TERSE USERS") + self.validate_identity("SHOW USERS LIKE '_foo%' STARTS WITH 'bar' LIMIT 5 FROM 'baz'") + ast = parse_one("SHOW COLUMNS LIKE '_testing%' IN dt_test", read="snowflake") table = ast.find(exp.Table) literal = ast.find(exp.Literal) @@ -1536,6 +1544,10 @@ MATCH_RECOGNIZE ( table = ast.find(exp.Table) self.assertEqual(table.sql(dialect="snowflake"), "db1.schema1") + users_exp = self.validate_identity("SHOW USERS") + self.assertTrue(isinstance(users_exp, exp.Show)) + self.assertEqual(users_exp.this, "USERS") + def test_swap(self): ast = parse_one("ALTER TABLE a SWAP WITH b", read="snowflake") assert isinstance(ast, exp.AlterTable) |