summaryrefslogtreecommitdiffstats
path: root/tests
diff options
context:
space:
mode:
Diffstat (limited to 'tests')
-rw-r--r--tests/dialects/test_bigquery.py4
-rw-r--r--tests/dialects/test_clickhouse.py13
-rw-r--r--tests/dialects/test_duckdb.py20
-rw-r--r--tests/dialects/test_postgres.py8
-rw-r--r--tests/dialects/test_tsql.py16
-rw-r--r--tests/fixtures/identity.sql1
-rw-r--r--tests/fixtures/pretty.sql10
-rw-r--r--tests/test_expressions.py2
-rw-r--r--tests/test_transpile.py8
9 files changed, 70 insertions, 12 deletions
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index e3fb04f..45f3856 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -289,6 +289,10 @@ LANGUAGE js AS
r"REGEXP_EXTRACT(svc_plugin_output, r'\\\((.*)')",
r"REGEXP_EXTRACT(svc_plugin_output, '\\\\\\((.*)')",
)
+ self.validate_identity(
+ "SELECT CAST(1 AS BYTEINT)",
+ "SELECT CAST(1 AS INT64)",
+ )
self.validate_all(
"SAFE_CAST(some_date AS DATE FORMAT 'DD MONTH YYYY')",
diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index 92fbd4b..2356ad0 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -28,6 +28,8 @@ class TestClickhouse(Validator):
self.assertEqual(expr.sql(dialect="clickhouse"), "COUNT(x)")
self.assertIsNone(expr._meta)
+ self.validate_identity("SELECT STR_TO_DATE(str, fmt, tz)")
+ self.validate_identity("SELECT STR_TO_DATE('05 12 2000', '%d %m %Y')")
self.validate_identity("SELECT EXTRACT(YEAR FROM toDateTime('2023-02-01'))")
self.validate_identity("extract(haystack, pattern)")
self.validate_identity("SELECT * FROM x LIMIT 1 UNION ALL SELECT * FROM y")
@@ -154,6 +156,17 @@ class TestClickhouse(Validator):
)
self.validate_all(
+ "SELECT CAST(STR_TO_DATE('05 12 2000', '%d %m %Y') AS DATE)",
+ read={
+ "clickhouse": "SELECT CAST(STR_TO_DATE('05 12 2000', '%d %m %Y') AS DATE)",
+ "postgres": "SELECT TO_DATE('05 12 2000', 'DD MM YYYY')",
+ },
+ write={
+ "clickhouse": "SELECT CAST(STR_TO_DATE('05 12 2000', '%d %m %Y') AS DATE)",
+ "postgres": "SELECT CAST(CAST(TO_DATE('05 12 2000', 'DD MM YYYY') AS TIMESTAMP) AS DATE)",
+ },
+ )
+ self.validate_all(
"SELECT * FROM x PREWHERE y = 1 WHERE z = 2",
write={
"": "SELECT * FROM x WHERE z = 2",
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index d092d3b..15af086 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -8,6 +8,8 @@ class TestDuckDB(Validator):
dialect = "duckdb"
def test_duckdb(self):
+ self.validate_identity("x::int[3]", "CAST(x AS INT[3])")
+
with self.assertRaises(ParseError):
parse_one("1 //", read="duckdb")
@@ -293,10 +295,20 @@ class TestDuckDB(Validator):
self.validate_identity("x -> '$.family'")
self.validate_identity("CREATE TABLE color (name ENUM('RED', 'GREEN', 'BLUE'))")
self.validate_identity("SELECT * FROM foo WHERE bar > $baz AND bla = $bob")
+ self.validate_identity("SUMMARIZE tbl").assert_is(exp.Summarize)
+ self.validate_identity("SUMMARIZE SELECT * FROM tbl").assert_is(exp.Summarize)
+ self.validate_identity("CREATE TABLE tbl_summary AS SELECT * FROM (SUMMARIZE tbl)")
+ self.validate_identity(
+ "SUMMARIZE TABLE 'https://blobs.duckdb.org/data/Star_Trek-Season_1.csv'"
+ ).assert_is(exp.Summarize)
self.validate_identity(
"SELECT * FROM x LEFT JOIN UNNEST(y)", "SELECT * FROM x LEFT JOIN UNNEST(y) ON TRUE"
)
self.validate_identity(
+ "SELECT col FROM t WHERE JSON_EXTRACT_STRING(col, '$.id') NOT IN ('b')",
+ "SELECT col FROM t WHERE NOT (col ->> '$.id') IN ('b')",
+ )
+ self.validate_identity(
"SELECT a, LOGICAL_OR(b) FROM foo GROUP BY a",
"SELECT a, BOOL_OR(b) FROM foo GROUP BY a",
)
@@ -839,10 +851,10 @@ class TestDuckDB(Validator):
self.assertEqual(
cm.output,
[
- "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)",
+ "INFO:sqlglot:Applying array index offset (-1)",
+ "INFO:sqlglot:Applying array index offset (1)",
+ "INFO:sqlglot:Applying array index offset (1)",
+ "INFO:sqlglot:Applying array index offset (1)",
],
)
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 21c7e2b..5df5e31 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -1071,10 +1071,10 @@ class TestPostgres(Validator):
self.assertEqual(
cm.output,
[
- "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)",
+ "INFO:sqlglot:Applying array index offset (-1)",
+ "INFO:sqlglot:Applying array index offset (1)",
+ "INFO:sqlglot:Applying array index offset (1)",
+ "INFO:sqlglot:Applying array index offset (1)",
],
)
diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py
index 9658a02..cfdcb8d 100644
--- a/tests/dialects/test_tsql.py
+++ b/tests/dialects/test_tsql.py
@@ -49,6 +49,16 @@ class TestTSQL(Validator):
)
self.validate_all(
+ "WITH A AS (SELECT 2 AS value), C AS (SELECT * FROM A) SELECT * INTO TEMP_NESTED_WITH FROM (SELECT * FROM C) AS temp",
+ read={
+ "snowflake": "CREATE TABLE TEMP_NESTED_WITH AS WITH C AS (WITH A AS (SELECT 2 AS value) SELECT * FROM A) SELECT * FROM C",
+ "tsql": "WITH A AS (SELECT 2 AS value), C AS (SELECT * FROM A) SELECT * INTO TEMP_NESTED_WITH FROM (SELECT * FROM C) AS temp",
+ },
+ write={
+ "snowflake": "CREATE TABLE TEMP_NESTED_WITH AS WITH A AS (SELECT 2 AS value), C AS (SELECT * FROM A) SELECT * FROM (SELECT * FROM C) AS temp",
+ },
+ )
+ self.validate_all(
"SELECT IIF(cond <> 0, 'True', 'False')",
read={
"spark": "SELECT IF(cond, 'True', 'False')",
@@ -797,6 +807,7 @@ class TestTSQL(Validator):
f"UNIQUE {clustered_keyword} ([internal_id] ASC))",
)
+ self.validate_identity("CREATE VIEW t AS WITH cte AS (SELECT 1 AS c) SELECT c FROM cte")
self.validate_identity(
"ALTER TABLE tbl SET SYSTEM_VERSIONING=ON(HISTORY_TABLE=db.tbl, DATA_CONSISTENCY_CHECK=OFF, HISTORY_RETENTION_PERIOD=5 DAYS)"
)
@@ -1135,6 +1146,11 @@ WHERE
self.validate_all("ISNULL(x, y)", write={"spark": "COALESCE(x, y)"})
def test_json(self):
+ self.validate_identity(
+ """JSON_QUERY(REPLACE(REPLACE(x , '''', '"'), '""', '"'))""",
+ """ISNULL(JSON_QUERY(REPLACE(REPLACE(x, '''', '"'), '""', '"'), '$'), JSON_VALUE(REPLACE(REPLACE(x, '''', '"'), '""', '"'), '$'))""",
+ )
+
self.validate_all(
"JSON_QUERY(r.JSON, '$.Attr_INT')",
write={
diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql
index 31cea81..7892adb 100644
--- a/tests/fixtures/identity.sql
+++ b/tests/fixtures/identity.sql
@@ -868,6 +868,7 @@ SELECT name
SELECT copy
SELECT rollup
SELECT unnest
+SELECT cube, cube.x FROM cube
SELECT * FROM a STRAIGHT_JOIN b
SELECT COUNT(DISTINCT "foo bar") FROM (SELECT 1 AS "foo bar") AS t
SELECT vector
diff --git a/tests/fixtures/pretty.sql b/tests/fixtures/pretty.sql
index 62ba01c..d4eb3b9 100644
--- a/tests/fixtures/pretty.sql
+++ b/tests/fixtures/pretty.sql
@@ -395,3 +395,13 @@ JOIN b
JOIN d
USING (f)
USING (g);
+
+('aaaaaaaaaaa', 'bbbbbbbbbbbbbbbb', 'ccccccccccccc', 'ddddddddddd', 'eeeeeeeeeeeeeeeeeeeee');
+(
+ 'aaaaaaaaaaa',
+ 'bbbbbbbbbbbbbbbb',
+ 'ccccccccccccc',
+ 'ddddddddddd',
+ 'eeeeeeeeeeeeeeeeeeeee'
+);
+
diff --git a/tests/test_expressions.py b/tests/test_expressions.py
index 9ac2110..37a9720 100644
--- a/tests/test_expressions.py
+++ b/tests/test_expressions.py
@@ -1001,6 +1001,8 @@ FROM foo""",
self.assertEqual(exp.DataType.build("ARRAY<UNKNOWN>").sql(), "ARRAY<UNKNOWN>")
self.assertEqual(exp.DataType.build("ARRAY<NULL>").sql(), "ARRAY<NULL>")
self.assertEqual(exp.DataType.build("varchar(100) collate 'en-ci'").sql(), "VARCHAR(100)")
+ self.assertEqual(exp.DataType.build("int[3]").sql(dialect="duckdb"), "INT[3]")
+ self.assertEqual(exp.DataType.build("int[3][3]").sql(dialect="duckdb"), "INT[3][3]")
with self.assertRaises(ParseError):
exp.DataType.build("varchar(")
diff --git a/tests/test_transpile.py b/tests/test_transpile.py
index 5ea5ffe..d27739c 100644
--- a/tests/test_transpile.py
+++ b/tests/test_transpile.py
@@ -815,10 +815,10 @@ FROM x""",
self.assertEqual(
cm.output,
[
- "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)",
+ "INFO:sqlglot:Applying array index offset (1)",
+ "INFO:sqlglot:Applying array index offset (-1)",
+ "INFO:sqlglot:Applying array index offset (1)",
+ "INFO:sqlglot:Applying array index offset (1)",
],
)
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-10 07:07:26 +0000