summaryrefslogtreecommitdiffstats
path: root/tests/dialects
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2023-06-29 13:02:29 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2023-06-29 13:02:29 +0000
commit9b39dac84e82bf473216939e50b8836170f01d23 (patch)
tree9b405bc86ef7e2ea28cddc6b787ed70355cf7fce /tests/dialects
parentReleasing debian version 16.4.2-1. (diff)
downloadsqlglot-9b39dac84e82bf473216939e50b8836170f01d23.tar.xz
sqlglot-9b39dac84e82bf473216939e50b8836170f01d23.zip
Merging upstream version 16.7.3.
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'tests/dialects')
-rw-r--r--tests/dialects/test_bigquery.py192
-rw-r--r--tests/dialects/test_dialect.py3
-rw-r--r--tests/dialects/test_mysql.py11
-rw-r--r--tests/dialects/test_presto.py20
-rw-r--r--tests/dialects/test_redshift.py4
-rw-r--r--tests/dialects/test_snowflake.py7
-rw-r--r--tests/dialects/test_spark.py4
7 files changed, 172 insertions, 69 deletions
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index e05fca0..e95ff3e 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -1,3 +1,5 @@
+from unittest import mock
+
from sqlglot import ErrorLevel, ParseError, UnsupportedError, transpile
from tests.dialects.test_dialect import Validator
@@ -6,6 +8,35 @@ class TestBigQuery(Validator):
dialect = "bigquery"
def test_bigquery(self):
+ with self.assertRaises(ValueError):
+ transpile("'\\'", read="bigquery")
+
+ # Reference: https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#set_operators
+ with self.assertRaises(UnsupportedError):
+ transpile(
+ "SELECT * FROM a INTERSECT ALL SELECT * FROM b",
+ write="bigquery",
+ unsupported_level=ErrorLevel.RAISE,
+ )
+
+ with self.assertRaises(UnsupportedError):
+ transpile(
+ "SELECT * FROM a EXCEPT ALL SELECT * FROM b",
+ write="bigquery",
+ unsupported_level=ErrorLevel.RAISE,
+ )
+
+ with self.assertRaises(ParseError):
+ transpile("SELECT * FROM UNNEST(x) AS x(y)", read="bigquery")
+
+ self.validate_identity("SELECT `project-id`.udfs.func(call.dir)")
+ self.validate_identity("SELECT CAST(CURRENT_DATE AS STRING FORMAT 'DAY') AS current_day")
+ self.validate_identity("SAFE_CAST(encrypted_value AS STRING FORMAT 'BASE64')")
+ self.validate_identity("CAST(encrypted_value AS STRING FORMAT 'BASE64')")
+ self.validate_identity("STRING_AGG(a)")
+ self.validate_identity("STRING_AGG(a, ' & ')")
+ self.validate_identity("STRING_AGG(DISTINCT a, ' & ')")
+ self.validate_identity("STRING_AGG(a, ' & ' ORDER BY LENGTH(a))")
self.validate_identity("DATE(2016, 12, 25)")
self.validate_identity("DATE(CAST('2016-12-25 23:59:59' AS DATETIME))")
self.validate_identity("SELECT foo IN UNNEST(bar) AS bla")
@@ -21,16 +52,8 @@ class TestBigQuery(Validator):
self.validate_identity("x <> ''")
self.validate_identity("DATE_TRUNC(col, WEEK(MONDAY))")
self.validate_identity("SELECT b'abc'")
- self.validate_identity("""SELECT * FROM UNNEST(ARRAY<STRUCT<x INT64>>[1, 2])""")
+ self.validate_identity("""SELECT * FROM UNNEST(ARRAY<STRUCT<x INT64>>[])""")
self.validate_identity("SELECT AS STRUCT 1 AS a, 2 AS b")
- self.validate_all(
- "SELECT AS STRUCT ARRAY(SELECT AS STRUCT b FROM x) AS y FROM z",
- write={
- "": "SELECT AS STRUCT ARRAY(SELECT AS STRUCT b FROM x) AS y FROM z",
- "bigquery": "SELECT AS STRUCT ARRAY(SELECT AS STRUCT b FROM x) AS y FROM z",
- "duckdb": "SELECT {'y': ARRAY(SELECT {'b': b} FROM x)} FROM z",
- },
- )
self.validate_identity("SELECT DISTINCT AS STRUCT 1 AS a, 2 AS b")
self.validate_identity("SELECT AS VALUE STRUCT(1 AS a, 2 AS b)")
self.validate_identity("SELECT STRUCT<ARRAY<STRING>>(['2023-01-17'])")
@@ -38,6 +61,13 @@ class TestBigQuery(Validator):
self.validate_identity("SELECT * FROM q UNPIVOT(values FOR quarter IN (b, c))")
self.validate_identity("""CREATE TABLE x (a STRUCT<values ARRAY<INT64>>)""")
self.validate_identity("""CREATE TABLE x (a STRUCT<b STRING OPTIONS (description='b')>)""")
+ self.validate_identity("CAST(x AS TIMESTAMP)")
+ self.validate_identity("REGEXP_EXTRACT(`foo`, 'bar: (.+?)', 1, 1)")
+ self.validate_identity("BEGIN A B C D E F")
+ self.validate_identity("BEGIN TRANSACTION")
+ self.validate_identity("COMMIT TRANSACTION")
+ self.validate_identity("ROLLBACK TRANSACTION")
+ self.validate_identity("CAST(x AS BIGNUMERIC)")
self.validate_identity(
"DATE(CAST('2016-12-25 05:30:00+07' AS DATETIME), 'America/Los_Angeles')"
)
@@ -50,8 +80,55 @@ class TestBigQuery(Validator):
self.validate_identity(
"CREATE TABLE IF NOT EXISTS foo AS SELECT * FROM bla EXCEPT DISTINCT (SELECT * FROM bar) LIMIT 0"
)
+ self.validate_identity(
+ "SELECT ROW() OVER (y ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM x WINDOW y AS (PARTITION BY CATEGORY)"
+ )
+ self.validate_identity(
+ "SELECT item, purchases, LAST_VALUE(item) OVER (item_window ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popular FROM Produce WINDOW item_window AS (ORDER BY purchases)"
+ )
+ self.validate_identity(
+ "SELECT LAST_VALUE(a IGNORE NULLS) OVER y FROM x WINDOW y AS (PARTITION BY CATEGORY)",
+ )
self.validate_all("SELECT SPLIT(foo)", write={"bigquery": "SELECT SPLIT(foo, ',')"})
+ self.validate_all("SELECT 1 AS hash", write={"bigquery": "SELECT 1 AS `hash`"})
+ self.validate_all("SELECT 1 AS at", write={"bigquery": "SELECT 1 AS `at`"})
+ self.validate_all('x <> ""', write={"bigquery": "x <> ''"})
+ self.validate_all('x <> """"""', write={"bigquery": "x <> ''"})
+ self.validate_all("x <> ''''''", write={"bigquery": "x <> ''"})
+ self.validate_all("CAST(x AS DATETIME)", read={"": "x::timestamp"})
+ self.validate_all("LEAST(x, y)", read={"sqlite": "MIN(x, y)"})
+ self.validate_all("CAST(x AS CHAR)", write={"bigquery": "CAST(x AS STRING)"})
+ self.validate_all("CAST(x AS NCHAR)", write={"bigquery": "CAST(x AS STRING)"})
+ self.validate_all("CAST(x AS NVARCHAR)", write={"bigquery": "CAST(x AS STRING)"})
+ self.validate_all("CAST(x AS TIMESTAMPTZ)", write={"bigquery": "CAST(x AS TIMESTAMP)"})
+ self.validate_all("CAST(x AS RECORD)", write={"bigquery": "CAST(x AS STRUCT)"})
+ self.validate_all(
+ "SELECT CAST(TIMESTAMP '2008-12-25 00:00:00+00:00' AS STRING FORMAT 'YYYY-MM-DD HH24:MI:SS TZH:TZM') AS date_time_to_string",
+ write={
+ "bigquery": "SELECT CAST(CAST('2008-12-25 00:00:00+00:00' AS TIMESTAMP) AS STRING FORMAT 'YYYY-MM-DD HH24:MI:SS TZH:TZM') AS date_time_to_string",
+ },
+ )
+ self.validate_all(
+ "SELECT CAST(TIMESTAMP '2008-12-25 00:00:00+00:00' AS STRING FORMAT 'YYYY-MM-DD HH24:MI:SS TZH:TZM' AT TIME ZONE 'Asia/Kolkata') AS date_time_to_string",
+ write={
+ "bigquery": "SELECT CAST(CAST('2008-12-25 00:00:00+00:00' AS TIMESTAMP) AS STRING FORMAT 'YYYY-MM-DD HH24:MI:SS TZH:TZM' AT TIME ZONE 'Asia/Kolkata') AS date_time_to_string",
+ },
+ )
+ self.validate_all(
+ "WITH cte AS (SELECT [1, 2, 3] AS arr) SELECT col FROM cte CROSS JOIN UNNEST(arr) AS col",
+ read={
+ "spark": "WITH cte AS (SELECT ARRAY(1, 2, 3) AS arr) SELECT EXPLODE(arr) FROM cte"
+ },
+ )
+ self.validate_all(
+ "SELECT AS STRUCT ARRAY(SELECT AS STRUCT b FROM x) AS y FROM z",
+ write={
+ "": "SELECT AS STRUCT ARRAY(SELECT AS STRUCT b FROM x) AS y FROM z",
+ "bigquery": "SELECT AS STRUCT ARRAY(SELECT AS STRUCT b FROM x) AS y FROM z",
+ "duckdb": "SELECT {'y': ARRAY(SELECT {'b': b} FROM x)} FROM z",
+ },
+ )
self.validate_all(
"cast(x as date format 'MM/DD/YYYY')",
write={
@@ -64,10 +141,6 @@ class TestBigQuery(Validator):
"bigquery": "PARSE_TIMESTAMP('%Y.%m.%d %I:%M:%S%z', x)",
},
)
- self.validate_all("SELECT 1 AS hash", write={"bigquery": "SELECT 1 AS `hash`"})
- self.validate_all('x <> ""', write={"bigquery": "x <> ''"})
- self.validate_all('x <> """"""', write={"bigquery": "x <> ''"})
- self.validate_all("x <> ''''''", write={"bigquery": "x <> ''"})
self.validate_all(
"CREATE TEMP TABLE foo AS SELECT 1",
write={"bigquery": "CREATE TEMPORARY TABLE foo AS SELECT 1"},
@@ -82,14 +155,6 @@ class TestBigQuery(Validator):
"SELECT * FROM `my-project.my-dataset.my-table`",
write={"bigquery": "SELECT * FROM `my-project`.`my-dataset`.`my-table`"},
)
- self.validate_all("CAST(x AS DATETIME)", read={"": "x::timestamp"})
- self.validate_identity("CAST(x AS TIMESTAMP)")
- self.validate_all("LEAST(x, y)", read={"sqlite": "MIN(x, y)"})
- self.validate_all("CAST(x AS CHAR)", write={"bigquery": "CAST(x AS STRING)"})
- self.validate_all("CAST(x AS NCHAR)", write={"bigquery": "CAST(x AS STRING)"})
- self.validate_all("CAST(x AS NVARCHAR)", write={"bigquery": "CAST(x AS STRING)"})
- self.validate_all("CAST(x AS TIMESTAMPTZ)", write={"bigquery": "CAST(x AS TIMESTAMP)"})
- self.validate_all("CAST(x AS RECORD)", write={"bigquery": "CAST(x AS STRUCT)"})
self.validate_all(
"SELECT ARRAY(SELECT AS STRUCT 1 a, 2 b)",
write={
@@ -121,9 +186,6 @@ class TestBigQuery(Validator):
"spark": "'x\\''",
},
)
- with self.assertRaises(ValueError):
- transpile("'\\'", read="bigquery")
-
self.validate_all(
"r'x\\''",
write={
@@ -301,7 +363,6 @@ class TestBigQuery(Validator):
"spark": "CURRENT_TIMESTAMP()",
},
)
-
self.validate_all(
"DIV(x, y)",
write={
@@ -309,19 +370,6 @@ class TestBigQuery(Validator):
"duckdb": "x // y",
},
)
-
- self.validate_identity(
- "SELECT ROW() OVER (y ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM x WINDOW y AS (PARTITION BY CATEGORY)"
- )
-
- self.validate_identity(
- "SELECT item, purchases, LAST_VALUE(item) OVER (item_window ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popular FROM Produce WINDOW item_window AS (ORDER BY purchases)"
- )
-
- self.validate_identity(
- "SELECT LAST_VALUE(a IGNORE NULLS) OVER y FROM x WINDOW y AS (PARTITION BY CATEGORY)",
- )
-
self.validate_all(
"CREATE TABLE db.example_table (col_a struct<struct_col_a:int, struct_col_b:string>)",
write={
@@ -358,25 +406,6 @@ class TestBigQuery(Validator):
"spark": "SELECT * FROM a WHERE b IN (SELECT UNNEST(ARRAY(1, 2, 3)))",
},
)
-
- # Reference: https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#set_operators
- with self.assertRaises(UnsupportedError):
- transpile(
- "SELECT * FROM a INTERSECT ALL SELECT * FROM b",
- write="bigquery",
- unsupported_level=ErrorLevel.RAISE,
- )
-
- with self.assertRaises(UnsupportedError):
- transpile(
- "SELECT * FROM a EXCEPT ALL SELECT * FROM b",
- write="bigquery",
- unsupported_level=ErrorLevel.RAISE,
- )
-
- with self.assertRaises(ParseError):
- transpile("SELECT * FROM UNNEST(x) AS x(y)", read="bigquery")
-
self.validate_all(
"DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)",
write={
@@ -465,14 +494,6 @@ class TestBigQuery(Validator):
"duckdb": "SELECT REGEXP_EXTRACT(abc, 'pattern(group)', 1) FROM table",
},
)
- self.validate_identity("REGEXP_EXTRACT(`foo`, 'bar: (.+?)', 1, 1)")
- self.validate_identity("BEGIN A B C D E F")
- self.validate_identity("BEGIN TRANSACTION")
- self.validate_identity("COMMIT TRANSACTION")
- self.validate_identity("ROLLBACK TRANSACTION")
- self.validate_identity("CAST(x AS BIGNUMERIC)")
-
- self.validate_identity("SELECT * FROM UNNEST([1]) WITH ORDINALITY")
self.validate_all(
"SELECT * FROM UNNEST([1]) WITH OFFSET",
write={"bigquery": "SELECT * FROM UNNEST([1]) WITH OFFSET AS offset"},
@@ -497,6 +518,16 @@ class TestBigQuery(Validator):
},
)
+ self.validate_identity(
+ "SELECT y + 1 z FROM x GROUP BY y + 1 ORDER BY z",
+ "SELECT y + 1 AS z FROM x GROUP BY z ORDER BY z",
+ )
+ self.validate_identity(
+ "SELECT y + 1 z FROM x GROUP BY y + 1",
+ "SELECT y + 1 AS z FROM x GROUP BY y + 1",
+ )
+ self.validate_identity("SELECT y + 1 FROM x GROUP BY y + 1 ORDER BY 1")
+
def test_user_defined_functions(self):
self.validate_identity(
"CREATE TEMPORARY FUNCTION a(x FLOAT64, y FLOAT64) RETURNS FLOAT64 NOT DETERMINISTIC LANGUAGE js AS 'return x*y;'"
@@ -568,3 +599,34 @@ class TestBigQuery(Validator):
"bigquery": "ALTER TABLE db.t1 RENAME TO t2",
},
)
+
+ @mock.patch("sqlglot.dialects.bigquery.logger")
+ def test_pushdown_cte_column_names(self, mock_logger):
+ with self.assertRaises(UnsupportedError):
+ transpile(
+ "WITH cte(foo) AS (SELECT * FROM tbl) SELECT foo FROM cte",
+ read="spark",
+ write="bigquery",
+ unsupported_level=ErrorLevel.RAISE,
+ )
+
+ self.validate_all(
+ "WITH cte AS (SELECT 1 AS foo) SELECT foo FROM cte",
+ read={"spark": "WITH cte(foo) AS (SELECT 1) SELECT foo FROM cte"},
+ )
+ self.validate_all(
+ "WITH cte AS (SELECT 1 AS foo) SELECT foo FROM cte",
+ read={"spark": "WITH cte(foo) AS (SELECT 1 AS bar) SELECT foo FROM cte"},
+ )
+ self.validate_all(
+ "WITH cte AS (SELECT 1 AS bar) SELECT bar FROM cte",
+ read={"spark": "WITH cte AS (SELECT 1 AS bar) SELECT bar FROM cte"},
+ )
+ self.validate_all(
+ "WITH cte AS (SELECT 1 AS foo, 2) SELECT foo FROM cte",
+ read={"postgres": "WITH cte(foo) AS (SELECT 1, 2) SELECT foo FROM cte"},
+ )
+ self.validate_all(
+ "WITH cte AS (SELECT 1 AS foo UNION ALL SELECT 2) SELECT foo FROM cte",
+ read={"postgres": "WITH cte(foo) AS (SELECT 1 UNION ALL SELECT 2) SELECT foo FROM cte"},
+ )
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index 3ac05cf..78f87ff 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -1413,7 +1413,8 @@ class TestDialect(Validator):
"presto": "SELECT a AS b FROM x GROUP BY 1",
"hive": "SELECT a AS b FROM x GROUP BY 1",
"oracle": "SELECT a AS b FROM x GROUP BY 1",
- "spark": "SELECT a AS b FROM x GROUP BY 1",
+ "spark": "SELECT a AS b FROM x GROUP BY b",
+ "spark2": "SELECT a AS b FROM x GROUP BY 1",
},
)
self.validate_all(
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index b8f7af0..ca2f921 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -6,6 +6,7 @@ class TestMySQL(Validator):
dialect = "mysql"
def test_ddl(self):
+ self.validate_identity("CREATE TABLE foo (id BIGINT)")
self.validate_identity("UPDATE items SET items.price = 0 WHERE items.id >= 5 LIMIT 10")
self.validate_identity("DELETE FROM t WHERE a <= 10 LIMIT 10")
self.validate_identity(
@@ -397,6 +398,16 @@ class TestMySQL(Validator):
self.validate_identity("TIME_STR_TO_UNIX(x)", "UNIX_TIMESTAMP(x)")
def test_mysql(self):
+ self.validate_all("CAST(x AS SIGNED)", write={"mysql": "CAST(x AS SIGNED)"})
+ self.validate_all("CAST(x AS SIGNED INTEGER)", write={"mysql": "CAST(x AS SIGNED)"})
+ self.validate_all("CAST(x AS UNSIGNED)", write={"mysql": "CAST(x AS UNSIGNED)"})
+ self.validate_all("CAST(x AS UNSIGNED INTEGER)", write={"mysql": "CAST(x AS UNSIGNED)"})
+ self.validate_all(
+ "SELECT DATE_ADD('2023-06-23 12:00:00', INTERVAL 2 * 2 MONTH) FROM foo",
+ write={
+ "mysql": "SELECT DATE_ADD('2023-06-23 12:00:00', INTERVAL (2 * 2) MONTH) FROM foo",
+ },
+ )
self.validate_all(
"SELECT * FROM t LOCK IN SHARE MODE", write={"mysql": "SELECT * FROM t FOR SHARE"}
)
diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py
index 852b494..49139f9 100644
--- a/tests/dialects/test_presto.py
+++ b/tests/dialects/test_presto.py
@@ -1,3 +1,5 @@
+from unittest import mock
+
from sqlglot import UnsupportedError
from tests.dialects.test_dialect import Validator
@@ -439,7 +441,8 @@ class TestPresto(Validator):
},
)
- def test_presto(self):
+ @mock.patch("sqlglot.helper.logger")
+ def test_presto(self, mock_logger):
self.validate_identity("SELECT * FROM x OFFSET 1 LIMIT 1")
self.validate_identity("SELECT * FROM x OFFSET 1 FETCH FIRST 1 ROWS ONLY")
self.validate_identity("SELECT BOOL_OR(a > 10) FROM asd AS T(a)")
@@ -453,6 +456,21 @@ class TestPresto(Validator):
self.validate_all("(5 * INTERVAL '7' day)", read={"": "INTERVAL '5' week"})
self.validate_all("(5 * INTERVAL '7' day)", read={"": "INTERVAL '5' WEEKS"})
self.validate_all(
+ "SELECT COALESCE(ELEMENT_AT(MAP_FROM_ENTRIES(ARRAY[(51, '1')]), id), quantity) FROM my_table",
+ write={
+ "postgres": UnsupportedError,
+ "presto": "SELECT COALESCE(ELEMENT_AT(MAP_FROM_ENTRIES(ARRAY[(51, '1')]), id), quantity) FROM my_table",
+ },
+ )
+ self.validate_all(
+ "SELECT ELEMENT_AT(ARRAY[1, 2, 3], 4)",
+ write={
+ "": "SELECT ARRAY(1, 2, 3)[3]",
+ "postgres": "SELECT (ARRAY[1, 2, 3])[4]",
+ "presto": "SELECT ELEMENT_AT(ARRAY[1, 2, 3], 4)",
+ },
+ )
+ self.validate_all(
"SELECT SUBSTRING(a, 1, 3), SUBSTRING(a, LENGTH(a) - (3 - 1))",
read={
"redshift": "SELECT LEFT(a, 3), RIGHT(a, 3)",
diff --git a/tests/dialects/test_redshift.py b/tests/dialects/test_redshift.py
index 88168ab..620aae2 100644
--- a/tests/dialects/test_redshift.py
+++ b/tests/dialects/test_redshift.py
@@ -82,7 +82,7 @@ class TestRedshift(Validator):
},
)
self.validate_all("SELECT INTERVAL '5 days'", read={"": "SELECT INTERVAL '5' days"})
- self.validate_all("CONVERT(INTEGER, x)", write={"redshift": "CAST(x AS INTEGER)"})
+ self.validate_all("CONVERT(INT, x)", write={"redshift": "CAST(x AS INTEGER)"})
self.validate_all(
"DATEADD('day', ndays, caldate)", write={"redshift": "DATEADD(day, ndays, caldate)"}
)
@@ -104,7 +104,7 @@ class TestRedshift(Validator):
"SELECT ST_AsEWKT(ST_GeomFromEWKT('SRID=4326;POINT(10 20)')::geography)",
write={
"redshift": "SELECT ST_ASEWKT(CAST(ST_GEOMFROMEWKT('SRID=4326;POINT(10 20)') AS GEOGRAPHY))",
- "bigquery": "SELECT ST_ASEWKT(SAFE_CAST(ST_GEOMFROMEWKT('SRID=4326;POINT(10 20)') AS GEOGRAPHY))",
+ "bigquery": "SELECT ST_AsEWKT(SAFE_CAST(ST_GeomFromEWKT('SRID=4326;POINT(10 20)') AS GEOGRAPHY))",
},
)
self.validate_all(
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 48bb2f7..4d2c392 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("WITH x AS (SELECT 1 AS foo) SELECT foo FROM IDENTIFIER('x')")
+ self.validate_identity("WITH x AS (SELECT 1 AS foo) SELECT IDENTIFIER('foo') FROM x")
self.validate_identity("INITCAP('iqamqinterestedqinqthisqtopic', 'q')")
self.validate_identity("CAST(x AS GEOMETRY)")
self.validate_identity("OBJECT_CONSTRUCT(*)")
@@ -23,6 +25,9 @@ class TestSnowflake(Validator):
self.validate_identity("CREATE TABLE foo (bar FLOAT AUTOINCREMENT START 0 INCREMENT 1)")
self.validate_identity("ALTER TABLE IF EXISTS foo SET TAG a = 'a', b = 'b', c = 'c'")
self.validate_identity("ALTER TABLE foo UNSET TAG a, b, c")
+ self.validate_identity("ALTER TABLE foo SET COMMENT = 'bar'")
+ self.validate_identity("ALTER TABLE foo SET CHANGE_TRACKING = FALSE")
+ self.validate_identity("ALTER TABLE foo UNSET DATA_RETENTION_TIME_IN_DAYS, CHANGE_TRACKING")
self.validate_identity("COMMENT IF EXISTS ON TABLE foo IS 'bar'")
self.validate_identity("SELECT CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', col)")
self.validate_identity(
@@ -582,6 +587,8 @@ class TestSnowflake(Validator):
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 IDENTIFIER('foo') (COLUMN1 VARCHAR, COLUMN2 VARCHAR)")
+ self.validate_identity("CREATE TABLE IDENTIFIER($foo) (col1 VARCHAR, col2 VARCHAR)")
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'))"
)
diff --git a/tests/dialects/test_spark.py b/tests/dialects/test_spark.py
index 54c39e7..8acc48e 100644
--- a/tests/dialects/test_spark.py
+++ b/tests/dialects/test_spark.py
@@ -7,6 +7,10 @@ class TestSpark(Validator):
def test_ddl(self):
self.validate_identity("CREATE TABLE foo (col VARCHAR(50))")
self.validate_identity("CREATE TABLE foo (col STRUCT<struct_col_a: VARCHAR((50))>)")
+ self.validate_identity("CREATE TABLE foo (col STRING) CLUSTERED BY (col) INTO 10 BUCKETS")
+ self.validate_identity(
+ "CREATE TABLE foo (col STRING) CLUSTERED BY (col) SORTED BY (col) INTO 10 BUCKETS"
+ )
self.validate_all(
"CREATE TABLE db.example_table (col_a struct<struct_col_a:int, struct_col_b:string>)",