Bug: select queries on repositories are not always idempotent

[sfermigier]

Description

When executing twice the same select query with a whereclause on a relationship, the second query gives an empty result.

Ex select(State).where(State.country == usa) (see below for complete reproducible code).

URL to code causing the issue

No response

MCVE

from advanced_alchemy.base import UUIDBase
from advanced_alchemy.repository import SQLAlchemySyncRepository
from sqlalchemy import create_engine, select, ForeignKey, func
from sqlalchemy.orm import Mapped, Session, sessionmaker, mapped_column, relationship


class Country(UUIDBase):
    name: Mapped[str]


class State(UUIDBase):
    name: Mapped[str]
    country_id: Mapped[str] = mapped_column(ForeignKey(Country.id))

    country = relationship(Country)


class USStateRepository(SQLAlchemySyncRepository[State]):
    model_type = State


engine = create_engine("sqlite:///:memory:", future=True, echo=True)
# engine = create_engine("postgresql://localhost/sandbox", future=True)
session_factory: sessionmaker[Session] = sessionmaker(engine, expire_on_commit=False)


def run_script() -> None:
    with engine.begin() as conn:
        State.metadata.create_all(conn)

    with session_factory() as db_session:
        usa = Country(name="United States of America")
        france = Country(name="France")
        db_session.add(usa)
        db_session.add(france)

        california = State(name="California", country=usa)
        oregon = State(name="Oregon", country=usa)
        ile_de_france = State(name="Île-de-France", country=france)

        repo = USStateRepository(session=db_session)
        repo.add(california)
        repo.add(oregon)
        repo.add(ile_de_france)
        db_session.commit()

        print("\n" + "-" * 80 + "\n")

        # Using only the ORM, this works fine:

        stmt = select(State).where(State.country_id == usa.id).with_only_columns(func.count())
        count = db_session.execute(stmt).scalar_one()
        assert count == 2, f"Expected 2, got {count}"
        count = db_session.execute(stmt).scalar_one()
        assert count == 2, f"Expected 2, got {count}"

        stmt = select(State).where(State.country == usa).with_only_columns(func.count())
        count = db_session.execute(stmt).scalar_one()
        assert count == 2, f"Expected 2, got {count}"
        count = db_session.execute(stmt).scalar_one()
        assert count == 2, f"Expected 2, got {count}"

        print("\n" + "-" * 80 + "\n")

        # Using the repository, this works:
        stmt1 = select(State).where(State.country_id == usa.id)

        print("First query")
        count = repo.count(statement=stmt1)
        assert count == 2, f"Expected 2, got {count}"

        print("Second query")
        count = repo.count(statement=stmt1)
        assert count == 2, f"Expected 2, got {count}"

        print("\n" + "-" * 80 + "\n")

        # But this fails (only after the second query):
        stmt2 = select(State).where(State.country == usa)

        print("First query")
        count = repo.count(statement=stmt2)
        assert count == 2, f"Expected 2, got {count}"

        print("Second query")
        count = repo.count(statement=stmt2)
        assert count == 2, f"Expected 2, got {count}"

        # It also fails with
        states = repo.list(statement=stmt2)
        count = len(states)
        assert count == 2, f"Expected 2, got {count}"



if __name__ == "__main__":
    run_script()

Steps to reproduce

No response

Screenshots

No response

Logs

First query
2024-02-01 14:12:48,387 INFO sqlalchemy.engine.Engine SELECT count(state.id) AS count_1
FROM state
WHERE ? = state.country_id
2024-02-01 14:12:48,387 INFO sqlalchemy.engine.Engine [generated in 0.00005s] (<memory at 0x1072a13c0>,)
Second query
2024-02-01 14:12:48,387 INFO sqlalchemy.engine.Engine SELECT count(state.id) AS count_1
FROM state
WHERE ? = state.country_id
2024-02-01 14:12:48,387 INFO sqlalchemy.engine.Engine [cached since 0.0002423s ago] (None,)
2024-02-01 14:12:48,387 INFO sqlalchemy.engine.Engine ROLLBACK
Traceback (most recent call last):
  File "/Users/fermigier/projects/abilian-analytics/sandbox/debug_aa.py", line 97, in <module>
    run_script()
  File "/Users/fermigier/projects/abilian-analytics/sandbox/debug_aa.py", line 87, in run_script
    assert count == 2, f"Expected 2, got {count}"
AssertionError: Expected 2, got 0

Jolt Project Version

0.7.0

Platform

• Linux
• Mac
• Windows
• Other (Please specify in the description above)

---

Funding

• If you would like to see an issue prioritized, make a pledge towards it!
• We receive the pledge once the issue is completed & verified

[cofin]

Same as #120

Likely lambda_stmt related.