- Fixed regression where :meth:`.ResultProxy.keys` would return

un-adjusted internal symbol names for "anonymous" labels, which
are the "foo_1" types of labels we see generated for SQL functions
without labels and similar.  This was a side effect of the
performance enhancements implemented as part of references #918.
fixes #3483

6 files changed, 123 insertions, 12 deletions

diff --git a/doc/build/changelog/changelog_10.rst b/doc/build/changelog/changelog_10.rst
index 7dae2bebd..1e00ddcd4 100644
--- a/doc/build/changelog/changelog_10.rst
+++ b/doc/build/changelog/changelog_10.rst
@@ -19,6 +19,17 @@
     :version: 1.0.7
 
     .. change::
+        :tags: bug, engine
+        :tickets: 3483
+
+        Fixed regression where :meth:`.ResultProxy.keys` would return
+        un-adjusted internal symbol names for "anonymous" labels, which
+        are the "foo_1" types of labels we see generated for SQL functions
+        without labels and similar.  This was a side effect of the
+        performance enhancements implemented as part of #918.
+
+
+    .. change::
         :tags: bug, sql
         :tickets: 3490
 
diff --git a/lib/sqlalchemy/engine/result.py b/lib/sqlalchemy/engine/result.py
index b2b78dee8..3fcab873b 100644
--- a/lib/sqlalchemy/engine/result.py
+++ b/lib/sqlalchemy/engine/result.py
@@ -221,7 +221,7 @@ class ResultMetaData(object):
                 in enumerate(result_columns)
             ]
             self.keys = [
-                elem[1] for elem in result_columns
+                elem[0] for elem in result_columns
             ]
         else:
             # case 2 - raw string, or number of columns in result does
@@ -236,7 +236,8 @@ class ResultMetaData(object):
             # that SQLAlchemy has used up through 0.9.
 
             if num_ctx_cols:
-                result_map = self._create_result_map(result_columns)
+                result_map = self._create_result_map(
+                    result_columns, case_sensitive)
 
             raw = []
             self.keys = []
@@ -329,10 +330,12 @@ class ResultMetaData(object):
                 ])
 
     @classmethod
-    def _create_result_map(cls, result_columns):
+    def _create_result_map(cls, result_columns, case_sensitive=True):
         d = {}
         for elem in result_columns:
             key, rec = elem[0], elem[1:]
+            if not case_sensitive:
+                key = key.lower()
             if key in d:
                 # conflicting keyname, just double up the list
                 # of objects.  this will cause an "ambiguous name"
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index d2fa1d553..a036dcc42 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -1270,9 +1270,6 @@ class SQLCompiler(Compiled):
         return " AS " + alias_name_text
 
     def _add_to_result_map(self, keyname, name, objects, type_):
-        if not self.dialect.case_sensitive:
-            keyname = keyname.lower()
-
         self._result_columns.append((keyname, name, objects, type_))
 
     def _label_select_column(self, select, column,
diff --git a/lib/sqlalchemy/testing/__init__.py b/lib/sqlalchemy/testing/__init__.py
index 7482e32a1..bd6377eb7 100644
--- a/lib/sqlalchemy/testing/__init__.py
+++ b/lib/sqlalchemy/testing/__init__.py
@@ -21,7 +21,8 @@ def against(*queries):
 from .assertions import emits_warning, emits_warning_on, uses_deprecated, \
     eq_, ne_, le_, is_, is_not_, startswith_, assert_raises, \
     assert_raises_message, AssertsCompiledSQL, ComparesTables, \
-    AssertsExecutionResults, expect_deprecated, expect_warnings
+    AssertsExecutionResults, expect_deprecated, expect_warnings, \
+    in_, not_in_
 
 from .util import run_as_contextmanager, rowset, fail, \
     provide_metadata, adict, force_drop_names, \
diff --git a/lib/sqlalchemy/testing/assertions.py b/lib/sqlalchemy/testing/assertions.py
index 01fa0b8a9..21dc3e71a 100644
--- a/lib/sqlalchemy/testing/assertions.py
+++ b/lib/sqlalchemy/testing/assertions.py
@@ -229,6 +229,16 @@ def is_not_(a, b, msg=None):
     assert a is not b, msg or "%r is %r" % (a, b)
 
 
+def in_(a, b, msg=None):
+    """Assert a in b, with repr messaging on failure."""
+    assert a in b, msg or "%r not in %r" % (a, b)
+
+
+def not_in_(a, b, msg=None):
+    """Assert a in not b, with repr messaging on failure."""
+    assert a not in b, msg or "%r is in %r" % (a, b)
+
+
 def startswith_(a, fragment, msg=None):
     """Assert a.startswith(fragment), with repr messaging on failure."""
     assert a.startswith(fragment), msg or "%r does not start with %r" % (
diff --git a/test/sql/test_query.py b/test/sql/test_query.py
index 98f375018..02deeb536 100644
--- a/test/sql/test_query.py
+++ b/test/sql/test_query.py
@@ -1,4 +1,5 @@
-from sqlalchemy.testing import eq_, assert_raises_message, assert_raises, is_
+from sqlalchemy.testing import eq_, assert_raises_message, assert_raises, \
+    is_, in_, not_in_
 from sqlalchemy import testing
 from sqlalchemy.testing import fixtures, engines
 from sqlalchemy import util
@@ -1018,6 +1019,11 @@ class QueryTest(fixtures.TestBase):
         ).first()
 
         eq_(list(row.keys()), ["case_insensitive", "CaseSensitive"])
+
+        in_("case_insensitive", row._keymap)
+        in_("CaseSensitive", row._keymap)
+        not_in_("casesensitive", row._keymap)
+
         eq_(row["case_insensitive"], 1)
         eq_(row["CaseSensitive"], 2)
 
@@ -1030,6 +1036,32 @@ class QueryTest(fixtures.TestBase):
             lambda: row["casesensitive"]
         )
 
