From dd4e09d38729c2479af19da082a07f0c44f7026c Mon Sep 17 00:00:00 2001
From: Mike Bayer <mike_mp@zzzcomputing.com>
Date: Fri, 28 Oct 2016 14:13:31 -0400
Subject: [PATCH] Add check for blank string coming from MySQL's enum

MySQL's native ENUM type supports any non-valid value being sent, and
in response will return a blank string.  A hardcoded rule to check for
"is returning the blank string" has been added to the  MySQL
implementation for ENUM so that this blank string is returned to the
application rather than being rejected as a non-valid value.  Note that
if your MySQL enum is linking values to objects, you still get the
blank string back.

Change-Id: I61f85c20293a48b0c11a31f2a19f6756c206bd20
Fixes: #3841
---
 doc/build/changelog/changelog_11.rst        | 12 +++++++++++
 lib/sqlalchemy/dialects/mysql/enumerated.py | 10 +++++++++
 test/dialect/mysql/test_types.py            | 24 +++++++++++++++++++++
 3 files changed, 46 insertions(+)

diff --git a/doc/build/changelog/changelog_11.rst b/doc/build/changelog/changelog_11.rst
index dd260d260c..05c4529bb7 100644
--- a/doc/build/changelog/changelog_11.rst
+++ b/doc/build/changelog/changelog_11.rst
@@ -21,6 +21,18 @@
 .. changelog::
     :version: 1.1.4
 
+    .. change::
+        :tags: bug, mysql
+        :tickets: 3841
+
+        MySQL's native ENUM type supports any non-valid value being sent, and
+        in response will return a blank string.  A hardcoded rule to check for
+        "is returning the blank string" has been added to the  MySQL
+        implementation for ENUM so that this blank string is returned to the
+        application rather than being rejected as a non-valid value.  Note that
+        if your MySQL enum is linking values to objects, you still get the
+        blank string back.
+
 .. changelog::
     :version: 1.1.3
     :released: October 27, 2016
diff --git a/lib/sqlalchemy/dialects/mysql/enumerated.py b/lib/sqlalchemy/dialects/mysql/enumerated.py
index a47d94ca75..a7cd8911c1 100644
--- a/lib/sqlalchemy/dialects/mysql/enumerated.py
+++ b/lib/sqlalchemy/dialects/mysql/enumerated.py
@@ -130,6 +130,16 @@ class ENUM(sqltypes.Enum, _EnumeratedValues):
         values, length = self._init_values(values, kw)
         return sqltypes.Enum._setup_for_values(self, values, objects, kw)
 
+    def _object_value_for_elem(self, elem):
+        # mysql sends back a blank string for any value that
+        # was persisted that was not in the enums; that is, it does no
+        # validation on the incoming data, it "truncates" it to be
+        # the blank string.  Return it straight.
+        if elem == "":
+            return elem
+        else:
+            return super(ENUM, self)._object_value_for_elem(elem)
+
     def __repr__(self):
         return util.generic_repr(
             self, to_inspect=[ENUM, _StringType, sqltypes.Enum])
diff --git a/test/dialect/mysql/test_types.py b/test/dialect/mysql/test_types.py
index 0cbb507c54..48663da25d 100644
--- a/test/dialect/mysql/test_types.py
+++ b/test/dialect/mysql/test_types.py
@@ -1063,6 +1063,30 @@ class EnumSetTest(
             eq_(t.c.e6.type.values, ("", "a"))
             eq_(t.c.e7.type.values, ("", "'a'", "b'b", "'"))
 
+    @testing.provide_metadata
+    def test_broken_enum_returns_blanks(self):
+        t = Table(
+            'enum_missing',
+            self.metadata,
+            Column('id', Integer, primary_key=True),
+            Column('e1', sqltypes.Enum('one', 'two', 'three')),
+            Column('e2', mysql.ENUM('one', 'two', 'three'))
+        )
+        t.create()
+
+        with testing.db.connect() as conn:
+            conn.execute(t.insert(), {"e1": "nonexistent", "e2": "nonexistent"})
+            conn.execute(t.insert(), {"e1": "", "e2": ""})
+            conn.execute(t.insert(), {"e1": "two", "e2": "two"})
+            conn.execute(t.insert(), {"e1": None, "e2": None})
+
+            eq_(
+                conn.execute(
+                    select([t.c.e1, t.c.e2]).order_by(t.c.id)
+                ).fetchall(),
+                [("", ""), ("", ""), ("two", "two"), (None, None)]
+            )
+
 
 def colspec(c):
     return testing.db.dialect.ddl_compiler(
-- 
2.47.2