diff options
Diffstat (limited to 'tests/dialects/test_mysql.py')
| -rw-r--r-- | tests/dialects/test_mysql.py | 118 |
1 files changed, 80 insertions, 38 deletions
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py index e8af5c6..53e2dab 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") @@ -226,29 +217,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 +466,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 +608,19 @@ class TestMySQL(Validator): def test_mysql(self): 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 +1131,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})", + }, + ) |