summaryrefslogtreecommitdiffstats
path: root/tests/dialects
diff options
context:
space:
mode:
Diffstat (limited to 'tests/dialects')
-rw-r--r--tests/dialects/test_clickhouse.py65
-rw-r--r--tests/dialects/test_duckdb.py16
-rw-r--r--tests/dialects/test_mysql.py25
-rw-r--r--tests/dialects/test_oracle.py28
-rw-r--r--tests/dialects/test_postgres.py3
-rw-r--r--tests/dialects/test_redshift.py14
6 files changed, 116 insertions, 35 deletions
diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index ab2379d..2cda0dc 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -6,30 +6,19 @@ class TestClickhouse(Validator):
dialect = "clickhouse"
def test_clickhouse(self):
- self.validate_all(
- "DATE_ADD('day', 1, x)",
- read={
- "clickhouse": "dateAdd(day, 1, x)",
- "presto": "DATE_ADD('day', 1, x)",
- },
- write={
- "clickhouse": "DATE_ADD('day', 1, x)",
- "presto": "DATE_ADD('day', 1, x)",
- "": "DATE_ADD(x, 1, 'day')",
- },
- )
- self.validate_all(
- "DATE_DIFF('day', a, b)",
- read={
- "clickhouse": "dateDiff('day', a, b)",
- "presto": "DATE_DIFF('day', a, b)",
- },
- write={
- "clickhouse": "DATE_DIFF('day', a, b)",
- "presto": "DATE_DIFF('day', a, b)",
- "": "DATEDIFF(b, a, day)",
- },
- )
+ string_types = [
+ "BLOB",
+ "LONGBLOB",
+ "LONGTEXT",
+ "MEDIUMBLOB",
+ "MEDIUMTEXT",
+ "TINYBLOB",
+ "TINYTEXT",
+ "VARCHAR(255)",
+ ]
+
+ for string_type in string_types:
+ self.validate_identity(f"CAST(x AS {string_type})", "CAST(x AS String)")
expr = parse_one("count(x)")
self.assertEqual(expr.sql(dialect="clickhouse"), "COUNT(x)")
@@ -72,8 +61,8 @@ class TestClickhouse(Validator):
self.validate_identity("position(haystack, needle)")
self.validate_identity("position(haystack, needle, position)")
self.validate_identity("CAST(x AS DATETIME)")
- self.validate_identity("CAST(x AS VARCHAR(255))", "CAST(x AS String)")
- self.validate_identity("CAST(x AS BLOB)", "CAST(x AS String)")
+ self.validate_identity("CAST(x as MEDIUMINT)", "CAST(x AS Int32)")
+
self.validate_identity(
'SELECT CAST(tuple(1 AS "a", 2 AS "b", 3.0 AS "c").2 AS Nullable(String))'
)
@@ -94,6 +83,30 @@ class TestClickhouse(Validator):
)
self.validate_all(
+ "DATE_ADD('day', 1, x)",
+ read={
+ "clickhouse": "dateAdd(day, 1, x)",
+ "presto": "DATE_ADD('day', 1, x)",
+ },
+ write={
+ "clickhouse": "DATE_ADD('day', 1, x)",
+ "presto": "DATE_ADD('day', 1, x)",
+ "": "DATE_ADD(x, 1, 'day')",
+ },
+ )
+ self.validate_all(
+ "DATE_DIFF('day', a, b)",
+ read={
+ "clickhouse": "dateDiff('day', a, b)",
+ "presto": "DATE_DIFF('day', a, b)",
+ },
+ write={
+ "clickhouse": "DATE_DIFF('day', a, b)",
+ "presto": "DATE_DIFF('day', a, b)",
+ "": "DATEDIFF(b, a, day)",
+ },
+ )
+ self.validate_all(
"SELECT xor(1, 0)",
read={
"clickhouse": "SELECT xor(1, 0)",
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index aca0d7a..c5ee679 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -19,6 +19,7 @@ class TestDuckDB(Validator):
parse_one("a // b", read="duckdb").assert_is(exp.IntDiv).sql(dialect="duckdb"), "a // b"
)
+ self.validate_identity("VAR_POP(a)")
self.validate_identity("SELECT * FROM foo ASOF LEFT JOIN bar ON a = b")
self.validate_identity("PIVOT Cities ON Year USING SUM(Population)")
self.validate_identity("PIVOT Cities ON Year USING FIRST(Population)")
@@ -34,6 +35,9 @@ class TestDuckDB(Validator):
self.validate_identity("SELECT (x, x + 1, y) FROM (SELECT 1 AS x, 'a' AS y)")
self.validate_identity("SELECT a.x FROM (SELECT {'x': 1, 'y': 2, 'z': 3} AS a)")
self.validate_identity("ATTACH DATABASE ':memory:' AS new_database")
+ self.validate_identity("FROM x SELECT x UNION SELECT 1", "SELECT x FROM x UNION SELECT 1")
+ self.validate_identity("FROM (FROM tbl)", "SELECT * FROM (SELECT * FROM tbl)")
+ self.validate_identity("FROM tbl", "SELECT * FROM tbl")
self.validate_identity(
"SELECT {'yes': 'duck', 'maybe': 'goose', 'huh': NULL, 'no': 'heron'}"
)
@@ -53,14 +57,20 @@ class TestDuckDB(Validator):
"SELECT * FROM (PIVOT Cities ON Year USING SUM(Population) GROUP BY Country) AS pivot_alias"
)
- self.validate_identity("FROM x SELECT x UNION SELECT 1", "SELECT x FROM x UNION SELECT 1")
- self.validate_all("FROM (FROM tbl)", write={"duckdb": "SELECT * FROM (SELECT * FROM tbl)"})
- self.validate_all("FROM tbl", write={"duckdb": "SELECT * FROM tbl"})
self.validate_all("0b1010", write={"": "0 AS b1010"})
self.validate_all("0x1010", write={"": "0 AS x1010"})
self.validate_all("x ~ y", write={"duckdb": "REGEXP_MATCHES(x, y)"})
self.validate_all("SELECT * FROM 'x.y'", write={"duckdb": 'SELECT * FROM "x.y"'})
self.validate_all(
+ "VAR_POP(x)",
+ read={
+ "": "VARIANCE_POP(x)",
+ },
+ write={
+ "": "VARIANCE_POP(x)",
+ },
+ )
+ self.validate_all(
"DATE_DIFF('day', CAST(b AS DATE), CAST(a AS DATE))",
read={
"duckdb": "DATE_DIFF('day', CAST(b AS DATE), CAST(a AS DATE))",
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index 1f1c2e9..6104e3f 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -27,6 +27,9 @@ class TestMySQL(Validator):
self.validate_identity("CREATE TABLE foo (a BIGINT, FULLTEXT INDEX (b))")
self.validate_identity("CREATE TABLE foo (a BIGINT, SPATIAL INDEX (b))")
self.validate_identity(
+ "CREATE TABLE `x` (`username` VARCHAR(200), PRIMARY KEY (`username`(16)))"
+ )
+ self.validate_identity(
"UPDATE items SET items.price = 0 WHERE items.id >= 5 ORDER BY items.id LIMIT 10"
)
self.validate_identity(
@@ -204,21 +207,21 @@ class TestMySQL(Validator):
self.validate_identity("CAST(x AS MEDIUMINT) + CAST(y AS YEAR(4))")
self.validate_all(
- "CAST(x AS MEDIUMTEXT) + CAST(y AS LONGTEXT)",
+ "CAST(x AS MEDIUMTEXT) + CAST(y AS LONGTEXT) + CAST(z AS TINYTEXT)",
read={
- "mysql": "CAST(x AS MEDIUMTEXT) + CAST(y AS LONGTEXT)",
+ "mysql": "CAST(x AS MEDIUMTEXT) + CAST(y AS LONGTEXT) + CAST(z AS TINYTEXT)",
},
write={
- "spark": "CAST(x AS TEXT) + CAST(y AS TEXT)",
+ "spark": "CAST(x AS TEXT) + CAST(y AS TEXT) + CAST(z AS TEXT)",
},
)
self.validate_all(
- "CAST(x AS MEDIUMBLOB) + CAST(y AS LONGBLOB)",
+ "CAST(x AS MEDIUMBLOB) + CAST(y AS LONGBLOB) + CAST(z AS TINYBLOB)",
read={
- "mysql": "CAST(x AS MEDIUMBLOB) + CAST(y AS LONGBLOB)",
+ "mysql": "CAST(x AS MEDIUMBLOB) + CAST(y AS LONGBLOB) + CAST(z AS TINYBLOB)",
},
write={
- "spark": "CAST(x AS BLOB) + CAST(y AS BLOB)",
+ "spark": "CAST(x AS BLOB) + CAST(y AS BLOB) + CAST(z AS BLOB)",
},
)
self.validate_all("CAST(x AS TIMESTAMP)", write={"mysql": "CAST(x AS DATETIME)"})
@@ -240,6 +243,15 @@ class TestMySQL(Validator):
)
def test_escape(self):
+ self.validate_identity("""'"abc"'""")
+ self.validate_identity(
+ r"'\'a'",
+ "'''a'",
+ )
+ self.validate_identity(
+ '''"'abc'"''',
+ "'''abc'''",
+ )
self.validate_all(
r"'a \' b '' '",
write={
@@ -525,6 +537,7 @@ class TestMySQL(Validator):
"mysql": "SELECT DATE(DATE_SUB(`dt`, INTERVAL (DAYOFMONTH(`dt`) - 1) DAY)) AS __timestamp FROM tableT",
},
)
+ self.validate_identity("SELECT name FROM temp WHERE name = ? FOR UPDATE")
self.validate_all(
"SELECT a FROM tbl FOR UPDATE",
write={
diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py
index 01a9ca3..2dfd179 100644
--- a/tests/dialects/test_oracle.py
+++ b/tests/dialects/test_oracle.py
@@ -6,6 +6,7 @@ class TestOracle(Validator):
dialect = "oracle"
def test_oracle(self):
+ self.validate_identity("SELECT JSON_OBJECT(k1: v1 FORMAT JSON, k2: v2 FORMAT JSON)")
self.validate_identity("SELECT JSON_OBJECT('name': first_name || ' ' || last_name) FROM t")
self.validate_identity("COALESCE(c1, c2, c3)")
self.validate_identity("SELECT * FROM TABLE(foo)")
@@ -26,6 +27,15 @@ class TestOracle(Validator):
self.validate_identity("SELECT * FROM table_name SAMPLE (25) s")
self.validate_identity("SELECT * FROM V$SESSION")
self.validate_identity(
+ "SELECT JSON_ARRAYAGG(JSON_OBJECT('RNK': RNK, 'RATING_CODE': RATING_CODE, 'DATE_VALUE': DATE_VALUE, 'AGENT_ID': AGENT_ID RETURNING CLOB) RETURNING CLOB) AS JSON_DATA FROM tablename"
+ )
+ self.validate_identity(
+ "SELECT JSON_ARRAY(FOO() FORMAT JSON, BAR() NULL ON NULL RETURNING CLOB STRICT)"
+ )
+ self.validate_identity(
+ "SELECT JSON_ARRAYAGG(FOO() FORMAT JSON ORDER BY bar NULL ON NULL RETURNING CLOB STRICT)"
+ )
+ self.validate_identity(
"SELECT COUNT(1) INTO V_Temp FROM TABLE(CAST(somelist AS data_list)) WHERE col LIKE '%contact'"
)
self.validate_identity(
@@ -190,3 +200,21 @@ MATCH_RECOGNIZE (
) MR""",
pretty=True,
)
+
+ def test_json_table(self):
+ self.validate_identity(
+ "SELECT * FROM JSON_TABLE(foo FORMAT JSON, 'bla' ERROR ON ERROR NULL ON EMPTY COLUMNS (foo PATH 'bar'))"
+ )
+ self.validate_identity(
+ "SELECT * FROM JSON_TABLE(foo FORMAT JSON, 'bla' ERROR ON ERROR NULL ON EMPTY COLUMNS foo PATH 'bar')",
+ "SELECT * FROM JSON_TABLE(foo FORMAT JSON, 'bla' ERROR ON ERROR NULL ON EMPTY COLUMNS (foo PATH 'bar'))",
+ )
+ self.validate_identity(
+ """SELECT
+ CASE WHEN DBMS_LOB.GETLENGTH(info) < 32000 THEN DBMS_LOB.SUBSTR(info) END AS info_txt,
+ info AS info_clob
+FROM schemaname.tablename ar
+INNER JOIN JSON_TABLE(:emps, '$[*]' COLUMNS (empno NUMBER PATH '$')) jt
+ ON ar.empno = jt.empno""",
+ pretty=True,
+ )
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 21196b7..151f3af 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -133,6 +133,9 @@ class TestPostgres(Validator):
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)
+ # Checks that user-defined types are parsed into DataType instead of Identifier
+ parse_one("CREATE TABLE t (a udt)").this.expressions[0].args["kind"].assert_is(exp.DataType)
+
self.assertIsInstance(expr, exp.AlterTable)
self.assertEqual(expr.sql(dialect="postgres"), alter_table_only)
diff --git a/tests/dialects/test_redshift.py b/tests/dialects/test_redshift.py
index aea8b69..e261c01 100644
--- a/tests/dialects/test_redshift.py
+++ b/tests/dialects/test_redshift.py
@@ -360,3 +360,17 @@ class TestRedshift(Validator):
"redshift": "CREATE OR REPLACE VIEW v1 AS SELECT cola, colb FROM t1 WITH NO SCHEMA BINDING",
},
)
+
+ def test_concat(self):
+ self.validate_all(
+ "SELECT CONCAT('abc', 'def')",
+ write={
+ "redshift": "SELECT COALESCE(CAST('abc' AS VARCHAR(MAX)), '') || COALESCE(CAST('def' AS VARCHAR(MAX)), '')",
+ },
+ )
+ self.validate_all(
+ "SELECT CONCAT_WS('DELIM', 'abc', 'def', 'ghi')",
+ write={
+ "redshift": "SELECT COALESCE(CAST('abc' AS VARCHAR(MAX)), '') || 'DELIM' || COALESCE(CAST('def' AS VARCHAR(MAX)), '') || 'DELIM' || COALESCE(CAST('ghi' AS VARCHAR(MAX)), '')",
+ },
+ )
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-02 02:45:23 +0000