Repository: tobymao/sqlglot

Issue:
MySQL parser not support JSON Function
It is understandable that a JSON function for a word is considered anonymou.
But sqlglot parser not support  `MEMBER OF()` function that introduced in 8.0.17.

example sql: 
> SELECT 'ab' MEMBER OF('[23, "abc", 17, "ab", 10]');


refs: https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of



Discussion:

Patch:
diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py
index 5f743eef..07e744df 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -123,14 +123,15 @@ class MySQL(Dialect):
         KEYWORDS = {
             **tokens.Tokenizer.KEYWORDS,
             "CHARSET": TokenType.CHARACTER_SET,
+            "ENUM": TokenType.ENUM,
             "FORCE": TokenType.FORCE,
             "IGNORE": TokenType.IGNORE,
             "LONGBLOB": TokenType.LONGBLOB,
             "LONGTEXT": TokenType.LONGTEXT,
             "MEDIUMBLOB": TokenType.MEDIUMBLOB,
             "MEDIUMTEXT": TokenType.MEDIUMTEXT,
+            "MEMBER OF": TokenType.MEMBER_OF,
             "SEPARATOR": TokenType.SEPARATOR,
-            "ENUM": TokenType.ENUM,
             "START": TokenType.BEGIN,
             "SIGNED": TokenType.BIGINT,
             "SIGNED INTEGER": TokenType.BIGINT,
@@ -185,11 +186,24 @@ class MySQL(Dialect):
         COMMANDS = tokens.Tokenizer.COMMANDS - {TokenType.SHOW}
 
     class Parser(parser.Parser):
-        FUNC_TOKENS = {*parser.Parser.FUNC_TOKENS, TokenType.SCHEMA, TokenType.DATABASE}
+        FUNC_TOKENS = {
+            *parser.Parser.FUNC_TOKENS,
+            TokenType.DATABASE,
+            TokenType.SCHEMA,
+            TokenType.VALUES,
+        }
+
         TABLE_ALIAS_TOKENS = (
             parser.Parser.TABLE_ALIAS_TOKENS - parser.Parser.TABLE_INDEX_HINT_TOKENS
         )
 
+        RANGE_PARSERS = {
+            **parser.Parser.RANGE_PARSERS,
+            TokenType.MEMBER_OF: lambda self, this: self.expression(
+                exp.JSONArrayContains, this=this, expression=self._parse_wrapped(self._parse_string)
+            ),
+        }
+
         FUNCTIONS = {
             **parser.Parser.FUNCTIONS,
             "DATE_ADD": parse_date_delta_with_interval(exp.DateAdd),
@@ -207,6 +221,10 @@ class MySQL(Dialect):
                 this=self._parse_lambda(),
                 separator=self._match(TokenType.SEPARATOR) and self._parse_field(),
             ),
+            # https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_values
+            "VALUES": lambda self: self.expression(
+                exp.Anonymous, this="VALUES", expressions=[self._parse_id_var()]
+            ),
         }
 
         STATEMENT_PARSERS = {
@@ -399,6 +417,7 @@ class MySQL(Dialect):
         NULL_ORDERING_SUPPORTED = False
         JOIN_HINTS = False
         TABLE_HINTS = True
+        DUPLICATE_KEY_UPDATE_WITH_SET = False
 
         TRANSFORMS = {
             **generator.Generator.TRANSFORMS,
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index de88d72d..11e8a59d 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -1525,6 +1525,7 @@ class Insert(Expression):
         "partition": False,
         "alternative": False,
         "where": False,
+        "ignore": False,
     }
 
     def with_(
@@ -3623,6 +3624,11 @@ class Is(Binary, Predicate):
     pass
 
 
+# https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of
+class JSONArrayContains(Binary, Predicate):
+    pass
+
+
 class Kwarg(Binary):
     """Kwarg in special functions like func(kwarg => y)."""
 
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index d7794f70..b2c10e02 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -143,6 +143,9 @@ class Generator:
     # Whether or not comparing against booleans (e.g. x IS TRUE) is supported
     IS_BOOL_ALLOWED = True
 
+    # Whether or not to include the "SET" keyword in the "INSERT ... ON DUPLICATE KEY UPDATE" statement
+    DUPLICATE_KEY_UPDATE_WITH_SET = True
+
     # https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax
     SELECT_KINDS: t.Tuple[str, ...] = ("STRUCT", "VALUE")
 
@@ -1105,6 +1108,8 @@ class Generator:
 
         alternative = expression.args.get("alternative")
         alternative = f" OR {alternative}" if alternative else ""
+        ignore = " IGNORE" if expression.args.get("ignore") else ""
+
         this = f"{this} {self.sql(expression, 'this')}"
 
         exists = " IF EXISTS" if expression.args.get("exists") else ""
@@ -1116,7 +1121,7 @@ class Generator:
         expression_sql = f"{self.sep()}{self.sql(expression, 'expression')}"
         conflict = self.sql(expression, "conflict")
         returning = self.sql(expression, "returning")
-        sql = f"INSERT{alternative}{this}{exists}{partition_sql}{where}{expression_sql}{conflict}{returning}"
+        sql = f"INSERT{alternative}{ignore}{this}{exists}{partition_sql}{where}{expression_sql}{conflict}{returning}"
         return self.prepend_ctes(expression, sql)
 
     def intersect_sql(self, expression: exp.Intersect) -> str:
@@ -1143,8 +1148,9 @@ class Generator:
         do = "" if expression.args.get("duplicate") else " DO "
         nothing = "NOTHING" if expression.args.get("nothing") else ""
         expressions = self.expressions(expression, flat=True)
+        set_keyword = "SET " if self.DUPLICATE_KEY_UPDATE_WITH_SET else ""
         if expressions:
-            expressions = f"UPDATE SET {expressions}"
+            expressions = f"UPDATE {set_keyword}{expressions}"
         return f"{self.seg(conflict)} {constraint}{key}{do}{nothing}{expressions}"
 
     def returning_sql(self, expression: exp.Returning) -> str:
@@ -2434,6 +2440,9 @@ class Generator:
 
         return self.func("ANY_VALUE", this)
 
+    def jsonarraycontains_sql(self, expression: exp.JSONArrayContains) -> str:
+        return f"{self.sql(expression, 'this')} MEMBER OF({self.sql(expression, 'expression')})"
+
 
 def cached_generator(
     cache: t.Optional[t.Dict[int, str]] = None
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index b7704de1..13a61ed0 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -1679,6 +1679,7 @@ class Parser(metaclass=_Parser):
 
     def _parse_insert(self) -> exp.Insert:
         overwrite = self._match(TokenType.OVERWRITE)
+        ignore = self._match(TokenType.IGNORE)
         local = self._match_text_seq("LOCAL")
         alternative = None
 
@@ -1709,6 +1710,7 @@ class Parser(metaclass=_Parser):
             returning=self._parse_returning(),
             overwrite=overwrite,
             alternative=alternative,
+            ignore=ignore,
         )
 
     def _parse_on_conflict(self) -> t.Optional[exp.OnConflict]:
@@ -1734,7 +1736,8 @@ class Parser(metaclass=_Parser):
             nothing = True
         else:
             self._match(TokenType.UPDATE)
-            expressions = self._match(TokenType.SET) and self._parse_csv(self._parse_equality)
+            self._match(TokenType.SET)
+            expressions = self._parse_csv(self._parse_equality)
 
         return self.expression(
             exp.OnConflict,
@@ -1917,7 +1920,7 @@ class Parser(metaclass=_Parser):
                 self.raise_error("Cannot specify both ALL and DISTINCT after SELECT")
 
             limit = self._parse_limit(top=True)
-            expressions = self._parse_csv(self._parse_expression)
+            expressions = self._parse_expressions()
 
             this = self.expression(
                 exp.Select,
@@ -2091,9 +2094,7 @@ class Parser(metaclass=_Parser):
 
         partition = self._parse_partition_by()
         order = self._parse_order()
-        measures = (
-            self._parse_csv(self._parse_expression) if self._match_text_seq("MEASURES") else None
-        )
+        measures = self._parse_expressions() if self._match_text_seq("MEASURES") else None
 
         if self._match_text_seq("ONE", "ROW", "PER", "MATCH"):
             rows = exp.var("ONE ROW PER MATCH")
@@ -3174,7 +3175,7 @@ class Parser(metaclass=_Parser):
             if query:
                 expressions = [query]
             else:
-                expressions = self._parse_csv(self._parse_expression)
+                expressions = self._parse_expressions()
 
             this = self._parse_query_modifiers(seq_get(expressions, 0))
 
@@ -4226,7 +4227,7 @@ class Parser(metaclass=_Parser):
             return None
         if self._match(TokenType.L_PAREN, advance=False):
             return self._parse_wrapped_csv(self._parse_expression)
-        return self._parse_csv(self._parse_expression)
+        return self._parse_expressions()
 
     def _parse_csv(
         self, parse_method: t.Callable, sep: TokenType = TokenType.COMMA
@@ -4276,6 +4277,9 @@ class Parser(metaclass=_Parser):
             self._match_r_paren()
         return parse_result
 
+    def _parse_expressions(self) -> t.List[t.Optional[exp.Expression]]:
+        return self._parse_csv(self._parse_expression)
+
     def _parse_select_or_expression(self, alias: bool = False) -> t.Optional[exp.Expression]:
         return self._parse_select() or self._parse_set_operations(
             self._parse_expression() if alias else self._parse_conjunction()
diff --git a/sqlglot/tokens.py b/sqlglot/tokens.py
index 79f7a659..8657b73b 100644
--- a/sqlglot/tokens.py
+++ b/sqlglot/tokens.py
@@ -239,6 +239,7 @@ class TokenType(AutoName):
     LOCK = auto()
     MAP = auto()
     MATCH_RECOGNIZE = auto()
+    MEMBER_OF = auto()
     MERGE = auto()
     MOD = auto()
     NATURAL = auto()


Test patch:
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index ca2f9210..6cc50e05 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -10,7 +10,19 @@ 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"
+            "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 +60,11 @@ class TestMySQL(Validator):
         )
 
     def test_identity(self):
+        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 17 MEMBER OF('[23, "abc", 17, "ab", 10]')""")
         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)")