diff options
Diffstat (limited to 'tests/dialects/test_snowflake.py')
| -rw-r--r-- | tests/dialects/test_snowflake.py | 36 |
1 files changed, 34 insertions, 2 deletions
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py index 3e0d600..40413c8 100644 --- a/tests/dialects/test_snowflake.py +++ b/tests/dialects/test_snowflake.py @@ -107,10 +107,10 @@ WHERE "SELECT * FROM quarterly_sales PIVOT(SUM(amount) FOR quarter IN (SELECT DISTINCT quarter FROM ad_campaign_types_by_quarter WHERE television = TRUE ORDER BY quarter)) ORDER BY empid" ) self.validate_identity( - "SELECT * FROM quarterly_sales PIVOT(SUM(amount) FOR IN (ANY ORDER BY quarter)) ORDER BY empid" + "SELECT * FROM quarterly_sales PIVOT(SUM(amount) FOR quarter IN (ANY ORDER BY quarter)) ORDER BY empid" ) self.validate_identity( - "SELECT * FROM quarterly_sales PIVOT(SUM(amount) FOR IN (ANY)) ORDER BY empid" + "SELECT * FROM quarterly_sales PIVOT(SUM(amount) FOR quarter IN (ANY)) ORDER BY empid" ) self.validate_identity( "MERGE INTO my_db AS ids USING (SELECT new_id FROM my_model WHERE NOT col IS NULL) AS new_ids ON ids.type = new_ids.type AND ids.source = new_ids.source WHEN NOT MATCHED THEN INSERT VALUES (new_ids.new_id)" @@ -904,6 +904,28 @@ WHERE }, ) + self.validate_identity( + "SELECT UUID_STRING(), UUID_STRING('fe971b24-9572-4005-b22f-351e9c09274d', 'foo')" + ) + + self.validate_all( + "UUID_STRING('fe971b24-9572-4005-b22f-351e9c09274d', 'foo')", + read={ + "snowflake": "UUID_STRING('fe971b24-9572-4005-b22f-351e9c09274d', 'foo')", + }, + write={ + "hive": "UUID()", + "spark2": "UUID()", + "spark": "UUID()", + "databricks": "UUID()", + "duckdb": "UUID()", + "presto": "UUID()", + "trino": "UUID()", + "postgres": "GEN_RANDOM_UUID()", + "bigquery": "GENERATE_UUID()", + }, + ) + def test_null_treatment(self): self.validate_all( r"SELECT FIRST_VALUE(TABLE1.COLUMN1) OVER (PARTITION BY RANDOM_COLUMN1, RANDOM_COLUMN2 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MY_ALIAS FROM TABLE1", @@ -2115,6 +2137,16 @@ SINGLE = TRUE""", self.validate_identity("SELECT t.$23:a.b", "SELECT GET_PATH(t.$23, 'a.b')") self.validate_identity("SELECT t.$17:a[0].b[0].c", "SELECT GET_PATH(t.$17, 'a[0].b[0].c')") + self.validate_all( + """ + SELECT col:"customer's department" + """, + write={ + "snowflake": """SELECT GET_PATH(col, '["customer\\'s department"]')""", + "postgres": "SELECT JSON_EXTRACT_PATH(col, 'customer''s department')", + }, + ) + def test_alter_set_unset(self): self.validate_identity("ALTER TABLE tbl SET DATA_RETENTION_TIME_IN_DAYS=1") self.validate_identity("ALTER TABLE tbl SET DEFAULT_DDL_COLLATION='test'") |