Fix joinedload + of_type() + and_() invalid SQL for subclass columns by joaquinhuigomez · Pull Request #13206 · sqlalchemy/sqlalchemy

joaquinhuigomez · 2026-03-28T21:04:10Z

When using joinedload(Parent.children.of_type(SubClass).and_(SubClass.attr == value)), the SubClass.attr column reference in the and_() clause was not adapted to the subquery alias. The generated SQL referenced subclass_table.col instead of anon_1.subclass_col, causing OperationalError: no such column.

The issue is in mark_exclude_cols in _JoinCondition.join_targets — it used identity checks (is not self.prop.mapper) to decide whether a column belongs to the relationship target. Since self.prop.mapper is the base class mapper (e.g. Animal), columns from subclass mappers (e.g. Dog) failed this check and were annotated with should_not_adapt, preventing the ClauseAdapter from rewriting them.

Added a mapper.isa() check so subclass mapper columns are recognized as belonging to the relationship target. selectinload and subqueryload were not affected because they emit separate queries where the column is directly available.

Fixes #13203

…ss columns When joinedload uses of_type() targeting a joined-table subclass combined with and_() referencing a column on that subclass, the column was not adapted to the subquery alias. This happened because mark_exclude_cols in _JoinCondition.join_targets used identity checks against prop.mapper, which is the base class mapper. Subclass mappers failed this check and got annotated with should_not_adapt, preventing the ClauseAdapter from rewriting the column reference. Fixed by adding a mapper.isa() check so that subclass mapper columns are recognized as belonging to the relationship target and are properly adapted. Fixes: sqlalchemy#13203 Change-Id: I13203a

zzzeek · 2026-03-28T21:26:00Z

wow, the robots are truly taking over :) well, looks mostly good, will haev to see if that test suite goes through in python 3.7/3.8 for 2.0.x but thanks

sqla-tester

OK, this is sqla-tester setting up my work on behalf of zzzeek to try to get revision 3026c21 of this pull request into gerrit so we can run tests and reviews and stuff

sqla-tester · 2026-03-28T21:26:13Z

New Gerrit review created for change 3026c21: https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/6638

zzzeek · 2026-03-28T21:31:01Z

+            s.add(Animal(id=2, name="Generic", type="animal", owner=o))
+            s.commit()
+
+    def test_joinedload_of_type_and_subclass_col(self):


these are good tests but it would be even better if they were done in this style:

sqlalchemy/test/orm/test_of_type.py

Lines 1299 to 1373 in d3a8d49

@testing.variation("format_", ["chained", "suboption"])

@testing.variation("loader", ["joined", "selectin"])

def test_joinedload_of_type_chained_vs_options(

self, format_: testing.Variation, loader: testing.Variation

):

"""Test that joinedload().joinedload(...of_type()) and

joinedload().options(joinedload(...of_type())) generate equivalent SQL.

Regression test for issue #13202 where using .options() to apply

a nested joinedload with of_type() would not propagate the

polymorphic loading strategy correctly, resulting in missing

polymorphic LEFT OUTER JOIN clauses.

"""

A, B, C, CSub = self.classes("A", "B", "C", "CSub")

c_poly = with_polymorphic(C, "*", flat=True)

if format_.chained:

if loader.selectin:

stmt = select(A).options(

selectinload(A.bs).selectinload(B.cs.of_type(c_poly))

)

elif loader.joined:

stmt = select(A).options(

joinedload(A.bs).joinedload(B.cs.of_type(c_poly))

)

else:

loader.fail()

elif format_.suboption:

if loader.selectin:

stmt = select(A).options(

selectinload(A.bs).options(

selectinload(B.cs.of_type(c_poly))

)

)

elif loader.joined:

stmt = select(A).options(

joinedload(A.bs).options(joinedload(B.cs.of_type(c_poly)))

)

else:

loader.fail()

else:

format_.fail()

session = fixture_session()

with self.sql_execution_asserter(testing.db) as asserter_:

eq_(

session.scalars(stmt).unique().all(),

[A(bs=[B(cs=[CSub(data="csub1")])])],

)

if loader.selectin:

asserter_.assert_(

CompiledSQL("SELECT a.id FROM a"),

CompiledSQL(

"SELECT b.a_id, b.id FROM b WHERE b.a_id IN"

" (__[POSTCOMPILE_primary_keys])"

),

CompiledSQL(

"SELECT c_1.b_id, c_1.id, c_1.type, c_sub_1.id,"

" c_sub_1.data FROM c AS c_1 LEFT OUTER JOIN c_sub AS"

" c_sub_1 ON c_1.id = c_sub_1.id WHERE c_1.b_id IN"

" (__[POSTCOMPILE_primary_keys])"

),

)

elif loader.joined:

asserter_.assert_(

CompiledSQL(

"SELECT a.id, c_1.id AS id_1, c_1.b_id, c_1.type,"

" c_sub_1.id AS id_2, c_sub_1.data, b_1.id AS id_3,"

" b_1.a_id FROM a LEFT OUTER JOIN b AS b_1 ON a.id ="

" b_1.a_id LEFT OUTER JOIN (c AS c_1 LEFT OUTER JOIN c_sub"

" AS c_sub_1 ON c_1.id = c_sub_1.id) ON b_1.id = c_1.b_id"

)

)

that is, using self.sql_execution_asserter to actually assert all the SQL emitted for the entire operation

(and also asserting the result)

joaquinhuigomez · 2026-03-29T02:54:31Z

Thanks for running it through Gerrit — happy to address anything that comes up in the test run.

zzzeek · 2026-03-29T17:07:35Z

Thanks for running it through Gerrit — happy to address anything that comes up in the test run.

I've requested changes in the review are you able to make these?

joaquinhuigomez · 2026-03-30T14:08:22Z

