summaryrefslogtreecommitdiffstats
path: root/tests/dialects/test_snowflake.py
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2023-06-02 23:59:11 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2023-06-02 23:59:11 +0000
commitcaea5267cb8e1fea3702adbdf6f68fd37d13b3b7 (patch)
treef06f1da1ab3b6906beca1c3c7222d28ff00766ac /tests/dialects/test_snowflake.py
parentAdding upstream version 12.2.0. (diff)
downloadsqlglot-caea5267cb8e1fea3702adbdf6f68fd37d13b3b7.tar.xz
sqlglot-caea5267cb8e1fea3702adbdf6f68fd37d13b3b7.zip
Adding upstream version 15.0.0.upstream/15.0.0
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.py44
1 files changed, 37 insertions, 7 deletions
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 57ee235..941f2aa 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -6,6 +6,8 @@ class TestSnowflake(Validator):
dialect = "snowflake"
def test_snowflake(self):
+ self.validate_identity("INITCAP('iqamqinterestedqinqthisqtopic', 'q')")
+ self.validate_identity("CAST(x AS GEOMETRY)")
self.validate_identity("OBJECT_CONSTRUCT(*)")
self.validate_identity("SELECT TO_DATE('2019-02-28') + INTERVAL '1 day, 1 year'")
self.validate_identity("SELECT CAST('2021-01-01' AS DATE) + INTERVAL '1 DAY'")
@@ -25,7 +27,21 @@ class TestSnowflake(Validator):
'COPY INTO NEW_TABLE ("foo", "bar") FROM (SELECT $1, $2, $3, $4 FROM @%old_table)'
)
self.validate_identity("COMMENT IF EXISTS ON TABLE foo IS 'bar'")
+ self.validate_identity("SELECT CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', col)")
+ 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(
+ "OBJECT_CONSTRUCT(a, b, c, d)",
+ read={
+ "": "STRUCT(a as b, c as d)",
+ },
+ write={
+ "duckdb": "{'a': b, 'c': d}",
+ "snowflake": "OBJECT_CONSTRUCT(a, b, c, d)",
+ },
+ )
self.validate_all(
"SELECT i, p, o FROM qt QUALIFY ROW_NUMBER() OVER (PARTITION BY p ORDER BY o) = 1",
write={
@@ -284,7 +300,7 @@ 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 NULLS LAST, lname NULLS LAST",
+ "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",
@@ -458,7 +474,8 @@ class TestSnowflake(Validator):
)
def test_timestamps(self):
- self.validate_identity("SELECT EXTRACT(month FROM a)")
+ self.validate_identity("SELECT CAST('12:00:00' AS TIME)")
+ self.validate_identity("SELECT DATE_PART(month, a)")
self.validate_all(
"SELECT CAST(a AS TIMESTAMP)",
@@ -487,19 +504,19 @@ class TestSnowflake(Validator):
self.validate_all(
"SELECT EXTRACT('month', a)",
write={
- "snowflake": "SELECT EXTRACT('month' FROM a)",
+ "snowflake": "SELECT DATE_PART('month', a)",
},
)
self.validate_all(
"SELECT DATE_PART('month', a)",
write={
- "snowflake": "SELECT EXTRACT('month' FROM a)",
+ "snowflake": "SELECT DATE_PART('month', a)",
},
)
self.validate_all(
"SELECT DATE_PART(month, a::DATETIME)",
write={
- "snowflake": "SELECT EXTRACT(month FROM CAST(a AS DATETIME))",
+ "snowflake": "SELECT DATE_PART(month, CAST(a AS DATETIME))",
},
)
self.validate_all(
@@ -554,10 +571,23 @@ class TestSnowflake(Validator):
)
def test_ddl(self):
+ self.validate_identity("CREATE TABLE geospatial_table (id INT, g GEOGRAPHY)")
+ self.validate_identity("CREATE MATERIALIZED VIEW a COMMENT='...' AS SELECT 1 FROM x")
+ self.validate_identity("CREATE DATABASE mytestdb_clone CLONE mytestdb")
+ self.validate_identity("CREATE SCHEMA mytestschema_clone CLONE testschema")
+ self.validate_identity("CREATE TABLE orders_clone CLONE orders")
+ self.validate_identity(
+ "CREATE TABLE orders_clone_restore CLONE orders AT (TIMESTAMP => TO_TIMESTAMP_TZ('04/05/2013 01:02:03', 'mm/dd/yyyy hh24:mi:ss'))"
+ )
+ self.validate_identity(
+ "CREATE TABLE orders_clone_restore CLONE orders BEFORE (STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726')"
+ )
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")
+ self.validate_identity(
+ "CREATE SCHEMA mytestschema_clone_restore CLONE testschema BEFORE (TIMESTAMP => TO_TIMESTAMP(40 * 365 * 86400))"
+ )
self.validate_all(
"CREATE OR REPLACE TRANSIENT TABLE a (id INT)",
@@ -758,7 +788,7 @@ FROM persons AS p, LATERAL FLATTEN(input => p.c, path => 'contact') AS f, LATERA
def test_values(self):
self.validate_all(
- 'SELECT c0, c1 FROM (VALUES (1, 2), (3, 4)) AS "t0"(c0, c1)',
+ 'SELECT "c0", "c1" FROM (VALUES (1, 2), (3, 4)) AS "t0"("c0", "c1")',
read={
"spark": "SELECT `c0`, `c1` FROM (VALUES (1, 2), (3, 4)) AS `t0`(`c0`, `c1`)",
},