Relationship to Object using Joined Table Inheritance uses an inner join

[Former user]

On 0.7.3 using Oracle.

When creating a relationship to an object using joined table inheritance and using the lazy='joined' parameters, SQLAlchemy generates SQL using inner joins instead of outer joins.

Workaround: use 'subquery' or 'dynamic'.

[Mike Bayer]

hi there -

The inner/outer join is determined by the innerjoin=True flag passed to either relationship() or joinedload(). It's an outerjoin by default. Specifically with joined table inheritance, the target mapper's selectable is wrapped inside of a subquery, where the innerjoins are inside the subquery, the relationship from parent->child is an outerjoin by default. It's an extremely well-covered case.

For example:

./sqla_nose.py -v test.orm.inheritance.test_query:SubClassEagerToSubClassTest.test_joinedload --log-debug=sqlalchemy.engine

...

SELECT subparent.id AS subparent_id, parent.id AS parent_id, 
parent.type AS parent_type, subparent.data AS subparent_data, 
anon_1.sub_id AS anon_1_sub_id, anon_1.base_id AS anon_1_base_id, 
anon_1.base_type AS anon_1_base_type, anon_1.sub_data AS anon_1_sub_data, 
anon_1.sub_subparent_id AS anon_1_sub_subparent_id 
FROM parent JOIN subparent ON parent.id = subparent.id 
LEFT OUTER JOIN (SELECT base.id AS base_id, base.type AS base_type, 
sub.id AS sub_id, sub.data AS sub_data, sub.subparent_id AS sub_subparent_id 
FROM base JOIN sub ON base.id = sub.id) AS anon_1 
ON subparent.id = anon_1.sub_subparent_id ORDER BY anon_1.base_id

that's actually a joined-table subclass eager loading a joined-table subclass. The structure there is "A JOIN B <onclause> LEFT OUTER JOIN (SELECT * FROM B JOIN C) AS anon_1 <onclause>".

I'm not sure if you had an issue with uploading a test case or with the tracker, but I don't at the moment see a test script attached here. There are certainly potential configurational patterns which mis-handle this case, but without a full test case here illustrating specifically how to produce the results you consider erroneous, they can't be evaluated and this ticket will need to be closed. Guidelines for posting tickets are at http://www.sqlalchemy.org/participate.html#bugs. If you need help posting or otherwise, check in on the mailing list at http://groups.google.com/group/sqlalchemy .

[Mike Bayer]

that should be:

A JOIN B <onclause> LEFT OUTER JOIN (SELECT * FROM C JOIN D <onclause>) AS anon_1 <onclause>".

[Mike Bayer]

if you have a test case please feel free to reopen this ticket with an attachment, thanks !