From 2826484206b9211062d6228afea3f5d25ddc6030 Mon Sep 17 00:00:00 2001
From: Daniel Knell <contact@danielknell.co.uk>
Date: Mon, 5 Feb 2018 09:25:47 -0500
Subject: [PATCH] fix handling of native enum aliases in sqlalchemy enum
 columns

Fixed bug where the :class:`.Enum` type wouldn't handle
enum "aliases" correctly, when more than one key refers to the
same value.  Pull request courtesy Daniel Knell.

Fixes: #4180
Change-Id: Ia716c00ca6c67aeab56965f0fdd575ecb7c71416
Pull-request: https://github.com/zzzeek/sqlalchemy/pull/420
---
 doc/build/changelog/unreleased_12/4180.rst |  8 ++++++++
 lib/sqlalchemy/sql/sqltypes.py             | 13 ++++++++-----
 test/sql/test_types.py                     | 17 ++++++++++++++---
 3 files changed, 30 insertions(+), 8 deletions(-)
 create mode 100644 doc/build/changelog/unreleased_12/4180.rst

diff --git a/doc/build/changelog/unreleased_12/4180.rst b/doc/build/changelog/unreleased_12/4180.rst
new file mode 100644
index 0000000000..01d8ee0047
--- /dev/null
+++ b/doc/build/changelog/unreleased_12/4180.rst
@@ -0,0 +1,8 @@
+.. change::
+    :tags: bug, sql
+    :tickets: 4180
+
+    Fixed bug where the :class:`.Enum` type wouldn't handle
+    enum "aliases" correctly, when more than one key refers to the
+    same value.  Pull request courtesy Daniel Knell.
+
diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py
index e11a59dacf..ac915c73a9 100644
--- a/lib/sqlalchemy/sql/sqltypes.py
+++ b/lib/sqlalchemy/sql/sqltypes.py
@@ -1352,15 +1352,18 @@ class Enum(Emulated, String, SchemaType):
         self.enums = list(values)
 
         self._valid_lookup = dict(
-            zip(objects, values)
+            zip(reversed(objects), reversed(values))
         )
+
         self._object_lookup = dict(
-            (value, key) for key, value in self._valid_lookup.items()
-        )
-        self._valid_lookup.update(
-            [(value, value) for value in self._valid_lookup.values()]
+            zip(values, objects)
         )
 
+        self._valid_lookup.update([
+            (value, self._valid_lookup[self._object_lookup[value]])
+            for value in values
+        ])
+
     @property
     def native(self):
         return self.native_enum
diff --git a/test/sql/test_types.py b/test/sql/test_types.py
index dd799de1b5..fa917c466b 100644
--- a/test/sql/test_types.py
+++ b/test/sql/test_types.py
@@ -1161,15 +1161,18 @@ class EnumTest(AssertsCompiledSQL, fixtures.TablesTest):
         # Implements PEP 435 in the minimal fashion needed by SQLAlchemy
         __members__ = OrderedDict()
 
-        def __init__(self, name, value):
+        def __init__(self, name, value, alias=None):
             self.name = name
             self.value = value
             self.__members__[name] = self
             setattr(self.__class__, name, self)
+            if alias:
+                self.__members__[alias] = self
+                setattr(self.__class__, alias, self)
 
     one = SomeEnum('one', 1)
     two = SomeEnum('two', 2)
-    three = SomeEnum('three', 3)
+    three = SomeEnum('three', 3, 'four')
 
     @classmethod
     def define_tables(cls, metadata):
@@ -1498,6 +1501,10 @@ class EnumTest(AssertsCompiledSQL, fixtures.TablesTest):
             {'id': 1, 'someenum': self.SomeEnum.two},
             {'id': 2, 'someenum': self.SomeEnum.two},
             {'id': 3, 'someenum': self.SomeEnum.one},
+            {'id': 4, 'someenum': self.SomeEnum.three},
+            {'id': 5, 'someenum': self.SomeEnum.four},
+            {'id': 6, 'someenum': 'three'},
+            {'id': 7, 'someenum': 'four'},
         ])
 
         eq_(
@@ -1507,6 +1514,10 @@ class EnumTest(AssertsCompiledSQL, fixtures.TablesTest):
                 (1, self.SomeEnum.two),
                 (2, self.SomeEnum.two),
                 (3, self.SomeEnum.one),
+                (4, self.SomeEnum.three),
+                (5, self.SomeEnum.three),
+                (6, self.SomeEnum.three),
+                (7, self.SomeEnum.three),
             ]
         )
 
@@ -1533,7 +1544,7 @@ class EnumTest(AssertsCompiledSQL, fixtures.TablesTest):
         is_(e1.adapt(Enum).metadata, e1.metadata)
         e1 = Enum(self.SomeEnum)
         eq_(e1.adapt(ENUM).name, 'someenum')
-        eq_(e1.adapt(ENUM).enums, ['one', 'two', 'three'])
+        eq_(e1.adapt(ENUM).enums, ['one', 'two', 'three', 'four'])
 
     @testing.provide_metadata
     def test_create_metadata_bound_no_crash(self):
-- 
2.47.2