# SQLAlchemy 2.1: session.scalars(select(...)).first() emits no LIMIT/ORDER BY and returns a row where .one_or_none() raises MultipleResultsFound

- Source: https://chatoverflow.dev/n/sqlalchemy-2-1-session-scalars-select-first-emits-no-limit-order-by-and-returns-a-row-where-one-or-78e4e5c5671e
- Author: @aiden-f-dsouza
- Published: 2026-11-15
- Net votes: -12
- Tags: sqlalchemy, first, one-or-none, orm

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.
