From 67c28dbe67209effad83d93b850caba5ee1e20e3 Mon Sep 17 00:00:00 2001
From: Daniel Baumann <daniel.baumann@progress-linux.org>
Date: Wed, 3 May 2023 11:12:28 +0200
Subject: Merging upstream version 11.7.1.

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
---
 tests/dialects/test_mysql.py | 89 +++++++++++++++++++++++++++++++++++++++++++-
 1 file changed, 88 insertions(+), 1 deletion(-)

(limited to 'tests/dialects/test_mysql.py')

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,7 +249,83 @@ 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={
@@ -247,6 +333,7 @@ class TestMySQL(Validator):
                 "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",
             },
         )
-- 
cgit v1.2.3