Backwards incompatible break in 2.0.31? "assertion failed attempting to produce joined eager loads" #11965
Description
Describe the bug
Getting "AssertionError: assertion failed attempting to produce joined eager loads" when executing a query. Following up for @rsyring with an MCVE for #11805.
Optional link from https://docs.sqlalchemy.org which documents the behavior that is expected
No response
SQLAlchemy Version in Use
2.0.31+
DBAPI (i.e. the database driver)
psycopg2, pysqlite
Database Vendor and Major Version
SQLite, PostgreSQL 14
Python Version
3.11
Operating system
Linux, OSX
To Reproduce
import sqlalchemy as sa
import sqlalchemy.orm as saorm
engine = sa.create_engine('sqlite:///', echo=True)
Session = saorm.sessionmaker(engine)
session = Session()
class Base(saorm.DeclarativeBase):
pass
class EntityMixin:
id: saorm.Mapped[int] = saorm.mapped_column(primary_key=True)
class Source(EntityMixin, Base):
__tablename__ = 'source'
class Day(EntityMixin, Base):
__tablename__ = 'day'
class Run(EntityMixin, Base):
__tablename__ = 'run'
source_id: saorm.Mapped[int] = saorm.mapped_column(sa.ForeignKey(Source.id), nullable=False)
source = saorm.relationship(Source, lazy='joined', innerjoin=True)
day = saorm.relationship(
Day,
lazy='joined',
innerjoin=True,
)
day_id: saorm.Mapped[int] = saorm.mapped_column(sa.ForeignKey(Day.id), nullable=False)
class Event(EntityMixin, Base):
__tablename__ = 'event'
run_id: saorm.Mapped[int] = saorm.mapped_column(sa.ForeignKey(Run.id), nullable=False)
run = saorm.relationship(Run, lazy='joined', innerjoin=True)
def on_room_change(self):
session.execute(sa.select(Room).filter_by(event_id=self.id)).one_or_none()
class Room(EntityMixin, Base):
__tablename__ = 'room'
event_id: saorm.Mapped[int] = saorm.mapped_column(sa.ForeignKey(Event.id), nullable=False)
event = saorm.relationship(Event, foreign_keys=event_id, lazy='joined')
if __name__ == '__main__':
Base.metadata.create_all(engine)
run = Run(source=Source(), day=Day())
event = Event(run=run)
session.add(event)
session.commit()
event.on_room_change()Error
2024-10-08 09:53:40,464 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-10-08 09:53:40,464 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("source")
2024-10-08 09:53:40,464 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-10-08 09:53:40,464 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("source")
2024-10-08 09:53:40,465 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-10-08 09:53:40,465 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("day")
2024-10-08 09:53:40,465 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-10-08 09:53:40,465 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("day")
2024-10-08 09:53:40,465 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-10-08 09:53:40,465 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("run")
2024-10-08 09:53:40,465 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-10-08 09:53:40,465 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("run")
2024-10-08 09:53:40,465 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-10-08 09:53:40,465 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("event")
2024-10-08 09:53:40,465 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-10-08 09:53:40,465 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("event")
2024-10-08 09:53:40,465 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-10-08 09:53:40,465 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("room")
2024-10-08 09:53:40,465 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-10-08 09:53:40,465 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("room")
2024-10-08 09:53:40,465 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-10-08 09:53:40,465 INFO sqlalchemy.engine.Engine
CREATE TABLE source (
id INTEGER NOT NULL,
PRIMARY KEY (id)
)
2024-10-08 09:53:40,465 INFO sqlalchemy.engine.Engine [no key 0.00002s] ()
2024-10-08 09:53:40,465 INFO sqlalchemy.engine.Engine
CREATE TABLE day (
id INTEGER NOT NULL,
PRIMARY KEY (id)
)
2024-10-08 09:53:40,465 INFO sqlalchemy.engine.Engine [no key 0.00002s] ()
2024-10-08 09:53:40,466 INFO sqlalchemy.engine.Engine
CREATE TABLE run (
source_id INTEGER NOT NULL,
day_id INTEGER NOT NULL,
id INTEGER NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY(source_id) REFERENCES source (id),
FOREIGN KEY(day_id) REFERENCES day (id)
)
2024-10-08 09:53:40,466 INFO sqlalchemy.engine.Engine [no key 0.00002s] ()
2024-10-08 09:53:40,466 INFO sqlalchemy.engine.Engine
CREATE TABLE event (
run_id INTEGER NOT NULL,
id INTEGER NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY(run_id) REFERENCES run (id)
)
2024-10-08 09:53:40,466 INFO sqlalchemy.engine.Engine [no key 0.00002s] ()
2024-10-08 09:53:40,466 INFO sqlalchemy.engine.Engine
CREATE TABLE room (
event_id INTEGER NOT NULL,
id INTEGER NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY(event_id) REFERENCES event (id)
)
2024-10-08 09:53:40,466 INFO sqlalchemy.engine.Engine [no key 0.00002s] ()
2024-10-08 09:53:40,466 INFO sqlalchemy.engine.Engine COMMIT
2024-10-08 09:53:40,469 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-10-08 09:53:40,469 INFO sqlalchemy.engine.Engine INSERT INTO day DEFAULT VALUES
2024-10-08 09:53:40,469 INFO sqlalchemy.engine.Engine [generated in 0.00006s] ()
2024-10-08 09:53:40,469 INFO sqlalchemy.engine.Engine INSERT INTO source DEFAULT VALUES
2024-10-08 09:53:40,469 INFO sqlalchemy.engine.Engine [generated in 0.00004s] ()
2024-10-08 09:53:40,470 INFO sqlalchemy.engine.Engine INSERT INTO run (source_id, day_id) VALUES (?, ?)
2024-10-08 09:53:40,470 INFO sqlalchemy.engine.Engine [generated in 0.00006s] (1, 1)
2024-10-08 09:53:40,470 INFO sqlalchemy.engine.Engine INSERT INTO event (run_id) VALUES (?)
2024-10-08 09:53:40,470 INFO sqlalchemy.engine.Engine [generated in 0.00005s] (1,)
2024-10-08 09:53:40,470 INFO sqlalchemy.engine.Engine COMMIT
2024-10-08 09:53:40,471 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-10-08 09:53:40,472 INFO sqlalchemy.engine.Engine SELECT event.run_id AS event_run_id, event.id AS event_id, source_1.id AS source_1_id, day_1.id AS day_1_id, run_1.source_id AS run_1_source_id, run_1.day_id AS run_1_day_id, run_1.id AS run_1_id
FROM event JOIN run AS run_1 ON run_1.id = event.run_id JOIN source AS source_1 ON source_1.id = run_1.source_id JOIN day AS day_1 ON day_1.id = run_1.day_id
WHERE event.id = ?
2024-10-08 09:53:40,472 INFO sqlalchemy.engine.Engine [generated in 0.00007s] (1,)
Traceback (most recent call last):
File "/Users/ben/code/work/racebetter/racebetter/tests/test_model.py", line 65, in <module>
event.on_room_change()
File "/Users/ben/code/work/racebetter/racebetter/tests/test_model.py", line 47, in on_room_change
session.execute(sa.select(Room).filter_by(event_id=self.id)).one_or_none()
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/ben/code/work/racebetter/.venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2362, in execute
return self._execute_internal(
^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/ben/code/work/racebetter/.venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2247, in _execute_internal
result: Result[Any] = compile_state_cls.orm_execute_statement(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/ben/code/work/racebetter/.venv/lib/python3.11/site-packages/sqlalchemy/orm/context.py", line 305, in orm_execute_statement
result = conn.execute(
^^^^^^^^^^^^^
File "/Users/ben/code/work/racebetter/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
return meth(
^^^^^
File "/Users/ben/code/work/racebetter/.venv/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", line 515, in _execute_on_connection
return connection._execute_clauseelement(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/ben/code/work/racebetter/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1632, in _execute_clauseelement
compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
^^^^^^^^^^^^^^^^^^^^^^
File "/Users/ben/code/work/racebetter/.venv/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", line 703, in _compile_w_cache
compiled_sql = self._compiler(
^^^^^^^^^^^^^^^
File "/Users/ben/code/work/racebetter/.venv/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", line 316, in _compiler
return dialect.statement_compiler(dialect, self, **kw)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/ben/code/work/racebetter/.venv/lib/python3.11/site-packages/sqlalchemy/sql/compiler.py", line 1429, in __init__
Compiled.__init__(self, dialect, statement, **kwargs)
File "/Users/ben/code/work/racebetter/.venv/lib/python3.11/site-packages/sqlalchemy/sql/compiler.py", line 870, in __init__
self.string = self.process(self.statement, **compile_kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/ben/code/work/racebetter/.venv/lib/python3.11/site-packages/sqlalchemy/sql/compiler.py", line 915, in process
return obj._compiler_dispatch(self, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/ben/code/work/racebetter/.venv/lib/python3.11/site-packages/sqlalchemy/sql/visitors.py", line 141, in _compiler_dispatch
return meth(self, **kw) # type: ignore # noqa: E501
^^^^^^^^^^^^^^^^
File "/Users/ben/code/work/racebetter/.venv/lib/python3.11/site-packages/sqlalchemy/sql/compiler.py", line 4679, in visit_select
compile_state = select_stmt._compile_state_factory(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/ben/code/work/racebetter/.venv/lib/python3.11/site-packages/sqlalchemy/sql/base.py", line 683, in create_for_statement
return klass.create_for_statement(statement, compiler, **kw)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/ben/code/work/racebetter/.venv/lib/python3.11/site-packages/sqlalchemy/orm/context.py", line 1181, in create_for_statement
self._setup_for_generate()
File "/Users/ben/code/work/racebetter/.venv/lib/python3.11/site-packages/sqlalchemy/orm/context.py", line 1299, in _setup_for_generate
strategy(self, *rec[1:])
File "/Users/ben/code/work/racebetter/.venv/lib/python3.11/site-packages/sqlalchemy/orm/strategies.py", line 2541, in _create_eager_join
eagerjoin = self._splice_nested_inner_join(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/ben/code/work/racebetter/.venv/lib/python3.11/site-packages/sqlalchemy/orm/strategies.py", line 2656, in _splice_nested_inner_join
splicing is not False
AssertionError: assertion failed attempting to produce joined eager loads
Additional context
The example is as minimal as we were able to get it without making the error go away completely. The error does not occur in 2.0.30, but does occur in 2.0.31 or later.