fix: Update reserved words list of MySQL / MariaDB dialect

Reorganized the list of reserved words into two separate lists, one for
MySQL and one for MariaDB, so that these diverging sets of words can be
managed more accurately; adjusted the MySQL/MariaDB dialect to switch among
these lists based on either explicitly configured or
server-version-detected "MySQL" or "MariaDB" backend. Added all current
reserved words through MySQL 8 and current MariaDB versions including
recently added keywords like "lead" . Pull request courtesy Kevin Kirsche.

1. Move reserved words to it's own file.
2. Add missing reserved words from https://mariadb.com/kb/en/reserved-words/
    * Note: this only adds MariaDB though links to MySQL, it also does not
      include the reserved words for Oracle mode, as listed in the link.

Fixes: #7167
Supercedes: #7197
Closes: #7207
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/7207
Pull-request-sha: 5a682e331069520ccad9e6bf3cc5e4a77a889ef0

Change-Id: Ib25be8148568899f56b5c9b42d4f530ade8a04e3

5 files changed, 563 insertions, 308 deletions

diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 530e7c9cc..ad38fee97 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -983,6 +983,8 @@ from .enumerated import SET
 from .json import JSON
 from .json import JSONIndexType
 from .json import JSONPathType
+from .reserved_words import RESERVED_WORDS_MARIADB
+from .reserved_words import RESERVED_WORDS_MYSQL
 from .types import _FloatType
 from .types import _IntegerType
 from .types import _MatchType
@@ -1037,301 +1039,6 @@ from ...types import DATE
 from ...types import VARBINARY
 from ...util import topological
 
