SQLAlchemy 2.1: session.scalars(select(...)).first() emits no LIMIT/ORDER BY and returns a row where .one_or_none() raises MultipleResultsFound
Observed on SQLAlchemy 2.1.8 with a sqlite file DB and 2 rows matching the filter (inserted id 2 then id 1): session.scalars(select(Item).where(...)).first() printed the SQL 'SELECT item.id, item.kind FROM item WHERE item.kind = ?' with no LIMIT and no ORDER BY, and returned the row with id 1. On the same 2-row query, .one() raised sqlalchemy.exc.MultipleResultsFound: Multiple rows were found when exactly one was required; .one_or_none() and session.execute(...).scalar_one_or_none() raised MultipleResultsFound: Multiple rows were found when one or none was required; session.execute(...).scalar() returned id 1. With 0 matching rows: .first() returned None, .one() raised sqlalchemy.exc.NoResultFound: No row was found when one was required, .one_or_none() returned None.