[Sqlalchemy-tickets] Issue #4343: Deleting middle of many-to-many-to-one relationship via query fails (zzzeek/sqlalchemy)

[John T. W. I. <iss...@bi...>]

New issue 4343: Deleting middle of many-to-many-to-one relationship via query fails
https://bitbucket.org/zzzeek/sqlalchemy/issues/4343/deleting-middle-of-many-to-many-to-one

John Thorvald Wodder II:

The following code declares three classes joined by two many-to-one
relationships, creates & adds an object of each class, and then deletes the
"middle" object:

    import sqlalchemy as S
    from   sqlalchemy.ext.declarative import declarative_base
    from   sqlalchemy.orm             import backref, relationship, sessionmaker

    Base = declarative_base()

    class Project(Base):
        __tablename__ = 'projects'

        id = S.Column(S.Integer, primary_key=True, nullable=False)
        name = S.Column(S.Unicode(2048), nullable=False)

        def __repr__(self):
            return 'Project(name={!r})'.format(self.name)


    class Release(Base):
        __tablename__ = 'releases'

        id = S.Column(S.Integer, primary_key=True, nullable=False)
        version = S.Column(S.Unicode(2048), nullable=False)
        project_id = S.Column(S.Integer, S.ForeignKey('projects.id'), nullable=False)
        project = relationship('Project', backref=backref('releases', cascade='all, delete, delete-orphan'))

        def __repr__(self):
            return 'Release(project={!r}, version={!r})'\
                    .format(self.project, self.version)


    class Asset(Base):
        __tablename__ = 'assets'

        id = S.Column(S.Integer, primary_key=True, nullable=False)
        filename = S.Column(S.Unicode(2048), nullable=False)
        release_id = S.Column(S.Integer, S.ForeignKey('releases.id'), nullable=False)
        release = relationship('Release', backref=backref('assets', cascade='all, delete, delete-orphan'))

        def __repr__(self):
            return 'Asset(release={!r}, filename={!r})'\
                    .format(self.release, self.filename)


    engine = S.create_engine('sqlite:///:memory:')
    Base.metadata.create_all(engine)
    session = sessionmaker(bind=engine)()
    session.execute("PRAGMA foreign_keys=ON")

    p = Project(name='FooBar')
    session.add(p)
    r = Release(project=p, version='1.0')
    session.add(r)
    a = Asset(release=r, filename='FooBar-1.0.tar.gz')
    session.add(a)

    print(session.query(Project).all())
    print(session.query(Release).all())
    print(session.query(Asset).all())

    #session.delete(r)  # Good
    session.query(Release).filter(Release.project == p).delete()  # Bad

    print()
    print(session.query(Project).all())
    print(session.query(Release).all())
    print(session.query(Asset).all())