-
-RESERVED_WORDS = set(
-    [
-        "accessible",
-        "action",
-        "add",
-        "admin",
-        "all",
-        "alter",
-        "analyze",
-        "and",
-        "array",  # 8.0
-        "as",
-        "asc",
-        "asensitive",
-        "before",
-        "between",
-        "bigint",
-        "binary",
-        "blob",
-        "both",
-        "by",
-        "call",
-        "cascade",
-        "case",
-        "change",
-        "char",
-        "character",
-        "check",
-        "collate",
-        "column",
-        "columns",
-        "condition",
-        "constraint",
-        "continue",
-        "convert",
-        "create",
-        "cross",
-        "cube",
-        "cume_dist",
-        "current_date",
-        "current_time",
-        "current_timestamp",
-        "current_user",
-        "cursor",
-        "database",
-        "databases",
-        "day_hour",
-        "day_microsecond",
-        "day_minute",
-        "day_second",
-        "dec",
-        "decimal",
-        "declare",
-        "default",
-        "delayed",
-        "delete",
-        "desc",
-        "describe",
-        "deterministic",
-        "distinct",
-        "distinctrow",
-        "div",
-        "double",
-        "drop",
-        "dual",
-        "each",
-        "else",
-        "elseif",
-        "empty",
-        "enclosed",
-        "escaped",
-        "except",
-        "exists",
-        "exit",
-        "explain",
-        "false",
-        "fetch",
-        "fields",
-        "first_value",
-        "float",
-        "float4",
-        "float8",
-        "for",
-        "force",
-        "foreign",
-        "from",
-        "fulltext",
-        "function",
-        "general",
-        "generated",
-        "get",
-        "grant",
-        "group",
-        "grouping",
-        "groups",
-        "having",
-        "high_priority",
-        "hour_microsecond",
-        "hour_minute",
-        "hour_second",
-        "if",
-        "ignore",
-        "ignore_server_ids",
-        "in",
-        "index",
-        "infile",
-        "inner",
-        "inout",
-        "insensitive",
-        "insert",
-        "int",
-        "int1",
-        "int2",
-        "int3",
-        "int4",
-        "int8",
-        "integer",
-        "interval",
-        "into",
-        "io_after_gtids",
-        "io_before_gtids",
-        "is",
-        "iterate",
-        "join",
-        "json_table",
-        "key",
-        "keys",
-        "kill",
-        "last_value",
-        "lateral",
-        "leading",
-        "leave",
-        "left",
-        "level",
-        "like",
-        "limit",
-        "linear",
-        "linear",
-        "lines",
-        "load",
-        "localtime",
-        "localtimestamp",
-        "lock",
-        "long",
-        "longblob",
-        "longtext",
-        "loop",
-        "low_priority",
-        "master_bind",
-        "master_heartbeat_period",
-        "master_ssl_verify_server_cert",
-        "master_ssl_verify_server_cert",
-        "match",
-        "maxvalue",
-        "mediumblob",
-        "mediumint",
-        "mediumtext",
-        "member",  # 8.0
-        "middleint",
-        "minute_microsecond",
-        "minute_second",
-        "mod",
-        "mode",
-        "modifies",
-        "natural",
-        "no_write_to_binlog",
-        "not",
-        "nth_value",
-        "ntile",
-        "null",
-        "numeric",
-        "of",
-        "on",
-        "one_shot",
-        "optimize",
-        "optimizer_costs",
-        "option",
-        "optionally",
-        "or",
-        "order",
-        "out",
-        "outer",
-        "outfile",
-        "over",
-        "partition",
-        "percent_rank",
-        "persist",
-        "persist_only",
-        "precision",
-        "primary",
-        "privileges",
-        "procedure",
-        "purge",
-        "range",
-        "range",
-        "rank",
-        "read",
-        "read_only",
-        "read_only",
-        "read_write",
-        "read_write",  # 5.1
-        "reads",
-        "real",
-        "recursive",
-        "references",
-        "regexp",
-        "release",
-        "rename",
-        "repeat",
-        "replace",
-        "require",
-        "resignal",
-        "restrict",
-        "return",
-        "revoke",
-        "right",
-        "rlike",
-        "role",
-        "row",
-        "row_number",
-        "rows",
-        "schema",
-        "schemas",
-        "second_microsecond",
-        "select",
-        "sensitive",
-        "separator",
-        "set",
-        "show",
-        "signal",
-        "slow",  # 5.5
-        "smallint",
-        "soname",
-        "spatial",
-        "specific",
-        "sql",
-        "sql_after_gtids",
-        "sql_before_gtids",  # 5.6
-        "sql_big_result",
-        "sql_calc_found_rows",
-        "sql_small_result",
-        "sqlexception",
-        "sqlstate",
-        "sqlwarning",
-        "ssl",
-        "starting",
-        "status",
-        "stored",
-        "straight_join",
-        "system",
-        "table",
-        "tables",  # 4.1
-        "terminated",
-        "text",
-        "then",
-        "time",
-        "tinyblob",
-        "tinyint",
-        "tinytext",
-        "to",
-        "trailing",
-        "trigger",
-        "true",
-        "undo",
-        "union",
-        "unique",
-        "unlock",
-        "unsigned",
-        "update",
-        "usage",
-        "use",
-        "using",
-        "utc_date",
-        "utc_time",
-        "utc_timestamp",
-        "values",
-        "varbinary",
-        "varchar",
-        "varcharacter",
-        "varying",
-        "virtual",  # 5.7
-        "when",
-        "where",
-        "while",
-        "window",  # 8.0
-        "with",
-        "write",
-        "x509",
-        "xor",
-        "year_month",
-        "zerofill",  # 5.0
-    ]
-)
-
 AUTOCOMMIT_RE = re.compile(
     r"\s*(?:UPDATE|INSERT|CREATE|DELETE|DROP|ALTER|LOAD +DATA|REPLACE)",
     re.I | re.UNICODE,
@@ -2586,8 +2293,7 @@ class MySQLTypeCompiler(compiler.GenericTypeCompiler):
 
 
 class MySQLIdentifierPreparer(compiler.IdentifierPreparer):
-
-    reserved_words = RESERVED_WORDS
+    reserved_words = RESERVED_WORDS_MYSQL
 
     def __init__(self, dialect, server_ansiquotes=False, **kw):
         if not server_ansiquotes:
@@ -2605,6 +2311,10 @@ class MySQLIdentifierPreparer(compiler.IdentifierPreparer):
         return tuple([self.quote_identifier(i) for i in ids if i is not None])
 
 
+class MariaDBIdentifierPreparer(MySQLIdentifierPreparer):
+    reserved_words = RESERVED_WORDS_MARIADB
+
+
 @log.class_logger
 class MySQLDialect(default.DefaultDialect):
     """Details of the MySQL dialect.
@@ -2834,6 +2544,11 @@ class MySQLDialect(default.DefaultDialect):
                 "MySQL version %s is not a MariaDB variant."
                 % (server_version_info,)
             )
+        if is_mariadb:
+            self.preparer = MariaDBIdentifierPreparer
+            # this would have been set by the default dialect already,
+            # so set it again
+            self.identifier_preparer = self.preparer(self)
         self.is_mariadb = is_mariadb
 
     def do_begin_twophase(self, connection, xid):
diff --git a/lib/sqlalchemy/dialects/mysql/mariadb.py b/lib/sqlalchemy/dialects/mysql/mariadb.py
index 8ebde462b..568c3f0cf 100644
--- a/lib/sqlalchemy/dialects/mysql/mariadb.py
+++ b/lib/sqlalchemy/dialects/mysql/mariadb.py
@@ -1,3 +1,4 @@
+from .base import MariaDBIdentifierPreparer
 from .base import MySQLDialect
 
 
@@ -5,6 +6,7 @@ class MariaDBDialect(MySQLDialect):
     is_mariadb = True
     supports_statement_cache = True
     name = "mariadb"
+    preparer = MariaDBIdentifierPreparer
 
 
 def loader(driver):
diff --git a/lib/sqlalchemy/dialects/mysql/mariadbconnector.py b/lib/sqlalchemy/dialects/mysql/mariadbconnector.py
index 0997abc7a..14ed11b19 100644
--- a/lib/sqlalchemy/dialects/mysql/mariadbconnector.py
+++ b/lib/sqlalchemy/dialects/mysql/mariadbconnector.py
@@ -32,7 +32,6 @@ import re
 from .base import MySQLCompiler
 from .base import MySQLDialect
 from .base import MySQLExecutionContext
-from .base import MySQLIdentifierPreparer
 from ... import sql
 from ... import util
 
@@ -51,10 +50,6 @@ class MySQLCompiler_mariadbconnector(MySQLCompiler):
     pass
 
 
-class MySQLIdentifierPreparer_mariadbconnector(MySQLIdentifierPreparer):
-    pass
-
-
 class MySQLDialect_mariadbconnector(MySQLDialect):
     driver = "mariadbconnector"
     supports_statement_cache = True
@@ -78,7 +73,6 @@ class MySQLDialect_mariadbconnector(MySQLDialect):
     default_paramstyle = "qmark"
     execution_ctx_cls = MySQLExecutionContext_mariadbconnector
     statement_compiler = MySQLCompiler_mariadbconnector
-    preparer = MySQLIdentifierPreparer_mariadbconnector
 
     supports_server_side_cursors = True
 
diff --git a/lib/sqlalchemy/dialects/mysql/mysqldb.py b/lib/sqlalchemy/dialects/mysql/mysqldb.py
index 72a64d3b8..dfe719c28 100644
--- a/lib/sqlalchemy/dialects/mysql/mysqldb.py
+++ b/lib/sqlalchemy/dialects/mysql/mysqldb.py
@@ -120,10 +120,6 @@ class MySQLCompiler_mysqldb(MySQLCompiler):
     pass
 
 
-class MySQLIdentifierPreparer_mysqldb(MySQLIdentifierPreparer):
-    pass
-
-
 class MySQLDialect_mysqldb(MySQLDialect):
     driver = "mysqldb"
     supports_statement_cache = True
@@ -136,7 +132,7 @@ class MySQLDialect_mysqldb(MySQLDialect):
     default_paramstyle = "format"
     execution_ctx_cls = MySQLExecutionContext_mysqldb
     statement_compiler = MySQLCompiler_mysqldb
-    preparer = MySQLIdentifierPreparer_mysqldb
+    preparer = MySQLIdentifierPreparer
 
     def __init__(self, **kwargs):
         super(MySQLDialect_mysqldb, self).__init__(**kwargs)
diff --git a/lib/sqlalchemy/dialects/mysql/reserved_words.py b/lib/sqlalchemy/dialects/mysql/reserved_words.py
new file mode 100644
index 000000000..fc2b13b44
--- /dev/null
+++ b/lib/sqlalchemy/dialects/mysql/reserved_words.py
@@ -0,0 +1,548 @@
+# mysql/reserved_words.py
+# Copyright (C) 2005-2021 the SQLAlchemy authors and contributors
+# <see AUTHORS file>
+#
+# This module is part of SQLAlchemy and is released under
+# the MIT License: https://www.opensource.org/licenses/mit-license.php
+
+# generated using:
+# https://gist.github.com/kkirsche/4f31f2153ed7a3248be1ec44ca6ddbc9
+#
+# https://mariadb.com/kb/en/reserved-words/
+# includes: Reserved Words
+# excludes: Exceptions, Oracle Mode, Function Names
+RESERVED_WORDS_MARIADB = {
+    "accessible",
+    "add",
+    "all",
+    "alter",
+    "analyze",
+    "and",
+    "as",
+    "asc",
+    "asensitive",
+    "before",
+    "between",
+    "bigint",
+    "binary",
+    "blob",
+    "both",
+    "by",
+    "call",
+    "cascade",
+    "case",
+    "change",
+    "char",
+    "character",
+    "check",
+    "collate",
+    "column",
+    "condition",
+    "constraint",
+    "continue",
+    "convert",
+    "create",
+    "cross",
+    "current_date",
+    "current_role",
+    "current_time",
+    "current_timestamp",
+    "current_user",
+    "cursor",
+    "database",
+    "databases",
+    "day_hour",
+    "day_microsecond",
+    "day_minute",
+    "day_second",
+    "dec",
+    "decimal",
+    "declare",
+    "default",
+    "delayed",
+    "delete",
+    "desc",
+    "describe",
+    "deterministic",
+    "distinct",
+    "distinctrow",
+    "div",
+    "do_domain_ids",
+    "double",
+    "drop",
+    "dual",
+    "each",
+    "else",
+    "elseif",
+    "enclosed",
+    "escaped",
+    "except",
+    "exists",
+    "exit",
+    "explain",
+    "false",
+    "fetch",
+    "float",
+    "float4",
+    "float8",
+    "for",
+    "force",
+    "foreign",
+    "from",
+    "fulltext",
+    "general",
+    "grant",
+    "group",
+    "having",
+    "high_priority",
+    "hour_microsecond",
+    "hour_minute",
+    "hour_second",
+    "if",
+    "ignore",
+    "ignore_domain_ids",
+    "ignore_server_ids",
+    "in",
+    "index",
+    "infile",
+    "inner",
+    "inout",
+    "insensitive",
+    "insert",
+    "int",
+    "int1",
+    "int2",
+    "int3",
+    "int4",
+    "int8",
+    "integer",
+    "intersect",
+    "interval",
+    "into",
+    "is",
+    "iterate",
+    "join",
+    "key",
+    "keys",
+    "kill",
+    "leading",
+    "leave",
+    "left",
+    "like",
+    "limit",
+    "linear",
+    "lines",
+    "load",
+    "localtime",
+    "localtimestamp",
+    "lock",
+    "long",
+    "longblob",
+    "longtext",
+    "loop",
+    "low_priority",
+    "master_heartbeat_period",
+    "master_ssl_verify_server_cert",
+    "match",
+    "maxvalue",
+    "mediumblob",
+    "mediumint",
+    "mediumtext",
+    "middleint",
+    "minute_microsecond",
+    "minute_second",
+    "mod",
+    "modifies",
+    "natural",
+    "no_write_to_binlog",
+    "not",
+    "null",
+    "numeric",
+    "offset",
+    "on",
+    "optimize",
+    "option",
+    "optionally",
+    "or",
+    "order",
+    "out",
+    "outer",
+    "outfile",
+    "over",
+    "page_checksum",
+    "parse_vcol_expr",
+    "partition",
+    "position",
+    "precision",
+    "primary",
+    "procedure",
+    "purge",
+    "range",
+    "read",
+    "read_write",
+    "reads",
+    "real",
+    "recursive",
+    "ref_system_id",
+    "references",
+    "regexp",
+    "release",
+    "rename",
+    "repeat",
+    "replace",
+    "require",
+    "resignal",
+    "restrict",
+    "return",
+    "returning",
+    "revoke",
+    "right",
+    "rlike",
+    "rows",
+    "schema",
+    "schemas",
+    "second_microsecond",
+    "select",
+    "sensitive",
+    "separator",
+    "set",
+    "show",
+    "signal",
+    "slow",
+    "smallint",
+    "spatial",
+    "specific",
+    "sql",
+    "sql_big_result",
+    "sql_calc_found_rows",
+    "sql_small_result",
+    "sqlexception",
+    "sqlstate",
+    "sqlwarning",
+    "ssl",
+    "starting",
+    "stats_auto_recalc",
+    "stats_persistent",
+    "stats_sample_pages",
+    "straight_join",
+    "table",
+    "terminated",
+    "then",
+    "tinyblob",
+    "tinyint",
+    "tinytext",
+    "to",
+    "trailing",
+    "trigger",
+    "true",
+    "undo",
+    "union",
+    "unique",
+    "unlock",
+    "unsigned",
+    "update",
+    "usage",
+    "use",
+    "using",
+    "utc_date",
+    "utc_time",
+    "utc_timestamp",
+    "values",
+    "varbinary",
+    "varchar",
+    "varcharacter",
+    "varying",
+    "when",
+    "where",
+    "while",
+    "window",
+    "with",
+    "write",
+    "xor",
+    "year_month",
+    "zerofill",
+}
+
+# https://dev.mysql.com/doc/refman/8.0/en/keywords.html
+# https://dev.mysql.com/doc/refman/5.7/en/keywords.html
+# https://dev.mysql.com/doc/refman/5.6/en/keywords.html
+# includes: MySQL x.0 Keywords and Reserved Words
+# excludes: MySQL x.0 New Keywords and Reserved Words,
+#       MySQL x.0 Removed Keywords and Reserved Words
+RESERVED_WORDS_MYSQL = {
+    "accessible",
+    "add",
+    "admin",
+    "all",
+    "alter",
+    "analyze",
+    "and",
+    "array",
+    "as",
+    "asc",
+    "asensitive",
+    "before",
+    "between",
+    "bigint",
+    "binary",
+    "blob",
+    "both",
+    "by",
+    "call",
+    "cascade",
+    "case",
+    "change",
+    "char",
+    "character",
+    "check",
+    "collate",
+    "column",
+    "condition",
+    "constraint",
+    "continue",
+    "convert",
+    "create",
+    "cross",
+    "cube",
+    "cume_dist",
+    "current_date",
+    "current_time",
+    "current_timestamp",
+    "current_user",
+    "cursor",
+    "database",
+    "databases",
+    "day_hour",
+    "day_microsecond",
+    "day_minute",
+    "day_second",
+    "dec",
+    "decimal",
+    "declare",
+    "default",
+    "delayed",
+    "delete",
+    "dense_rank",
+    "desc",
+    "describe",
+    "deterministic",
+    "distinct",
+    "distinctrow",
+    "div",
+    "double",
+    "drop",
+    "dual",
+    "each",
+    "else",
+    "elseif",
+    "empty",
+    "enclosed",
+    "escaped",
+    "except",
+    "exists",
+    "exit",
+    "explain",
+    "false",
+    "fetch",
+    "first_value",
+    "float",
+    "float4",
+    "float8",
+    "for",
+    "force",
+    "foreign",
+    "from",
+    "fulltext",
+    "function",
+    "general",
+    "generated",
+    "get",
+    "get_master_public_key",
+    "grant",
+    "group",
+    "grouping",
+    "groups",
+    "having",
+    "high_priority",
+    "hour_microsecond",
+    "hour_minute",
+    "hour_second",
+    "if",
+    "ignore",
+    "ignore_server_ids",
+    "in",
+    "index",
+    "infile",
+    "inner",
+    "inout",
+    "insensitive",
+    "insert",
+    "int",
+    "int1",
+    "int2",
+    "int3",
+    "int4",
+    "int8",
+    "integer",
+    "interval",
+    "into",
+    "io_after_gtids",
+    "io_before_gtids",
+    "is",
+    "iterate",
+    "join",
+    "json_table",
+    "key",
+    "keys",
+    "kill",
+    "lag",
+    "last_value",
+    "lateral",
+    "lead",
+    "leading",
+    "leave",
+    "left",
+    "like",
+    "limit",
+    "linear",
+    "lines",
+    "load",
+    "localtime",
+    "localtimestamp",
+    "lock",
+    "long",
+    "longblob",
+    "longtext",
+    "loop",
+    "low_priority",
+    "master_bind",
+    "master_heartbeat_period",
+    "master_ssl_verify_server_cert",
+    "match",
+    "maxvalue",
+    "mediumblob",
+    "mediumint",
+    "mediumtext",
+    "member",
+    "middleint",
+    "minute_microsecond",
+    "minute_second",
+    "mod",
+    "modifies",
+    "natural",
+    "no_write_to_binlog",
+    "not",
+    "nth_value",
+    "ntile",
+    "null",
+    "numeric",
+    "of",
+    "on",
+    "optimize",
+    "optimizer_costs",
+    "option",
+    "optionally",
+    "or",
+    "order",
+    "out",
+    "outer",
+    "outfile",
+    "over",
+    "parse_gcol_expr",
+    "partition",
+    "percent_rank",
+    "persist",
+    "persist_only",
+    "precision",
+    "primary",
+    "procedure",
+    "purge",
+    "range",
+    "rank",
+    "read",
+    "read_write",
+    "reads",
+    "real",
+    "recursive",
+    "references",
+    "regexp",
+    "release",
+    "rename",
+    "repeat",
+    "replace",
+    "require",
+    "resignal",
+    "restrict",
+    "return",
+    "revoke",
+    "right",
+    "rlike",
+    "role",
+    "row",
+    "row_number",
+    "rows",
+    "schema",
+    "schemas",
+    "second_microsecond",
+    "select",
+    "sensitive",
+    "separator",
+    "set",
+    "show",
+    "signal",
+    "slow",
+    "smallint",
+    "spatial",
+    "specific",
+    "sql",
+    "sql_after_gtids",
+    "sql_before_gtids",
+    "sql_big_result",
+    "sql_calc_found_rows",
+    "sql_small_result",
+    "sqlexception",
+    "sqlstate",
+    "sqlwarning",
+    "ssl",
+    "starting",
+    "stored",
+    "straight_join",
+    "system",
+    "table",
+    "terminated",
+    "then",
+    "tinyblob",
+    "tinyint",
+    "tinytext",
+    "to",
+    "trailing",
+    "trigger",
+    "true",
+    "undo",
+    "union",
+    "unique",
+    "unlock",
+    "unsigned",
+    "update",
+    "usage",
+    "use",
+    "using",
+    "utc_date",
+    "utc_time",
+    "utc_timestamp",
+    "values",
+    "varbinary",
+    "varchar",
+    "varcharacter",
+    "varying",
+    "virtual",
+    "when",
+    "where",
+    "while",
+    "window",
+    "with",
+    "write",
+    "xor",
+    "year_month",
+    "zerofill",
+}