Moved the changelog to unreleased_20 and reworked the tests to use sql_execution_asserter with result checks. Let me know if the SQL assertions need adjusting.

sqla-tester

OK, this is sqla-tester setting up my work on behalf of zzzeek to try to get revision 76d61d0 of this pull request into gerrit so we can run tests and reviews and stuff

sqla-tester · 2026-03-30T14:39:13Z

Patchset 76d61d0 added to existing Gerrit review https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/6638

zzzeek · 2026-04-01T13:16:10Z

looks like the test is actually failing. need that to be passing to move forward

joaquinhuigomez · 2026-04-01T14:38:57Z

Fixed the SQL assertions — was using the wrong alias pattern. Should pass now.

sqla-tester

OK, this is sqla-tester setting up my work on behalf of zzzeek to try to get revision ba55b0c of this pull request into gerrit so we can run tests and reviews and stuff

sqla-tester · 2026-04-01T17:29:39Z

Patchset ba55b0c added to existing Gerrit review https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/6638

zzzeek requested a review from sqla-tester March 28, 2026 21:26

sqla-tester reviewed Mar 28, 2026

View reviewed changes

zzzeek requested changes Mar 28, 2026

View reviewed changes

Move changelog to unreleased_20 and use sql_execution_asserter in tests

76d61d0

zzzeek requested a review from sqla-tester March 30, 2026 14:39

sqla-tester reviewed Mar 30, 2026

View reviewed changes

Fix SQL assertions to match actual query output

ba55b0c

zzzeek requested a review from sqla-tester April 1, 2026 17:29

sqla-tester reviewed Apr 1, 2026

View reviewed changes

	@testing.variation("format_", ["chained", "suboption"])
	@testing.variation("loader", ["joined", "selectin"])
	def test_joinedload_of_type_chained_vs_options(
	self, format_: testing.Variation, loader: testing.Variation
	):
	"""Test that joinedload().joinedload(...of_type()) and
	joinedload().options(joinedload(...of_type())) generate equivalent SQL.

	Regression test for issue #13202 where using .options() to apply
	a nested joinedload with of_type() would not propagate the
	polymorphic loading strategy correctly, resulting in missing
	polymorphic LEFT OUTER JOIN clauses.
	"""
	A, B, C, CSub = self.classes("A", "B", "C", "CSub")

	c_poly = with_polymorphic(C, "*", flat=True)

	if format_.chained:
	if loader.selectin:
	stmt = select(A).options(
	selectinload(A.bs).selectinload(B.cs.of_type(c_poly))
	)
	elif loader.joined:
	stmt = select(A).options(
	joinedload(A.bs).joinedload(B.cs.of_type(c_poly))
	)
	else:
	loader.fail()
	elif format_.suboption:
	if loader.selectin:
	stmt = select(A).options(
	selectinload(A.bs).options(
	selectinload(B.cs.of_type(c_poly))
	)
	)
	elif loader.joined:
	stmt = select(A).options(
	joinedload(A.bs).options(joinedload(B.cs.of_type(c_poly)))
	)
	else:
	loader.fail()
	else:
	format_.fail()

	session = fixture_session()
	with self.sql_execution_asserter(testing.db) as asserter_:
	eq_(
	session.scalars(stmt).unique().all(),
	[A(bs=[B(cs=[CSub(data="csub1")])])],
	)

	if loader.selectin:
	asserter_.assert_(
	CompiledSQL("SELECT a.id FROM a"),
	CompiledSQL(
	"SELECT b.a_id, b.id FROM b WHERE b.a_id IN"
	" (__[POSTCOMPILE_primary_keys])"
	),
	CompiledSQL(
	"SELECT c_1.b_id, c_1.id, c_1.type, c_sub_1.id,"
	" c_sub_1.data FROM c AS c_1 LEFT OUTER JOIN c_sub AS"
	" c_sub_1 ON c_1.id = c_sub_1.id WHERE c_1.b_id IN"
	" (__[POSTCOMPILE_primary_keys])"
	),
	)
	elif loader.joined:
	asserter_.assert_(
	CompiledSQL(
	"SELECT a.id, c_1.id AS id_1, c_1.b_id, c_1.type,"
	" c_sub_1.id AS id_2, c_sub_1.data, b_1.id AS id_3,"
	" b_1.a_id FROM a LEFT OUTER JOIN b AS b_1 ON a.id ="
	" b_1.a_id LEFT OUTER JOIN (c AS c_1 LEFT OUTER JOIN c_sub"
	" AS c_sub_1 ON c_1.id = c_sub_1.id) ON b_1.id = c_1.b_id"
	)
	)

Uh oh!

Conversation

joaquinhuigomez commented Mar 28, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

zzzeek commented Mar 28, 2026

Uh oh!

sqla-tester left a comment

Choose a reason for hiding this comment

Uh oh!

sqla-tester commented Mar 28, 2026

Uh oh!

Uh oh!

zzzeek Mar 28, 2026

Choose a reason for hiding this comment

Uh oh!

zzzeek Mar 28, 2026

Choose a reason for hiding this comment

Uh oh!

joaquinhuigomez commented Mar 29, 2026

Uh oh!

zzzeek commented Mar 29, 2026

Uh oh!

joaquinhuigomez commented Mar 30, 2026

Uh oh!

sqla-tester left a comment

Choose a reason for hiding this comment

Uh oh!

sqla-tester commented Mar 30, 2026

Uh oh!

zzzeek commented Apr 1, 2026

Uh oh!

joaquinhuigomez commented Apr 1, 2026

Uh oh!

sqla-tester left a comment

Choose a reason for hiding this comment

Uh oh!

sqla-tester commented Apr 1, 2026

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

joaquinhuigomez commented Mar 28, 2026 •

edited

Loading