then again all the columns that are mapped dont have annotations in them with a classical mapping... will have to look at this to see what can be done

more to the point, the column_property() is against Store.id and this mapper is Retailer. So...why is Store.id in the translate here? this patch adjusts that, but again breaks those same polymorphic rel tests, but this is more appropriate in any case so maybe find a way for this to change behavior based on the with polymorphic

diff --git a/lib/sqlalchemy/orm/mapper.py b/lib/sqlalchemy/orm/mapper.py
index 0241a31232..2e0fd2e78b 100644
--- a/lib/sqlalchemy/orm/mapper.py
+++ b/lib/sqlalchemy/orm/mapper.py
@@ -2839,7 +2839,7 @@ class Mapper(
                 else:
                     result[binary.right] = {binary.left}
 
-        for mapper in self.base_mapper.self_and_descendants:
+        for mapper in self.iterate_to_root():
             if mapper.inherit_condition is not None:
                 visitors.traverse(
                     mapper.inherit_condition, {}, {"binary": visit_binary}

like if the root mapper is with poly on a UNION of all the tables, then we're out again. kind of a weird problem

Mike Bayer has proposed a fix for this issue in the main branch:

WIP: try to limit wpoly adapt in column_property https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/3947

the test case here was fixed by #8456. Need to determine more closely

indeed, #8456 lists this as a regression in 1.4, the test case also succeeds on 1.3 with the requisite API calling styles.

the user's test in #8167 for their original POC still fails in the way they mention, if they call like this:

print(Retailer.store_count.expression)

however that's the col prop without anything to correlate towards, this does work

print(select(Retailer.store_count).select_from(Retailer))

so that seems OK.

The original case they have "works" but not exactly because Store is the join from Customer to Store, so they get this:

SELECT (SELECT count(store.id) AS count_1 
FROM customer JOIN store ON customer.id = store.id 
WHERE store.retailer_id = retailer.id) AS anon_1 
FROM customer JOIN retailer ON customer.id = retailer.id

whereas using just the table gets this:

SELECT (SELECT count(store.id) AS count_1 
FROM store 
WHERE store.retailer_id = retailer.id) AS anon_1 
FROM customer JOIN retailer ON customer.id = retailer.id

those are still the correct SQL, in the first case the JOIN is expected.

EDIT: now I am not getting this result at all. so not sure.

Mike Bayer has proposed a fix for this issue in the main branch:

Add tests for issue #8168 https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/4264

Mike Bayer has proposed a fix for this issue in the rel_1_4 branch:

Add tests for issue #8168 https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/4265

OK, the issue still exists if we use polymorphic_load="inline". exists fully, they have to use an alias of the table to work around. Still happens with the base table.

this bug is still opened, then, though is not that critical. will commit the test cases.

Mike Bayer referenced this issue:

Add tests for issue #8168 https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/4264

Mike Bayer referenced this issue:

Add tests for issue #8168 https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/4265

Mike Bayer has proposed a fix for this issue in the main branch:

disable polymorphic adaption in most cases https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/4275