diff options
Diffstat (limited to 'tests/dialects/test_mysql.py')
| -rw-r--r-- | tests/dialects/test_mysql.py | 67 |
1 files changed, 65 insertions, 2 deletions
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'" ) |