summaryrefslogtreecommitdiffstats
path: root/tests/dialects/test_mysql.py
diff options
context:
space:
mode:
Diffstat (limited to 'tests/dialects/test_mysql.py')
-rw-r--r--tests/dialects/test_mysql.py131
1 files changed, 93 insertions, 38 deletions
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index e8af5c6..84fb3c2 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -1,4 +1,5 @@
from sqlglot import expressions as exp
+from sqlglot.dialects.mysql import MySQL
from tests.dialects.test_dialect import Validator
@@ -6,21 +7,11 @@ class TestMySQL(Validator):
dialect = "mysql"
def test_ddl(self):
- int_types = {"BIGINT", "INT", "MEDIUMINT", "SMALLINT", "TINYINT"}
-
- for t in int_types:
+ for t in ("BIGINT", "INT", "MEDIUMINT", "SMALLINT", "TINYINT"):
self.validate_identity(f"CREATE TABLE t (id {t} UNSIGNED)")
self.validate_identity(f"CREATE TABLE t (id {t}(10) UNSIGNED)")
self.validate_identity("CREATE TABLE t (id DECIMAL(20, 4) UNSIGNED)")
-
- self.validate_all(
- "CREATE TABLE t (id INT UNSIGNED)",
- write={
- "duckdb": "CREATE TABLE t (id UINTEGER)",
- },
- )
-
self.validate_identity("CREATE TABLE foo (a BIGINT, UNIQUE (b) USING BTREE)")
self.validate_identity("CREATE TABLE foo (id BIGINT)")
self.validate_identity("CREATE TABLE 00f (1d BIGINT)")
@@ -98,6 +89,13 @@ class TestMySQL(Validator):
)
self.validate_all(
+ "CREATE TABLE t (id INT UNSIGNED)",
+ write={
+ "duckdb": "CREATE TABLE t (id UINTEGER)",
+ "mysql": "CREATE TABLE t (id INT UNSIGNED)",
+ },
+ )
+ self.validate_all(
"CREATE TABLE z (a INT) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin COMMENT='x'",
write={
"duckdb": "CREATE TABLE z (a INT)",
@@ -109,15 +107,10 @@ class TestMySQL(Validator):
self.validate_all(
"CREATE TABLE x (id int not null auto_increment, primary key (id))",
write={
+ "mysql": "CREATE TABLE x (id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id))",
"sqlite": "CREATE TABLE x (id INTEGER NOT NULL AUTOINCREMENT PRIMARY KEY)",
},
)
- self.validate_all(
- "CREATE TABLE x (id int not null auto_increment)",
- write={
- "sqlite": "CREATE TABLE x (id INTEGER NOT NULL)",
- },
- )
def test_identity(self):
self.validate_identity("ALTER TABLE test_table ALTER COLUMN test_column SET DEFAULT 1")
@@ -135,8 +128,6 @@ class TestMySQL(Validator):
self.validate_identity("SELECT CAST('[4,5]' AS JSON) MEMBER OF('[[3,4],[4,5]]')")
self.validate_identity("""SELECT 'ab' MEMBER OF('[23, "abc", 17, "ab", 10]')""")
self.validate_identity("""SELECT * FROM foo WHERE 'ab' MEMBER OF(content)""")
- self.validate_identity("CAST(x AS ENUM('a', 'b'))")
- self.validate_identity("CAST(x AS SET('a', 'b'))")
self.validate_identity("SELECT CURRENT_TIMESTAMP(6)")
self.validate_identity("x ->> '$.name'")
self.validate_identity("SELECT CAST(`a`.`b` AS CHAR) FROM foo")
@@ -158,6 +149,9 @@ class TestMySQL(Validator):
"SELECT * FROM t1, t2, t3 FOR SHARE OF t1 NOWAIT FOR UPDATE OF t2, t3 SKIP LOCKED"
)
self.validate_identity(
+ "REPLACE INTO table SELECT id FROM table2 WHERE cnt > 100", check_command_warning=True
+ )
+ self.validate_identity(
"""SELECT * FROM foo WHERE 3 MEMBER OF(info->'$.value')""",
"""SELECT * FROM foo WHERE 3 MEMBER OF(JSON_EXTRACT(info, '$.value'))""",
)
@@ -226,29 +220,47 @@ class TestMySQL(Validator):
self.validate_identity("SELECT * FROM t1 PARTITION(p0)")
def test_types(self):
- self.validate_identity("CAST(x AS MEDIUMINT) + CAST(y AS YEAR(4))")
+ for char_type in MySQL.Generator.CHAR_CAST_MAPPING:
+ with self.subTest(f"MySQL cast into {char_type}"):
+ self.validate_identity(f"CAST(x AS {char_type.value})", "CAST(x AS CHAR)")
+
+ for signed_type in MySQL.Generator.SIGNED_CAST_MAPPING:
+ with self.subTest(f"MySQL cast into {signed_type}"):
+ self.validate_identity(f"CAST(x AS {signed_type.value})", "CAST(x AS SIGNED)")
+
+ self.validate_identity("CAST(x AS ENUM('a', 'b'))")
+ self.validate_identity("CAST(x AS SET('a', 'b'))")
+ self.validate_identity(
+ "CAST(x AS MEDIUMINT) + CAST(y AS YEAR(4))",
+ "CAST(x AS SIGNED) + CAST(y AS YEAR(4))",
+ )
+ self.validate_identity(
+ "CAST(x AS TIMESTAMP)",
+ "CAST(x AS DATETIME)",
+ )
+ self.validate_identity(
+ "CAST(x AS TIMESTAMPTZ)",
+ "TIMESTAMP(x)",
+ )
+ self.validate_identity(
+ "CAST(x AS TIMESTAMPLTZ)",
+ "TIMESTAMP(x)",
+ )
self.validate_all(
"CAST(x AS MEDIUMTEXT) + CAST(y AS LONGTEXT) + CAST(z AS TINYTEXT)",
- read={
- "mysql": "CAST(x AS MEDIUMTEXT) + CAST(y AS LONGTEXT) + CAST(z AS TINYTEXT)",
- },
write={
+ "mysql": "CAST(x AS CHAR) + CAST(y AS CHAR) + CAST(z AS CHAR)",
"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(z AS TINYBLOB)",
- read={
- "mysql": "CAST(x AS MEDIUMBLOB) + CAST(y AS LONGBLOB) + CAST(z AS TINYBLOB)",
- },
write={
+ "mysql": "CAST(x AS CHAR) + CAST(y AS CHAR) + CAST(z AS CHAR)",
"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)"})
- self.validate_all("CAST(x AS TIMESTAMPTZ)", write={"mysql": "TIMESTAMP(x)"})
- self.validate_all("CAST(x AS TIMESTAMPLTZ)", write={"mysql": "TIMESTAMP(x)"})
def test_canonical_functions(self):
self.validate_identity("SELECT LEFT('str', 2)", "SELECT LEFT('str', 2)")
@@ -457,63 +469,63 @@ class TestMySQL(Validator):
"SELECT DATE_FORMAT('2017-06-15', '%Y')",
write={
"mysql": "SELECT DATE_FORMAT('2017-06-15', '%Y')",
- "snowflake": "SELECT TO_CHAR(CAST('2017-06-15' AS TIMESTAMPNTZ), 'yyyy')",
+ "snowflake": "SELECT TO_CHAR(CAST('2017-06-15' AS TIMESTAMP), 'yyyy')",
},
)
self.validate_all(
"SELECT DATE_FORMAT('2017-06-15', '%m')",
write={
"mysql": "SELECT DATE_FORMAT('2017-06-15', '%m')",
- "snowflake": "SELECT TO_CHAR(CAST('2017-06-15' AS TIMESTAMPNTZ), 'mm')",
+ "snowflake": "SELECT TO_CHAR(CAST('2017-06-15' AS TIMESTAMP), 'mm')",
},
)
self.validate_all(
"SELECT DATE_FORMAT('2017-06-15', '%d')",
write={
"mysql": "SELECT DATE_FORMAT('2017-06-15', '%d')",
- "snowflake": "SELECT TO_CHAR(CAST('2017-06-15' AS TIMESTAMPNTZ), 'DD')",
+ "snowflake": "SELECT TO_CHAR(CAST('2017-06-15' AS TIMESTAMP), 'DD')",
},
)
self.validate_all(
"SELECT DATE_FORMAT('2017-06-15', '%Y-%m-%d')",
write={
"mysql": "SELECT DATE_FORMAT('2017-06-15', '%Y-%m-%d')",
- "snowflake": "SELECT TO_CHAR(CAST('2017-06-15' AS TIMESTAMPNTZ), 'yyyy-mm-DD')",
+ "snowflake": "SELECT TO_CHAR(CAST('2017-06-15' AS TIMESTAMP), 'yyyy-mm-DD')",
},
)
self.validate_all(
"SELECT DATE_FORMAT('2017-06-15 22:23:34', '%H')",
write={
"mysql": "SELECT DATE_FORMAT('2017-06-15 22:23:34', '%H')",
- "snowflake": "SELECT TO_CHAR(CAST('2017-06-15 22:23:34' AS TIMESTAMPNTZ), 'hh24')",
+ "snowflake": "SELECT TO_CHAR(CAST('2017-06-15 22:23:34' AS TIMESTAMP), 'hh24')",
},
)
self.validate_all(
"SELECT DATE_FORMAT('2017-06-15', '%w')",
write={
"mysql": "SELECT DATE_FORMAT('2017-06-15', '%w')",
- "snowflake": "SELECT TO_CHAR(CAST('2017-06-15' AS TIMESTAMPNTZ), 'dy')",
+ "snowflake": "SELECT TO_CHAR(CAST('2017-06-15' AS TIMESTAMP), 'dy')",
},
)
self.validate_all(
"SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y')",
write={
"mysql": "SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y')",
- "snowflake": "SELECT TO_CHAR(CAST('2009-10-04 22:23:00' AS TIMESTAMPNTZ), 'DY mmmm yyyy')",
+ "snowflake": "SELECT TO_CHAR(CAST('2009-10-04 22:23:00' AS TIMESTAMP), 'DY mmmm yyyy')",
},
)
self.validate_all(
"SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s')",
write={
"mysql": "SELECT DATE_FORMAT('2007-10-04 22:23:00', '%T')",
- "snowflake": "SELECT TO_CHAR(CAST('2007-10-04 22:23:00' AS TIMESTAMPNTZ), 'hh24:mi:ss')",
+ "snowflake": "SELECT TO_CHAR(CAST('2007-10-04 22:23:00' AS TIMESTAMP), 'hh24:mi:ss')",
},
)
self.validate_all(
"SELECT DATE_FORMAT('1900-10-04 22:23:00', '%d %y %a %d %m %b')",
write={
"mysql": "SELECT DATE_FORMAT('1900-10-04 22:23:00', '%d %y %W %d %m %b')",
- "snowflake": "SELECT TO_CHAR(CAST('1900-10-04 22:23:00' AS TIMESTAMPNTZ), 'DD yy DY DD mm mon')",
+ "snowflake": "SELECT TO_CHAR(CAST('1900-10-04 22:23:00' AS TIMESTAMP), 'DD yy DY DD mm mon')",
},
)
@@ -599,6 +611,29 @@ class TestMySQL(Validator):
def test_mysql(self):
self.validate_all(
+ "SELECT CONCAT('11', '22')",
+ read={
+ "postgres": "SELECT '11' || '22'",
+ },
+ write={
+ "mysql": "SELECT CONCAT('11', '22')",
+ "postgres": "SELECT CONCAT('11', '22')",
+ },
+ )
+ self.validate_all(
+ "SELECT department, GROUP_CONCAT(name) AS employee_names FROM data GROUP BY department",
+ read={
+ "postgres": "SELECT department, array_agg(name) AS employee_names FROM data GROUP BY department",
+ },
+ )
+ self.validate_all(
+ "SELECT UNIX_TIMESTAMP(CAST('2024-04-29 12:00:00' AS DATETIME))",
+ read={
+ "mysql": "SELECT UNIX_TIMESTAMP(CAST('2024-04-29 12:00:00' AS DATETIME))",
+ "postgres": "SELECT EXTRACT(epoch FROM TIMESTAMP '2024-04-29 12:00:00')",
+ },
+ )
+ self.validate_all(
"SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]')",
read={
"sqlite": "SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]')",
@@ -1109,3 +1144,23 @@ COMMENT='客户账户表'"""
"tsql": "CAST(a AS FLOAT) / NULLIF(b, 0)",
},
)
+
+ def test_timestamp_trunc(self):
+ for dialect in ("postgres", "snowflake", "duckdb", "spark", "databricks"):
+ for unit in (
+ "MILLISECOND",
+ "SECOND",
+ "DAY",
+ "MONTH",
+ "YEAR",
+ ):
+ with self.subTest(f"MySQL -> {dialect} Timestamp Trunc with unit {unit}: "):
+ self.validate_all(
+ f"DATE_ADD('0000-01-01 00:00:00', INTERVAL (TIMESTAMPDIFF({unit}, '0000-01-01 00:00:00', CAST('2001-02-16 20:38:40' AS DATETIME))) {unit})",
+ read={
+ dialect: f"DATE_TRUNC({unit}, TIMESTAMP '2001-02-16 20:38:40')",
+ },
+ write={
+ "mysql": f"DATE_ADD('0000-01-01 00:00:00', INTERVAL (TIMESTAMPDIFF({unit}, '0000-01-01 00:00:00', CAST('2001-02-16 20:38:40' AS DATETIME))) {unit})",
+ },
+ )
generated by cgit v1.2.3 (git 2.39.1) at 2024-05-23 06:15:14 +0000