Object Consistency Issue When consulting object before flushing it when using an association table

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:

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

Tests:

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....

Comments (9)