logic to ensure parent cols present in joined eager load inappropriately turns off for m2m

[Mike Bayer]

this issue is usually missed because a. we always undefer the primary columns of the parent mapper and b. the join condition of a relationship almost always refers to the primary columns of the parent mapper. if we are using joined-table-inheritance, then c. the PK col in the subclass is usually named the same as the parent PK col, is grouped into the same attribute name, and is therefore undeferred via the logic in https://bitbucket.org/zzzeek/sqlalchemy/src/ef9a4cb60b4e7fe305367c5223e8bb2cbf2b3b0f/lib/sqlalchemy/orm/strategies.py?at=master&fileviewer=file-view-default#strategies.py-230.

If all of the above is not the case, then the logic at https://bitbucket.org/zzzeek/sqlalchemy/src/ef9a4cb60b4e7fe305367c5223e8bb2cbf2b3b0f/lib/sqlalchemy/orm/strategies.py?at=master&fileviewer=file-view-default#strategies.py-1370 adds the column to the query anyway, but only if there's no secondaryjoin. The whole block here appears to be uncovered by tests in any case.

So the criteria is:

1. The query defers/excludes a parent column used in the join condition
2. joinedload is used
3. the excluded column is not part of the mapper primary key of the parent, note this includes both columns that are totally not PK or are a differently-named PK col on a joined-inh subtable
4. the relationship includes "secondary"
5. the query uses limit/offset so is subject to the subquery-on-joinedload behavior.

If you have all five of those things, here's the bug:

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

Base = declarative_base()


class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)


class ASub(A):
    __tablename__ = 'asub'
    sub_id = Column(ForeignKey('a.id'), primary_key=True)

    bs = relationship("B", secondary=Table(
        'atob', Base.metadata,
        Column('aid', ForeignKey('asub.sub_id')),
        Column('bid', ForeignKey('b.id'))
    ))


class B(Base):
    __tablename__ = 'b'
    id = Column(Integer, primary_key=True)

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

s = Session(e)
s.query(ASub).options(load_only('id'), joinedload(ASub.bs)).limit(10).all()

error:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: asub.sub_id [SQL: u'SELECT anon_1.a_id AS anon_1_a_id, anon_2.b_1_id AS b_1_id \nFROM (SELECT a.id AS a_id \nFROM a JOIN asub ON a.id = asub.sub_id\n LIMIT ? OFFSET ?) AS anon_1 LEFT OUTER JOIN (SELECT atob_1.aid AS atob_1_aid, atob_1.bid AS atob_1_bid, b_1.id AS b_1_id \nFROM atob AS atob_1 JOIN b AS b_1 ON b_1.id = atob_1.bid) AS anon_2 ON asub.sub_id = anon_2.atob_1_aid'] [parameters: (10, 0)]

patch!

diff --git a/lib/sqlalchemy/orm/strategies.py b/lib/sqlalchemy/orm/strategies.py
index 67dac1c..7de470d 100644
--- a/lib/sqlalchemy/orm/strategies.py
+++ b/lib/sqlalchemy/orm/strategies.py
@@ -1367,8 +1367,7 @@ class JoinedLoader(AbstractRelationshipLoader):
         # send a hint to the Query as to where it may "splice" this join
         eagerjoin.stop_on = entity.selectable

-        if self.parent_property.secondary is None and \
-                not parentmapper:
+        if not parentmapper:
             # for parentclause that is the non-eager end of the join,
             # ensure all the parent cols in the primaryjoin are actually
             # in the

[Mike Bayer]

• Fixed joinedload bug which would occur when a. the query includes limit/offset criteria that forces a subquery b. the relationship uses "secondary" c. the primaryjoin of the relationship refers to a column that is either not part of the primary key, or is a PK col in a joined-inheritance subclass table that is under a different attribute name than the parent table's primary key column d. the query defers the columns that are present in the primaryjoin, typically via not being included in load_only(); the necessary column(s) would not be present in the subquery and produce invalid SQL. fixes #3592

→ <<cset 60c36ca8418c>>

[Mike Bayer]

• Fixed joinedload bug which would occur when a. the query includes limit/offset criteria that forces a subquery b. the relationship uses "secondary" c. the primaryjoin of the relationship refers to a column that is either not part of the primary key, or is a PK col in a joined-inheritance subclass table that is under a different attribute name than the parent table's primary key column d. the query defers the columns that are present in the primaryjoin, typically via not being included in load_only(); the necessary column(s) would not be present in the subquery and produce invalid SQL. fixes #3592

(cherry picked from commit 60c36ca8418cec180733a4d97637699fa2d3c36e)

→ <<cset 7998f15b1687>>