From 3d48060515ba25b4c49d975a520ee0682327d1b7 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Fri, 16 Feb 2024 06:45:52 +0100 Subject: Merging upstream version 21.1.1. Signed-off-by: Daniel Baumann --- tests/dialects/test_snowflake.py | 18 +++++++++++++++--- 1 file changed, 15 insertions(+), 3 deletions(-) (limited to 'tests/dialects/test_snowflake.py') 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 @@ -84,6 +84,10 @@ WHERE self.validate_identity( "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) -- cgit v1.2.3