+    def test_row_case_sensitive_unoptimized(self):
+        ins_db = engines.testing_engine(options={"case_sensitive": True})
+        row = ins_db.execute(
+            select([
+                literal_column("1").label("case_insensitive"),
+                literal_column("2").label("CaseSensitive"),
+                text("3 AS screw_up_the_cols")
+            ])
+        ).first()
+
+        eq_(
+            list(row.keys()),
+            ["case_insensitive", "CaseSensitive", "screw_up_the_cols"])
+
+        in_("case_insensitive", row._keymap)
+        in_("CaseSensitive", row._keymap)
+        not_in_("casesensitive", row._keymap)
+
+        eq_(row["case_insensitive"], 1)
+        eq_(row["CaseSensitive"], 2)
+        eq_(row["screw_up_the_cols"], 3)
+
+        assert_raises(KeyError, lambda: row["Case_insensitive"])
+        assert_raises(KeyError, lambda: row["casesensitive"])
+        assert_raises(KeyError, lambda: row["screw_UP_the_cols"])
+
     def test_row_case_insensitive(self):
         ins_db = engines.testing_engine(options={"case_sensitive": False})
         row = ins_db.execute(
@@ -1040,11 +1072,41 @@ class QueryTest(fixtures.TestBase):
         ).first()
 
         eq_(list(row.keys()), ["case_insensitive", "CaseSensitive"])
+
+        in_("case_insensitive", row._keymap)
+        in_("CaseSensitive", row._keymap)
+        in_("casesensitive", row._keymap)
+
         eq_(row["case_insensitive"], 1)
         eq_(row["CaseSensitive"], 2)
         eq_(row["Case_insensitive"], 1)
         eq_(row["casesensitive"], 2)
 
+    def test_row_case_insensitive_unoptimized(self):
+        ins_db = engines.testing_engine(options={"case_sensitive": False})
+        row = ins_db.execute(
+            select([
+                literal_column("1").label("case_insensitive"),
+                literal_column("2").label("CaseSensitive"),
+                text("3 AS screw_up_the_cols")
+            ])
+        ).first()
+
+        eq_(
+            list(row.keys()),
+            ["case_insensitive", "CaseSensitive", "screw_up_the_cols"])
+
+        in_("case_insensitive", row._keymap)
+        in_("CaseSensitive", row._keymap)
+        in_("casesensitive", row._keymap)
+
+        eq_(row["case_insensitive"], 1)
+        eq_(row["CaseSensitive"], 2)
+        eq_(row["screw_up_the_cols"], 3)
+        eq_(row["Case_insensitive"], 1)
+        eq_(row["casesensitive"], 2)
+        eq_(row["screw_UP_the_cols"], 3)
+
     def test_row_as_args(self):
         users.insert().execute(user_id=1, user_name='john')
         r = users.select(users.c.user_id == 1).execute().first()
@@ -1241,10 +1303,37 @@ class QueryTest(fixtures.TestBase):
 
     def test_keys(self):
         users.insert().execute(user_id=1, user_name='foo')
-        r = users.select().execute()
-        eq_([x.lower() for x in list(r.keys())], ['user_id', 'user_name'])
-        r = r.first()
-        eq_([x.lower() for x in list(r.keys())], ['user_id', 'user_name'])
+        result = users.select().execute()
+        eq_(
+            result.keys(),
+            ['user_id', 'user_name']
+        )
+        row = result.first()
+        eq_(
+            row.keys(),
+            ['user_id', 'user_name']
+        )
+
+    def test_keys_anon_labels(self):
+        """test [ticket:3483]"""
+
+        users.insert().execute(user_id=1, user_name='foo')
+        result = testing.db.execute(
+            select([
+                users.c.user_id,
+                users.c.user_name.label(None), func.count(1)]).
+            group_by(users.c.user_id, users.c.user_name)
+        )
+
+        eq_(
+            result.keys(),
+            ['user_id', 'user_name_1', 'count_1']
+        )
+        row = result.first()
+        eq_(
+            row.keys(),
+            ['user_id', 'user_name_1', 'count_1']
+        )
 
     def test_items(self):
         users.insert().execute(user_id=1, user_name='foo')