[Sqlalchemy-tickets] Issue #3922: Old entity returns after joinedload usage (zzzeek/sqlalchemy)

[Dima K. <iss...@bi...>]

New issue 3922: Old entity returns after joinedload usage
https://bitbucket.org/zzzeek/sqlalchemy/issues/3922/old-entity-returns-after-joinedload-usage

Dima Kukushkin:

hello!

I've got strange behaviour when querying entity already loaded with joined load. Here is some example:


```
#!python

class Company(Base):

    __tablename__ = 'company'

    id = Column(Integer, primary_key=True, unique=True, nullable=False)
    name = Column(String(255), nullable=False)


class Account(Base):

    __tablename__ = 'account'

    id = Column(Integer, primary_key=True, unique=True, nullable=False)
    company_id = Column(BigInteger, ForeignKey('company.id', ondelete='SET NULL'), nullable=True, index=True)

    clan = relationship('Company', foreign_keys=clan_id, uselist=False, backref=backref('accounts', uselist=True))


# Try to load account, then change company in concurrent transaction, then query for company

# Begin transaction READ COMMITTED
account = db.query(Account).options(joinedload('company')).filter_by(company_id=company_id)
old_company_name = account.company.name
import pdb; pdb.set_trace() # Here we start concurrent transaction in another terminal session to change company name [1]
company = db.query(Company).with_for_update(of=Company, key_share=True).filter_by(company_id=company_id).one_or_none()
assert company.name != old_company_name # It fails!
```

Concurrent transaction:
```
db=# begin;
db=# select name from company where id=123 for no key update of company; # here we will press [c]ontinue in first transaction to check that it will wait 2nd commited
db=# update company set name = 'new_name' where id=123;
db=# commit;
```

I would like to say that sometimes `db.query(Company).with_for_update(of=Company, key_share=True).get(company_id)` returns None, while row with this id is present in the table all time.

SQLAlchemy version is 1.1.5
PostgreSQL 9.5