joined subclass to 2 level subquery load fails on join

[Mike Bayer]

Currently cannot be tested on master due to #3963.

under 1.1, the third query is losing join context

SELECT dept.id AS dept_id, owner_1.dept_id AS owner_1_dept_id 
FROM (SELECT DISTINCT milestone.owner_id AS milestone_owner_id 
FROM milestone, sprint 
WHERE sprint.id IN (?)) AS anon_1 JOIN owner AS owner_1 ON owner_1.id = anon_1.milestone_owner_id JOIN dept ON dept.id = owner_1.dept_id ORDER BY owner_1.dept_id

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class Milestone(Base):
    __tablename__ = 'milestone'
    id = Column(Integer, primary_key=True)

    milestone_type = Column(String(64), nullable=False)
    owner_id = Column(
        Integer,
        ForeignKey('owner.id', name='milestone_owner_id_owner_id_fkey'))

    owner = relationship('Owner', back_populates='milestone')

    __mapper_args__ = {
        'polymorphic_on': milestone_type,
    }


class Sprint(Milestone):
    __tablename__ = 'sprint'
    id = Column(
        Integer,
        ForeignKey('milestone.id', name='sprint_id_milestone_id_fkey'),
        primary_key=True)

    __mapper_args__ = {
        'polymorphic_identity': 'sprint'
    }


class Owner(Base):
    __tablename__ = 'owner'
    id = Column(Integer, primary_key=True)

    dept_id = Column(
        Integer,
        ForeignKey('dept.id', name='owner_dept_id_dept_id_fkey'))
    dept = relationship('Dept', back_populates='owner')
    milestone = relationship('Milestone', back_populates='owner')


class Dept(Base):
    __tablename__ = 'dept'
    id = Column(Integer, primary_key=True)
    owner = relationship('Owner', back_populates='dept')


e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

sess = Session(e)

d = Dept(id=1)
o = Owner(id=1)
o.dept = d
s = Sprint(id=1)
s.owner = o
sess.add(o)
sess.commit()

sess.query(Sprint).filter(Sprint.id.in_([1])).options(
    subqueryload(Sprint.owner).subqueryload(Owner.dept)).all()

[Mike Bayer]

https://gerrit.sqlalchemy.org/431

[Mike Bayer]

Set complete FROM list for subquery eagerload's orig query

Instead of checking that the "orig_entity" we receive applies as a correct FROM element for the subquery we're building, set the FROM clause of the query to exactly what it already is based on column_descriptions (assuming there is no FROM list already), thereby ensuring that the FROM list will remain intact, regardless of what orig_entity turns out to be and what the target_cols ultimately refer towards.

Fixed issue with subquery eagerloading which continues on from the series of issues fixed in 2699, 3106, 3893 involving that the "subquery" contains the correct FROM clause when beginning from a joined inheritance subclass and then subquery eager loading onto a relationship from the base class, while the query also includes criteria against the subclass. The fix in the previous tickets did not accommodate for additional subqueryload operations loading more deeply from the first level, so the fix has been further generalized.

Change-Id: Ic909590814f71e577d8266b1dbc4c393dc48e019 Fixes: #4011 (cherry picked from commit 6d2a03c1ec21cc428c91476c170ad7dbe219926c)

→ <<cset d2131156de65>>

[Mike Bayer]

Set complete FROM list for subquery eagerload's orig query

Instead of checking that the "orig_entity" we receive applies as a correct FROM element for the subquery we're building, set the FROM clause of the query to exactly what it already is based on column_descriptions (assuming there is no FROM list already), thereby ensuring that the FROM list will remain intact, regardless of what orig_entity turns out to be and what the target_cols ultimately refer towards.

Fixed issue with subquery eagerloading which continues on from the series of issues fixed in 2699, 3106, 3893 involving that the "subquery" contains the correct FROM clause when beginning from a joined inheritance subclass and then subquery eager loading onto a relationship from the base class, while the query also includes criteria against the subclass. The fix in the previous tickets did not accommodate for additional subqueryload operations loading more deeply from the first level, so the fix has been further generalized.

Change-Id: Ic909590814f71e577d8266b1dbc4c393dc48e019 Fixes: #4011

→ <<cset a991eedcf559>>