summaryrefslogtreecommitdiffstats
path: root/tests/dialects/test_mysql.py
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2023-05-03 09:12:24 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2023-05-03 09:12:24 +0000
commit98d5537435b2951b36c45f1fda667fa27c165794 (patch)
treed26b4dfa6cf91847100fe10a94a04dcc2ad36a86 /tests/dialects/test_mysql.py
parentAdding upstream version 11.5.2. (diff)
downloadsqlglot-98d5537435b2951b36c45f1fda667fa27c165794.tar.xz
sqlglot-98d5537435b2951b36c45f1fda667fa27c165794.zip
Adding upstream version 11.7.1.upstream/11.7.1
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'tests/dialects/test_mysql.py')
-rw-r--r--tests/dialects/test_mysql.py89
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",
},
)