diff options
Diffstat (limited to 'tests/dialects/test_mysql.py')
| -rw-r--r-- | tests/dialects/test_mysql.py | 56 |
1 files changed, 53 insertions, 3 deletions
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py index 3c165a3..19245f0 100644 --- a/tests/dialects/test_mysql.py +++ b/tests/dialects/test_mysql.py @@ -114,8 +114,17 @@ class TestMySQL(Validator): "mysql": "CREATE TABLE test (ts DATETIME, ts_tz TIMESTAMP, ts_ltz TIMESTAMP)", }, ) + self.validate_all( + "ALTER TABLE test_table ALTER COLUMN test_column SET DATA TYPE LONGTEXT", + write={ + "mysql": "ALTER TABLE test_table MODIFY COLUMN test_column LONGTEXT", + }, + ) + self.validate_identity("ALTER TABLE test_table ALTER COLUMN test_column SET DEFAULT 1") def test_identity(self): + self.validate_identity("SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:00.0000')") + self.validate_identity("SELECT @var1 := 1, @var2") self.validate_identity("UNLOCK TABLES") self.validate_identity("LOCK TABLES `app_fields` WRITE") self.validate_identity("SELECT 1 XOR 0") @@ -523,7 +532,15 @@ class TestMySQL(Validator): ) self.validate_all( "SELECT DATEDIFF(x, y)", - write={"mysql": "SELECT DATEDIFF(x, y)", "presto": "SELECT DATE_DIFF('day', y, x)"}, + read={ + "presto": "SELECT DATE_DIFF('day', y, x)", + "redshift": "SELECT DATEDIFF(day, y, x)", + }, + write={ + "mysql": "SELECT DATEDIFF(x, y)", + "presto": "SELECT DATE_DIFF('day', y, x)", + "redshift": "SELECT DATEDIFF(day, y, x)", + }, ) self.validate_all( "DAYOFYEAR(x)", @@ -574,10 +591,16 @@ class TestMySQL(Validator): def test_mysql(self): self.validate_all( + "SELECT * FROM x LEFT JOIN y ON x.id = y.id UNION SELECT * FROM x RIGHT JOIN y ON x.id = y.id LIMIT 0", + read={ + "postgres": "SELECT * FROM x FULL JOIN y ON x.id = y.id LIMIT 0", + }, + ) + self.validate_all( # MySQL doesn't support FULL OUTER joins - "SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.x = t2.x UNION SELECT * FROM t1 RIGHT OUTER JOIN t2 ON t1.x = t2.x", + "WITH t1 AS (SELECT 1) SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.x = t2.x UNION SELECT * FROM t1 RIGHT OUTER JOIN t2 ON t1.x = t2.x", read={ - "postgres": "SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.x = t2.x", + "postgres": "WITH t1 AS (SELECT 1) SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.x = t2.x", }, ) self.validate_all( @@ -601,7 +624,9 @@ class TestMySQL(Validator): "mysql": "SELECT * FROM test LIMIT 1 OFFSET 1", "postgres": "SELECT * FROM test LIMIT 0 + 1 OFFSET 0 + 1", "presto": "SELECT * FROM test OFFSET 1 LIMIT 1", + "snowflake": "SELECT * FROM test LIMIT 1 OFFSET 1", "trino": "SELECT * FROM test OFFSET 1 LIMIT 1", + "bigquery": "SELECT * FROM test LIMIT 1 OFFSET 1", }, ) self.validate_all( @@ -984,3 +1009,28 @@ COMMENT='客户账户表'""" "mysql": "DATE_FORMAT(x, '%M')", }, ) + + def test_safe_div(self): + self.validate_all( + "a / b", + write={ + "bigquery": "a / NULLIF(b, 0)", + "clickhouse": "a / b", + "databricks": "a / NULLIF(b, 0)", + "duckdb": "a / b", + "hive": "a / b", + "mysql": "a / b", + "oracle": "a / NULLIF(b, 0)", + "snowflake": "a / NULLIF(b, 0)", + "spark": "a / b", + "starrocks": "a / b", + "drill": "CAST(a AS DOUBLE) / NULLIF(b, 0)", + "postgres": "CAST(a AS DOUBLE PRECISION) / NULLIF(b, 0)", + "presto": "CAST(a AS DOUBLE) / NULLIF(b, 0)", + "redshift": "CAST(a AS DOUBLE PRECISION) / NULLIF(b, 0)", + "sqlite": "CAST(a AS REAL) / b", + "teradata": "CAST(a AS DOUBLE) / NULLIF(b, 0)", + "trino": "CAST(a AS DOUBLE) / NULLIF(b, 0)", + "tsql": "CAST(a AS FLOAT) / NULLIF(b, 0)", + }, + ) |