From 6da8c6d0299b055414d3350f4c2d73f9e911d7ae Mon Sep 17 00:00:00 2001
From: Mike Bayer <mike_mp@zzzcomputing.com>
Date: Mon, 18 Oct 2021 15:43:27 -0400
Subject: [PATCH] use coercions for label element, ensure propagate_attrs

Fixed bug where the ORM "plugin", necessary for features such as
:func:`_orm.with_loader_criteria` to work correctly, would not be applied
to a :func:`_sql.select` which queried from an ORM column expression if it
made use of the :meth:`_sql.ColumnElement.label` modifier.

Fixes: #7205
Change-Id: I72b84442e14df8b5ece33916f3c51ca3f358864b
---
 doc/build/changelog/unreleased_14/7205.rst | 10 ++++++
 lib/sqlalchemy/sql/elements.py             | 18 +++++++++--
 test/orm/test_relationship_criteria.py     | 36 ++++++++++++++++++++++
 3 files changed, 61 insertions(+), 3 deletions(-)
 create mode 100644 doc/build/changelog/unreleased_14/7205.rst

diff --git a/doc/build/changelog/unreleased_14/7205.rst b/doc/build/changelog/unreleased_14/7205.rst
new file mode 100644
index 0000000000..cdc37991f4
--- /dev/null
+++ b/doc/build/changelog/unreleased_14/7205.rst
@@ -0,0 +1,10 @@
+.. change::
+    :tags: bug, orm
+    :tickets: 7205
+
+    Fixed bug where the ORM "plugin", necessary for features such as
+    :func:`_orm.with_loader_criteria` to work correctly, would not be applied
+    to a :func:`_sql.select` which queried from an ORM column expression if it
+    made use of the :meth:`_sql.ColumnElement.label` modifier.
+
+
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index e49665019a..3699f872bb 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -4521,19 +4521,31 @@ class Label(roles.LabeledColumnExprRole, ColumnElement):
 
         """
 
-        if isinstance(element, Label):
-            self._resolve_label = element._label
-
+        orig_element = element
+        element = coercions.expect(
+            roles.ExpressionElementRole,
+            element,
+            apply_propagate_attrs=self,
+        )
         while isinstance(element, Label):
+            # TODO: this is only covered in test_text.py, but nothing
+            # fails if it's removed.  determine rationale
             element = element.element
 
         if name:
             self.name = name
+
+            # TODO: nothing fails if this is removed.  this is related
+            # to the order_by() string feature tested in test_text.py.
             self._resolve_label = self.name
         else:
             self.name = _anonymous_label.safe_construct(
                 id(self), getattr(element, "name", "anon")
             )
+            if isinstance(orig_element, Label):
+                # TODO: no coverage for this block, again would be in
+                # test_text.py where the resolve_label concept is important
+                self._resolve_label = orig_element._label
 
         self.key = self._tq_label = self._tq_key_label = self.name
         self._element = element
diff --git a/test/orm/test_relationship_criteria.py b/test/orm/test_relationship_criteria.py
index cccf91f7b6..86f7e9fc91 100644
--- a/test/orm/test_relationship_criteria.py
+++ b/test/orm/test_relationship_criteria.py
@@ -7,6 +7,7 @@ from sqlalchemy import event
 from sqlalchemy import ForeignKey
 from sqlalchemy import func
 from sqlalchemy import Integer
+from sqlalchemy import literal_column
 from sqlalchemy import orm
 from sqlalchemy import select
 from sqlalchemy import sql
@@ -26,6 +27,7 @@ from sqlalchemy.orm.decl_api import declared_attr
 from sqlalchemy.testing import eq_
 from sqlalchemy.testing.assertsql import CompiledSQL
 from sqlalchemy.testing.fixtures import fixture_session
+from sqlalchemy.testing.util import resolve_lambda
 from test.orm import _fixtures
 
 
@@ -450,6 +452,40 @@ class LoaderCriteriaTest(_Fixtures, testing.AssertsCompiledSQL):
             "FROM users AS users_1 WHERE users_1.name != :name_1",
         )
 
+    @testing.combinations(
+        (lambda User: [User.id], "users.id"),
+        (lambda User: [User.id.label("foo")], "users.id AS foo"),
+        (lambda User: [User.name + "bar"], "users.name || :name_1 AS anon_1"),
+        (
+            lambda User: [(User.name + "bar").label("foo")],
+            "users.name || :name_1 AS foo",
+        ),
+        (lambda User: [func.count(User.id)], "count(users.id) AS count_1"),
+        (
+            lambda User: [func.count(User.id).label("foo")],
+            "count(users.id) AS foo",
+        ),
+        argnames="case, expected",
+    )
+    def test_select_expr_with_criteria(
+        self, case, expected, user_address_fixture
+    ):
+        """test #7205"""
+        User, Address = user_address_fixture
+
+        stmt = select(*resolve_lambda(case, User=User)).options(
+            # use non-bound value so that we dont have to accommodate for
+            # the "anon" counter
+            with_loader_criteria(
+                User, User.name != literal_column("some_crit")
+            )
+        )
+
+        self.assert_compile(
+            stmt,
+            "SELECT %s FROM users WHERE users.name != some_crit" % (expected,),
+        )
+
     def test_select_from_aliased_inclaliased_criteria(
         self, user_address_fixture
     ):
-- 
2.47.3