get() query does not load joinedloaded relationship when reloading expired object

[종빈 박]

I'm not so sure this is intended behavior or is a bug, but query().get() does not joinedload the relationship that is specified as lazy=joinedload when reloading the instance, marked as expired in identity map.

https://gist.github.com/chongkong/1460884de96866d42f5a197fcec5652b

[종빈 박]

I justs added sample2.py file and it seems to be a bug. For conveninece I'll append it to comment:

from sqlalchemy import Column, BigInteger, ForeignKey, create_engine
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
Session = sessionmaker()

class Foo(Base):
    __tablename__ = 'foo'

    id = Column(BigInteger, primary_key=True, autoincrement=True)
    bars = relationship('Bar', backref='foo', lazy='joined')

class Bar(Base):
    __tablename__ = 'bar'

    foo_id = Column(BigInteger, ForeignKey('foo.id'), primary_key=True)
    bar_id = Column(BigInteger, primary_key=True, autoincrement=False)


def main(sqlalchemy_dabatase_uri):
    engine = create_engine(sqlalchemy_dabatase_uri, echo=True)
    Base.metadata.create_all(bind=engine)
    Session.configure(bind=engine)
    session = Session()

    try:
        foo = Foo()
        foo.bars.append(Bar(bar_id=1))
        foo.bars.append(Bar(bar_id=2))
        session.add(foo)
        session.commit()

        print('----------')
        foo.bars
        print('----------')
    except:
        pass

    Base.metadata.drop_all(bind=engine)

if __name__ == '__main__':
    main('mysql+pymysql://local:Password!2@localhost:3306/test_utf8mb4?charset=utf8mb4')

Run result:

...
----------
2016-08-29 13:52:51,991 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2016-08-29 13:52:51,992 INFO sqlalchemy.engine.base.Engine SELECT foo.id AS foo_id 
FROM foo 
WHERE foo.id = %(param_1)s
2016-08-29 13:52:51,992 INFO sqlalchemy.engine.base.Engine {'param_1': 2}
2016-08-29 13:52:51,993 INFO sqlalchemy.engine.base.Engine SELECT bar.foo_id AS bar_foo_id, bar.bar_id AS bar_bar_id 
FROM bar 
WHERE %(param_1)s = bar.foo_id
2016-08-29 13:52:51,993 INFO sqlalchemy.engine.base.Engine {'param_1': 2}
----------
...

Expected to generate only one SQL.

[Mike Bayer]

hello -

thanks for the great detail on this. This is a known issue in #1763 and before I mark as a dupe, note that #1763 was marked as a "wontfix". However, this is the second time this has come up in the past 6 years, so perhaps #1763 should be re-examined, however running the eager loaders on an in-place unexpire is an intricate operation and there may have been some side effects observed as well.

[Mike Bayer]

Duplicate of #1763.