diff options
Diffstat (limited to 'tests/dialects/test_snowflake.py')
| -rw-r--r-- | tests/dialects/test_snowflake.py | 39 |
1 files changed, 30 insertions, 9 deletions
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py index 30a1f03..a217394 100644 --- a/tests/dialects/test_snowflake.py +++ b/tests/dialects/test_snowflake.py @@ -78,12 +78,33 @@ class TestSnowflake(Validator): r"SELECT $$a ' \ \t \x21 z $ $$", r"SELECT 'a \' \\ \\t \\x21 z $ '", ) + self.validate_identity( + "SELECT {'test': 'best'}::VARIANT", + "SELECT CAST(OBJECT_CONSTRUCT('test', 'best') AS VARIANT)", + ) self.validate_all("CAST(x AS BYTEINT)", write={"snowflake": "CAST(x AS INT)"}) self.validate_all("CAST(x AS CHAR VARYING)", write={"snowflake": "CAST(x AS VARCHAR)"}) self.validate_all("CAST(x AS CHARACTER VARYING)", write={"snowflake": "CAST(x AS VARCHAR)"}) self.validate_all("CAST(x AS NCHAR VARYING)", write={"snowflake": "CAST(x AS VARCHAR)"}) self.validate_all( + "ARRAY_GENERATE_RANGE(0, 3)", + write={ + "bigquery": "GENERATE_ARRAY(0, 3 - 1)", + "postgres": "GENERATE_SERIES(0, 3 - 1)", + "presto": "SEQUENCE(0, 3 - 1)", + "snowflake": "ARRAY_GENERATE_RANGE(0, (3 - 1) + 1)", + }, + ) + self.validate_all( + "ARRAY_GENERATE_RANGE(0, 3 + 1)", + read={ + "bigquery": "GENERATE_ARRAY(0, 3)", + "postgres": "GENERATE_SERIES(0, 3)", + "presto": "SEQUENCE(0, 3)", + }, + ) + self.validate_all( "SELECT DATE_PART('year', TIMESTAMP '2020-01-01')", write={ "hive": "SELECT EXTRACT(year FROM CAST('2020-01-01' AS TIMESTAMP))", @@ -258,13 +279,13 @@ class TestSnowflake(Validator): self.validate_all( "SELECT * EXCLUDE a, b FROM xxx", write={ - "snowflake": "SELECT * EXCLUDE (a, b) FROM xxx", + "snowflake": "SELECT * EXCLUDE (a), b FROM xxx", }, ) self.validate_all( "SELECT * RENAME a AS b, c AS d FROM xxx", write={ - "snowflake": "SELECT * RENAME (a AS b, c AS d) FROM xxx", + "snowflake": "SELECT * RENAME (a AS b), c AS d FROM xxx", }, ) self.validate_all( @@ -364,12 +385,12 @@ class TestSnowflake(Validator): self.validate_all( "SELECT fname, lname, age FROM person ORDER BY age DESC NULLS FIRST, fname ASC NULLS LAST, lname", write={ - "duckdb": "SELECT fname, lname, age FROM person ORDER BY age DESC NULLS FIRST, fname, lname", - "postgres": "SELECT fname, lname, age FROM person ORDER BY age DESC, fname, lname", - "presto": "SELECT fname, lname, age FROM person ORDER BY age DESC NULLS FIRST, fname, lname", - "hive": "SELECT fname, lname, age FROM person ORDER BY age DESC NULLS FIRST, fname NULLS LAST, lname NULLS LAST", - "spark": "SELECT fname, lname, age FROM person ORDER BY age DESC NULLS FIRST, fname NULLS LAST, lname NULLS LAST", - "snowflake": "SELECT fname, lname, age FROM person ORDER BY age DESC, fname, lname", + "duckdb": "SELECT fname, lname, age FROM person ORDER BY age DESC NULLS FIRST, fname ASC, lname", + "postgres": "SELECT fname, lname, age FROM person ORDER BY age DESC, fname ASC, lname", + "presto": "SELECT fname, lname, age FROM person ORDER BY age DESC NULLS FIRST, fname ASC, lname", + "hive": "SELECT fname, lname, age FROM person ORDER BY age DESC NULLS FIRST, fname ASC NULLS LAST, lname NULLS LAST", + "spark": "SELECT fname, lname, age FROM person ORDER BY age DESC NULLS FIRST, fname ASC NULLS LAST, lname NULLS LAST", + "snowflake": "SELECT fname, lname, age FROM person ORDER BY age DESC, fname ASC, lname", }, ) self.validate_all( @@ -867,7 +888,7 @@ FROM persons AS p, LATERAL FLATTEN(input => p.c, path => 'contact') AS f, LATERA """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')""", + "spark": """SELECT ${1} AS `_1` FROM VALUES ('a'), ('b')""", }, ) |