Eager loading not working when entity in identity_map

[Aidan Kane]

I'm seeing slightly surprising behaviour around loading options when an entity is already in the session identity_map. I've attached a self-contained test case so you can more easily see what I mean.

Depending on how you query for an entity (get() vs .filter().one()) you get different loading behaviour if the entity is already in the identity_map.

In the simplest case:

x = session.query(A).get(1)

# the loading options are ignored here
x = session.query(A).options(...loading_options...).get(1)

# while they're honoured here
x = session.query(A).options(...loading_options...).filter(A.id==1).one()

To run my test case:

import loading_behaviour_bug
loading_behaviour_bug.setup()

# subquery loading works
loading_behaviour_bug.test_expected1()

# subquery loading works
loading_behaviour_bug.test_expected2()

# subquery loading doesn't work
loading_behaviour_bug.test_unexpected()

I suspect this might be expected behaviour but I couldn't find anything about it in the docs.

Whenever you call get() it's coming from the identity_map, and it may have different children than those in the db (because another bit of code has made a change / dirtied the object) - though I would have thought that would be ok because it's in the same transaction.

My issue is that depending on what code has run up to this point I can't use .get() because I can't trust the eager loading to run (my current workaround is to use the filter().one() pattern in critical bits of code). It might that I just need a little more management around the scope of the unit-or-work but you can see how easy it would be to get this wrong (especially in a web application where the recommendation is to tie the unit-of-work to the entire request).

Then again, I may have just completely missed something because it seems implausible to me that I could find anything resembling a bug in SQLA :)

[Mike Bayer]

There's two things here. One is a misreading of the documentation, the other is a dupe. The misreading is that get() does not emit SQL when the object is already present:

http://docs.sqlalchemy.org/en/rel_0_9/orm/query.html?highlight=get#sqlalchemy.orm.query.Query.get

get() is special in that it provides direct access to the identity map of the owning Session. If the given primary key identifier is present in the local identity map, the object is returned directly from this collection and no SQL is emitted, unless the object has been marked fully expired.

So let's try to fix that test so that SQL is emitted:

def test_unexpected():
    session.rollback()
    # put 'a' into the identity_map without before any eager loading
    a = session.query(A).get(1)

    session.expire(a)
    print "------------"
    # eager loading now ignored so traversing the relationships is inefficient
    a = base_q.get(1)

now we get just the first SELECT, but not the eager loaders. that's a bug! and it is this one: #1763. There is no fix on the horizon for this issue as it isn't high priority. if you want to ensure the eager loaders run, use "filter_by(id = 1).first()".

[Mike Bayer]

Duplicate of #1763.

[Aidan Kane]

Thanks for the information. That all confirms what I suspected. Sorry I didn't find the previous issue in the tracker (I did have a hunt for it).

Good to know that the filter.first/one pattern is the right one to use. I'll carry on with that approach.