[Sqlalchemy-tickets] Issue #3822: load_only on joined parent row causes issuing queries for already fetched columns (zzzeek/sqlalchemy)

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

New issue 3822: load_only on joined parent row causes issuing queries for already fetched columns
https://bitbucket.org/zzzeek/sqlalchemy/issues/3822/load_only-on-joined-parent-row-causes

Marcin Barczyński:

Consider the following example:
```
#!python

from sqlalchemy.engine import create_engine
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import aliased, contains_eager, relationship
from sqlalchemy.orm import sessionmaker


Base = declarative_base()
class Node(Base):
    __tablename__ = 'node'

    id = Column(Integer, primary_key=True)
    parent_id = Column(ForeignKey('node.id'))
    parent = relationship('Node', remote_side=[id])
    name = Column(String)


engine = create_engine("postgresql://test:test@localhost/test", echo=True)
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)

session_class = sessionmaker(bind=engine)
session1 = session_class()

node1 = Node(id=1, parent=None, name='str1')
node2 = Node(id=2, parent=None, name='str2')
node3 = Node(id=3, parent=node2, name='str3')
node1.parent = node2

session1.add(node1)
session1.add(node2)
session1.add(node3)
session1.commit()

session2 = session_class()
ParentNode = aliased(Node)
query = session2.query(Node).\
    outerjoin(ParentNode, Node.parent).\
    options(contains_eager(Node.parent, alias=ParentNode).load_only(ParentNode.id, ParentNode.parent_id))

for row in query.order_by(Node.id):
    print row.id, row.name

```


Here are the queries emitted by SQLAlchemy:
```
2016-10-13 12:14:38,778 INFO sqlalchemy.engine.base.Engine SELECT node_1.id AS node_1_id, node_1.parent_id AS node_1_parent_id, node.id AS node_id, node.parent_id AS node_parent_id, node.name AS node_name 
FROM node LEFT OUTER JOIN node AS node_1 ON node_1.id = node.parent_id ORDER BY node.id
2016-10-13 12:14:38,778 INFO sqlalchemy.engine.base.Engine {}
2016-10-13 12:14:38,780 INFO sqlalchemy.engine.base.Engine SELECT node.name AS node_name 
FROM node 
WHERE node.id = %(param_1)s
2016-10-13 12:14:38,780 INFO sqlalchemy.engine.base.Engine {'param_1': 2}
```

Despite the fact that the first query fetches all necessary columns, an additional query is issued for ```name``` of the second node.
Note that ```order_by``` is crucial here - without it everything works as expected.