diff options
Diffstat (limited to 'tests/dialects/test_snowflake.py')
| -rw-r--r-- | tests/dialects/test_snowflake.py | 103 |
1 files changed, 97 insertions, 6 deletions
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py index 997c27b..4d8168a 100644 --- a/tests/dialects/test_snowflake.py +++ b/tests/dialects/test_snowflake.py @@ -36,6 +36,8 @@ WHERE )""", ) + self.validate_identity("SELECT TO_ARRAY(CAST(x AS ARRAY))") + self.validate_identity("SELECT TO_ARRAY(CAST(['test'] AS VARIANT))") self.validate_identity("SELECT user_id, value FROM table_name sample ($s) SEED (0)") self.validate_identity("SELECT ARRAY_UNIQUE_AGG(x)") self.validate_identity("SELECT OBJECT_CONSTRUCT()") @@ -73,6 +75,18 @@ WHERE 'DESCRIBE TABLE "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF100TCL"."WEB_SITE" type=stage' ) self.validate_identity( + "SELECT * FROM foo at", + "SELECT * FROM foo AS at", + ) + self.validate_identity( + "SELECT * FROM foo before", + "SELECT * FROM foo AS before", + ) + self.validate_identity( + "SELECT * FROM foo at (col)", + "SELECT * FROM foo AS at(col)", + ) + self.validate_identity( "SELECT * FROM unnest(x) with ordinality", "SELECT * FROM TABLE(FLATTEN(INPUT => x)) AS _u(seq, key, path, index, value, this)", ) @@ -115,11 +129,37 @@ WHERE "SELECT TO_TIMESTAMP(x) FROM t", "SELECT CAST(x AS TIMESTAMPNTZ) FROM t", ) + self.validate_identity( + "CAST(x AS BYTEINT)", + "CAST(x AS INT)", + ) + self.validate_identity( + "CAST(x AS CHAR VARYING)", + "CAST(x AS VARCHAR)", + ) + self.validate_identity( + "CAST(x AS CHARACTER VARYING)", + "CAST(x AS VARCHAR)", + ) + self.validate_identity( + "CAST(x AS NCHAR VARYING)", + "CAST(x AS VARCHAR)", + ) - 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( + "SELECT TO_ARRAY(['test'])", + write={ + "snowflake": "SELECT TO_ARRAY(['test'])", + "spark": "SELECT ARRAY('test')", + }, + ) + self.validate_all( + "SELECT TO_ARRAY(['test'])", + write={ + "snowflake": "SELECT TO_ARRAY(['test'])", + "spark": "SELECT ARRAY('test')", + }, + ) self.validate_all( # We need to qualify the columns in this query because "value" would be ambiguous 'WITH t(x, "value") AS (SELECT [1, 2, 3], 1) SELECT IFF(_u.pos = _u_2.pos_2, _u_2."value", NULL) AS "value" FROM t, TABLE(FLATTEN(INPUT => ARRAY_GENERATE_RANGE(0, (GREATEST(ARRAY_SIZE(t.x)) - 1) + 1))) AS _u(seq, key, path, index, pos, this) CROSS JOIN TABLE(FLATTEN(INPUT => t.x)) AS _u_2(seq, key, path, pos_2, "value", this) WHERE _u.pos = _u_2.pos_2 OR (_u.pos > (ARRAY_SIZE(t.x) - 1) AND _u_2.pos_2 = (ARRAY_SIZE(t.x) - 1))', @@ -489,8 +529,8 @@ WHERE self.validate_all( "TO_ARRAY(x)", write={ - "spark": "ARRAY(x)", - "snowflake": "[x]", + "spark": "IF(x IS NULL, NULL, ARRAY(x))", + "snowflake": "TO_ARRAY(x)", }, ) self.validate_all( @@ -626,6 +666,10 @@ WHERE "SELECT * FROM @mystage t (c1)", "SELECT * FROM @mystage AS t(c1)", ) + self.validate_identity( + "SELECT * FROM @foo/bar (PATTERN => 'test', FILE_FORMAT => ds_sandbox.test.my_csv_format) AS bla", + "SELECT * FROM @foo/bar (FILE_FORMAT => ds_sandbox.test.my_csv_format, PATTERN => 'test') AS bla", + ) def test_sample(self): self.validate_identity("SELECT * FROM testtable TABLESAMPLE BERNOULLI (20.3)") @@ -775,6 +819,53 @@ WHERE }, ) + def test_historical_data(self): + self.validate_identity("SELECT * FROM my_table AT (STATEMENT => $query_id_var)") + self.validate_identity("SELECT * FROM my_table AT (OFFSET => -60 * 5)") + self.validate_identity("SELECT * FROM my_table BEFORE (STATEMENT => $query_id_var)") + self.validate_identity("SELECT * FROM my_table BEFORE (OFFSET => -60 * 5)") + self.validate_identity("CREATE SCHEMA restored_schema CLONE my_schema AT (OFFSET => -3600)") + self.validate_identity( + "CREATE TABLE restored_table CLONE my_table AT (TIMESTAMP => CAST('Sat, 09 May 2015 01:01:00 +0300' AS TIMESTAMPTZ))", + ) + self.validate_identity( + "CREATE DATABASE restored_db CLONE my_db BEFORE (STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726')" + ) + self.validate_identity( + "SELECT * FROM my_table AT (TIMESTAMP => TO_TIMESTAMP(1432669154242, 3))" + ) + self.validate_identity( + "SELECT * FROM my_table AT (OFFSET => -60 * 5) AS T WHERE T.flag = 'valid'" + ) + self.validate_identity( + "SELECT * FROM my_table AT (STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726')" + ) + self.validate_identity( + "SELECT * FROM my_table BEFORE (STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726')" + ) + self.validate_identity( + "SELECT * FROM my_table AT (TIMESTAMP => 'Fri, 01 May 2015 16:20:00 -0700'::timestamp)", + "SELECT * FROM my_table AT (TIMESTAMP => CAST('Fri, 01 May 2015 16:20:00 -0700' AS TIMESTAMPNTZ))", + ) + self.validate_identity( + "SELECT * FROM my_table AT(TIMESTAMP => 'Fri, 01 May 2015 16:20:00 -0700'::timestamp_tz)", + "SELECT * FROM my_table AT (TIMESTAMP => CAST('Fri, 01 May 2015 16:20:00 -0700' AS TIMESTAMPTZ))", + ) + self.validate_identity( + "SELECT * FROM my_table BEFORE (TIMESTAMP => 'Fri, 01 May 2015 16:20:00 -0700'::timestamp_tz);", + "SELECT * FROM my_table BEFORE (TIMESTAMP => CAST('Fri, 01 May 2015 16:20:00 -0700' AS TIMESTAMPTZ))", + ) + self.validate_identity( + """ + SELECT oldt.* , newt.* + FROM my_table BEFORE(STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726') AS oldt + FULL OUTER JOIN my_table AT(STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726') AS newt + ON oldt.id = newt.id + WHERE oldt.id IS NULL OR newt.id IS NULL; + """, + "SELECT oldt.*, newt.* FROM my_table BEFORE (STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726') AS oldt FULL OUTER JOIN my_table AT (STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726') AS newt ON oldt.id = newt.id WHERE oldt.id IS NULL OR newt.id IS NULL", + ) + def test_ddl(self): self.validate_identity( """create external table et2( |