If the deletion is performed with `session.delete(r)`, then the code works as
intended, with all of `r`'s assets (`a`) being deleted along with it.  However,
if `r` is instead deleted by query (the line marked "`# Bad`"), then we get
this error:

    Traceback (most recent call last):
      File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
        context)
      File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
        cursor.execute(statement, parameters)
    sqlite3.IntegrityError: FOREIGN KEY constraint failed

    The above exception was the direct cause of the following exception:

    Traceback (most recent call last):
      File "delcache3.py", line 62, in <module>
        session.query(Release).filter(Release.project == p).delete()
      File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3353, in delete
        delete_op.exec_()
      File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 1329, in exec_
        self._do_exec()
      File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 1521, in _do_exec
        self._execute_stmt(delete_stmt)
      File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 1336, in _execute_stmt
        mapper=self.mapper)
      File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 1176, in execute
        bind, close_with_result=True).execute(clause, params or {})
      File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 948, in execute
        return meth(self, multiparams, params)
      File "/usr/local/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
        return connection._execute_clauseelement(self, multiparams, params)
      File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
        compiled_sql, distilled_params
      File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
        context)
      File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
        exc_info
      File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
        reraise(type(exception), exception, tb=exc_tb, cause=cause)
      File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 248, in reraise
        raise value.with_traceback(tb)
      File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
        context)
      File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
        cursor.execute(statement, parameters)
    sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) FOREIGN KEY constraint failed [SQL: 'DELETE FROM releases WHERE ? = releases.project_id'] [parameters: (1,)] (Background on this error at: http://sqlalche.me/e/gkpj)

If the `session.execute("PRAGMA foreign_keys=ON")` line is commented out, then
the delete query completes without error, but the script's output then shows
that `a` is not deleted, and adding the line
`print(session.query(Asset).first().release_id)` shows that `a.release_id` is
still pointing to a `Release` that no longer exists.

If we instead use PostgreSQL rather than SQLite, we get a slightly more
informative error:

    Traceback (most recent call last):
      File "/home/jwodder/tmp/delcache/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
        context)
      File "/home/jwodder/tmp/delcache/venv/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
        cursor.execute(statement, parameters)
    psycopg2.IntegrityError: update or delete on table "releases" violates foreign key constraint "assets_release_id_fkey" on table "assets"
    DETAIL:  Key (id)=(1) is still referenced from table "assets".


    The above exception was the direct cause of the following exception:

    Traceback (most recent call last):
      File "delcache3.py", line 70, in <module>
        session.query(Release).filter(Release.project == p).delete()
      File "/home/jwodder/tmp/delcache/venv/lib/python3.5/site-packages/sqlalchemy/orm/query.py", line 3353, in delete
        delete_op.exec_()
      File "/home/jwodder/tmp/delcache/venv/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py", line 1329, in exec_
        self._do_exec()
      File "/home/jwodder/tmp/delcache/venv/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py", line 1521, in _do_exec
        self._execute_stmt(delete_stmt)
      File "/home/jwodder/tmp/delcache/venv/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py", line 1336, in _execute_stmt
        mapper=self.mapper)
      File "/home/jwodder/tmp/delcache/venv/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 1176, in execute
        bind, close_with_result=True).execute(clause, params or {})
      File "/home/jwodder/tmp/delcache/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 948, in execute
        return meth(self, multiparams, params)
      File "/home/jwodder/tmp/delcache/venv/lib/python3.5/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
        return connection._execute_clauseelement(self, multiparams, params)
      File "/home/jwodder/tmp/delcache/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
        compiled_sql, distilled_params
      File "/home/jwodder/tmp/delcache/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
        context)
      File "/home/jwodder/tmp/delcache/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
        exc_info
      File "/home/jwodder/tmp/delcache/venv/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
        reraise(type(exception), exception, tb=exc_tb, cause=cause)
      File "/home/jwodder/tmp/delcache/venv/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 248, in reraise
        raise value.with_traceback(tb)
      File "/home/jwodder/tmp/delcache/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
        context)
      File "/home/jwodder/tmp/delcache/venv/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
        cursor.execute(statement, parameters)
    sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) update or delete on table "releases" violates foreign key constraint "assets_release_id_fkey" on table "assets"
    DETAIL:  Key (id)=(1) is still referenced from table "assets".
     [SQL: 'DELETE FROM releases WHERE %(param_1)s = releases.project_id'] [parameters: {'param_1': 1}] (Background on this error at: http://sqlalche.me/e/gkpj)

This problem was observed on the following platforms:

- SQLAlchemy version: 1.2.12  
  Python version: 3.5.2  
  OS: Ubuntu Xenial 16.04.5  
  Database: Python's built-in SQLite3 (`sqlite3.version = '2.6.0'`, `sqlite3.sqlite_version = '3.11.0'`)

- SQLAlchemy version: 1.2.12  
  Python version: 3.7.0  
  OS: Mac OS X 10.13.6  
  Database: Python's built-in SQLite3 (`sqlite3.version = '2.6.0'`, `sqlite3.sqlite_version = '3.24.0'`)

- SQLAlchemy version: 1.2.12  
  Python version: 3.5.2  
  OS: Ubuntu Xenial 16.04.5  
  Database: PostgreSQL 9.6.10-1.pgdg90+1  
  Driver: psycopg2 (installed via `psycopg2-binary` v2.7.5)