Bug: query values seem either cached or wrong

Description

I encountered this in a project I'm working on, where the first query after startup would work, but the second query (with an additional parameter) would not. After enabling echo=True in the engine, I found that the wrong parameters were passed to the sql query.

In my example there were 2 separate columns with UUIDs. In my MCVE I have used strings to make the logging more apparent as it still reproduces the issue.

The first query has 3 parameters (the 2 uuid's and a third parameter), the second query has 2 parameters (the same 2 uuids). The first one works, the second doesn't. If you reverse which one is first, the first one still works.

URL to code causing the issue

No response

MCVE

from enum import Enum

from advanced_alchemy.base import UUIDBase
from advanced_alchemy.repository import SQLAlchemySyncRepository
from sqlalchemy import create_engine
from sqlalchemy.orm import (
    Mapped,
    Session,
    sessionmaker,
)

class Category(Enum):
    CHOICE_ONE = "choice_one"
    CHOICE_TWO = "choice_two"

class Item(UUIDBase):
    uuid_one: Mapped[str]
    uuid_two: Mapped[str]
    category: Mapped[Category]
    name: Mapped[str]

class UserRepository(SQLAlchemySyncRepository[Item]):
    model_type = Item

first_uuid = "a1f020a4-ca19-4492-860e-b1f40fd06ac8"
second_uuid = "a444feb2-18a1-4a10-a9e2-c055baabdb17"

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

with engine.begin() as conn:
    Item.metadata.create_all(conn)

with session_factory() as session:
    repository = UserRepository(session=session)
    repository.add_many(
        [
            Item(
                uuid_one=first_uuid,
                uuid_two=second_uuid,
                category=Category.CHOICE_ONE,
                name="foo",
            ),
        ]
    )
    session.commit()

    items, total = repository.list_and_count(
        Item.uuid_one == first_uuid,
        Item.uuid_two == second_uuid,
        Item.category == Category.CHOICE_ONE,
    )
    assert total == 1, f"Total items should be 1 on first query, but was {total}"

    items, total = repository.list_and_count(
        Item.uuid_one == first_uuid, Item.uuid_two == second_uuid
    )
    assert total == 1, f"Total items should be 1 on second query, but was {total}"

Steps to reproduce

No response

Screenshots

No response

Logs

python example.py
2024-08-20 15:56:34,548 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-08-20 15:56:34,548 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("item")
2024-08-20 15:56:34,548 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-08-20 15:56:34,548 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("item")
2024-08-20 15:56:34,548 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-08-20 15:56:34,548 INFO sqlalchemy.engine.Engine 
CREATE TABLE item (
        uuid_one VARCHAR NOT NULL, 
        uuid_two VARCHAR NOT NULL, 
        category VARCHAR(10) NOT NULL, 
        name VARCHAR NOT NULL, 
        id BINARY(16) NOT NULL, 
        sa_orm_sentinel INTEGER, 
        CONSTRAINT pk_item PRIMARY KEY (id)
)

2024-08-20 15:56:34,548 INFO sqlalchemy.engine.Engine [no key 0.00004s] ()
2024-08-20 15:56:34,548 INFO sqlalchemy.engine.Engine COMMIT
2024-08-20 15:56:34,549 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-08-20 15:56:34,549 INFO sqlalchemy.engine.Engine INSERT INTO item (uuid_one, uuid_two, category, name, id) VALUES (?, ?, ?, ?, ?)
2024-08-20 15:56:34,549 INFO sqlalchemy.engine.Engine [generated in 0.00009s] ('a1f020a4-ca19-4492-860e-b1f40fd06ac8', 'a444feb2-18a1-4a10-a9e2-c055baabdb17', 'CHOICE_ONE', 'foo', <memory at 0x7a50b
c524940>)
2024-08-20 15:56:34,550 INFO sqlalchemy.engine.Engine COMMIT
2024-08-20 15:56:34,551 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-08-20 15:56:34,552 INFO sqlalchemy.engine.Engine SELECT item.uuid_one, item.uuid_two, item.category, item.name, item.id, item.sa_orm_sentinel, count(*) OVER () AS anon_1 
FROM item 
WHERE item.uuid_one = ? AND item.uuid_two = ? AND item.category = ?
2024-08-20 15:56:34,552 INFO sqlalchemy.engine.Engine [generated in 0.00007s] ('a1f020a4-ca19-4492-860e-b1f40fd06ac8', 'a444feb2-18a1-4a10-a9e2-c055baabdb17', 'CHOICE_ONE')
2024-08-20 15:56:34,552 INFO sqlalchemy.engine.Engine SELECT item.uuid_one, item.uuid_two, item.category, item.name, item.id, item.sa_orm_sentinel, count(*) OVER () AS anon_1 
FROM item 
WHERE item.uuid_one = ? AND item.uuid_two = ?
2024-08-20 15:56:34,552 INFO sqlalchemy.engine.Engine [generated in 0.00006s] ('a444feb2-18a1-4a10-a9e2-c055baabdb17', 'a444feb2-18a1-4a10-a9e2-c055baabdb17')
2024-08-20 15:56:34,552 INFO sqlalchemy.engine.Engine ROLLBACK
Traceback (most recent call last):
  File "/home/jcorbett/dev/adv-alchemy-bug/example.py", line 65, in <module>
    assert total == 1, f"Total items should be 1 on second query, but was {total}"
           ^^^^^^^^^^
AssertionError: Total items should be 1 on second query, but was 0

Package Version

0.19.0

Platform

[X] Linux
[ ] Mac
[ ] Windows
[ ] Other (Please specify in the description above)

litestar-org / advanced-alchemy