Render correct DDL for unsetting table comments

Fixed issue where the DDL emitted for :class:`.DropTableComment`, which
will be used by an upcoming version of Alembic, was incorrect for the MySQL
and Oracle databases.

Fixes: #4436
Change-Id: I196de09495a37adface4caa9dcbc29a6d0ad159a

6 files changed, 51 insertions, 1 deletions

diff --git a/doc/build/changelog/unreleased_12/4436.rst b/doc/build/changelog/unreleased_12/4436.rst
new file mode 100644
index 000000000..c359b42f7
--- /dev/null
+++ b/doc/build/changelog/unreleased_12/4436.rst
@@ -0,0 +1,7 @@
+.. change::
+   :tags: bug, sql, oracle, mysql
+   :tickets: 4436
+
+   Fixed issue where the DDL emitted for :class:`.DropTableComment`, which
+   will be used by an upcoming version of Alembic, was incorrect for the MySQL
+   and Oracle databases.
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 4a0d82fe1..6e1b4e49a 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -1724,6 +1724,11 @@ class MySQLDDLCompiler(compiler.DDLCompiler):
             ),
         )
 
+    def visit_drop_table_comment(self, create):
+        return "ALTER TABLE %s COMMENT ''" % (
+            self.preparer.format_table(create.element)
+        )
+
     def visit_set_column_comment(self, create):
         return "ALTER TABLE %s CHANGE %s %s" % (
             self.preparer.format_table(create.element.table),
diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py
index b58d038cf..7e9a79909 100644
--- a/lib/sqlalchemy/dialects/oracle/base.py
+++ b/lib/sqlalchemy/dialects/oracle/base.py
@@ -994,6 +994,11 @@ class OracleDDLCompiler(compiler.DDLCompiler):
 
         return text
 
+    def visit_drop_table_comment(self, drop):
+        return "COMMENT ON TABLE %s IS ''" % self.preparer.format_table(
+            drop.element
+        )
+
     def visit_create_index(self, create):
         index = create.element
         self._verify_index_table(index)
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index e77b2880f..898ca75b2 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -1868,6 +1868,11 @@ class PGDDLCompiler(compiler.DDLCompiler):
             colspec += " NOT NULL"
         return colspec
 
+    def visit_drop_table_comment(self, drop):
+        return "COMMENT ON TABLE %s IS NULL" % self.preparer.format_table(
+            drop.element
+        )
+
     def visit_create_enum_type(self, create):
         type_ = create.element
 
diff --git a/lib/sqlalchemy/sql/ddl.py b/lib/sqlalchemy/sql/ddl.py
index f247fa782..d58f378f8 100644
--- a/lib/sqlalchemy/sql/ddl.py
+++ b/lib/sqlalchemy/sql/ddl.py
@@ -688,7 +688,11 @@ class SetTableComment(_CreateDropBase):
 
 
 class DropTableComment(_CreateDropBase):
-    """Represent a COMMENT ON TABLE IS NULL statement."""
+    """Represent a COMMENT ON TABLE '' statement.
+
+    Note this varies a lot across database backends.
+
+    """
 
     __visit_name__ = "drop_table_comment"
 
diff --git a/lib/sqlalchemy/testing/suite/test_ddl.py b/lib/sqlalchemy/testing/suite/test_ddl.py
index 8317590c8..fe6911c40 100644
--- a/lib/sqlalchemy/testing/suite/test_ddl.py
+++ b/lib/sqlalchemy/testing/suite/test_ddl.py
@@ -4,7 +4,9 @@ from .. import util
 from ..assertions import eq_
 from ..config import requirements
 from ... import Column
+from ... import inspect
 from ... import Integer
+from ... import schema
 from ... import String
 from ... import Table
 
@@ -55,5 +57,27 @@ class TableDDLTest(fixtures.TestBase):
         table.create(config.db, checkfirst=False)
         self._simple_roundtrip(table)
 
+    @requirements.comment_reflection
+    @util.provide_metadata
+    def test_add_table_comment(self):
+        table = self._simple_fixture()
+        table.create(config.db, checkfirst=False)
+        table.comment = "a comment"
+        config.db.execute(schema.SetTableComment(table))
+        eq_(
+            inspect(config.db).get_table_comment("test_table"),
+            {"text": "a comment"},
+        )
+
+    @requirements.comment_reflection
+    @util.provide_metadata
+    def test_drop_table_comment(self):
+        table = self._simple_fixture()
+        table.create(config.db, checkfirst=False)
+        table.comment = "a comment"
+        config.db.execute(schema.SetTableComment(table))
+        config.db.execute(schema.DropTableComment(table))
+        eq_(inspect(config.db).get_table_comment("test_table"), {"text": None})
+
 
 __all__ = ("TableDDLTest",)