[Sqlalchemy-tickets] Issue #3638: Object Consistency Issue When consulting object before flushing it when using an association table (zzzeek/sqlalchemy)

[Maxime L. <iss...@bi...>]

New issue 3638: Object Consistency Issue When consulting object before flushing it when using an association table
https://bitbucket.org/zzzeek/sqlalchemy/issues/3638/object-consistency-issue-when-consulting

Maxime Lecours:

If the object is consulted before flushing the session, the consistency of the relation between the objects is wrong. The following tests should pass, but test_1 fails on the second assertion. Both tests are identical, excepted for that one line:
```
#!python
self.assertEqual(session.query(Child).first().parent_asso.parent_id, 1)
```

Tests:

```
#!python
import unittest

from sqlalchemy import create_engine
from sqlalchemy.orm import relationship, backref
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.sql.schema import Column, ForeignKey
from sqlalchemy.sql.sqltypes import Integer, String

from sqlalchemy.ext.declarative.api import declarative_base
Base = declarative_base()
Session = sessionmaker(autoflush=False)


class SqlAlchemyConsistencyBugTest(unittest.TestCase):
    def test_1(self):
        engine = create_engine('sqlite:///:memory:', echo=False)
        Base.metadata.create_all(engine)

        Session.configure(bind=engine)
        session = Session()

        parent1 = Parent(name='parent1name')
        children1 = Child(name='children1name')
        parent1.children.append(children1)
        session.add(parent1)
        session.flush()

        parent2 = Parent(name='parent2name')
        parent2.children.append(children1)
        session.add(parent2)

        self.assertEqual(session.query(Child).first().parent_asso.parent_id, 1)

        session.flush()

        self.assertEqual(session.query(Child).first().parent_asso.parent_id, 2)

        session.rollback()

    def test_2(self):
        engine = create_engine('sqlite:///:memory:', echo=False)
        Base.metadata.create_all(engine)

        Session.configure(bind=engine)
        session = Session()

        parent1 = Parent(name='parent1name')
        children1 = Child(name='children1name')
        parent1.children.append(children1)
        session.add(parent1)
        session.flush()

        parent2 = Parent(name='parent2name')
        parent2.children.append(children1)
        session.add(parent2)

        session.flush()

        self.assertEqual(session.query(Child).first().parent_asso.parent_id, 2)

        session.rollback()


class AssociationTable(Base):
    __tablename__ = 'association'
    parent_id = Column(Integer, ForeignKey('parent.id'),
                       primary_key=True)
    child_id = Column(Integer, ForeignKey('child.id'),
                      primary_key=True,
                      unique=True)

    extra_data = Column(String)

    parent = relationship('Parent',
                          backref=backref("child_asso"),
                          uselist=False)
    child = relationship('Child', backref=backref("parent_asso",
                                                  uselist=False),
                         uselist=False)


class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    name = Column(String)

    children = relationship("Child",
                            secondary='association',
                            backref=backref('parent',
                                            uselist=False))


class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    name = Column(String, unique=True)


```
Problem was in version 1.0.11 and so I tested 1.1.0b1. Same results.
I encountered the problem in another situation with more complex relations and ended up doing with a test to find the problem. I did this test (the one up here) afterwards to simplify the details of implementation. 

In test_1 after the second "flush()", in an IDE or whatever the way you want, show the objects with 'session.query(*Object*).all()' (replacing *Object* with Parent, Child and AssociationTable), you'll find that there is only one return to "AssociationTable" (of course) but the first Parent returns an 'AssociationTable' through "child_asso" that does not exist anymore. And of course this doesn't happen in test_2....