summaryrefslogtreecommitdiffstats
path: root/tests/dialects/test_snowflake.py
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2022-09-30 05:07:13 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2022-09-30 05:07:13 +0000
commit276f5723c8ab7e0b2938693db546dbd30be3f01a (patch)
treee6294c63de34a03e373245ec4cb1efbca1edfe61 /tests/dialects/test_snowflake.py
parentAdding upstream version 6.2.1. (diff)
downloadsqlglot-276f5723c8ab7e0b2938693db546dbd30be3f01a.tar.xz
sqlglot-276f5723c8ab7e0b2938693db546dbd30be3f01a.zip
Adding upstream version 6.2.6.upstream/6.2.6
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'tests/dialects/test_snowflake.py')
-rw-r--r--tests/dialects/test_snowflake.py73
1 files changed, 73 insertions, 0 deletions
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 165f8e2..b7e39a7 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -143,6 +143,31 @@ class TestSnowflake(Validator):
"snowflake": r"SELECT 'a \' \\ \\t \\x21 z $ '",
},
)
+ self.validate_identity("SELECT REGEXP_LIKE(a, b, c)")
+ self.validate_all(
+ "SELECT RLIKE(a, b)",
+ write={
+ "snowflake": "SELECT REGEXP_LIKE(a, b)",
+ },
+ )
+ self.validate_all(
+ "SELECT a FROM test SAMPLE BLOCK (0.5) SEED (42)",
+ write={
+ "snowflake": "SELECT a FROM test TABLESAMPLE BLOCK (0.5) SEED (42)",
+ },
+ )
+ self.validate_all(
+ "SELECT a FROM test pivot",
+ write={
+ "snowflake": "SELECT a FROM test AS pivot",
+ },
+ )
+ self.validate_all(
+ "SELECT a FROM test unpivot",
+ write={
+ "snowflake": "SELECT a FROM test AS unpivot",
+ },
+ )
def test_null_treatment(self):
self.validate_all(
@@ -220,3 +245,51 @@ class TestSnowflake(Validator):
"snowflake": "SELECT EXTRACT(month FROM CAST(a AS DATETIME))",
},
)
+
+ def test_semi_structured_types(self):
+ self.validate_identity("SELECT CAST(a AS VARIANT)")
+ self.validate_all(
+ "SELECT a::VARIANT",
+ write={
+ "snowflake": "SELECT CAST(a AS VARIANT)",
+ "tsql": "SELECT CAST(a AS SQL_VARIANT)",
+ },
+ )
+ self.validate_identity("SELECT CAST(a AS ARRAY)")
+ self.validate_all(
+ "ARRAY_CONSTRUCT(0, 1, 2)",
+ write={
+ "snowflake": "[0, 1, 2]",
+ "bigquery": "[0, 1, 2]",
+ "duckdb": "LIST_VALUE(0, 1, 2)",
+ "presto": "ARRAY[0, 1, 2]",
+ "spark": "ARRAY(0, 1, 2)",
+ },
+ )
+ self.validate_all(
+ "SELECT a::OBJECT",
+ write={
+ "snowflake": "SELECT CAST(a AS OBJECT)",
+ },
+ )
+
+ def test_ddl(self):
+ self.validate_identity(
+ "CREATE TABLE a (x DATE, y BIGINT) WITH (PARTITION BY (x), integration='q', auto_refresh=TRUE, file_format=(type = parquet))"
+ )
+ self.validate_identity("CREATE MATERIALIZED VIEW a COMMENT='...' AS SELECT 1 FROM x")
+
+ def test_user_defined_functions(self):
+ self.validate_all(
+ "CREATE FUNCTION a(x DATE, y BIGINT) RETURNS ARRAY LANGUAGE JAVASCRIPT AS $$ SELECT 1 $$",
+ write={
+ "snowflake": "CREATE FUNCTION a(x DATE, y BIGINT) RETURNS ARRAY LANGUAGE JAVASCRIPT AS ' SELECT 1 '",
+ },
+ )
+ self.validate_all(
+ "CREATE FUNCTION a() RETURNS TABLE (b INT) AS 'SELECT 1'",
+ write={
+ "snowflake": "CREATE FUNCTION a() RETURNS TABLE (b INT) AS 'SELECT 1'",
+ "bigquery": "CREATE TABLE FUNCTION a() RETURNS TABLE <b INT64> AS SELECT 1",
+ },
+ )