summaryrefslogtreecommitdiffstats
path: root/tests/dialects/test_mysql.py
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2023-07-06 07:28:09 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2023-07-06 07:28:09 +0000
commit52f4a5e2260f3e5b919b4e270339afd670bf0b8a (patch)
tree5ca419af0e2e409018492b82f5b9847f0112b5fb /tests/dialects/test_mysql.py
parentAdding upstream version 16.7.7. (diff)
downloadsqlglot-52f4a5e2260f3e5b919b4e270339afd670bf0b8a.tar.xz
sqlglot-52f4a5e2260f3e5b919b4e270339afd670bf0b8a.zip
Adding upstream version 17.2.0.upstream/17.2.0
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.py45
1 files changed, 44 insertions, 1 deletions
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index ca2f921..3539ad0 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -10,7 +10,28 @@ class TestMySQL(Validator):
self.validate_identity("UPDATE items SET items.price = 0 WHERE items.id >= 5 LIMIT 10")
self.validate_identity("DELETE FROM t WHERE a <= 10 LIMIT 10")
self.validate_identity(
- "INSERT INTO x VALUES (1, 'a', 2.0) ON DUPLICATE KEY UPDATE SET x.id = 1"
+ "DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.id IS NULL"
+ )
+ self.validate_identity(
+ "DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id = t2.id AND t2.id = t3.id"
+ )
+ self.validate_identity(
+ "DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id = t2.id AND t2.id = t3.id"
+ )
+ self.validate_identity(
+ "INSERT IGNORE INTO subscribers (email) VALUES ('john.doe@gmail.com'), ('jane.smith@ibm.com')"
+ )
+ self.validate_identity(
+ "INSERT INTO t1 (a, b, c) VALUES (1, 2, 3), (4, 5, 6) ON DUPLICATE KEY UPDATE c = VALUES(a) + VALUES(b)"
+ )
+ self.validate_identity(
+ "INSERT INTO t1 (a, b) SELECT c, d FROM t2 UNION SELECT e, f FROM t3 ON DUPLICATE KEY UPDATE b = b + c"
+ )
+ self.validate_identity(
+ "INSERT INTO t1 (a, b, c) VALUES (1, 2, 3) ON DUPLICATE KEY UPDATE c = c + 1"
+ )
+ self.validate_identity(
+ "INSERT INTO x VALUES (1, 'a', 2.0) ON DUPLICATE KEY UPDATE x.id = 1"
)
self.validate_all(
@@ -48,6 +69,14 @@ class TestMySQL(Validator):
)
def test_identity(self):
+ self.validate_identity("SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM t1 INNER JOIN t2")
+ self.validate_identity("SELECT /*+ MERGE(dt) */ * FROM (SELECT * FROM t1) AS dt")
+ self.validate_identity("SELECT /*+ INDEX(t, i) */ c1 FROM t WHERE c2 = 'value'")
+ self.validate_identity("SELECT @a MEMBER OF(@c), @b MEMBER OF(@c)")
+ self.validate_identity("SELECT JSON_ARRAY(4, 5) MEMBER OF('[[3,4],[4,5]]')")
+ self.validate_identity("SELECT CAST('[4,5]' AS JSON) MEMBER OF('[[3,4],[4,5]]')")
+ self.validate_identity("""SELECT 'ab' MEMBER OF('[23, "abc", 17, "ab", 10]')""")
+ self.validate_identity("""SELECT * FROM foo WHERE 'ab' MEMBER OF(content)""")
self.validate_identity("CAST(x AS ENUM('a', 'b'))")
self.validate_identity("CAST(x AS SET('a', 'b'))")
self.validate_identity("SELECT CURRENT_TIMESTAMP(6)")
@@ -61,8 +90,15 @@ class TestMySQL(Validator):
self.validate_identity("CREATE TABLE A LIKE B")
self.validate_identity("SELECT * FROM t1, t2 FOR SHARE OF t1, t2 SKIP LOCKED")
self.validate_identity(
+ """SELECT * FROM foo WHERE 3 MEMBER OF(JSON_EXTRACT(info, '$.value'))"""
+ )
+ self.validate_identity(
"SELECT * FROM t1, t2, t3 FOR SHARE OF t1 NOWAIT FOR UPDATE OF t2, t3 SKIP LOCKED"
)
+ self.validate_identity(
+ """SELECT * FROM foo WHERE 3 MEMBER OF(info->'$.value')""",
+ """SELECT * FROM foo WHERE 3 MEMBER OF(JSON_EXTRACT(info, '$.value'))""",
+ )
# Index hints
self.validate_identity(
@@ -403,6 +439,13 @@ class TestMySQL(Validator):
self.validate_all("CAST(x AS UNSIGNED)", write={"mysql": "CAST(x AS UNSIGNED)"})
self.validate_all("CAST(x AS UNSIGNED INTEGER)", write={"mysql": "CAST(x AS UNSIGNED)"})
self.validate_all(
+ """SELECT 17 MEMBER OF('[23, "abc", 17, "ab", 10]')""",
+ write={
+ "": """SELECT JSON_ARRAY_CONTAINS(17, '[23, "abc", 17, "ab", 10]')""",
+ "mysql": """SELECT 17 MEMBER OF('[23, "abc", 17, "ab", 10]')""",
+ },
+ )
+ self.validate_all(
"SELECT DATE_ADD('2023-06-23 12:00:00', INTERVAL 2 * 2 MONTH) FROM foo",
write={
"mysql": "SELECT DATE_ADD('2023-06-23 12:00:00', INTERVAL (2 * 2) MONTH) FROM foo",