add with_loader_criteria() test for #8064 / #9091

test related to #8064, added after discussion #9091 which
requested this behavior for with_loader_criteria() where it was
found to be working as of this issue, just not tested

Change-Id: I703f25af3708e49380b6004badd3a8ffb783ef70
References: #8064
References: #9091
(cherry picked from commit a2bf6f1bb4a56dfe6c6d1fe22c3a4e84f5da99bc)

1 files changed, 62 insertions, 0 deletions

diff --git a/test/orm/test_relationship_criteria.py b/test/orm/test_relationship_criteria.py
index ed4ab3295..e866fe018 100644
--- a/test/orm/test_relationship_criteria.py
+++ b/test/orm/test_relationship_criteria.py
@@ -15,6 +15,7 @@ from sqlalchemy import sql
 from sqlalchemy import String
 from sqlalchemy import testing
 from sqlalchemy.orm import aliased
+from sqlalchemy.orm import column_property
 from sqlalchemy.orm import defer
 from sqlalchemy.orm import join as orm_join
 from sqlalchemy.orm import joinedload
@@ -58,6 +59,35 @@ class _Fixtures(_fixtures.FixtureTest):
         return User, Address
 
     @testing.fixture
+    def user_address_col_property_fixture(self):
+        users, Address, addresses, User = (
+            self.tables.users,
+            self.classes.Address,
+            self.tables.addresses,
+            self.classes.User,
+        )
+
+        self.mapper_registry.map_imperatively(Address, addresses)
+
+        self.mapper_registry.map_imperatively(
+            User,
+            users,
+            properties={
+                "addresses": relationship(
+                    Address,
+                    order_by=Address.id,
+                ),
+                "num_addresses": column_property(
+                    select(func.count(Address.id))
+                    .where(Address.user_id == users.c.id)
+                    .correlate_except(Address)
+                    .scalar_subquery()
+                ),
+            },
+        )
+        return User, Address
+
+    @testing.fixture
     def user_address_custom_strat_fixture(self):
         users, Address, addresses, User = (
             self.tables.users,
@@ -454,6 +484,38 @@ class LoaderCriteriaTest(_Fixtures, testing.AssertsCompiledSQL):
             "WHERE users.name != :name_1",
         )
 
+    def test_criteria_applies_to_column_property(
+        self, user_address_col_property_fixture
+    ):
+        """test related to #8064, added after discussion #9091 which
+        requested this behavior for with_loader_criteria() where it was
+        found to be working as of this issue, just not tested"""
+
+        User, Address = user_address_col_property_fixture
+
+        stmt = select(User)
+
+        self.assert_compile(
+            stmt,
+            "SELECT (SELECT count(addresses.id) AS count_1 FROM addresses "
+            "WHERE addresses.user_id = users.id) AS anon_1, "
+            "users.id, users.name FROM users",
+        )
+
+        stmt = select(User).options(
+            with_loader_criteria(
+                Address, Address.email_address != "email_address"
+            )
+        )
+
+        self.assert_compile(
+            stmt,
+            "SELECT (SELECT count(addresses.id) AS count_1 FROM addresses "
+            "WHERE addresses.user_id = users.id AND "
+            "addresses.email_address != :email_address_1) AS anon_1, "
+            "users.id, users.name FROM users",
+        )
+
     def test_select_from_mapper_mapper_criteria(self, user_address_fixture):
         User, Address = user_address_fixture