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

2 files changed, 102 insertions, 13 deletions

diff --git a/test/dialect/mysql/test_compiler.py b/test/dialect/mysql/test_compiler.py
index b43f7de71..708039f94 100644
--- a/test/dialect/mysql/test_compiler.py
+++ b/test/dialect/mysql/test_compiler.py
@@ -9,6 +9,7 @@ from sqlalchemy import CheckConstraint
 from sqlalchemy import CLOB
 from sqlalchemy import Column
 from sqlalchemy import Computed
+from sqlalchemy import create_engine
 from sqlalchemy import DATE
 from sqlalchemy import Date
 from sqlalchemy import DATETIME
@@ -60,25 +61,87 @@ from sqlalchemy.testing import AssertsCompiledSQL
 from sqlalchemy.testing import eq_
 from sqlalchemy.testing import expect_warnings
 from sqlalchemy.testing import fixtures
+from sqlalchemy.testing import mock
 
 
-class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
-
-    __dialect__ = mysql.dialect()
-
-    def test_reserved_words(self):
+class ReservedWordFixture(AssertsCompiledSQL):
+    @testing.fixture()
+    def mysql_mariadb_reserved_words(self):
         table = Table(
-            "mysql_table",
+            "rw_table",
             MetaData(),
-            Column("col1", Integer),
-            Column("master_ssl_verify_server_cert", Integer),
+            Column("mysql_reserved", Integer),
+            Column("mdb_mysql_reserved", Integer),
+            Column("mdb_reserved", Integer),
         )
-        x = select(table.c.col1, table.c.master_ssl_verify_server_cert)
 
+        expected_mysql = (
+            "SELECT rw_table.`mysql_reserved`, "
+            "rw_table.`mdb_mysql_reserved`, "
+            "rw_table.mdb_reserved FROM rw_table"
+        )
+        expected_mdb = (
+            "SELECT rw_table.mysql_reserved, "
+            "rw_table.`mdb_mysql_reserved`, "
+            "rw_table.`mdb_reserved` FROM rw_table"
+        )
+
+        from sqlalchemy.dialects.mysql import reserved_words
+
+        reserved_words.RESERVED_WORDS_MARIADB.add("mdb_reserved")
+        reserved_words.RESERVED_WORDS_MYSQL.add("mysql_reserved")
+        reserved_words.RESERVED_WORDS_MYSQL.add("mdb_mysql_reserved")
+        reserved_words.RESERVED_WORDS_MARIADB.add("mdb_mysql_reserved")
+
+        try:
+            yield table, expected_mysql, expected_mdb
+        finally:
+
+            reserved_words.RESERVED_WORDS_MARIADB.discard("mdb_reserved")
+            reserved_words.RESERVED_WORDS_MYSQL.discard("mysql_reserved")
+            reserved_words.RESERVED_WORDS_MYSQL.discard("mdb_mysql_reserved")
+            reserved_words.RESERVED_WORDS_MARIADB.discard("mdb_mysql_reserved")
+
+
+class CompileTest(ReservedWordFixture, fixtures.TestBase, AssertsCompiledSQL):
+
+    __dialect__ = mysql.dialect()
+
+    @testing.combinations(
+        ("mariadb", True),
+        ("mysql", False),
+        (mysql.dialect(), False),
+        (mysql.dialect(is_mariadb=True), True),
+        (
+            create_engine(
+                "mysql+pymysql://", module=mock.Mock(paramstyle="format")
+            ).dialect,
+            False,
+        ),
+        (
+            create_engine(
+                "mariadb+pymysql://", module=mock.Mock(paramstyle="format")
+            ).dialect,
+            True,
+        ),
+        argnames="dialect, expect_mariadb",
+    )
+    def test_reserved_words_mysql_vs_mariadb(
+        self, dialect, expect_mariadb, mysql_mariadb_reserved_words
+    ):
+        """test #7167 - compiler level
+
+        We want to make sure that the "is mariadb" flag as well as the
+        correct identifier preparer are set up for dialects no matter how they
+        determine their "is_mariadb" flag.
+
+        """
+
+        table, expected_mysql, expected_mdb = mysql_mariadb_reserved_words
         self.assert_compile(
-            x,
-            "SELECT mysql_table.col1, "
-            "mysql_table.`master_ssl_verify_server_cert` FROM mysql_table",
+            select(table),
+            expected_mdb if expect_mariadb else expected_mysql,
+            dialect=dialect,
         )
 
     def test_create_index_simple(self):
diff --git a/test/dialect/mysql/test_dialect.py b/test/dialect/mysql/test_dialect.py
index 48fe3d3b1..f314bd0af 100644
--- a/test/dialect/mysql/test_dialect.py
+++ b/test/dialect/mysql/test_dialect.py
@@ -9,6 +9,7 @@ from sqlalchemy import exc
 from sqlalchemy import func
 from sqlalchemy import Integer
 from sqlalchemy import MetaData
+from sqlalchemy import select
 from sqlalchemy import Table
 from sqlalchemy import testing
 from sqlalchemy.dialects import mysql
@@ -21,13 +22,38 @@ from sqlalchemy.testing import fixtures
 from sqlalchemy.testing import in_
 from sqlalchemy.testing import is_
 from sqlalchemy.testing import mock
+from sqlalchemy.testing.assertions import AssertsCompiledSQL
+from .test_compiler import ReservedWordFixture
 from ...engine import test_deprecations
 
 
-class BackendDialectTest(fixtures.TestBase):
+class BackendDialectTest(
+    ReservedWordFixture, fixtures.TestBase, AssertsCompiledSQL
+):
     __backend__ = True
     __only_on__ = "mysql", "mariadb"
 
+    def test_reserved_words_mysql_vs_mariadb(
+        self, mysql_mariadb_reserved_words
+    ):
+        """test #7167 - real backend level
+
+        We want to make sure that the "is mariadb" flag as well as the
+        correct identifier preparer are set up for dialects no matter how they
+        determine their "is_mariadb" flag.
+
+        """
+
+        dialect = testing.db.dialect
+        expect_mariadb = testing.only_on("mariadb").enabled
+
+        table, expected_mysql, expected_mdb = mysql_mariadb_reserved_words
+        self.assert_compile(
+            select(table),
+            expected_mdb if expect_mariadb else expected_mysql,
+            dialect=dialect,
+        )
+
     def test_no_show_variables(self):
         from sqlalchemy.testing import mock