From 348d76072c108e996baf59900fc45468f48c4cce Mon Sep 17 00:00:00 2001
From: "J. Nick Koston" <nick@koston.org>
Date: Sat, 25 Mar 2023 22:00:25 -0400
Subject: [PATCH] Fix creating zero length char with MySQL dialect

Fixed issue where string datatypes such as :class:`.CHAR`,
:class:`.VARCHAR`, :class:`.TEXT`, as well as binary :class:`.BLOB`, could
not be produced with an explicit length of zero, which has special meaning
for MySQL. Pull request courtesy J. Nick Koston.

Fixes: #9544
Closes: #9543
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/9543
Pull-request-sha: dc17fc3e93f0ba90881c4efb06016ddf83c7af8b

Change-Id: I96925d45f16887f5dfd68a5d4f9284b3abc46d25
---
 doc/build/changelog/unreleased_20/9543.rst |  8 +++++++
 lib/sqlalchemy/dialects/mysql/base.py      | 12 +++++------
 test/dialect/mysql/test_compiler.py        | 25 ++++++++++++++++++++++
 3 files changed, 39 insertions(+), 6 deletions(-)
 create mode 100644 doc/build/changelog/unreleased_20/9543.rst

diff --git a/doc/build/changelog/unreleased_20/9543.rst b/doc/build/changelog/unreleased_20/9543.rst
new file mode 100644
index 0000000000..593507c410
--- /dev/null
+++ b/doc/build/changelog/unreleased_20/9543.rst
@@ -0,0 +1,8 @@
+.. change::
+    :tags: bug, mysql
+    :tickets: 9544
+
+    Fixed issue where string datatypes such as :class:`.CHAR`,
+    :class:`.VARCHAR`, :class:`.TEXT`, as well as binary :class:`.BLOB`, could
+    not be produced with an explicit length of zero, which has special meaning
+    for MySQL. Pull request courtesy J. Nick Koston.
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index ebef48a772..387b0141c4 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -2252,7 +2252,7 @@ class MySQLTypeCompiler(compiler.GenericTypeCompiler):
             return "YEAR(%s)" % type_.display_width
 
     def visit_TEXT(self, type_, **kw):
-        if type_.length:
+        if type_.length is not None:
             return self._extend_string(type_, {}, "TEXT(%d)" % type_.length)
         else:
             return self._extend_string(type_, {}, "TEXT")
@@ -2267,7 +2267,7 @@ class MySQLTypeCompiler(compiler.GenericTypeCompiler):
         return self._extend_string(type_, {}, "LONGTEXT")
 
     def visit_VARCHAR(self, type_, **kw):
-        if type_.length:
+        if type_.length is not None:
             return self._extend_string(type_, {}, "VARCHAR(%d)" % type_.length)
         else:
             raise exc.CompileError(
@@ -2275,7 +2275,7 @@ class MySQLTypeCompiler(compiler.GenericTypeCompiler):
             )
 
     def visit_CHAR(self, type_, **kw):
-        if type_.length:
+        if type_.length is not None:
             return self._extend_string(
                 type_, {}, "CHAR(%(length)s)" % {"length": type_.length}
             )
@@ -2285,7 +2285,7 @@ class MySQLTypeCompiler(compiler.GenericTypeCompiler):
     def visit_NVARCHAR(self, type_, **kw):
         # We'll actually generate the equiv. "NATIONAL VARCHAR" instead
         # of "NVARCHAR".
-        if type_.length:
+        if type_.length is not None:
             return self._extend_string(
                 type_,
                 {"national": True},
@@ -2299,7 +2299,7 @@ class MySQLTypeCompiler(compiler.GenericTypeCompiler):
     def visit_NCHAR(self, type_, **kw):
         # We'll actually generate the equiv.
         # "NATIONAL CHAR" instead of "NCHAR".
-        if type_.length:
+        if type_.length is not None:
             return self._extend_string(
                 type_,
                 {"national": True},
@@ -2327,7 +2327,7 @@ class MySQLTypeCompiler(compiler.GenericTypeCompiler):
             return self._visit_enumerated_values("ENUM", type_, type_.enums)
 
     def visit_BLOB(self, type_, **kw):
-        if type_.length:
+        if type_.length is not None:
             return "BLOB(%d)" % type_.length
         else:
             return "BLOB"
diff --git a/test/dialect/mysql/test_compiler.py b/test/dialect/mysql/test_compiler.py
index 52d4529aec..cd7205163a 100644
--- a/test/dialect/mysql/test_compiler.py
+++ b/test/dialect/mysql/test_compiler.py
@@ -41,6 +41,7 @@ from sqlalchemy import String
 from sqlalchemy import Table
 from sqlalchemy import testing
 from sqlalchemy import TEXT
+from sqlalchemy import Text
 from sqlalchemy import text
 from sqlalchemy import TIME
 from sqlalchemy import Time
@@ -746,6 +747,7 @@ class SQLTest(fixtures.TestBase, AssertsCompiledSQL):
         (String(32), "CAST(t.col AS CHAR(32))"),
         (Unicode(32), "CAST(t.col AS CHAR(32))"),
         (CHAR(32), "CAST(t.col AS CHAR(32))"),
+        (CHAR(0), "CAST(t.col AS CHAR(0))"),
         (m.MSString, "CAST(t.col AS CHAR)"),
         (m.MSText, "CAST(t.col AS CHAR)"),
         (m.MSTinyText, "CAST(t.col AS CHAR)"),
@@ -1526,3 +1528,26 @@ class MatchExpressionTest(fixtures.TestBase, AssertsCompiledSQL):
             "MATCH ('x') AGAINST ('y' IN BOOLEAN MODE)",
             literal_binds=True,
         )
+
+    def test_char_zero(self):
+        """test #9544"""
+
+        t1 = Table(
+            "sometable",
+            MetaData(),
+            Column("a", CHAR(0)),
+            Column("b", VARCHAR(0)),
+            Column("c", String(0)),
+            Column("d", NVARCHAR(0)),
+            Column("e", NCHAR(0)),
+            Column("f", TEXT(0)),
+            Column("g", Text(0)),
+            Column("h", BLOB(0)),
+            Column("i", LargeBinary(0)),
+        )
+        self.assert_compile(
+            schema.CreateTable(t1),
+            "CREATE TABLE sometable (a CHAR(0), b VARCHAR(0), "
+            "c VARCHAR(0), d NATIONAL VARCHAR(0), e NATIONAL CHAR(0), "
+            "f TEXT(0), g TEXT(0), h BLOB(0), i BLOB(0))",
+        )
-- 
2.47.2