summaryrefslogtreecommitdiffstats
path: root/tests/dialects
diff options
context:
space:
mode:
Diffstat (limited to 'tests/dialects')
-rw-r--r--tests/dialects/test_athena.py20
-rw-r--r--tests/dialects/test_bigquery.py83
-rw-r--r--tests/dialects/test_clickhouse.py28
-rw-r--r--tests/dialects/test_dialect.py117
-rw-r--r--tests/dialects/test_drill.py63
-rw-r--r--tests/dialects/test_duckdb.py90
-rw-r--r--tests/dialects/test_hive.py27
-rw-r--r--tests/dialects/test_mysql.py67
-rw-r--r--tests/dialects/test_oracle.py68
-rw-r--r--tests/dialects/test_postgres.py147
-rw-r--r--tests/dialects/test_presto.py30
-rw-r--r--tests/dialects/test_prql.py17
-rw-r--r--tests/dialects/test_redshift.py38
-rw-r--r--tests/dialects/test_snowflake.py175
-rw-r--r--tests/dialects/test_spark.py37
-rw-r--r--tests/dialects/test_sqlite.py132
-rw-r--r--tests/dialects/test_teradata.py5
-rw-r--r--tests/dialects/test_tsql.py29
18 files changed, 905 insertions, 268 deletions
diff --git a/tests/dialects/test_athena.py b/tests/dialects/test_athena.py
new file mode 100644
index 0000000..3288ada
--- /dev/null
+++ b/tests/dialects/test_athena.py
@@ -0,0 +1,20 @@
+from tests.dialects.test_dialect import Validator
+
+
+class TestAthena(Validator):
+ dialect = "athena"
+ maxDiff = None
+
+ def test_athena(self):
+ self.validate_identity(
+ """USING EXTERNAL FUNCTION some_function(input VARBINARY)
+ RETURNS VARCHAR
+ LAMBDA 'some-name'
+ SELECT
+ some_function(1)""",
+ check_command_warning=True,
+ )
+
+ self.validate_identity(
+ "CREATE TABLE IF NOT EXISTS t (name STRING) LOCATION 's3://bucket/tmp/mytable/' TBLPROPERTIES ('table_type'='iceberg', 'FORMAT'='parquet')"
+ )
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index 0d94d19..300d492 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -51,6 +51,8 @@ class TestBigQuery(Validator):
self.assertEqual(table.name, "_y")
self.validate_identity("SELECT * FROM x-0.y")
+ self.assertEqual(exp.to_table("`a.b`.`c.d`", dialect="bigquery").sql(), '"a"."b"."c"."d"')
+ self.assertEqual(exp.to_table("`x`.`y.z`", dialect="bigquery").sql(), '"x"."y"."z"')
self.assertEqual(exp.to_table("`x.y.z`", dialect="bigquery").sql(), '"x"."y"."z"')
self.assertEqual(exp.to_table("`x.y.z`", dialect="bigquery").sql("bigquery"), "`x.y.z`")
self.assertEqual(exp.to_table("`x`.`y`", dialect="bigquery").sql("bigquery"), "`x`.`y`")
@@ -58,6 +60,8 @@ class TestBigQuery(Validator):
select_with_quoted_udf = self.validate_identity("SELECT `p.d.UdF`(data) FROM `p.d.t`")
self.assertEqual(select_with_quoted_udf.selects[0].name, "p.d.UdF")
+ self.validate_identity("SELECT ARRAY_TO_STRING(list, '--') AS text")
+ self.validate_identity("SELECT jsondoc['some_key']")
self.validate_identity("SELECT `p.d.UdF`(data).* FROM `p.d.t`")
self.validate_identity("SELECT * FROM `my-project.my-dataset.my-table`")
self.validate_identity("CREATE OR REPLACE TABLE `a.b.c` CLONE `a.b.d`")
@@ -178,6 +182,13 @@ class TestBigQuery(Validator):
"""SELECT JSON_EXTRACT_SCALAR('5')""", """SELECT JSON_EXTRACT_SCALAR('5', '$')"""
)
self.validate_identity(
+ "CREATE OR REPLACE VIEW test (tenant_id OPTIONS (description='Test description on table creation')) AS SELECT 1 AS tenant_id, 1 AS customer_id",
+ )
+ self.validate_identity(
+ "CREATE VIEW `d.v` OPTIONS (expiration_timestamp=TIMESTAMP '2020-01-02T04:05:06.007Z') AS SELECT 1 AS c",
+ "CREATE VIEW `d.v` OPTIONS (expiration_timestamp=CAST('2020-01-02T04:05:06.007Z' AS TIMESTAMP)) AS SELECT 1 AS c",
+ )
+ self.validate_identity(
"SELECT ARRAY(SELECT AS STRUCT 1 a, 2 b)",
"SELECT ARRAY(SELECT AS STRUCT 1 AS a, 2 AS b)",
)
@@ -186,10 +197,6 @@ class TestBigQuery(Validator):
"SELECT EXISTS(SELECT 1 FROM UNNEST([1, 2, 3]) AS _col WHERE _col = 1)",
)
self.validate_identity(
- "create or replace view test (tenant_id OPTIONS(description='Test description on table creation')) select 1 as tenant_id, 1 as customer_id;",
- "CREATE OR REPLACE VIEW test (tenant_id OPTIONS (description='Test description on table creation')) AS SELECT 1 AS tenant_id, 1 AS customer_id",
- )
- self.validate_identity(
"SELECT SPLIT(foo)",
"SELECT SPLIT(foo, ',')",
)
@@ -659,6 +666,13 @@ class TestBigQuery(Validator):
},
)
self.validate_all(
+ "SELECT CAST(STRUCT(1) AS STRUCT<INT64>)",
+ write={
+ "bigquery": "SELECT CAST(STRUCT(1) AS STRUCT<INT64>)",
+ "snowflake": "SELECT CAST(OBJECT_CONSTRUCT('_0', 1) AS OBJECT)",
+ },
+ )
+ self.validate_all(
"cast(x as date format 'MM/DD/YYYY')",
write={
"bigquery": "PARSE_DATE('%m/%d/%Y', x)",
@@ -724,10 +738,10 @@ class TestBigQuery(Validator):
self.validate_all(
"'\\\\'",
write={
- "bigquery": r"'\\'",
- "duckdb": r"'\\'",
- "presto": r"'\\'",
- "hive": r"'\\'",
+ "bigquery": "'\\\\'",
+ "duckdb": "'\\'",
+ "presto": "'\\'",
+ "hive": "'\\\\'",
},
)
self.validate_all(
@@ -1004,20 +1018,28 @@ class TestBigQuery(Validator):
},
)
self.validate_all(
- "SELECT cola, colb FROM UNNEST([STRUCT(1 AS cola, 'test' AS colb)])",
+ "SELECT cola, colb FROM UNNEST([STRUCT(1 AS cola, 'test' AS colb)]) AS tab",
read={
- "bigquery": "SELECT cola, colb FROM UNNEST([STRUCT(1 AS cola, 'test' AS colb)])",
+ "bigquery": "SELECT cola, colb FROM UNNEST([STRUCT(1 AS cola, 'test' AS colb)]) as tab",
"snowflake": "SELECT cola, colb FROM (VALUES (1, 'test')) AS tab(cola, colb)",
"spark": "SELECT cola, colb FROM VALUES (1, 'test') AS tab(cola, colb)",
},
)
self.validate_all(
- "SELECT * FROM UNNEST([STRUCT(1 AS id)]) CROSS JOIN UNNEST([STRUCT(1 AS id)])",
+ "SELECT * FROM UNNEST([STRUCT(1 AS _c0)]) AS t1",
read={
- "bigquery": "SELECT * FROM UNNEST([STRUCT(1 AS id)]) CROSS JOIN UNNEST([STRUCT(1 AS id)])",
+ "bigquery": "SELECT * FROM UNNEST([STRUCT(1 AS _c0)]) AS t1",
+ "postgres": "SELECT * FROM (VALUES (1)) AS t1",
+ },
+ )
+ self.validate_all(
+ "SELECT * FROM UNNEST([STRUCT(1 AS id)]) AS t1 CROSS JOIN UNNEST([STRUCT(1 AS id)]) AS t2",
+ read={
+ "bigquery": "SELECT * FROM UNNEST([STRUCT(1 AS id)]) AS t1 CROSS JOIN UNNEST([STRUCT(1 AS id)]) AS t2",
"postgres": "SELECT * FROM (VALUES (1)) AS t1(id) CROSS JOIN (VALUES (1)) AS t2(id)",
},
)
+
self.validate_all(
"SELECT REGEXP_EXTRACT(abc, 'pattern(group)') FROM table",
write={
@@ -1050,28 +1072,43 @@ class TestBigQuery(Validator):
)
self.validate_all(
"""SELECT
- `u`.`harness_user_email` AS `harness_user_email`,
- `d`.`harness_user_id` AS `harness_user_id`,
- `harness_account_id` AS `harness_account_id`
-FROM `analytics_staging`.`stg_mongodb__users` AS `u`, UNNEST(`u`.`harness_cluster_details`) AS `d`, UNNEST(`d`.`harness_account_ids`) AS `harness_account_id`
+ `u`.`user_email` AS `user_email`,
+ `d`.`user_id` AS `user_id`,
+ `account_id` AS `account_id`
+FROM `analytics_staging`.`stg_mongodb__users` AS `u`, UNNEST(`u`.`cluster_details`) AS `d`, UNNEST(`d`.`account_ids`) AS `account_id`
WHERE
- NOT `harness_account_id` IS NULL""",
+ NOT `account_id` IS NULL""",
read={
"": """
SELECT
- "u"."harness_user_email" AS "harness_user_email",
- "_q_0"."d"."harness_user_id" AS "harness_user_id",
- "_q_1"."harness_account_id" AS "harness_account_id"
+ "u"."user_email" AS "user_email",
+ "_q_0"."d"."user_id" AS "user_id",
+ "_q_1"."account_id" AS "account_id"
FROM
"analytics_staging"."stg_mongodb__users" AS "u",
- UNNEST("u"."harness_cluster_details") AS "_q_0"("d"),
- UNNEST("_q_0"."d"."harness_account_ids") AS "_q_1"("harness_account_id")
+ UNNEST("u"."cluster_details") AS "_q_0"("d"),
+ UNNEST("_q_0"."d"."account_ids") AS "_q_1"("account_id")
WHERE
- NOT "_q_1"."harness_account_id" IS NULL
+ NOT "_q_1"."account_id" IS NULL
"""
},
pretty=True,
)
+ self.validate_all(
+ "SELECT MOD(x, 10)",
+ read={"postgres": "SELECT x % 10"},
+ write={
+ "bigquery": "SELECT MOD(x, 10)",
+ "postgres": "SELECT x % 10",
+ },
+ )
+ self.validate_all(
+ "SELECT CAST(x AS DATETIME)",
+ write={
+ "": "SELECT CAST(x AS TIMESTAMP)",
+ "bigquery": "SELECT CAST(x AS DATETIME)",
+ },
+ )
def test_errors(self):
with self.assertRaises(TokenError):
diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index edf3da1..c5f9847 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -1,5 +1,6 @@
from sqlglot import exp, parse_one
from tests.dialects.test_dialect import Validator
+from sqlglot.errors import ErrorLevel
class TestClickhouse(Validator):
@@ -153,7 +154,9 @@ class TestClickhouse(Validator):
self.validate_identity("TRUNCATE TABLE t1 ON CLUSTER test_cluster")
self.validate_identity("TRUNCATE DATABASE db")
self.validate_identity("TRUNCATE DATABASE db ON CLUSTER test_cluster")
-
+ self.validate_identity(
+ "CREATE TABLE t (foo String CODEC(LZ4HC(9), ZSTD, DELTA), size String ALIAS formatReadableSize(size_bytes), INDEX idx1 a TYPE bloom_filter(0.001) GRANULARITY 1, INDEX idx2 a TYPE set(100) GRANULARITY 2, INDEX idx3 a TYPE minmax GRANULARITY 3)"
+ )
self.validate_all(
"SELECT arrayJoin([1,2,3])",
write={
@@ -390,6 +393,22 @@ class TestClickhouse(Validator):
)
self.validate_identity("SYSTEM STOP MERGES foo.bar", check_command_warning=True)
+ self.validate_identity(
+ "INSERT INTO FUNCTION s3('url', 'CSV', 'name String, value UInt32', 'gzip') SELECT name, value FROM existing_table"
+ )
+ self.validate_identity(
+ "INSERT INTO FUNCTION remote('localhost', default.simple_table) VALUES (100, 'inserted via remote()')"
+ )
+ self.validate_identity(
+ """INSERT INTO TABLE FUNCTION hdfs('hdfs://hdfs1:9000/test', 'TSV', 'name String, column2 UInt32, column3 UInt32') VALUES ('test', 1, 2)""",
+ """INSERT INTO FUNCTION hdfs('hdfs://hdfs1:9000/test', 'TSV', 'name String, column2 UInt32, column3 UInt32') VALUES ('test', 1, 2)""",
+ )
+
+ self.validate_identity("SELECT 1 FORMAT TabSeparated")
+ self.validate_identity("SELECT * FROM t FORMAT TabSeparated")
+ self.validate_identity("SELECT FORMAT")
+ self.validate_identity("1 AS FORMAT").assert_is(exp.Alias)
+
def test_cte(self):
self.validate_identity("WITH 'x' AS foo SELECT foo")
self.validate_identity("WITH ['c'] AS field_names SELECT field_names")
@@ -401,6 +420,13 @@ class TestClickhouse(Validator):
self.assertIsInstance(query.args["with"].expressions[0].this, exp.Subquery)
self.assertEqual(query.args["with"].expressions[0].alias, "y")
+ query = "WITH 1 AS var SELECT var"
+ for error_level in [ErrorLevel.IGNORE, ErrorLevel.RAISE, ErrorLevel.IMMEDIATE]:
+ self.assertEqual(
+ self.parse_one(query, error_level=error_level).sql(dialect=self.dialect),
+ query,
+ )
+
def test_ternary(self):
self.validate_all("x ? 1 : 2", write={"clickhouse": "CASE WHEN x THEN 1 ELSE 2 END"})
self.validate_all(
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index 5faed51..76ab94b 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -17,8 +17,8 @@ from sqlglot.parser import logger as parser_logger
class Validator(unittest.TestCase):
dialect = None
- def parse_one(self, sql):
- return parse_one(sql, read=self.dialect)
+ def parse_one(self, sql, **kwargs):
+ return parse_one(sql, read=self.dialect, **kwargs)
def validate_identity(self, sql, write_sql=None, pretty=False, check_command_warning=False):
if check_command_warning:
@@ -611,7 +611,7 @@ class TestDialect(Validator):
write={
"duckdb": "EPOCH(STRPTIME('2020-01-01', '%Y-%m-%d'))",
"hive": "UNIX_TIMESTAMP('2020-01-01', 'yyyy-MM-dd')",
- "presto": "TO_UNIXTIME(DATE_PARSE('2020-01-01', '%Y-%m-%d'))",
+ "presto": "TO_UNIXTIME(COALESCE(TRY(DATE_PARSE(CAST('2020-01-01' AS VARCHAR), '%Y-%m-%d')), PARSE_DATETIME(CAST('2020-01-01' AS VARCHAR), 'yyyy-MM-dd')))",
"starrocks": "UNIX_TIMESTAMP('2020-01-01', '%Y-%m-%d')",
"doris": "UNIX_TIMESTAMP('2020-01-01', '%Y-%m-%d')",
},
@@ -700,7 +700,7 @@ class TestDialect(Validator):
"hive": "TO_DATE(x)",
"postgres": "CAST(x AS DATE)",
"presto": "CAST(CAST(x AS TIMESTAMP) AS DATE)",
- "snowflake": "CAST(x AS DATE)",
+ "snowflake": "TO_DATE(x)",
"doris": "TO_DATE(x)",
"mysql": "DATE(x)",
},
@@ -961,6 +961,7 @@ class TestDialect(Validator):
"presto": "CAST(x AS DATE)",
"spark": "CAST(x AS DATE)",
"sqlite": "x",
+ "tsql": "CAST(x AS DATE)",
},
)
self.validate_all(
@@ -1509,7 +1510,7 @@ class TestDialect(Validator):
"POSITION(needle, haystack, pos)",
write={
"drill": "STRPOS(SUBSTR(haystack, pos), needle) + pos - 1",
- "presto": "STRPOS(haystack, needle, pos)",
+ "presto": "STRPOS(SUBSTR(haystack, pos), needle) + pos - 1",
"spark": "LOCATE(needle, haystack, pos)",
"clickhouse": "position(haystack, needle, pos)",
"snowflake": "POSITION(needle, haystack, pos)",
@@ -1719,6 +1720,11 @@ class TestDialect(Validator):
with self.subTest(f"{expression.__class__.__name__} {dialect} -> {expected}"):
self.assertEqual(expected, expression.sql(dialect=dialect))
+ self.assertEqual(
+ parse_one("CAST(x AS DECIMAL) / y", read="mysql").sql(dialect="postgres"),
+ "CAST(x AS DECIMAL) / NULLIF(y, 0)",
+ )
+
def test_limit(self):
self.validate_all(
"SELECT * FROM data LIMIT 10, 20",
@@ -2054,6 +2060,44 @@ SELECT
)
def test_logarithm(self):
+ for base in (2, 10):
+ with self.subTest(f"Transpiling LOG base {base}"):
+ self.validate_all(
+ f"LOG({base}, a)",
+ read={
+ "": f"LOG{base}(a)",
+ "bigquery": f"LOG{base}(a)",
+ "clickhouse": f"LOG{base}(a)",
+ "databricks": f"LOG{base}(a)",
+ "duckdb": f"LOG{base}(a)",
+ "mysql": f"LOG{base}(a)",
+ "postgres": f"LOG{base}(a)",
+ "presto": f"LOG{base}(a)",
+ "spark": f"LOG{base}(a)",
+ "sqlite": f"LOG{base}(a)",
+ "trino": f"LOG{base}(a)",
+ "tsql": f"LOG{base}(a)",
+ },
+ write={
+ "bigquery": f"LOG(a, {base})",
+ "clickhouse": f"LOG{base}(a)",
+ "duckdb": f"LOG({base}, a)",
+ "mysql": f"LOG({base}, a)",
+ "oracle": f"LOG({base}, a)",
+ "postgres": f"LOG({base}, a)",
+ "presto": f"LOG{base}(a)",
+ "redshift": f"LOG({base}, a)",
+ "snowflake": f"LOG({base}, a)",
+ "spark2": f"LOG({base}, a)",
+ "spark": f"LOG({base}, a)",
+ "sqlite": f"LOG({base}, a)",
+ "starrocks": f"LOG({base}, a)",
+ "tableau": f"LOG(a, {base})",
+ "trino": f"LOG({base}, a)",
+ "tsql": f"LOG(a, {base})",
+ },
+ )
+
self.validate_all(
"LOG(x)",
read={
@@ -2082,6 +2126,7 @@ SELECT
"bigquery": "LOG(n, b)",
"databricks": "LOG(b, n)",
"drill": "LOG(b, n)",
+ "duckdb": "LOG(b, n)",
"hive": "LOG(b, n)",
"mysql": "LOG(b, n)",
"oracle": "LOG(b, n)",
@@ -2089,8 +2134,13 @@ SELECT
"snowflake": "LOG(b, n)",
"spark": "LOG(b, n)",
"sqlite": "LOG(b, n)",
+ "trino": "LOG(b, n)",
"tsql": "LOG(n, b)",
},
+ write={
+ "clickhouse": UnsupportedError,
+ "presto": UnsupportedError,
+ },
)
def test_count_if(self):
@@ -2190,7 +2240,28 @@ SELECT
"WITH t1(x) AS (SELECT 1) SELECT * FROM (WITH t2(y) AS (SELECT 2) SELECT y FROM t2) AS subq",
write={
"duckdb": "WITH t1(x) AS (SELECT 1) SELECT * FROM (WITH t2(y) AS (SELECT 2) SELECT y FROM t2) AS subq",
- "tsql": "WITH t1(x) AS (SELECT 1), t2(y) AS (SELECT 2) SELECT * FROM (SELECT y AS y FROM t2) AS subq",
+ "tsql": "WITH t2(y) AS (SELECT 2), t1(x) AS (SELECT 1) SELECT * FROM (SELECT y AS y FROM t2) AS subq",
+ },
+ )
+ self.validate_all(
+ """
+WITH c AS (
+ WITH b AS (
+ WITH a1 AS (
+ SELECT 1
+ ), a2 AS (
+ SELECT 2
+ )
+ SELECT * FROM a1, a2
+ )
+ SELECT *
+ FROM b
+)
+SELECT *
+FROM c""",
+ write={
+ "duckdb": "WITH c AS (WITH b AS (WITH a1 AS (SELECT 1), a2 AS (SELECT 2) SELECT * FROM a1, a2) SELECT * FROM b) SELECT * FROM c",
+ "hive": "WITH a1 AS (SELECT 1), a2 AS (SELECT 2), b AS (SELECT * FROM a1, a2), c AS (SELECT * FROM b) SELECT * FROM c",
},
)
@@ -2312,3 +2383,37 @@ SELECT
self.validate_identity("TRUNCATE TABLE db.schema.test")
self.validate_identity("TRUNCATE TABLE IF EXISTS db.schema.test")
self.validate_identity("TRUNCATE TABLE t1, t2, t3")
+
+ def test_create_sequence(self):
+ self.validate_identity("CREATE SEQUENCE seq")
+ self.validate_identity(
+ "CREATE TEMPORARY SEQUENCE seq AS SMALLINT START WITH 3 INCREMENT BY 2 MINVALUE 1 MAXVALUE 10 CACHE 1 NO CYCLE OWNED BY table.col"
+ )
+ self.validate_identity(
+ "CREATE SEQUENCE seq START WITH 1 NO MINVALUE NO MAXVALUE CYCLE NO CACHE"
+ )
+ self.validate_identity("CREATE OR REPLACE TEMPORARY SEQUENCE seq INCREMENT BY 1 NO CYCLE")
+ self.validate_identity(
+ "CREATE OR REPLACE SEQUENCE IF NOT EXISTS seq COMMENT='test comment' ORDER"
+ )
+ self.validate_identity(
+ "CREATE SEQUENCE schema.seq SHARING=METADATA NOORDER NOKEEP SCALE EXTEND SHARD EXTEND SESSION"
+ )
+ self.validate_identity(
+ "CREATE SEQUENCE schema.seq SHARING=DATA ORDER KEEP NOSCALE NOSHARD GLOBAL"
+ )
+ self.validate_identity(
+ "CREATE SEQUENCE schema.seq SHARING=DATA NOCACHE NOCYCLE SCALE NOEXTEND"
+ )
+ self.validate_identity(
+ """CREATE TEMPORARY SEQUENCE seq AS BIGINT INCREMENT BY 2 MINVALUE 1 CACHE 1 NOMAXVALUE NO CYCLE OWNED BY NONE""",
+ """CREATE TEMPORARY SEQUENCE seq AS BIGINT INCREMENT BY 2 MINVALUE 1 CACHE 1 NOMAXVALUE NO CYCLE""",
+ )
+ self.validate_identity(
+ """CREATE TEMPORARY SEQUENCE seq START 1""",
+ """CREATE TEMPORARY SEQUENCE seq START WITH 1""",
+ )
+ self.validate_identity(
+ """CREATE TEMPORARY SEQUENCE seq START WITH = 1 INCREMENT BY = 2""",
+ """CREATE TEMPORARY SEQUENCE seq START WITH 1 INCREMENT BY 2""",
+ )
diff --git a/tests/dialects/test_drill.py b/tests/dialects/test_drill.py
index 41c02fb..634c247 100644
--- a/tests/dialects/test_drill.py
+++ b/tests/dialects/test_drill.py
@@ -5,68 +5,17 @@ class TestDrill(Validator):
dialect = "drill"
def test_drill(self):
- self.validate_all(
- "DATE_FORMAT(a, 'yyyy')",
- write={"drill": "TO_CHAR(a, 'yyyy')"},
+ self.validate_identity(
+ "SELECT * FROM table(dfs.`test_data.xlsx`(type => 'excel', sheetName => 'secondSheet'))"
+ )
+ self.validate_identity(
+ "SELECT * FROM (SELECT * FROM t) PIVOT(avg(c1) AS ac1 FOR c2 IN ('V' AS v))",
)
- def test_string_literals(self):
self.validate_all(
"SELECT '2021-01-01' + INTERVAL 1 MONTH",
write={
+ "drill": "SELECT '2021-01-01' + INTERVAL '1' MONTH",
"mysql": "SELECT '2021-01-01' + INTERVAL '1' MONTH",
},
)
-
- def test_quotes(self):
- self.validate_all(
- "'\\''",
- write={
- "duckdb": "''''",
- "presto": "''''",
- "hive": "'\\''",
- "spark": "'\\''",
- },
- )
- self.validate_all(
- "'\"x\"'",
- write={
- "duckdb": "'\"x\"'",
- "presto": "'\"x\"'",
- "hive": "'\"x\"'",
- "spark": "'\"x\"'",
- },
- )
- self.validate_all(
- "'\\\\a'",
- read={
- "presto": "'\\\\a'",
- },
- write={
- "duckdb": "'\\\\a'",
- "presto": "'\\\\a'",
- "hive": "'\\\\a'",
- "spark": "'\\\\a'",
- },
- )
-
- def test_table_function(self):
- self.validate_all(
- "SELECT * FROM table( dfs.`test_data.xlsx` (type => 'excel', sheetName => 'secondSheet'))",
- write={
- "drill": "SELECT * FROM table(dfs.`test_data.xlsx`(type => 'excel', sheetName => 'secondSheet'))",
- },
- )
-
- def test_validate_pivot(self):
- self.validate_all(
- "SELECT * FROM (SELECT education_level, salary, marital_status, "
- "EXTRACT(year FROM age(birth_date)) age FROM cp.`employee.json`) PIVOT (avg(salary) AS "
- "avg_salary, avg(age) AS avg_age FOR marital_status IN ('M' married, 'S' single))",
- write={
- "drill": "SELECT * FROM (SELECT education_level, salary, marital_status, "
- "EXTRACT(year FROM age(birth_date)) AS age FROM cp.`employee.json`) "
- "PIVOT(avg(salary) AS avg_salary, avg(age) AS avg_age FOR marital_status "
- "IN ('M' AS married, 'S' AS single))"
- },
- )
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index 58d1f06..5a7e93e 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -1,5 +1,6 @@
from sqlglot import ErrorLevel, UnsupportedError, exp, parse_one, transpile
from sqlglot.helper import logger as helper_logger
+from sqlglot.optimizer.annotate_types import annotate_types
from tests.dialects.test_dialect import Validator
@@ -7,6 +8,31 @@ class TestDuckDB(Validator):
dialect = "duckdb"
def test_duckdb(self):
+ query = "WITH _data AS (SELECT [{'a': 1, 'b': 2}, {'a': 2, 'b': 3}] AS col) SELECT t.col['b'] FROM _data, UNNEST(_data.col) AS t(col) WHERE t.col['a'] = 1"
+ expr = annotate_types(self.validate_identity(query))
+ self.assertEqual(
+ expr.sql(dialect="bigquery"),
+ "WITH _data AS (SELECT [STRUCT(1 AS a, 2 AS b), STRUCT(2 AS a, 3 AS b)] AS col) SELECT col.b FROM _data, UNNEST(_data.col) AS col WHERE col.a = 1",
+ )
+
+ self.validate_all(
+ "SELECT CAST('2020-01-01' AS DATE) + INTERVAL (day_offset) DAY FROM t",
+ read={
+ "duckdb": "SELECT CAST('2020-01-01' AS DATE) + INTERVAL (day_offset) DAY FROM t",
+ "mysql": "SELECT DATE '2020-01-01' + INTERVAL day_offset DAY FROM t",
+ },
+ )
+ self.validate_all(
+ "SELECT CAST('09:05:03' AS TIME) + INTERVAL 2 HOUR",
+ read={
+ "bigquery": "SELECT TIME_ADD(CAST('09:05:03' AS TIME), INTERVAL 2 HOUR)",
+ "snowflake": "SELECT TIMEADD(HOUR, 2, TO_TIME('09:05:03'))",
+ },
+ write={
+ "duckdb": "SELECT CAST('09:05:03' AS TIME) + INTERVAL '2' HOUR",
+ "snowflake": "SELECT CAST('09:05:03' AS TIME) + INTERVAL '2 HOUR'",
+ },
+ )
self.validate_all(
'STRUCT_PACK("a b" := 1)',
write={
@@ -15,7 +41,25 @@ class TestDuckDB(Validator):
"snowflake": "OBJECT_CONSTRUCT('a b', 1)",
},
)
-
+ self.validate_all(
+ "ARRAY_TO_STRING(arr, delim)",
+ read={
+ "bigquery": "ARRAY_TO_STRING(arr, delim)",
+ "postgres": "ARRAY_TO_STRING(arr, delim)",
+ "presto": "ARRAY_JOIN(arr, delim)",
+ "snowflake": "ARRAY_TO_STRING(arr, delim)",
+ "spark": "ARRAY_JOIN(arr, delim)",
+ },
+ write={
+ "bigquery": "ARRAY_TO_STRING(arr, delim)",
+ "duckdb": "ARRAY_TO_STRING(arr, delim)",
+ "postgres": "ARRAY_TO_STRING(arr, delim)",
+ "presto": "ARRAY_JOIN(arr, delim)",
+ "snowflake": "ARRAY_TO_STRING(arr, delim)",
+ "spark": "ARRAY_JOIN(arr, delim)",
+ "tsql": "STRING_AGG(arr, delim)",
+ },
+ )
self.validate_all(
"SELECT SUM(X) OVER (ORDER BY x)",
write={
@@ -131,13 +175,6 @@ class TestDuckDB(Validator):
},
)
self.validate_all(
- "WITH _data AS (SELECT [{'a': 1, 'b': 2}, {'a': 2, 'b': 3}] AS col) SELECT (SELECT col['b'] FROM UNNEST(col) AS t(col) WHERE col['a'] = 1) FROM _data",
- write={
- "bigquery": "WITH _data AS (SELECT [STRUCT(1 AS a, 2 AS b), STRUCT(2 AS a, 3 AS b)] AS col) SELECT (SELECT col.b FROM UNNEST(col) AS col WHERE col.a = 1) FROM _data",
- "duckdb": "WITH _data AS (SELECT [{'a': 1, 'b': 2}, {'a': 2, 'b': 3}] AS col) SELECT (SELECT col['b'] FROM UNNEST(col) AS t(col) WHERE col['a'] = 1) FROM _data",
- },
- )
- self.validate_all(
"SELECT {'bla': column1, 'foo': column2, 'bar': column3} AS data FROM source_table",
read={
"bigquery": "SELECT STRUCT(column1 AS bla, column2 AS foo, column3 AS bar) AS data FROM source_table",
@@ -201,6 +238,9 @@ class TestDuckDB(Validator):
parse_one("a // b", read="duckdb").assert_is(exp.IntDiv).sql(dialect="duckdb"), "a // b"
)
+ self.validate_identity("SELECT MAP(['key1', 'key2', 'key3'], [10, 20, 30])")
+ self.validate_identity("SELECT MAP {'x': 1}")
+ self.validate_identity("SELECT df1.*, df2.* FROM df1 POSITIONAL JOIN df2")
self.validate_identity("MAKE_TIMESTAMP(1992, 9, 20, 13, 34, 27.123456)")
self.validate_identity("MAKE_TIMESTAMP(1667810584123456)")
self.validate_identity("SELECT EPOCH_MS(10) AS t")
@@ -235,6 +275,18 @@ class TestDuckDB(Validator):
"""SELECT '{"foo": [1, 2, 3]}' -> '$.foo' -> '$[0]'""",
)
self.validate_identity(
+ "SELECT ($$hello)'world$$)",
+ "SELECT ('hello)''world')",
+ )
+ self.validate_identity(
+ "SELECT $$foo$$",
+ "SELECT 'foo'",
+ )
+ self.validate_identity(
+ "SELECT $tag$foo$tag$",
+ "SELECT 'foo'",
+ )
+ self.validate_identity(
"JSON_EXTRACT(x, '$.family')",
"x -> '$.family'",
)
@@ -679,7 +731,19 @@ class TestDuckDB(Validator):
},
)
self.validate_identity(
- "[x.STRING_SPLIT(' ')[1] FOR x IN ['1', '2', 3] IF x.CONTAINS('1')]"
+ "[x.STRING_SPLIT(' ')[i] FOR x IN ['1', '2', 3] IF x.CONTAINS('1')]"
+ )
+ self.validate_identity(
+ """SELECT LIST_VALUE(1)[i]""",
+ """SELECT ([1])[i]""",
+ )
+ self.validate_identity(
+ """{'x': LIST_VALUE(1)[i]}""",
+ """{'x': ([1])[i]}""",
+ )
+ self.validate_identity(
+ """SELECT LIST_APPLY(RANGE(1, 4), i -> {'f1': LIST_VALUE(1, 2, 3)[i], 'f2': LIST_VALUE(1, 2, 3)[i]})""",
+ """SELECT LIST_APPLY(RANGE(1, 4), i -> {'f1': ([1, 2, 3])[i], 'f2': ([1, 2, 3])[i]})""",
)
self.assertEqual(
@@ -689,8 +753,6 @@ class TestDuckDB(Validator):
"WARNING:sqlglot:Applying array index offset (1)",
"WARNING:sqlglot:Applying array index offset (1)",
"WARNING:sqlglot:Applying array index offset (1)",
- "WARNING:sqlglot:Applying array index offset (-1)",
- "WARNING:sqlglot:Applying array index offset (1)",
],
)
@@ -702,7 +764,7 @@ class TestDuckDB(Validator):
"SELECT MAKE_DATE(2016, 12, 25)", read={"bigquery": "SELECT DATE(2016, 12, 25)"}
)
self.validate_all(
- "SELECT CAST(CAST('2016-12-25 23:59:59' AS DATETIME) AS DATE)",
+ "SELECT CAST(CAST('2016-12-25 23:59:59' AS TIMESTAMP) AS DATE)",
read={"bigquery": "SELECT DATE(DATETIME '2016-12-25 23:59:59')"},
)
self.validate_all(
@@ -724,7 +786,7 @@ class TestDuckDB(Validator):
write={"duckdb": "SELECT (90 * INTERVAL '1' DAY)"},
)
self.validate_all(
- "SELECT ((DATE_TRUNC('DAY', CAST(CAST(DATE_TRUNC('DAY', CURRENT_TIMESTAMP) AS DATE) AS TIMESTAMP) + INTERVAL (0 - MOD((DAYOFWEEK(CAST(CAST(DATE_TRUNC('DAY', CURRENT_TIMESTAMP) AS DATE) AS TIMESTAMP)) % 7) - 1 + 7, 7)) DAY) + (7 * INTERVAL (-5) DAY))) AS t1",
+ "SELECT ((DATE_TRUNC('DAY', CAST(CAST(DATE_TRUNC('DAY', CURRENT_TIMESTAMP) AS DATE) AS TIMESTAMP) + INTERVAL (0 - (DAYOFWEEK(CAST(CAST(DATE_TRUNC('DAY', CURRENT_TIMESTAMP) AS DATE) AS TIMESTAMP)) % 7) - 1 + 7 % 7) DAY) + (7 * INTERVAL (-5) DAY))) AS t1",
read={
"presto": "SELECT ((DATE_ADD('week', -5, DATE_TRUNC('DAY', DATE_ADD('day', (0 - MOD((DAY_OF_WEEK(CAST(CAST(DATE_TRUNC('DAY', NOW()) AS DATE) AS TIMESTAMP)) % 7) - 1 + 7, 7)), CAST(CAST(DATE_TRUNC('DAY', NOW()) AS DATE) AS TIMESTAMP)))))) AS t1",
},
@@ -952,7 +1014,7 @@ class TestDuckDB(Validator):
"hive": "CAST(COL AS ARRAY<BIGINT>)",
"spark": "CAST(COL AS ARRAY<BIGINT>)",
"postgres": "CAST(COL AS BIGINT[])",
- "snowflake": "CAST(COL AS ARRAY)",
+ "snowflake": "CAST(COL AS ARRAY(BIGINT))",
},
)
self.validate_all(
diff --git a/tests/dialects/test_hive.py b/tests/dialects/test_hive.py
index b892dd6..33294ee 100644
--- a/tests/dialects/test_hive.py
+++ b/tests/dialects/test_hive.py
@@ -235,15 +235,18 @@ class TestHive(Validator):
},
)
self.validate_all(
- "'\\\\a'",
+ "'\\\\\\\\a'",
read={
+ "drill": "'\\\\\\\\a'",
+ "duckdb": "'\\\\a'",
"presto": "'\\\\a'",
},
write={
+ "drill": "'\\\\\\\\a'",
"duckdb": "'\\\\a'",
+ "hive": "'\\\\\\\\a'",
"presto": "'\\\\a'",
- "hive": "'\\\\a'",
- "spark": "'\\\\a'",
+ "spark": "'\\\\\\\\a'",
},
)
@@ -369,7 +372,7 @@ class TestHive(Validator):
"UNIX_TIMESTAMP(x)",
write={
"duckdb": "EPOCH(STRPTIME(x, '%Y-%m-%d %H:%M:%S'))",
- "presto": "TO_UNIXTIME(DATE_PARSE(x, '%Y-%m-%d %T'))",
+ "presto": "TO_UNIXTIME(COALESCE(TRY(DATE_PARSE(CAST(x AS VARCHAR), '%Y-%m-%d %T')), PARSE_DATETIME(CAST(x AS VARCHAR), 'yyyy-MM-dd HH:mm:ss')))",
"hive": "UNIX_TIMESTAMP(x)",
"spark": "UNIX_TIMESTAMP(x)",
"": "STR_TO_UNIX(x, '%Y-%m-%d %H:%M:%S')",
@@ -563,7 +566,7 @@ class TestHive(Validator):
"LOCATE('a', x, 3)",
write={
"duckdb": "STRPOS(SUBSTR(x, 3), 'a') + 3 - 1",
- "presto": "STRPOS(x, 'a', 3)",
+ "presto": "STRPOS(SUBSTR(x, 3), 'a') + 3 - 1",
"hive": "LOCATE('a', x, 3)",
"spark": "LOCATE('a', x, 3)",
},
@@ -654,15 +657,6 @@ class TestHive(Validator):
},
)
self.validate_all(
- "LOG(10, 2)",
- write={
- "duckdb": "LOG(10, 2)",
- "presto": "LOG(10, 2)",
- "hive": "LOG(10, 2)",
- "spark": "LOG(10, 2)",
- },
- )
- self.validate_all(
'ds = "2020-01-01"',
write={
"duckdb": "ds = '2020-01-01'",
@@ -745,13 +739,12 @@ class TestHive(Validator):
)
def test_escapes(self) -> None:
- self.validate_identity("'\n'")
+ self.validate_identity("'\n'", "'\\n'")
self.validate_identity("'\\n'")
- self.validate_identity("'\\\n'")
+ self.validate_identity("'\\\n'", "'\\\\\\n'")
self.validate_identity("'\\\\n'")
self.validate_identity("''")
self.validate_identity("'\\\\'")
- self.validate_identity("'\\z'")
self.validate_identity("'\\\\z'")
def test_data_type(self):
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index 5f23c44..7a9d6bf 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -86,9 +86,16 @@ class TestMySQL(Validator):
"ALTER TABLE test_table MODIFY COLUMN test_column LONGTEXT",
)
self.validate_identity(
+ "ALTER TABLE test_table MODIFY COLUMN test_column LONGTEXT",
+ )
+ self.validate_identity(
"CREATE TABLE t (c DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC",
"CREATE TABLE t (c DATETIME DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP()) DEFAULT CHARACTER SET=utf8 ROW_FORMAT=DYNAMIC",
)
+ self.validate_identity(
+ "CREATE TABLE `foo` (a VARCHAR(10), KEY idx_a (a DESC))",
+ "CREATE TABLE `foo` (a VARCHAR(10), INDEX idx_a (a DESC))",
+ )
self.validate_all(
"CREATE TABLE z (a INT) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin COMMENT='x'",
@@ -510,9 +517,8 @@ class TestMySQL(Validator):
)
def test_mysql_time(self):
- self.validate_identity("FROM_UNIXTIME(a, b)")
- self.validate_identity("FROM_UNIXTIME(a, b, c)")
self.validate_identity("TIME_STR_TO_UNIX(x)", "UNIX_TIMESTAMP(x)")
+ self.validate_identity("SELECT FROM_UNIXTIME(1711366265, '%Y %D %M')")
self.validate_all(
"SELECT TO_DAYS(x)",
write={
@@ -578,6 +584,17 @@ class TestMySQL(Validator):
self.validate_all(
"STR_TO_DATE(x, '%Y-%m-%dT%T')", write={"presto": "DATE_PARSE(x, '%Y-%m-%dT%T')"}
)
+ self.validate_all(
+ "SELECT FROM_UNIXTIME(col)",
+ read={
+ "postgres": "SELECT TO_TIMESTAMP(col)",
+ },
+ write={
+ "mysql": "SELECT FROM_UNIXTIME(col)",
+ "postgres": "SELECT TO_TIMESTAMP(col)",
+ "redshift": "SELECT (TIMESTAMP 'epoch' + col * INTERVAL '1 SECOND')",
+ },
+ )
def test_mysql(self):
self.validate_all(
@@ -723,6 +740,52 @@ class TestMySQL(Validator):
"postgres": "STRING_AGG(DISTINCT x, '' ORDER BY y DESC NULLS LAST)",
},
)
+ self.validate_all(
+ "GROUP_CONCAT(a, b, c SEPARATOR ',')",
+ write={
+ "mysql": "GROUP_CONCAT(CONCAT(a, b, c) SEPARATOR ',')",
+ "sqlite": "GROUP_CONCAT(a || b || c, ',')",
+ "tsql": "STRING_AGG(CONCAT(a, b, c), ',')",
+ "postgres": "STRING_AGG(CONCAT(a, b, c), ',')",
+ "presto": "ARRAY_JOIN(ARRAY_AGG(CONCAT(CAST(a AS VARCHAR), CAST(b AS VARCHAR), CAST(c AS VARCHAR))), ',')",
+ },
+ )
+ self.validate_all(
+ "GROUP_CONCAT(a, b, c SEPARATOR '')",
+ write={
+ "mysql": "GROUP_CONCAT(CONCAT(a, b, c) SEPARATOR '')",
+ "sqlite": "GROUP_CONCAT(a || b || c, '')",
+ "tsql": "STRING_AGG(CONCAT(a, b, c), '')",
+ "postgres": "STRING_AGG(CONCAT(a, b, c), '')",
+ },
+ )
+ self.validate_all(
+ "GROUP_CONCAT(DISTINCT a, b, c SEPARATOR '')",
+ write={
+ "mysql": "GROUP_CONCAT(DISTINCT CONCAT(a, b, c) SEPARATOR '')",
+ "sqlite": "GROUP_CONCAT(DISTINCT a || b || c, '')",
+ "tsql": "STRING_AGG(CONCAT(a, b, c), '')",
+ "postgres": "STRING_AGG(DISTINCT CONCAT(a, b, c), '')",
+ },
+ )
+ self.validate_all(
+ "GROUP_CONCAT(a, b, c ORDER BY d SEPARATOR '')",
+ write={
+ "mysql": "GROUP_CONCAT(CONCAT(a, b, c) ORDER BY d SEPARATOR '')",
+ "sqlite": "GROUP_CONCAT(a || b || c, '')",
+ "tsql": "STRING_AGG(CONCAT(a, b, c), '') WITHIN GROUP (ORDER BY d)",
+ "postgres": "STRING_AGG(CONCAT(a, b, c), '' ORDER BY d NULLS FIRST)",
+ },
+ )
+ self.validate_all(
+ "GROUP_CONCAT(DISTINCT a, b, c ORDER BY d SEPARATOR '')",
+ write={
+ "mysql": "GROUP_CONCAT(DISTINCT CONCAT(a, b, c) ORDER BY d SEPARATOR '')",
+ "sqlite": "GROUP_CONCAT(DISTINCT a || b || c, '')",
+ "tsql": "STRING_AGG(CONCAT(a, b, c), '') WITHIN GROUP (ORDER BY d)",
+ "postgres": "STRING_AGG(DISTINCT CONCAT(a, b, c), '' ORDER BY d NULLS FIRST)",
+ },
+ )
self.validate_identity(
"CREATE TABLE z (a INT) ENGINE=InnoDB AUTO_INCREMENT=1 CHARACTER SET=utf8 COLLATE=utf8_bin COMMENT='x'"
)
diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py
index 9438507..526b0b5 100644
--- a/tests/dialects/test_oracle.py
+++ b/tests/dialects/test_oracle.py
@@ -94,8 +94,21 @@ class TestOracle(Validator):
"SELECT * FROM t SAMPLE (0.25)",
)
self.validate_identity("SELECT TO_CHAR(-100, 'L99', 'NL_CURRENCY = '' AusDollars '' ')")
+ self.validate_identity(
+ "SELECT * FROM t START WITH col CONNECT BY NOCYCLE PRIOR col1 = col2"
+ )
self.validate_all(
+ "CURRENT_TIMESTAMP BETWEEN TO_DATE(f.C_SDATE, 'yyyy/mm/dd') AND TO_DATE(f.C_EDATE, 'yyyy/mm/dd')",
+ read={
+ "postgres": "CURRENT_TIMESTAMP BETWEEN TO_DATE(f.C_SDATE, 'yyyy/mm/dd') AND TO_DATE(f.C_EDATE, 'yyyy/mm/dd')",
+ },
+ write={
+ "oracle": "CURRENT_TIMESTAMP BETWEEN TO_DATE(f.C_SDATE, 'yyyy/mm/dd') AND TO_DATE(f.C_EDATE, 'yyyy/mm/dd')",
+ "postgres": "CURRENT_TIMESTAMP BETWEEN TO_DATE(f.C_SDATE, 'yyyy/mm/dd') AND TO_DATE(f.C_EDATE, 'yyyy/mm/dd')",
+ },
+ )
+ self.validate_all(
"TO_CHAR(x)",
write={
"doris": "CAST(x AS STRING)",
@@ -103,6 +116,59 @@ class TestOracle(Validator):
},
)
self.validate_all(
+ "TO_NUMBER(expr, fmt, nlsparam)",
+ read={
+ "teradata": "TO_NUMBER(expr, fmt, nlsparam)",
+ },
+ write={
+ "oracle": "TO_NUMBER(expr, fmt, nlsparam)",
+ "teradata": "TO_NUMBER(expr, fmt, nlsparam)",
+ },
+ )
+ self.validate_all(
+ "TO_NUMBER(x)",
+ write={
+ "bigquery": "CAST(x AS FLOAT64)",
+ "doris": "CAST(x AS DOUBLE)",
+ "drill": "CAST(x AS DOUBLE)",
+ "duckdb": "CAST(x AS DOUBLE)",
+ "hive": "CAST(x AS DOUBLE)",
+ "mysql": "CAST(x AS DOUBLE)",
+ "oracle": "TO_NUMBER(x)",
+ "postgres": "CAST(x AS DOUBLE PRECISION)",
+ "presto": "CAST(x AS DOUBLE)",
+ "redshift": "CAST(x AS DOUBLE PRECISION)",
+ "snowflake": "TO_NUMBER(x)",
+ "spark": "CAST(x AS DOUBLE)",
+ "spark2": "CAST(x AS DOUBLE)",
+ "starrocks": "CAST(x AS DOUBLE)",
+ "tableau": "CAST(x AS DOUBLE)",
+ "teradata": "TO_NUMBER(x)",
+ },
+ )
+ self.validate_all(
+ "TO_NUMBER(x, fmt)",
+ read={
+ "databricks": "TO_NUMBER(x, fmt)",
+ "drill": "TO_NUMBER(x, fmt)",
+ "postgres": "TO_NUMBER(x, fmt)",
+ "snowflake": "TO_NUMBER(x, fmt)",
+ "spark": "TO_NUMBER(x, fmt)",
+ "redshift": "TO_NUMBER(x, fmt)",
+ "teradata": "TO_NUMBER(x, fmt)",
+ },
+ write={
+ "databricks": "TO_NUMBER(x, fmt)",
+ "drill": "TO_NUMBER(x, fmt)",
+ "oracle": "TO_NUMBER(x, fmt)",
+ "postgres": "TO_NUMBER(x, fmt)",
+ "snowflake": "TO_NUMBER(x, fmt)",
+ "spark": "TO_NUMBER(x, fmt)",
+ "redshift": "TO_NUMBER(x, fmt)",
+ "teradata": "TO_NUMBER(x, fmt)",
+ },
+ )
+ self.validate_all(
"SELECT TO_CHAR(TIMESTAMP '1999-12-01 10:00:00')",
write={
"oracle": "SELECT TO_CHAR(CAST('1999-12-01 10:00:00' AS TIMESTAMP), 'YYYY-MM-DD HH24:MI:SS')",
@@ -210,6 +276,8 @@ class TestOracle(Validator):
self.validate_identity(
"SELECT /*+ LEADING(e j) */ * FROM employees e, departments d, job_history j WHERE e.department_id = d.department_id AND e.hire_date = j.start_date"
)
+ self.validate_identity("INSERT /*+ APPEND */ INTO IAP_TBL (id, col1) VALUES (2, 'test2')")
+ self.validate_identity("INSERT /*+ APPEND_VALUES */ INTO dest_table VALUES (i, 'Value')")
def test_xml_table(self):
self.validate_identity("XMLTABLE('x')")
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 1d0ea8b..7a41cef 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -1,4 +1,4 @@
-from sqlglot import ParseError, UnsupportedError, exp, parse_one, transpile
+from sqlglot import ParseError, UnsupportedError, exp, transpile
from sqlglot.helper import logger as helper_logger
from tests.dialects.test_dialect import Validator
@@ -12,27 +12,12 @@ class TestPostgres(Validator):
self.validate_identity("|/ x", "SQRT(x)")
self.validate_identity("||/ x", "CBRT(x)")
- expr = parse_one(
- "SELECT * FROM r CROSS JOIN LATERAL UNNEST(ARRAY[1]) AS s(location)", read="postgres"
- )
+ expr = self.parse_one("SELECT * FROM r CROSS JOIN LATERAL UNNEST(ARRAY[1]) AS s(location)")
unnest = expr.args["joins"][0].this.this
unnest.assert_is(exp.Unnest)
alter_table_only = """ALTER TABLE ONLY "Album" ADD CONSTRAINT "FK_AlbumArtistId" FOREIGN KEY ("ArtistId") REFERENCES "Artist" ("ArtistId") ON DELETE NO ACTION ON UPDATE NO ACTION"""
- expr = parse_one(alter_table_only, read="postgres")
-
- # Checks that user-defined types are parsed into DataType instead of Identifier
- parse_one("CREATE TABLE t (a udt)", read="postgres").this.expressions[0].args[
- "kind"
- ].assert_is(exp.DataType)
-
- # Checks that OID is parsed into a DataType (ObjectIdentifier)
- self.assertIsInstance(
- parse_one("CREATE TABLE public.propertydata (propertyvalue oid)", read="postgres").find(
- exp.DataType
- ),
- exp.ObjectIdentifier,
- )
+ expr = self.parse_one(alter_table_only)
self.assertIsInstance(expr, exp.AlterTable)
self.assertEqual(expr.sql(dialect="postgres"), alter_table_only)
@@ -55,13 +40,6 @@ class TestPostgres(Validator):
self.validate_identity("CAST(x AS DATEMULTIRANGE)")
self.validate_identity("SELECT ARRAY[1, 2, 3] @> ARRAY[1, 2]")
self.validate_identity("SELECT ARRAY[1, 2, 3] <@ ARRAY[1, 2]")
- self.validate_all(
- "SELECT ARRAY[1, 2, 3] && ARRAY[1, 2]",
- write={
- "": "SELECT ARRAY_OVERLAPS(ARRAY(1, 2, 3), ARRAY(1, 2))",
- "postgres": "SELECT ARRAY[1, 2, 3] && ARRAY[1, 2]",
- },
- )
self.validate_identity("x$")
self.validate_identity("SELECT ARRAY[1, 2, 3]")
self.validate_identity("SELECT ARRAY(SELECT 1)")
@@ -86,6 +64,12 @@ class TestPostgres(Validator):
self.validate_identity("SELECT CURRENT_USER")
self.validate_identity("SELECT * FROM ONLY t1")
self.validate_identity(
+ """UPDATE "x" SET "y" = CAST('0 days 60.000000 seconds' AS INTERVAL) WHERE "x"."id" IN (2, 3)"""
+ )
+ self.validate_identity(
+ "WITH t1 AS MATERIALIZED (SELECT 1), t2 AS NOT MATERIALIZED (SELECT 2) SELECT * FROM t1, t2"
+ )
+ self.validate_identity(
"""LAST_VALUE("col1") OVER (ORDER BY "col2" RANGE BETWEEN INTERVAL '1 DAY' PRECEDING AND '1 month' FOLLOWING)"""
)
self.validate_identity(
@@ -107,9 +91,6 @@ class TestPostgres(Validator):
"SELECT SUM(x) OVER a, SUM(y) OVER b FROM c WINDOW a AS (PARTITION BY d), b AS (PARTITION BY e)"
)
self.validate_identity(
- "CREATE TABLE A (LIKE B INCLUDING CONSTRAINT INCLUDING COMPRESSION EXCLUDING COMMENTS)"
- )
- self.validate_identity(
"SELECT CASE WHEN SUBSTRING('abcdefg' FROM 1) IN ('ab') THEN 1 ELSE 0 END"
)
self.validate_identity(
@@ -167,6 +148,10 @@ class TestPostgres(Validator):
"SELECT 'Dianne''s horse'",
)
self.validate_identity(
+ "SELECT $$The price is $9.95$$ AS msg",
+ "SELECT 'The price is $9.95' AS msg",
+ )
+ self.validate_identity(
"COMMENT ON TABLE mytable IS $$doc this$$", "COMMENT ON TABLE mytable IS 'doc this'"
)
self.validate_identity(
@@ -329,6 +314,36 @@ class TestPostgres(Validator):
self.validate_identity("SELECT * FROM t1*", "SELECT * FROM t1")
self.validate_all(
+ 'SELECT * FROM "test_table" ORDER BY RANDOM() LIMIT 5',
+ write={
+ "bigquery": "SELECT * FROM `test_table` ORDER BY RAND() NULLS LAST LIMIT 5",
+ "duckdb": 'SELECT * FROM "test_table" ORDER BY RANDOM() LIMIT 5',
+ "postgres": 'SELECT * FROM "test_table" ORDER BY RANDOM() LIMIT 5',
+ "tsql": "SELECT TOP 5 * FROM [test_table] ORDER BY RAND()",
+ },
+ )
+ self.validate_all(
+ "SELECT (data -> 'en-US') AS acat FROM my_table",
+ write={
+ "duckdb": """SELECT (data -> '$."en-US"') AS acat FROM my_table""",
+ "postgres": "SELECT (data -> 'en-US') AS acat FROM my_table",
+ },
+ )
+ self.validate_all(
+ "SELECT (data ->> 'en-US') AS acat FROM my_table",
+ write={
+ "duckdb": """SELECT (data ->> '$."en-US"') AS acat FROM my_table""",
+ "postgres": "SELECT (data ->> 'en-US') AS acat FROM my_table",
+ },
+ )
+ self.validate_all(
+ "SELECT ARRAY[1, 2, 3] && ARRAY[1, 2]",
+ write={
+ "": "SELECT ARRAY_OVERLAPS(ARRAY(1, 2, 3), ARRAY(1, 2))",
+ "postgres": "SELECT ARRAY[1, 2, 3] && ARRAY[1, 2]",
+ },
+ )
+ self.validate_all(
"SELECT JSON_EXTRACT_PATH_TEXT(x, k1, k2, k3) FROM t",
read={
"clickhouse": "SELECT JSONExtractString(x, k1, k2, k3) FROM t",
@@ -518,15 +533,6 @@ class TestPostgres(Validator):
},
)
self.validate_all(
- "CREATE TABLE x (a UUID, b BYTEA)",
- write={
- "duckdb": "CREATE TABLE x (a UUID, b BLOB)",
- "presto": "CREATE TABLE x (a UUID, b VARBINARY)",
- "hive": "CREATE TABLE x (a UUID, b BINARY)",
- "spark": "CREATE TABLE x (a UUID, b BINARY)",
- },
- )
- self.validate_all(
"SELECT * FROM x FETCH 1 ROW",
write={
"postgres": "SELECT * FROM x FETCH FIRST 1 ROWS ONLY",
@@ -635,11 +641,30 @@ class TestPostgres(Validator):
"postgres": "x / y ^ z",
},
)
-
- self.assertIsInstance(parse_one("id::UUID", read="postgres"), exp.Cast)
+ self.validate_all(
+ "CAST(x AS NAME)",
+ read={
+ "redshift": "CAST(x AS NAME)",
+ },
+ write={
+ "postgres": "CAST(x AS NAME)",
+ "redshift": "CAST(x AS NAME)",
+ },
+ )
+ self.assertIsInstance(self.parse_one("id::UUID"), exp.Cast)
def test_ddl(self):
- expr = parse_one("CREATE TABLE t (x INTERVAL day)", read="postgres")
+ # Checks that user-defined types are parsed into DataType instead of Identifier
+ self.parse_one("CREATE TABLE t (a udt)").this.expressions[0].args["kind"].assert_is(
+ exp.DataType
+ )
+
+ # Checks that OID is parsed into a DataType (ObjectIdentifier)
+ self.assertIsInstance(
+ self.parse_one("CREATE TABLE p.t (c oid)").find(exp.DataType), exp.ObjectIdentifier
+ )
+
+ expr = self.parse_one("CREATE TABLE t (x INTERVAL day)")
cdef = expr.find(exp.ColumnDef)
cdef.args["kind"].assert_is(exp.DataType)
self.assertEqual(expr.sql(dialect="postgres"), "CREATE TABLE t (x INTERVAL DAY)")
@@ -667,6 +692,21 @@ class TestPostgres(Validator):
self.validate_identity("TRUNCATE TABLE t1 CONTINUE IDENTITY CASCADE")
self.validate_identity("TRUNCATE TABLE t1 RESTART IDENTITY RESTRICT")
self.validate_identity(
+ "CREATE TABLE t (vid INT NOT NULL, CONSTRAINT ht_vid_nid_fid_idx EXCLUDE (INT4RANGE(vid, nid) WITH &&, INT4RANGE(fid, fid, '[]') WITH &&))"
+ )
+ self.validate_identity(
+ "CREATE TABLE t (i INT, PRIMARY KEY (i), EXCLUDE USING gist(col varchar_pattern_ops DESC NULLS LAST WITH &&) WITH (sp1=1, sp2=2))"
+ )
+ self.validate_identity(
+ "CREATE TABLE t (i INT, EXCLUDE USING btree(INT4RANGE(vid, nid, '[]') ASC NULLS FIRST WITH &&) INCLUDE (col1, col2))"
+ )
+ self.validate_identity(
+ "CREATE TABLE t (i INT, EXCLUDE USING gin(col1 WITH &&, col2 WITH ||) USING INDEX TABLESPACE tablespace WHERE (id > 5))"
+ )
+ self.validate_identity(
+ "CREATE TABLE A (LIKE B INCLUDING CONSTRAINT INCLUDING COMPRESSION EXCLUDING COMMENTS)"
+ )
+ self.validate_identity(
"CREATE TABLE cust_part3 PARTITION OF customers FOR VALUES WITH (MODULUS 3, REMAINDER 2)"
)
self.validate_identity(
@@ -691,13 +731,13 @@ class TestPostgres(Validator):
"CREATE INDEX index_issues_on_title_trigram ON public.issues USING gin(title public.gin_trgm_ops)"
)
self.validate_identity(
- "INSERT INTO x VALUES (1, 'a', 2.0) ON CONFLICT (id) DO NOTHING RETURNING *"
+ "INSERT INTO x VALUES (1, 'a', 2.0) ON CONFLICT(id) DO NOTHING RETURNING *"
)
self.validate_identity(
- "INSERT INTO x VALUES (1, 'a', 2.0) ON CONFLICT (id) DO UPDATE SET x.id = 1 RETURNING *"
+ "INSERT INTO x VALUES (1, 'a', 2.0) ON CONFLICT(id) DO UPDATE SET x.id = 1 RETURNING *"
)
self.validate_identity(
- "INSERT INTO x VALUES (1, 'a', 2.0) ON CONFLICT (id) DO UPDATE SET x.id = excluded.id RETURNING *"
+ "INSERT INTO x VALUES (1, 'a', 2.0) ON CONFLICT(id) DO UPDATE SET x.id = excluded.id RETURNING *"
)
self.validate_identity(
"INSERT INTO x VALUES (1, 'a', 2.0) ON CONFLICT ON CONSTRAINT pkey DO NOTHING RETURNING *"
@@ -740,8 +780,7 @@ class TestPostgres(Validator):
check_command_warning=True,
)
self.validate_identity(
- "CREATE UNLOGGED TABLE foo AS WITH t(c) AS (SELECT 1) SELECT * FROM (SELECT c AS c FROM t) AS temp",
- check_command_warning=True,
+ "CREATE UNLOGGED TABLE foo AS WITH t(c) AS (SELECT 1) SELECT * FROM (SELECT c AS c FROM t) AS temp"
)
self.validate_identity(
"CREATE FUNCTION x(INT) RETURNS INT SET search_path TO 'public'",
@@ -793,7 +832,7 @@ class TestPostgres(Validator):
]))
)
""",
- "CREATE INDEX index_ci_builds_on_commit_id_and_artifacts_expireatandidpartial ON public.ci_builds USING btree(commit_id, artifacts_expire_at, id) WHERE ((CAST((type) AS TEXT) = CAST('Ci::Build' AS TEXT)) AND ((retried = FALSE) OR (retried IS NULL)) AND (CAST((name) AS TEXT) = ANY (ARRAY[CAST((CAST('sast' AS VARCHAR)) AS TEXT), CAST((CAST('dependency_scanning' AS VARCHAR)) AS TEXT), CAST((CAST('sast:container' AS VARCHAR)) AS TEXT), CAST((CAST('container_scanning' AS VARCHAR)) AS TEXT), CAST((CAST('dast' AS VARCHAR)) AS TEXT)])))",
+ "CREATE INDEX index_ci_builds_on_commit_id_and_artifacts_expireatandidpartial ON public.ci_builds USING btree(commit_id, artifacts_expire_at, id) WHERE ((CAST((type) AS TEXT) = CAST('Ci::Build' AS TEXT)) AND ((retried = FALSE) OR (retried IS NULL)) AND (CAST((name) AS TEXT) = ANY(ARRAY[CAST((CAST('sast' AS VARCHAR)) AS TEXT), CAST((CAST('dependency_scanning' AS VARCHAR)) AS TEXT), CAST((CAST('sast:container' AS VARCHAR)) AS TEXT), CAST((CAST('container_scanning' AS VARCHAR)) AS TEXT), CAST((CAST('dast' AS VARCHAR)) AS TEXT)])))",
)
self.validate_identity(
"CREATE INDEX index_ci_pipelines_on_project_idandrefandiddesc ON public.ci_pipelines USING btree(project_id, ref, id DESC)"
@@ -803,6 +842,16 @@ class TestPostgres(Validator):
"TRUNCATE TABLE ONLY t1, t2, ONLY t3, t4, t5 RESTART IDENTITY CASCADE",
)
+ self.validate_all(
+ "CREATE TABLE x (a UUID, b BYTEA)",
+ write={
+ "duckdb": "CREATE TABLE x (a UUID, b BLOB)",
+ "presto": "CREATE TABLE x (a UUID, b VARBINARY)",
+ "hive": "CREATE TABLE x (a UUID, b BINARY)",
+ "spark": "CREATE TABLE x (a UUID, b BINARY)",
+ },
+ )
+
with self.assertRaises(ParseError):
transpile("CREATE TABLE products (price DECIMAL CHECK price > 0)", read="postgres")
with self.assertRaises(ParseError):
@@ -857,7 +906,7 @@ class TestPostgres(Validator):
)
def test_operator(self):
- expr = parse_one("1 OPERATOR(+) 2 OPERATOR(*) 3", read="postgres")
+ expr = self.parse_one("1 OPERATOR(+) 2 OPERATOR(*) 3")
expr.left.assert_is(exp.Operator)
expr.left.left.assert_is(exp.Literal)
@@ -926,8 +975,8 @@ class TestPostgres(Validator):
def test_regexp_binary(self):
"""See https://github.com/tobymao/sqlglot/pull/2404 for details."""
- self.assertIsInstance(parse_one("'thomas' ~ '.*thomas.*'", read="postgres"), exp.Binary)
- self.assertIsInstance(parse_one("'thomas' ~* '.*thomas.*'", read="postgres"), exp.Binary)
+ self.assertIsInstance(self.parse_one("'thomas' ~ '.*thomas.*'"), exp.Binary)
+ self.assertIsInstance(self.parse_one("'thomas' ~* '.*thomas.*'"), exp.Binary)
def test_unnest_json_array(self):
trino_input = """
diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py
index 2ea595e..2162499 100644
--- a/tests/dialects/test_presto.py
+++ b/tests/dialects/test_presto.py
@@ -63,7 +63,7 @@ class TestPresto(Validator):
"duckdb": "CAST(a AS INT[])",
"presto": "CAST(a AS ARRAY(INTEGER))",
"spark": "CAST(a AS ARRAY<INT>)",
- "snowflake": "CAST(a AS ARRAY)",
+ "snowflake": "CAST(a AS ARRAY(INT))",
},
)
self.validate_all(
@@ -82,18 +82,17 @@ class TestPresto(Validator):
"duckdb": "CAST([1, 2] AS BIGINT[])",
"presto": "CAST(ARRAY[1, 2] AS ARRAY(BIGINT))",
"spark": "CAST(ARRAY(1, 2) AS ARRAY<BIGINT>)",
- "snowflake": "CAST([1, 2] AS ARRAY)",
+ "snowflake": "CAST([1, 2] AS ARRAY(BIGINT))",
},
)
self.validate_all(
- "CAST(MAP(ARRAY[1], ARRAY[1]) AS MAP(INT,INT))",
+ "CAST(MAP(ARRAY['key'], ARRAY[1]) AS MAP(VARCHAR, INT))",
write={
- "bigquery": "CAST(MAP([1], [1]) AS MAP<INT64, INT64>)",
- "duckdb": "CAST(MAP([1], [1]) AS MAP(INT, INT))",
- "presto": "CAST(MAP(ARRAY[1], ARRAY[1]) AS MAP(INTEGER, INTEGER))",
- "hive": "CAST(MAP(1, 1) AS MAP<INT, INT>)",
- "spark": "CAST(MAP_FROM_ARRAYS(ARRAY(1), ARRAY(1)) AS MAP<INT, INT>)",
- "snowflake": "CAST(OBJECT_CONSTRUCT(1, 1) AS OBJECT)",
+ "duckdb": "CAST(MAP(['key'], [1]) AS MAP(TEXT, INT))",
+ "presto": "CAST(MAP(ARRAY['key'], ARRAY[1]) AS MAP(VARCHAR, INTEGER))",
+ "hive": "CAST(MAP('key', 1) AS MAP<STRING, INT>)",
+ "snowflake": "CAST(OBJECT_CONSTRUCT('key', 1) AS MAP(VARCHAR, INT))",
+ "spark": "CAST(MAP_FROM_ARRAYS(ARRAY('key'), ARRAY(1)) AS MAP<STRING, INT>)",
},
)
self.validate_all(
@@ -104,7 +103,7 @@ class TestPresto(Validator):
"presto": "CAST(MAP(ARRAY['a', 'b', 'c'], ARRAY[ARRAY[1], ARRAY[2], ARRAY[3]]) AS MAP(VARCHAR, ARRAY(INTEGER)))",
"hive": "CAST(MAP('a', ARRAY(1), 'b', ARRAY(2), 'c', ARRAY(3)) AS MAP<STRING, ARRAY<INT>>)",
"spark": "CAST(MAP_FROM_ARRAYS(ARRAY('a', 'b', 'c'), ARRAY(ARRAY(1), ARRAY(2), ARRAY(3))) AS MAP<STRING, ARRAY<INT>>)",
- "snowflake": "CAST(OBJECT_CONSTRUCT('a', [1], 'b', [2], 'c', [3]) AS OBJECT)",
+ "snowflake": "CAST(OBJECT_CONSTRUCT('a', [1], 'b', [2], 'c', [3]) AS MAP(VARCHAR, ARRAY(INT)))",
},
)
self.validate_all(
@@ -178,6 +177,17 @@ class TestPresto(Validator):
"spark": "ARRAY_JOIN(x, '-', 'a')",
},
)
+ self.validate_all(
+ "STRPOS('ABC', 'A', 3)",
+ read={
+ "trino": "STRPOS('ABC', 'A', 3)",
+ },
+ write={
+ "presto": "STRPOS('ABC', 'A', 3)",
+ "trino": "STRPOS('ABC', 'A', 3)",
+ "snowflake": "POSITION('A', 'ABC')",
+ },
+ )
def test_interval_plural_to_singular(self):
# Microseconds, weeks and quarters are not supported in Presto/Trino INTERVAL literals
diff --git a/tests/dialects/test_prql.py b/tests/dialects/test_prql.py
new file mode 100644
index 0000000..9a42d0c
--- /dev/null
+++ b/tests/dialects/test_prql.py
@@ -0,0 +1,17 @@
+from tests.dialects.test_dialect import Validator
+
+
+class TestPRQL(Validator):
+ dialect = "prql"
+
+ def test_prql(self):
+ self.validate_identity("FROM x", "SELECT * FROM x")
+ self.validate_identity("FROM x DERIVE a + 1", "SELECT *, a + 1 FROM x")
+ self.validate_identity("FROM x DERIVE x = a + 1", "SELECT *, a + 1 AS x FROM x")
+ self.validate_identity("FROM x DERIVE {a + 1}", "SELECT *, a + 1 FROM x")
+ self.validate_identity("FROM x DERIVE {x = a + 1, b}", "SELECT *, a + 1 AS x, b FROM x")
+ self.validate_identity("FROM x TAKE 10", "SELECT * FROM x LIMIT 10")
+ self.validate_identity("FROM x TAKE 10 TAKE 5", "SELECT * FROM x LIMIT 5")
+ self.validate_identity(
+ "FROM x DERIVE {x = a + 1, b} SELECT {y = x, 2}", "SELECT a + 1 AS y, 2 FROM x"
+ )
diff --git a/tests/dialects/test_redshift.py b/tests/dialects/test_redshift.py
index 506f429..a91f4f9 100644
--- a/tests/dialects/test_redshift.py
+++ b/tests/dialects/test_redshift.py
@@ -139,6 +139,15 @@ class TestRedshift(Validator):
"presto": "LENGTH(x)",
},
)
+ self.validate_all(
+ "x LIKE 'abc' || '%'",
+ read={
+ "duckdb": "STARTS_WITH(x, 'abc')",
+ },
+ write={
+ "redshift": "x LIKE 'abc' || '%'",
+ },
+ )
self.validate_all(
"SELECT SYSDATE",
@@ -204,18 +213,6 @@ class TestRedshift(Validator):
},
)
self.validate_all(
- "SELECT * FROM venue WHERE (venuecity, venuestate) IN (('Miami', 'FL'), ('Tampa', 'FL')) ORDER BY venueid",
- write={
- "redshift": "SELECT * FROM venue WHERE (venuecity, venuestate) IN (('Miami', 'FL'), ('Tampa', 'FL')) ORDER BY venueid",
- },
- )
- self.validate_all(
- 'SELECT tablename, "column" FROM pg_table_def WHERE "column" LIKE \'%start\\_%\' LIMIT 5',
- write={
- "redshift": 'SELECT tablename, "column" FROM pg_table_def WHERE "column" LIKE \'%start\\_%\' LIMIT 5'
- },
- )
- self.validate_all(
"SELECT DISTINCT ON (a) a, b FROM x ORDER BY c DESC",
write={
"bigquery": "SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC NULLS FIRST) AS _row_number FROM x) AS _t WHERE _row_number = 1",
@@ -293,6 +290,7 @@ class TestRedshift(Validator):
)
def test_identity(self):
+ self.validate_identity("LISTAGG(DISTINCT foo, ', ')")
self.validate_identity("CREATE MATERIALIZED VIEW orders AUTO REFRESH YES AS SELECT 1")
self.validate_identity("SELECT DATEADD(DAY, 1, 'today')")
self.validate_identity("SELECT * FROM #x")
@@ -306,6 +304,12 @@ class TestRedshift(Validator):
self.validate_identity("SELECT APPROXIMATE AS y")
self.validate_identity("CREATE TABLE t (c BIGINT IDENTITY(0, 1))")
self.validate_identity(
+ "SELECT * FROM venue WHERE (venuecity, venuestate) IN (('Miami', 'FL'), ('Tampa', 'FL')) ORDER BY venueid"
+ )
+ self.validate_identity(
+ """SELECT tablename, "column" FROM pg_table_def WHERE "column" LIKE '%start\\\\_%' LIMIT 5"""
+ )
+ self.validate_identity(
"""SELECT JSON_EXTRACT_PATH_TEXT('{"f2":{"f3":1},"f4":{"f5":99,"f6":"star"}', 'f4', 'f6', TRUE)"""
)
self.validate_identity(
@@ -463,6 +467,10 @@ FROM (
"": "INSERT INTO t(a, b) SELECT a, b FROM (VALUES (1, 2), (3, 4)) AS t (a, b)",
},
)
+ self.validate_identity("CREATE TABLE table_backup BACKUP NO AS SELECT * FROM event")
+ self.validate_identity("CREATE TABLE table_backup BACKUP YES AS SELECT * FROM event")
+ self.validate_identity("CREATE TABLE table_backup (i INTEGER, b VARCHAR) BACKUP NO")
+ self.validate_identity("CREATE TABLE table_backup (i INTEGER, b VARCHAR) BACKUP YES")
def test_create_table_like(self):
self.validate_identity(
@@ -499,7 +507,11 @@ FROM (
def test_varchar_max(self):
self.validate_all(
- "CREATE TABLE TEST (cola VARCHAR(MAX))",
+ 'CREATE TABLE "TEST" ("cola" VARCHAR(MAX))',
+ read={
+ "redshift": "CREATE TABLE TEST (cola VARCHAR(max))",
+ "tsql": "CREATE TABLE TEST (cola VARCHAR(max))",
+ },
write={
"redshift": 'CREATE TABLE "TEST" ("cola" VARCHAR(MAX))',
},
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index e48f811..a41d35a 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -40,6 +40,19 @@ WHERE
)""",
)
+ self.validate_identity("SELECT TIMEADD(HOUR, 2, CAST('09:05:03' AS TIME))")
+ self.validate_identity("SELECT CAST(OBJECT_CONSTRUCT('a', 1) AS MAP(VARCHAR, INT))")
+ self.validate_identity("SELECT CAST(OBJECT_CONSTRUCT('a', 1) AS OBJECT(a CHAR NOT NULL))")
+ self.validate_identity("SELECT CAST([1, 2, 3] AS ARRAY(INT))")
+ self.validate_identity("SELECT CAST(obj AS OBJECT(x CHAR) RENAME FIELDS)")
+ self.validate_identity("SELECT CAST(obj AS OBJECT(x CHAR, y VARCHAR) ADD FIELDS)")
+ self.validate_identity("SELECT TO_TIMESTAMP(123.4)").selects[0].assert_is(exp.Anonymous)
+ self.validate_identity("SELECT TO_TIME(x) FROM t")
+ self.validate_identity("SELECT TO_TIMESTAMP(x) FROM t")
+ self.validate_identity("SELECT TO_TIMESTAMP_NTZ(x) FROM t")
+ self.validate_identity("SELECT TO_TIMESTAMP_LTZ(x) FROM t")
+ self.validate_identity("SELECT TO_TIMESTAMP_TZ(x) FROM t")
+ self.validate_identity("TO_DECIMAL(expr, fmt, precision, scale)")
self.validate_identity("ALTER TABLE authors ADD CONSTRAINT c1 UNIQUE (id, email)")
self.validate_identity("RM @parquet_stage", check_command_warning=True)
self.validate_identity("REMOVE @parquet_stage", check_command_warning=True)
@@ -59,7 +72,6 @@ WHERE
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'")
self.validate_identity("SELECT HLL(*)")
self.validate_identity("SELECT HLL(a)")
@@ -77,18 +89,29 @@ WHERE
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("REGEXP_REPLACE('target', 'pattern', '\n')")
self.validate_identity("ALTER TABLE a SWAP WITH b")
+ self.validate_identity("SELECT MATCH_CONDITION")
self.validate_identity(
'DESCRIBE TABLE "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF100TCL"."WEB_SITE" type=stage'
)
self.validate_identity(
"SELECT a FROM test PIVOT(SUM(x) FOR y IN ('z', 'q')) AS x TABLESAMPLE (0.1)"
)
+ self.validate_identity(
+ "SELECT * FROM DATA AS DATA_L ASOF JOIN DATA AS DATA_R MATCH_CONDITION (DATA_L.VAL > DATA_R.VAL) ON DATA_L.ID = DATA_R.ID"
+ )
+ self.validate_identity(
+ "REGEXP_REPLACE('target', 'pattern', '\n')",
+ "REGEXP_REPLACE('target', 'pattern', '\\n')",
+ )
+ self.validate_identity(
+ "SELECT a:from::STRING, a:from || ' test' ",
+ "SELECT CAST(GET_PATH(a, 'from') AS TEXT), GET_PATH(a, 'from') || ' test'",
+ )
self.validate_identity("x:from", "GET_PATH(x, 'from')")
self.validate_identity(
- "value:values::string",
- "CAST(GET_PATH(value, 'values') AS TEXT)",
+ "value:values::string::int",
+ "CAST(CAST(GET_PATH(value, 'values') AS TEXT) AS INT)",
)
self.validate_identity(
"""SELECT GET_PATH(PARSE_JSON('{"y": [{"z": 1}]}'), 'y[0]:z')""",
@@ -132,7 +155,11 @@ WHERE
)
self.validate_identity(
"v:attr[0]:name",
- "GET_PATH(GET_PATH(v, 'attr[0]'), 'name')",
+ "GET_PATH(v, 'attr[0].name')",
+ )
+ self.validate_identity(
+ "a.x:from.b:c.d::int",
+ "CAST(GET_PATH(a.x, 'from.b.c.d') AS INT)",
)
self.validate_identity(
"""SELECT PARSE_JSON('{"food":{"fruit":"banana"}}'):food.fruit::VARCHAR""",
@@ -190,10 +217,6 @@ WHERE
"SELECT CEIL(5.3)",
)
self.validate_identity(
- "SELECT TO_TIMESTAMP(x) FROM t",
- "SELECT CAST(x AS TIMESTAMPNTZ) FROM t",
- )
- self.validate_identity(
"CAST(x AS BYTEINT)",
"CAST(x AS INT)",
)
@@ -380,6 +403,7 @@ WHERE
write={
"duckdb": "{'a': b, 'c': d}",
"snowflake": "OBJECT_CONSTRUCT('a', b, 'c', d)",
+ "": "STRUCT(b AS a, d AS c)",
},
)
self.validate_identity("OBJECT_CONSTRUCT(a, b, c, d)")
@@ -419,6 +443,46 @@ WHERE
"sqlite": "SELECT MIN(c1), MIN(c2) FROM test",
},
)
+ for suffix in (
+ "",
+ " OVER ()",
+ ):
+ self.validate_all(
+ f"SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x){suffix}",
+ read={
+ "snowflake": f"SELECT MEDIAN(x){suffix}",
+ "postgres": f"SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x){suffix}",
+ },
+ write={
+ "": f"SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x NULLS LAST){suffix}",
+ "duckdb": f"SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x){suffix}",
+ "postgres": f"SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x){suffix}",
+ "snowflake": f"SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x){suffix}",
+ },
+ )
+ self.validate_all(
+ f"SELECT MEDIAN(x){suffix}",
+ write={
+ "": f"SELECT PERCENTILE_CONT(x, 0.5){suffix}",
+ "duckdb": f"SELECT QUANTILE_CONT(x, 0.5){suffix}",
+ "postgres": f"SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x){suffix}",
+ "snowflake": f"SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x){suffix}",
+ },
+ )
+ for func in (
+ "CORR",
+ "COVAR_POP",
+ "COVAR_SAMP",
+ ):
+ self.validate_all(
+ f"SELECT {func}(y, x){suffix}",
+ write={
+ "": f"SELECT {func}(y, x){suffix}",
+ "duckdb": f"SELECT {func}(y, x){suffix}",
+ "postgres": f"SELECT {func}(y, x){suffix}",
+ "snowflake": f"SELECT {func}(y, x){suffix}",
+ },
+ )
self.validate_all(
"TO_CHAR(x, y)",
read={
@@ -560,9 +624,9 @@ WHERE
self.validate_all(
'''SELECT PARSE_JSON('{"a": {"b c": "foo"}}'):a:"b c"''',
write={
- "duckdb": """SELECT JSON('{"a": {"b c": "foo"}}') -> '$.a' -> '$."b c"'""",
- "mysql": """SELECT JSON_EXTRACT(JSON_EXTRACT('{"a": {"b c": "foo"}}', '$.a'), '$."b c"')""",
- "snowflake": """SELECT GET_PATH(GET_PATH(PARSE_JSON('{"a": {"b c": "foo"}}'), 'a'), '["b c"]')""",
+ "duckdb": """SELECT JSON('{"a": {"b c": "foo"}}') -> '$.a."b c"'""",
+ "mysql": """SELECT JSON_EXTRACT('{"a": {"b c": "foo"}}', '$.a."b c"')""",
+ "snowflake": """SELECT GET_PATH(PARSE_JSON('{"a": {"b c": "foo"}}'), 'a["b c"]')""",
},
)
self.validate_all(
@@ -623,9 +687,16 @@ WHERE
self.validate_all(
"SELECT TO_TIMESTAMP('2013-04-05 01:02:03')",
write={
- "bigquery": "SELECT PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S', '2013-04-05 01:02:03')",
- "snowflake": "SELECT TO_TIMESTAMP('2013-04-05 01:02:03', 'yyyy-mm-DD hh24:mi:ss')",
- "spark": "SELECT TO_TIMESTAMP('2013-04-05 01:02:03', 'yyyy-MM-dd HH:mm:ss')",
+ "bigquery": "SELECT CAST('2013-04-05 01:02:03' AS DATETIME)",
+ "snowflake": "SELECT CAST('2013-04-05 01:02:03' AS TIMESTAMPNTZ)",
+ "spark": "SELECT CAST('2013-04-05 01:02:03' AS TIMESTAMP)",
+ },
+ )
+ self.validate_all(
+ "SELECT TO_TIME('12:05:00')",
+ write={
+ "bigquery": "SELECT CAST('12:05:00' AS TIME)",
+ "snowflake": "SELECT CAST('12:05:00' AS TIME)",
},
)
self.validate_all(
@@ -667,9 +738,13 @@ WHERE
)
self.validate_all(
"ARRAY_TO_STRING(x, '')",
+ read={
+ "duckdb": "ARRAY_TO_STRING(x, '')",
+ },
write={
"spark": "ARRAY_JOIN(x, '')",
"snowflake": "ARRAY_TO_STRING(x, '')",
+ "duckdb": "ARRAY_TO_STRING(x, '')",
},
)
self.validate_all(
@@ -930,6 +1005,9 @@ WHERE
)
self.validate_all(
"DATEADD(DAY, 5, CAST('2008-12-25' AS DATE))",
+ read={
+ "snowflake": "TIMESTAMPADD(DAY, 5, CAST('2008-12-25' AS DATE))",
+ },
write={
"bigquery": "DATE_ADD(CAST('2008-12-25' AS DATE), INTERVAL 5 DAY)",
"snowflake": "DATEADD(DAY, 5, CAST('2008-12-25' AS DATE))",
@@ -952,6 +1030,46 @@ WHERE
self.validate_identity("DATE_PART(yyy, x)", "DATE_PART(YEAR, x)")
self.validate_identity("DATE_TRUNC(yr, x)", "DATE_TRUNC('YEAR', x)")
+ self.validate_identity("TO_DATE('12345')").assert_is(exp.Anonymous)
+
+ self.validate_identity(
+ "SELECT TO_DATE('2019-02-28') + INTERVAL '1 day, 1 year'",
+ "SELECT CAST('2019-02-28' AS DATE) + INTERVAL '1 day, 1 year'",
+ )
+
+ self.validate_identity("DATE(x)").assert_is(exp.Anonymous)
+ self.validate_identity("TO_DATE(x)").assert_is(exp.Anonymous)
+ self.validate_identity("TRY_TO_DATE(x)").assert_is(exp.Anonymous)
+
+ self.validate_all(
+ "TO_DATE(x, 'MM-DD-YYYY')",
+ write={
+ "snowflake": "TO_DATE(x, 'mm-DD-yyyy')",
+ "duckdb": "CAST(STRPTIME(x, '%m-%d-%Y') AS DATE)",
+ },
+ )
+ self.validate_all(
+ "DATE('01-01-2000', 'MM-DD-YYYY')",
+ write={
+ "snowflake": "TO_DATE('01-01-2000', 'mm-DD-yyyy')",
+ "duckdb": "CAST(STRPTIME('01-01-2000', '%m-%d-%Y') AS DATE)",
+ },
+ )
+ self.validate_all(
+ "TO_DATE('01-01-2000', 'MM-DD-YYYY')",
+ write={
+ "snowflake": "TO_DATE('01-01-2000', 'mm-DD-yyyy')",
+ "duckdb": "CAST(STRPTIME('01-01-2000', '%m-%d-%Y') AS DATE)",
+ },
+ )
+ self.validate_all(
+ "TRY_TO_DATE('01-01-2000', 'MM-DD-YYYY')",
+ write={
+ "snowflake": "TRY_TO_DATE('01-01-2000', 'mm-DD-yyyy')",
+ "duckdb": "CAST(STRPTIME('01-01-2000', '%m-%d-%Y') AS DATE)",
+ },
+ )
+
def test_semi_structured_types(self):
self.validate_identity("SELECT CAST(a AS VARIANT)")
self.validate_identity("SELECT CAST(a AS ARRAY)")
@@ -1047,6 +1165,9 @@ WHERE
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(
+ "DROP function my_udf (OBJECT(city VARCHAR, zipcode DECIMAL, val ARRAY(BOOLEAN)))"
+ )
+ 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(
@@ -1061,6 +1182,17 @@ WHERE
self.validate_identity(
"CREATE OR REPLACE TABLE EXAMPLE_DB.DEMO.USERS (ID DECIMAL(38, 0) NOT NULL, PRIMARY KEY (ID), FOREIGN KEY (CITY_CODE) REFERENCES EXAMPLE_DB.DEMO.CITIES (CITY_CODE))"
)
+ self.validate_identity(
+ "CREATE ICEBERG TABLE my_iceberg_table (amount ARRAY(INT)) CATALOG='SNOWFLAKE' EXTERNAL_VOLUME='my_external_volume' BASE_LOCATION='my/relative/path/from/extvol'"
+ )
+ self.validate_identity(
+ "CREATE OR REPLACE FUNCTION my_udf(location OBJECT(city VARCHAR, zipcode DECIMAL, val ARRAY(BOOLEAN))) RETURNS VARCHAR AS $$ SELECT 'foo' $$",
+ "CREATE OR REPLACE FUNCTION my_udf(location OBJECT(city VARCHAR, zipcode DECIMAL, val ARRAY(BOOLEAN))) RETURNS VARCHAR AS ' SELECT \\'foo\\' '",
+ )
+ self.validate_identity(
+ "CREATE OR REPLACE FUNCTION my_udtf(foo BOOLEAN) RETURNS TABLE(col1 ARRAY(INT)) AS $$ WITH t AS (SELECT CAST([1, 2, 3] AS ARRAY(INT)) AS c) SELECT c FROM t $$",
+ "CREATE OR REPLACE FUNCTION my_udtf(foo BOOLEAN) RETURNS TABLE (col1 ARRAY(INT)) AS ' WITH t AS (SELECT CAST([1, 2, 3] AS ARRAY(INT)) AS c) SELECT c FROM t '",
+ )
self.validate_all(
"CREATE TABLE orders_clone CLONE orders",
@@ -1292,7 +1424,6 @@ FROM persons AS p, LATERAL FLATTEN(input => p.c, path => 'contact') AS _flattene
"spark": "SELECT `c0`, `c1` FROM (VALUES (1, 2), (3, 4)) AS `t0`(`c0`, `c1`)",
},
)
-
self.validate_all(
"""SELECT $1 AS "_1" FROM VALUES ('a'), ('b')""",
write={
@@ -1300,6 +1431,18 @@ FROM persons AS p, LATERAL FLATTEN(input => p.c, path => 'contact') AS _flattene
"spark": """SELECT ${1} AS `_1` FROM VALUES ('a'), ('b')""",
},
)
+ self.validate_all(
+ "SELECT * FROM (SELECT OBJECT_CONSTRUCT('a', 1) AS x) AS t",
+ read={
+ "duckdb": "SELECT * FROM (VALUES ({'a': 1})) AS t(x)",
+ },
+ )
+ self.validate_all(
+ "SELECT * FROM (SELECT OBJECT_CONSTRUCT('a', 1) AS x UNION ALL SELECT OBJECT_CONSTRUCT('a', 2)) AS t",
+ read={
+ "duckdb": "SELECT * FROM (VALUES ({'a': 1}), ({'a': 2})) AS t(x)",
+ },
+ )
def test_describe_table(self):
self.validate_all(
diff --git a/tests/dialects/test_spark.py b/tests/dialects/test_spark.py
index 1cf1ede..18f1fb7 100644
--- a/tests/dialects/test_spark.py
+++ b/tests/dialects/test_spark.py
@@ -1,6 +1,7 @@
from unittest import mock
from sqlglot import exp, parse_one
+from sqlglot.dialects.dialect import Dialects
from tests.dialects.test_dialect import Validator
@@ -245,13 +246,16 @@ TBLPROPERTIES (
self.validate_identity("SELECT TRANSFORM(ARRAY(1, 2, 3), (x, i) -> x + i)")
self.validate_identity("REFRESH TABLE a.b.c")
self.validate_identity("INTERVAL -86 DAYS")
- self.validate_identity("SELECT UNIX_TIMESTAMP()")
self.validate_identity("TRIM(' SparkSQL ')")
self.validate_identity("TRIM(BOTH 'SL' FROM 'SSparkSQLS')")
self.validate_identity("TRIM(LEADING 'SL' FROM 'SSparkSQLS')")
self.validate_identity("TRIM(TRAILING 'SL' FROM 'SSparkSQLS')")
self.validate_identity("SPLIT(str, pattern, lim)")
self.validate_identity(
+ "SELECT UNIX_TIMESTAMP()",
+ "SELECT UNIX_TIMESTAMP(CURRENT_TIMESTAMP())",
+ )
+ self.validate_identity(
"SELECT CAST('2023-01-01' AS TIMESTAMP) + INTERVAL 23 HOUR + 59 MINUTE + 59 SECONDS",
"SELECT CAST('2023-01-01' AS TIMESTAMP) + INTERVAL '23' HOUR + INTERVAL '59' MINUTE + INTERVAL '59' SECONDS",
)
@@ -281,6 +285,18 @@ TBLPROPERTIES (
)
self.validate_all(
+ "SELECT SPLIT('123|789', '\\\\|')",
+ read={
+ "duckdb": "SELECT STR_SPLIT_REGEX('123|789', '\\|')",
+ "presto": "SELECT REGEXP_SPLIT('123|789', '\\|')",
+ },
+ write={
+ "duckdb": "SELECT STR_SPLIT_REGEX('123|789', '\\|')",
+ "presto": "SELECT REGEXP_SPLIT('123|789', '\\|')",
+ "spark": "SELECT SPLIT('123|789', '\\\\|')",
+ },
+ )
+ self.validate_all(
"WITH tbl AS (SELECT 1 AS id, 'eggy' AS name UNION ALL SELECT NULL AS id, 'jake' AS name) SELECT COUNT(DISTINCT id, name) AS cnt FROM tbl",
write={
"clickhouse": "WITH tbl AS (SELECT 1 AS id, 'eggy' AS name UNION ALL SELECT NULL AS id, 'jake' AS name) SELECT COUNT(DISTINCT id, name) AS cnt FROM tbl",
@@ -366,7 +382,7 @@ TBLPROPERTIES (
"hive": "SELECT CAST(DATEDIFF(TO_DATE('2020-12-31'), TO_DATE('2020-01-01')) / 7 AS INT)",
"postgres": "SELECT CAST(EXTRACT(days FROM (CAST(CAST('2020-12-31' AS DATE) AS TIMESTAMP) - CAST(CAST('2020-01-01' AS DATE) AS TIMESTAMP))) / 7 AS BIGINT)",
"redshift": "SELECT DATEDIFF(WEEK, CAST('2020-01-01' AS DATE), CAST('2020-12-31' AS DATE))",
- "snowflake": "SELECT DATEDIFF(WEEK, CAST('2020-01-01' AS DATE), CAST('2020-12-31' AS DATE))",
+ "snowflake": "SELECT DATEDIFF(WEEK, TO_DATE('2020-01-01'), TO_DATE('2020-12-31'))",
"spark": "SELECT DATEDIFF(WEEK, TO_DATE('2020-01-01'), TO_DATE('2020-12-31'))",
},
)
@@ -644,10 +660,10 @@ TBLPROPERTIES (
"SELECT TRANSFORM(zip_code, name, age) USING 'cat' AS (a STRING, b STRING, c STRING) FROM person WHERE zip_code > 94511"
)
self.validate_identity(
- "SELECT TRANSFORM(name, age) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' NULL DEFINED AS 'NULL' USING 'cat' AS (name_age STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '@' LINES TERMINATED BY '\n' NULL DEFINED AS 'NULL' FROM person"
+ "SELECT TRANSFORM(name, age) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\n' NULL DEFINED AS 'NULL' USING 'cat' AS (name_age STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '@' LINES TERMINATED BY '\\n' NULL DEFINED AS 'NULL' FROM person"
)
self.validate_identity(
- "SELECT TRANSFORM(zip_code, name, age) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' WITH SERDEPROPERTIES ('field.delim'='\t') USING 'cat' AS (a STRING, b STRING, c STRING) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' WITH SERDEPROPERTIES ('field.delim'='\t') FROM person WHERE zip_code > 94511"
+ "SELECT TRANSFORM(zip_code, name, age) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' WITH SERDEPROPERTIES ('field.delim'='\\t') USING 'cat' AS (a STRING, b STRING, c STRING) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' WITH SERDEPROPERTIES ('field.delim'='\\t') FROM person WHERE zip_code > 94511"
)
self.validate_identity(
"SELECT TRANSFORM(zip_code, name, age) USING 'cat' FROM person WHERE zip_code > 94500"
@@ -720,3 +736,16 @@ TBLPROPERTIES (
"presto": "SELECT col, pos, IF(_u_2.pos_2 = _u_3.pos_3, _u_3.col_2) AS col_2, IF(_u_2.pos_2 = _u_3.pos_3, _u_3.pos_3) AS pos_3 FROM _u CROSS JOIN UNNEST(SEQUENCE(1, GREATEST(CARDINALITY(ARRAY[2, 3])))) AS _u_2(pos_2) CROSS JOIN UNNEST(ARRAY[2, 3]) WITH ORDINALITY AS _u_3(col_2, pos_3) WHERE _u_2.pos_2 = _u_3.pos_3 OR (_u_2.pos_2 > CARDINALITY(ARRAY[2, 3]) AND _u_3.pos_3 = CARDINALITY(ARRAY[2, 3]))",
},
)
+
+ def test_strip_modifiers(self):
+ without_modifiers = "SELECT * FROM t"
+ with_modifiers = f"{without_modifiers} CLUSTER BY y DISTRIBUTE BY x SORT BY z"
+ query = self.parse_one(with_modifiers)
+
+ for dialect in Dialects:
+ with self.subTest(f"Transpiling query with CLUSTER/DISTRIBUTE/SORT BY to {dialect}"):
+ name = dialect.value
+ if name in ("", "databricks", "hive", "spark", "spark2"):
+ self.assertEqual(query.sql(name), with_modifiers)
+ else:
+ self.assertEqual(query.sql(name), without_modifiers)
diff --git a/tests/dialects/test_sqlite.py b/tests/dialects/test_sqlite.py
index 2421987..f3cde0b 100644
--- a/tests/dialects/test_sqlite.py
+++ b/tests/dialects/test_sqlite.py
@@ -6,58 +6,6 @@ from sqlglot.helper import logger as helper_logger
class TestSQLite(Validator):
dialect = "sqlite"
- def test_ddl(self):
- self.validate_identity("INSERT OR ABORT INTO foo (x, y) VALUES (1, 2)")
- self.validate_identity("INSERT OR FAIL INTO foo (x, y) VALUES (1, 2)")
- self.validate_identity("INSERT OR IGNORE INTO foo (x, y) VALUES (1, 2)")
- self.validate_identity("INSERT OR REPLACE INTO foo (x, y) VALUES (1, 2)")
- self.validate_identity("INSERT OR ROLLBACK INTO foo (x, y) VALUES (1, 2)")
- self.validate_identity("CREATE TABLE foo (id INTEGER PRIMARY KEY ASC)")
- self.validate_identity("CREATE TEMPORARY TABLE foo (id INTEGER)")
-
- self.validate_all(
- """
- CREATE TABLE "Track"
- (
- CONSTRAINT "PK_Track" FOREIGN KEY ("TrackId"),
- FOREIGN KEY ("AlbumId") REFERENCES "Album" (
- "AlbumId"
- ) ON DELETE NO ACTION ON UPDATE NO ACTION,
- FOREIGN KEY ("AlbumId") ON DELETE CASCADE ON UPDATE RESTRICT,
- FOREIGN KEY ("AlbumId") ON DELETE SET NULL ON UPDATE SET DEFAULT
- )
- """,
- write={
- "sqlite": """CREATE TABLE "Track" (
- CONSTRAINT "PK_Track" FOREIGN KEY ("TrackId"),
- FOREIGN KEY ("AlbumId") REFERENCES "Album" (
- "AlbumId"
- ) ON DELETE NO ACTION ON UPDATE NO ACTION,
- FOREIGN KEY ("AlbumId") ON DELETE CASCADE ON UPDATE RESTRICT,
- FOREIGN KEY ("AlbumId") ON DELETE SET NULL ON UPDATE SET DEFAULT
-)""",
- },
- pretty=True,
- )
- self.validate_all(
- "CREATE TABLE z (a INTEGER UNIQUE PRIMARY KEY AUTOINCREMENT)",
- read={
- "mysql": "CREATE TABLE z (a INT UNIQUE PRIMARY KEY AUTO_INCREMENT)",
- },
- write={
- "sqlite": "CREATE TABLE z (a INTEGER UNIQUE PRIMARY KEY AUTOINCREMENT)",
- "mysql": "CREATE TABLE z (a INT UNIQUE PRIMARY KEY AUTO_INCREMENT)",
- "postgres": "CREATE TABLE z (a INT GENERATED BY DEFAULT AS IDENTITY NOT NULL UNIQUE PRIMARY KEY)",
- },
- )
- self.validate_all(
- """CREATE TABLE "x" ("Name" NVARCHAR(200) NOT NULL)""",
- write={
- "sqlite": """CREATE TABLE "x" ("Name" TEXT(200) NOT NULL)""",
- "mysql": "CREATE TABLE `x` (`Name` VARCHAR(200) NOT NULL)",
- },
- )
-
def test_sqlite(self):
self.validate_identity("SELECT DATE()")
self.validate_identity("SELECT DATE('now', 'start of month', '+1 month', '-1 day')")
@@ -65,7 +13,6 @@ class TestSQLite(Validator):
self.validate_identity("SELECT DATETIME(1092941466, 'auto')")
self.validate_identity("SELECT DATETIME(1092941466, 'unixepoch', 'localtime')")
self.validate_identity("SELECT UNIXEPOCH()")
- self.validate_identity("SELECT STRFTIME('%s')")
self.validate_identity("SELECT JULIANDAY('now') - JULIANDAY('1776-07-04')")
self.validate_identity("SELECT UNIXEPOCH() - UNIXEPOCH('2004-01-01 02:34:56')")
self.validate_identity("SELECT DATE('now', 'start of year', '+9 months', 'weekday 2')")
@@ -145,6 +92,29 @@ class TestSQLite(Validator):
write={"snowflake": "LEAST(x, y, z)"},
)
+ def test_strftime(self):
+ self.validate_identity("SELECT STRFTIME('%Y/%m/%d', 'now')")
+ self.validate_identity("SELECT STRFTIME('%Y-%m-%d', '2016-10-16', 'start of month')")
+ self.validate_identity(
+ "SELECT STRFTIME('%s')",
+ "SELECT STRFTIME('%s', CURRENT_TIMESTAMP)",
+ )
+
+ self.validate_all(
+ "SELECT STRFTIME('%Y-%m-%d', '2020-01-01 12:05:03')",
+ write={
+ "duckdb": "SELECT STRFTIME(CAST('2020-01-01 12:05:03' AS TIMESTAMP), '%Y-%m-%d')",
+ "sqlite": "SELECT STRFTIME('%Y-%m-%d', '2020-01-01 12:05:03')",
+ },
+ )
+ self.validate_all(
+ "SELECT STRFTIME('%Y-%m-%d', CURRENT_TIMESTAMP)",
+ write={
+ "duckdb": "SELECT STRFTIME(CAST(CURRENT_TIMESTAMP AS TIMESTAMP), '%Y-%m-%d')",
+ "sqlite": "SELECT STRFTIME('%Y-%m-%d', CURRENT_TIMESTAMP)",
+ },
+ )
+
def test_datediff(self):
self.validate_all(
"DATEDIFF(a, b, 'day')",
@@ -190,3 +160,59 @@ class TestSQLite(Validator):
)
self.assertIn("Named columns are not supported in table alias.", cm.output[0])
+
+ def test_ddl(self):
+ for conflict_action in ("ABORT", "FAIL", "IGNORE", "REPLACE", "ROLLBACK"):
+ with self.subTest(f"ON CONFLICT {conflict_action}"):
+ self.validate_identity("CREATE TABLE a (b, c, UNIQUE (b, c) ON CONFLICT IGNORE)")
+
+ self.validate_identity("INSERT OR ABORT INTO foo (x, y) VALUES (1, 2)")
+ self.validate_identity("INSERT OR FAIL INTO foo (x, y) VALUES (1, 2)")
+ self.validate_identity("INSERT OR IGNORE INTO foo (x, y) VALUES (1, 2)")
+ self.validate_identity("INSERT OR REPLACE INTO foo (x, y) VALUES (1, 2)")
+ self.validate_identity("INSERT OR ROLLBACK INTO foo (x, y) VALUES (1, 2)")
+ self.validate_identity("CREATE TABLE foo (id INTEGER PRIMARY KEY ASC)")
+ self.validate_identity("CREATE TEMPORARY TABLE foo (id INTEGER)")
+
+ self.validate_all(
+ """
+ CREATE TABLE "Track"
+ (
+ CONSTRAINT "PK_Track" FOREIGN KEY ("TrackId"),
+ FOREIGN KEY ("AlbumId") REFERENCES "Album" (
+ "AlbumId"
+ ) ON DELETE NO ACTION ON UPDATE NO ACTION,
+ FOREIGN KEY ("AlbumId") ON DELETE CASCADE ON UPDATE RESTRICT,
+ FOREIGN KEY ("AlbumId") ON DELETE SET NULL ON UPDATE SET DEFAULT
+ )
+ """,
+ write={
+ "sqlite": """CREATE TABLE "Track" (
+ CONSTRAINT "PK_Track" FOREIGN KEY ("TrackId"),
+ FOREIGN KEY ("AlbumId") REFERENCES "Album" (
+ "AlbumId"
+ ) ON DELETE NO ACTION ON UPDATE NO ACTION,
+ FOREIGN KEY ("AlbumId") ON DELETE CASCADE ON UPDATE RESTRICT,
+ FOREIGN KEY ("AlbumId") ON DELETE SET NULL ON UPDATE SET DEFAULT
+)""",
+ },
+ pretty=True,
+ )
+ self.validate_all(
+ "CREATE TABLE z (a INTEGER UNIQUE PRIMARY KEY AUTOINCREMENT)",
+ read={
+ "mysql": "CREATE TABLE z (a INT UNIQUE PRIMARY KEY AUTO_INCREMENT)",
+ },
+ write={
+ "sqlite": "CREATE TABLE z (a INTEGER UNIQUE PRIMARY KEY AUTOINCREMENT)",
+ "mysql": "CREATE TABLE z (a INT UNIQUE PRIMARY KEY AUTO_INCREMENT)",
+ "postgres": "CREATE TABLE z (a INT GENERATED BY DEFAULT AS IDENTITY NOT NULL UNIQUE PRIMARY KEY)",
+ },
+ )
+ self.validate_all(
+ """CREATE TABLE "x" ("Name" NVARCHAR(200) NOT NULL)""",
+ write={
+ "sqlite": """CREATE TABLE "x" ("Name" TEXT(200) NOT NULL)""",
+ "mysql": "CREATE TABLE `x` (`Name` VARCHAR(200) NOT NULL)",
+ },
+ )
diff --git a/tests/dialects/test_teradata.py b/tests/dialects/test_teradata.py
index f3894fd..a85ca8c 100644
--- a/tests/dialects/test_teradata.py
+++ b/tests/dialects/test_teradata.py
@@ -5,6 +5,7 @@ class TestTeradata(Validator):
dialect = "teradata"
def test_teradata(self):
+ self.validate_identity("TO_NUMBER(expr, fmt, nlsparam)")
self.validate_identity("SELECT TOP 10 * FROM tbl")
self.validate_identity("SELECT * FROM tbl SAMPLE 5")
self.validate_identity(
@@ -100,7 +101,9 @@ class TestTeradata(Validator):
self.validate_identity(
"CREATE VOLATILE SET TABLE example1 AS (SELECT col1, col2, col3 FROM table1) WITH DATA PRIMARY INDEX (col1) ON COMMIT PRESERVE ROWS"
)
-
+ self.validate_identity(
+ "CREATE SET GLOBAL TEMPORARY TABLE a, NO BEFORE JOURNAL, NO AFTER JOURNAL, MINIMUM DATABLOCKSIZE, BLOCKCOMPRESSION=NEVER (a INT)"
+ )
self.validate_all(
"""
CREATE SET TABLE test, NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL,
diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py
index ed474fd..aefd857 100644
--- a/tests/dialects/test_tsql.py
+++ b/tests/dialects/test_tsql.py
@@ -273,6 +273,28 @@ class TestTSQL(Validator):
)
self.validate_all(
+ "SELECT * FROM t ORDER BY (SELECT NULL) OFFSET 2 ROWS",
+ read={
+ "postgres": "SELECT * FROM t OFFSET 2",
+ },
+ write={
+ "postgres": "SELECT * FROM t ORDER BY (SELECT NULL) NULLS FIRST OFFSET 2",
+ "tsql": "SELECT * FROM t ORDER BY (SELECT NULL) OFFSET 2 ROWS",
+ },
+ )
+ self.validate_all(
+ "SELECT * FROM t ORDER BY (SELECT NULL) OFFSET 5 ROWS FETCH FIRST 10 ROWS ONLY",
+ read={
+ "duckdb": "SELECT * FROM t LIMIT 10 OFFSET 5",
+ "sqlite": "SELECT * FROM t LIMIT 5, 10",
+ "tsql": "SELECT * FROM t ORDER BY (SELECT NULL) OFFSET 5 ROWS FETCH FIRST 10 ROWS ONLY",
+ },
+ write={
+ "duckdb": "SELECT * FROM t ORDER BY (SELECT NULL) NULLS FIRST LIMIT 10 OFFSET 5",
+ "sqlite": "SELECT * FROM t ORDER BY (SELECT NULL) LIMIT 10 OFFSET 5",
+ },
+ )
+ self.validate_all(
"SELECT CAST([a].[b] AS SMALLINT) FROM foo",
write={
"tsql": "SELECT CAST([a].[b] AS SMALLINT) FROM foo",
@@ -720,6 +742,9 @@ class TestTSQL(Validator):
)
def test_ddl(self):
+ for view_attr in ("ENCRYPTION", "SCHEMABINDING", "VIEW_METADATA"):
+ self.validate_identity(f"CREATE VIEW a.b WITH {view_attr} AS SELECT * FROM x")
+
expression = parse_one("ALTER TABLE dbo.DocExe DROP CONSTRAINT FK_Column_B", dialect="tsql")
self.assertIsInstance(expression, exp.AlterTable)
self.assertIsInstance(expression.args["actions"][0], exp.Drop)
@@ -1549,7 +1574,7 @@ WHERE
"postgres": "CAST(DATE_TRUNC('MONTH', CAST(CURRENT_TIMESTAMP AS DATE)) + INTERVAL '1 MONTH' - INTERVAL '1 DAY' AS DATE)",
"presto": "LAST_DAY_OF_MONTH(CAST(CAST(CURRENT_TIMESTAMP AS TIMESTAMP) AS DATE))",
"redshift": "LAST_DAY(CAST(GETDATE() AS DATE))",
- "snowflake": "LAST_DAY(CAST(CURRENT_TIMESTAMP() AS DATE))",
+ "snowflake": "LAST_DAY(TO_DATE(CURRENT_TIMESTAMP()))",
"spark": "LAST_DAY(TO_DATE(CURRENT_TIMESTAMP()))",
"tsql": "EOMONTH(CAST(GETDATE() AS DATE))",
},
@@ -1564,7 +1589,7 @@ WHERE
"postgres": "CAST(DATE_TRUNC('MONTH', CAST(CURRENT_TIMESTAMP AS DATE) + INTERVAL '-1 MONTH') + INTERVAL '1 MONTH' - INTERVAL '1 DAY' AS DATE)",
"presto": "LAST_DAY_OF_MONTH(DATE_ADD('MONTH', CAST(-1 AS BIGINT), CAST(CAST(CURRENT_TIMESTAMP AS TIMESTAMP) AS DATE)))",
"redshift": "LAST_DAY(DATEADD(MONTH, -1, CAST(GETDATE() AS DATE)))",
- "snowflake": "LAST_DAY(DATEADD(MONTH, -1, CAST(CURRENT_TIMESTAMP() AS DATE)))",
+ "snowflake": "LAST_DAY(DATEADD(MONTH, -1, TO_DATE(CURRENT_TIMESTAMP())))",
"spark": "LAST_DAY(ADD_MONTHS(TO_DATE(CURRENT_TIMESTAMP()), -1))",
"tsql": "EOMONTH(DATEADD(MONTH, -1, CAST(GETDATE() AS DATE)))",
},
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-28 20:03:20 +0000