Regression in 0.9.x for polymorphic query

[Former user]

I have a reasonably complex query which worked in 0.8.x and no longer works in 0.9.x.

I have created a script that defines the problematic entities and then queries for them.

Here is an ERD for the script: [Image(http://i40.tinypic.com/2hf3uja.png)]

There are 2 join paths from Record to UserProfile: - Record -> User -> UserProfile, and - Record -> RecordUser2 -> User -> UserProfile

Looking at the SQL output the error is pretty obvious; the second join from User to UserProfile uses the foreign key from the first join.

[Former user]

Test case

[Former user]

Output of the test case using SQLAlchemy 0.8.4

[Former user]

Output of the test case using SQLAlchemy 0.9.1

[Mike Bayer]

(internal pdb notes follow)

the turning point that is different in 0.9 is due to clause adaptation failing for raw joins, branch point is in orm/util.py, when joining for joinedload for second User.profile:

        if prop:
            if sql_util.clause_is_present(on_selectable, left_info.selectable):
                adapt_from = on_selectable
            else:
                adapt_from = left_info.selectable

the clause_is_present() check fails and it the join doesn't have a specific enough "adapt_from".

that check fails because onclause.comparator._source_selectable() does this: the mapped entity is:

> /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/util.py(744)__init__()
-> left_orm_info = getattr(left, '_joined_from_info', left_info)
(Pdb) print onclause.comparator.property.parent._with_polymorphic_selectable
party JOIN "user" ON party.party_id = "user".party_id
(Pdb)

the _adapt_to_entity is:

(Pdb) print onclause.comparator._adapt_to_entity.selectable
party AS party_1 JOIN "user" AS user_1 ON party_1.party_id = user_1.party_id

the adaptation in _source_selectable() fails, by taking the _adapt_to_entity and replacing it for both "party" and "user" in the mapped selectable, forming a garbage selectable:

(Pdb) print onclause.comparator._source_selectable()       
party AS party_1 JOIN "user" AS user_1 ON party_1.party_id = user_1.party_id JOIN party AS party_1 JOIN "user" AS user_1 ON party_1.party_id = user_1.party_id ON party_1.party_id = user_1.party_id

so the issue is ultimately with _source_selectable().

so far a diff like this is working:

diff --git a/lib/sqlalchemy/orm/relationships.py b/lib/sqlalchemy/orm/relationships.py
index 982f10a..6fdedd3 100644
--- a/lib/sqlalchemy/orm/relationships.py
+++ b/lib/sqlalchemy/orm/relationships.py
@@ -747,11 +747,10 @@ class RelationshipProperty(StrategizedProperty):
             return self.property.parent

         def _source_selectable(self):
-            elem = self.property.parent._with_polymorphic_selectable
-            if self.adapter:
-                return self.adapter(elem)
+            if self._adapt_to_entity:
+                return self._adapt_to_entity.selectable
             else:
-                return elem
+                return self.property.parent._with_polymorphic_selectable

         def __clause_element__(self):
             adapt_from = self._source_selectable()

but we can't test it fully because #2907 is breaking all the tests, so have to fix that first.

[Mike Bayer]

bd74f81bb5b1158a1bc0a4