Async Many-To-Many not yielding all joined rows on subsequent selects

[lukasmetzner]

We have observed the following issue and are unsure, if it is a bug or expected behaviour. We have books and authors and whatever we select last does not have its relation filled (second print statement is an empty list). If we place session.expunge_all() between the two select statements everything works as expected.

Only the scalar_first and scalars_first_expunge_all functions work, all others lead to the described issue.

SQLAlchemy Version: 2.0.30
aiosqlite Version: 0.20.0

We also tested the script with PostgreSQL (asyncpg).

import asyncio
import os
from typing import List
from sqlalchemy import Column, ForeignKey, Table, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, selectinload
from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine

engine = create_async_engine(
    "sqlite+aiosqlite:///database.db",
    pool_pre_ping=True,
)


async_session_factory = async_sessionmaker(
    engine,
    expire_on_commit=False,
    autoflush=False,
    future=True,
)


class Base(DeclarativeBase):
    pass


authors_books_association = Table(
    "authors_books_association",
    Base.metadata,
    Column("author_id", ForeignKey("authors.id", ondelete="CASCADE")),
    Column("book_id", ForeignKey("books.id")),
)


class Author(Base):
    __tablename__ = "authors"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    name: Mapped[str] = mapped_column()
    books: Mapped[list["Book"]] = relationship(
        secondary=authors_books_association,
        back_populates="authors",
        lazy="noload",
        cascade="all, delete",
    )


class Book(Base):
    __tablename__ = "books"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column()
    authors: Mapped[List["Author"]] = relationship(
        secondary=authors_books_association,
        back_populates="books",
        lazy="noload",
        cascade="all, delete",
    )


async def scalars_first_expunge_all():
    async with async_session_factory() as session:
        authors_with_books = await session.scalars(
            select(Author)
            .where(Author.id == 1)
            .options(selectinload(Author.books))
        )
        session.expunge_all()
        books_with_authors = await session.scalars(
            select(Book)
            .where(Book.id == 1)
            .options(selectinload(Book.authors))
        )
        print(authors_with_books.first().books)
        print(books_with_authors.first().authors)


async def scalars_first():
    async with async_session_factory() as session:
        authors_with_books = await session.scalars(
            select(Author)
            .where(Author.id == 1)
            .options(selectinload(Author.books))
        )
        print(authors_with_books.first().books)
        books_with_authors = await session.scalars(
            select(Book)
            .where(Book.id == 1)
            .options(selectinload(Book.authors))
        )
        print(books_with_authors.first().authors)


async def execute():
    async with async_session_factory() as session:
        authors_with_books = (await session.execute(
            select(Author)
            .where(Author.id == 1)
            .options(selectinload(Author.books))
        )).all()
        print(authors_with_books[0][0].books)
        books_with_authors = (await session.execute(
            select(Book)
            .where(Book.id == 1)
            .options(selectinload(Book.authors))
        )).all()
        print(books_with_authors[0][0].authors)


async def scalars_all():
    async with async_session_factory() as session:
        authors_with_books = (await session.scalars(
            select(Author)
            .where(Author.id == 1)
            .options(selectinload(Author.books))
        )).all()
        print(authors_with_books[0].books)
        books_with_authors = (await session.scalars(
            select(Book)
            .where(Book.id == 1)
            .options(selectinload(Book.authors))
        )).all()
        print(books_with_authors[0].authors)


async def scalars_list_comprehension():
    async with async_session_factory() as session:
        authors_with_books = [x for x in await session.scalars(
            select(Author)
            .where(Author.id == 1)
            .options(selectinload(Author.books))
        )]
        print(authors_with_books[0].books)
        books_with_authors = [x for x in await session.scalars(
            select(Book)
            .where(Book.id == 1)
            .options(selectinload(Book.authors))
        )]
        print(books_with_authors[0].authors)


async def scalar():
    async with async_session_factory() as session:
        authors_with_books = await session.scalar(
            select(Author)
            .where(Author.id == 1)
            .options(selectinload(Author.books))
        )
        print(authors_with_books.books)
        books_with_authors = await session.scalar(
            select(Book)
            .where(Book.id == 1)
            .options(selectinload(Book.authors))
        )
        print(books_with_authors.authors)


async def main():
    os.system("rm -f database.db")
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)

    async with async_session_factory() as session:
        author1 = Author(name="Author1")
        author2 = Author(name="Author2")
        session.add_all([author1, author2])

        book1 = Book(name="Book1")
        book2 = Book(name="Book2")
        session.add_all([book1, book2])

        author1.books.append(book1)
        author1.books.append(book2)

        author2.books.append(book1)
        author2.books.append(book2)

        await session.commit()

    # works
    print("##### Scalars .first() #####")
    await scalars_first()
    print("##### Scalars .first() and expunge_all() #####")
    await scalars_first_expunge_all()
    # broken
    print("##### Scalars list comprehension #####")
    await scalars_list_comprehension()
    print("##### Scalar #####")
    await scalar()
    print("##### Scalars .all() #####")
    await scalars_all()
    print("##### Execute .all() #####")
    await execute()


if __name__ == "__main__":
    asyncio.run(main=main())

[zzzeek]

hi -

you have "noload" turned on. This will populate relationships with empty lists as you see. your selectinload does not take effect on the second run because those Book objects are already loaded, with their no-loaded blank list.

"noload" is a feature that never should have been added to SQLAlchemy, it has no legitimate uses, and I would turn it off to solve this problem.