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

1 files changed, 25 insertions, 0 deletions

diff --git a/test/dialect/mysql/test_compiler.py b/test/dialect/mysql/test_compiler.py
index 52d4529ae..cd7205163 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))",
+        )