revert MySQL to use DESCRIBE for has_table()

Restored the behavior of :meth:`.Inspector.has_table` to report on
temporary tables for MySQL / MariaDB. This is currently the behavior for
all other included dialects, but was removed for MySQL in 1.4 due to no
longer using the DESCRIBE command; there was no documented support for temp
tables being reported by the :meth:`.Inspector.has_table` method in this
version or on any previous version, so the previous behavior was undefined.

As SQLAlchemy 2.0 has added formal support for temp table status via
:meth:`.Inspector.has_table`, the MySQL /MariaDB dialect has been reverted
to use the "DESCRIBE" statement as it did in the SQLAlchemy 1.3 series and
previously, and test support is added to include MySQL / MariaDB for
this behavior.   The previous issues with ROLLBACK being emitted which
1.4 sought to improve upon don't apply in SQLAlchemy 2.0 due to
simplifications in how :class:`.Connection` handles transactions.

DESCRIBE is necessary as MariaDB in particular has no consistently
available public information schema of any kind in order to report on temp
tables other than DESCRIBE/SHOW COLUMNS, which rely on throwing an error
in order to report no results.

Fixes: #9058
Change-Id: Ic511bd5989ec17beb37b7cddd913732b626af0e6

3 files changed, 47 insertions, 29 deletions

diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index fbd20a0ba..9de616984 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -1013,7 +1013,6 @@ from itertools import compress
 import re
 
 from sqlalchemy import literal_column
-from sqlalchemy import text
 from sqlalchemy.sql import visitors
 from . import reflection as _reflection
 from .enumerated import ENUM
@@ -1070,7 +1069,6 @@ from ...sql import operators
 from ...sql import roles
 from ...sql import sqltypes
 from ...sql import util as sql_util
-from ...sql.sqltypes import Unicode
 from ...types import BINARY
 from ...types import BLOB
 from ...types import BOOLEAN
@@ -2671,21 +2669,30 @@ class MySQLDialect(default.DefaultDialect):
         if schema is None:
             schema = self.default_schema_name
 
-        rs = connection.execute(
-            text(
-                "SELECT COUNT(*) FROM information_schema.tables WHERE "
-                "table_schema = :table_schema AND "
-                "table_name = :table_name"
-            ).bindparams(
-                sql.bindparam("table_schema", type_=Unicode),
-                sql.bindparam("table_name", type_=Unicode),
-            ),
-            {
-                "table_schema": str(schema),
-                "table_name": str(table_name),
-            },
+        assert schema is not None
+
+        full_name = ".".join(
+            self.identifier_preparer._quote_free_identifiers(
+                schema, table_name
+            )
         )
-        return bool(rs.scalar())
+
+        # DESCRIBE *must* be used because there is no information schema
+        # table that returns information on temp tables that is consistently
+        # available on MariaDB / MySQL / engine-agnostic etc.
+        # therefore we have no choice but to use DESCRIBE and an error catch
+        # to detect "False".  See issue #9058
+
+        try:
+            with connection.exec_driver_sql(
+                f"DESCRIBE {full_name}",
+                execution_options={"skip_user_error_events": True},
+            ) as rs:
+                return rs.fetchone() is not None
+        except exc.DBAPIError as e:
+            if self._extract_error_code(e.orig) == 1146:
+                return False
+            raise
 
     @reflection.cache
     def has_sequence(self, connection, sequence_name, schema=None, **kw):
diff --git a/lib/sqlalchemy/engine/interfaces.py b/lib/sqlalchemy/engine/interfaces.py
index c32b1a1fb..c1de13221 100644
--- a/lib/sqlalchemy/engine/interfaces.py
+++ b/lib/sqlalchemy/engine/interfaces.py
@@ -1790,15 +1790,19 @@ class Dialect(EventTarget):
            published so that third-party dialects may provide an
            implementation. It is **not** the public API for checking for table
            presence. Please use the :meth:`.Inspector.has_table` method.
-           Alternatively, for legacy cross-compatibility, the
-           :meth:`_engine.Engine.has_table` method may be used.
 
-        .. versionchanged:: 2.0
+        .. versionchanged:: 2.0:: :meth:`_engine.Dialect.has_table` now
+           formally supports checking for additional table-like objects:
 
-            The :meth:`_engine.Dialect.has_table` method should also check
-            for the presence of views.  In previous versions this
-            behavior was dialect specific. New dialect suite tests were added
-            to ensure that dialects conform with this behavior consistently.
+           * any type of views (plain or materialized)
+           * temporary tables of any kind
+
+           Previously, these two checks were not formally specified and
+           different dialects would vary in their behavior.   The dialect
+           testing suite now includes tests for all of these object types,
+           and dialects to the degree that the backing database supports views
+           or temporary tables should seek to support locating these objects
+           for full compliance.
 
         """
 
diff --git a/lib/sqlalchemy/engine/reflection.py b/lib/sqlalchemy/engine/reflection.py
index 7e1fca0e5..050f18f2f 100644
--- a/lib/sqlalchemy/engine/reflection.py
+++ b/lib/sqlalchemy/engine/reflection.py
@@ -401,7 +401,8 @@ class Inspector(inspection.Inspectable["Inspector"]):
     def has_table(
         self, table_name: str, schema: Optional[str] = None, **kw: Any
     ) -> bool:
-        r"""Return True if the backend has a table or view of the given name.
+        r"""Return True if the backend has a table, view, or temporary
+        table of the given name.
 
         :param table_name: name of the table to check
         :param schema: schema name to query, if not the default schema.
@@ -412,11 +413,17 @@ class Inspector(inspection.Inspectable["Inspector"]):
         .. versionadded:: 1.4 - the :meth:`.Inspector.has_table` method
            replaces the :meth:`_engine.Engine.has_table` method.
 
-        .. versionchanged:: 2.0:: The method checks also for any type of
-           views (plain or materialized).
-           In previous version this behaviour was dialect specific. New
-           dialect suite tests were added to ensure all dialect conform with
-           this behaviour.
+        .. versionchanged:: 2.0:: :meth:`.Inspector.has_table` now formally
+           supports checking for additional table-like objects:
+
+           * any type of views (plain or materialized)
+           * temporary tables of any kind
+
+           Previously, these two checks were not formally specified and
+           different dialects would vary in their behavior.   The dialect
+           testing suite now includes tests for all of these object types
+           and should be supported by all SQLAlchemy-included dialects.
+           Support among third party dialects may be lagging, however.
 
         """
         with self._operation_context() as conn: