Add additional support to honor _proxy_key in Core selects

Fixed ORM regression where ad-hoc label names generated for hybrid
properties and potentially other similar types of ORM-enabled expressions
would usually be propagated outwards through subqueries, allowing the name
to be retained in the final keys of the result set even when selecting from
subqueries. Additional state is now tracked in this case that isn't lost
when a hybrid is selected out of a Core select / subquery.

as we have removed things like column.label() from
ORM, since we now have to export the cols with the same names
as what we will render, experiment with giving a greater role
to the _proxy_key annotation so that a desired name can be
carried through more transarently.

Fixes: #6718
Change-Id: Icb313244c13ea1a8f58d3e05d07aa3e1039e15bf

3 files changed, 25 insertions, 7 deletions

diff --git a/lib/sqlalchemy/orm/context.py b/lib/sqlalchemy/orm/context.py
index 78026efb1..a6efac9cf 100644
--- a/lib/sqlalchemy/orm/context.py
+++ b/lib/sqlalchemy/orm/context.py
@@ -2890,7 +2890,7 @@ class _ORMColumnEntity(_ColumnEntity):
                 ezero._adapter if ezero.is_aliased_class else None,
             )
 
-        if column._annotations:
+        if column._annotations and not column._expression_label:
             # annotated columns perform more slowly in compiler and
             # result due to the __eq__() method, so use deannotated
             column = column._deannotate()
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index 709106b6b..f06aee74f 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -887,6 +887,21 @@ class ColumnElement(
         else:
             return getattr(self, "name", "_no_label")
 
+    @util.memoized_property
+    def _expression_label(self):
+        """a suggested label to use in the case that the column has no name,
+        which should be used if possible as the explicit 'AS <label>'
+        where this expression would normally have an anon label.
+
+        """
+
+        if getattr(self, "name", None) is not None:
+            return None
+        elif self._annotations and "proxy_key" in self._annotations:
+            return self._annotations["proxy_key"]
+        else:
+            return None
+
     def _make_proxy(
         self, selectable, name=None, key=None, name_is_truncatable=False, **kw
     ):
diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py
index 557c443bf..6ac9f0dbd 100644
--- a/lib/sqlalchemy/sql/selectable.py
+++ b/lib/sqlalchemy/sql/selectable.py
@@ -5815,14 +5815,17 @@ class Select(
                         repeated = c._anon_name_label in names
                         names[c._anon_name_label] = c
                         return (None, c, repeated)
+                    else:
+                        name = effective_name = c._label
                 elif getattr(c, "name", None) is None:
                     # this is a scalar_select().  need to improve this case
-                    repeated = c._anon_name_label in names
-                    names[c._anon_name_label] = c
-                    return (None, c, repeated)
-
-                if use_tablename_labels:
-                    name = effective_name = c._label
+                    expr_label = c._expression_label
+                    if expr_label is None:
+                        repeated = c._anon_name_label in names
+                        names[c._anon_name_label] = c
+                        return (None, c, repeated)
+                    else:
+                        name = effective_name = expr_label
                 else:
                     name = None
                     effective_name = c.name