diff options
Diffstat (limited to 'tests/dialects/test_mysql.py')
| -rw-r--r-- | tests/dialects/test_mysql.py | 89 |
1 files changed, 88 insertions, 1 deletions
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py index f618728..524d95e 100644 --- a/tests/dialects/test_mysql.py +++ b/tests/dialects/test_mysql.py @@ -14,8 +14,18 @@ class TestMySQL(Validator): "spark": "CREATE TABLE z (a INT) COMMENT 'x'", }, ) + self.validate_all( + "CREATE TABLE t (c DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC", + write={ + "mysql": "CREATE TABLE t (c DATETIME DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP()) DEFAULT CHARACTER SET=utf8 ROW_FORMAT=DYNAMIC", + }, + ) + self.validate_identity( + "INSERT INTO x VALUES (1, 'a', 2.0) ON DUPLICATE KEY UPDATE SET x.id = 1" + ) def test_identity(self): + self.validate_identity("SELECT CURRENT_TIMESTAMP(6)") self.validate_identity("x ->> '$.name'") self.validate_identity("SELECT CAST(`a`.`b` AS INT) FROM foo") self.validate_identity("SELECT TRIM(LEADING 'bla' FROM ' XXX ')") @@ -186,7 +196,7 @@ class TestMySQL(Validator): self.validate_all( 'SELECT "2021-01-01" + INTERVAL 1 MONTH', write={ - "mysql": "SELECT '2021-01-01' + INTERVAL 1 MONTH", + "mysql": "SELECT '2021-01-01' + INTERVAL '1' MONTH", }, ) @@ -239,14 +249,91 @@ class TestMySQL(Validator): write={"mysql": "MATCH(a.b) AGAINST('abc')"}, ) + def test_date_format(self): + self.validate_all( + "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')", + }, + ) + 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')", + }, + ) + 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')", + }, + ) + 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')", + }, + ) + 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')", + }, + ) + 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')", + }, + ) + 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')", + }, + ) + 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')", + }, + ) + 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')", + }, + ) + + 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)") + def test_mysql(self): self.validate_all( + "SELECT DATE(DATE_SUB(`dt`, INTERVAL DAYOFMONTH(`dt`) - 1 DAY)) AS __timestamp FROM tableT", + write={ + "mysql": "SELECT DATE(DATE_SUB(`dt`, INTERVAL (DAYOFMONTH(`dt`) - 1) DAY)) AS __timestamp FROM tableT", + }, + ) + self.validate_all( "SELECT a FROM tbl FOR UPDATE", write={ "": "SELECT a FROM tbl", "mysql": "SELECT a FROM tbl FOR UPDATE", "oracle": "SELECT a FROM tbl FOR UPDATE", "postgres": "SELECT a FROM tbl FOR UPDATE", + "redshift": "SELECT a FROM tbl", "tsql": "SELECT a FROM tbl FOR UPDATE", }, ) |