Convert expression type for concat + Enum

Fixed bug involving new value translation and validation feature
in :class:`.Enum` whereby using the enum object in a string
concatenation would maintain the :class:`.Enum` type as the type
of the expression overall, producing missing lookups.  A string
concatenation against an :class:`.Enum`-typed column now uses
:class:`.String` as the datatype of the expression itself.

Change-Id: Id402054e3ef008e0250c740dbb7e1c80f339fe78
Fixes: #3833

3 files changed, 43 insertions, 0 deletions

diff --git a/doc/build/changelog/changelog_11.rst b/doc/build/changelog/changelog_11.rst
index 3c00736c7..6dd09c4d9 100644
--- a/doc/build/changelog/changelog_11.rst
+++ b/doc/build/changelog/changelog_11.rst
@@ -23,6 +23,17 @@
 
     .. change::
         :tags: bug, sql
+        :tickets: 3833
+
+        Fixed bug involving new value translation and validation feature
+        in :class:`.Enum` whereby using the enum object in a string
+        concatenation would maintain the :class:`.Enum` type as the type
+        of the expression overall, producing missing lookups.  A string
+        concatenation against an :class:`.Enum`-typed column now uses
+        :class:`.String` as the datatype of the expression itself.
+
+    .. change::
+        :tags: bug, sql
         :tickets: 3832
 
         Fixed regression which occurred as a side effect of :ticket:`2919`,
diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py
index cae23902b..ef1624fa0 100644
--- a/lib/sqlalchemy/sql/sqltypes.py
+++ b/lib/sqlalchemy/sql/sqltypes.py
@@ -1296,6 +1296,19 @@ class Enum(String, SchemaType):
                 raise LookupError(
                     '"%s" is not among the defined enum values' % elem)
 
+    class Comparator(String.Comparator):
+
+        def _adapt_expression(self, op, other_comparator):
+            op, typ = super(Enum.Comparator, self)._adapt_expression(
+                op, other_comparator)
+            if op is operators.concat_op:
+                typ = String(
+                    self.type.length,
+                    convert_unicode=self.type.convert_unicode)
+            return op, typ
+
+    comparator_factory = Comparator
+
     def _object_value_for_elem(self, elem):
         try:
             return self._object_lookup[elem]
diff --git a/test/sql/test_types.py b/test/sql/test_types.py
index 3374a6721..7f49991e6 100644
--- a/test/sql/test_types.py
+++ b/test/sql/test_types.py
@@ -1264,6 +1264,25 @@ class EnumTest(AssertsCompiledSQL, fixtures.TablesTest):
             ]
         )
 
+    def test_validators_not_in_concatenate_roundtrip(self):
+        enum_table = self.tables['non_native_enum_table']
+
+        enum_table.insert().execute([
+            {'id': 1, 'someenum': 'two'},
+            {'id': 2, 'someenum': 'two'},
+            {'id': 3, 'someenum': 'one'},
+        ])
+
+        eq_(
+            select(['foo' + enum_table.c.someenum]).
+            order_by(enum_table.c.id).execute().fetchall(),
+            [
+                ('footwo', ),
+                ('footwo', ),
+                ('fooone', )
+            ]
+        )
+
     @testing.fails_on(
         'postgresql+zxjdbc',
         'zxjdbc fails on ENUM: column "XXX" is of type XXX '