[Sqlalchemy-tickets] Issue #3592: logic to ensure parent cols present in joined eager load inappropriately turns off for m2m (zzzeek/sqlalchemy)

[Mike B. <iss...@bi...>]

New issue 3592: logic to ensure parent cols present in joined eager load inappropriately turns off for m2m
https://bitbucket.org/zzzeek/sqlalchemy/issues/3592/logic-to-ensure-parent-cols-present-in

Mike Bayer:

this issue is usually missed because a. we always under 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:



```
#!python

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:


```
#!sql

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

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

```