LIMIT 1 Without ORDER BY Returns Another Tenant

The short version

PostgreSQL does not guarantee which row it returns when you add LIMIT 1 to a SELECT with no ORDER BY. In a staging-only dev-login tool, the query resolving “one existing school_admin” was written in exactly that shape. While there is only one tenant it looks like the same row comes back every time, but once multi-tenant test data piles up in the staging DB, each dev-login run could return a different school’s admin uid. Review caught it, and we made it deterministic by adding a CASE-expression ORDER BY that always prefers the dev-login test school.

Where it started

dev-login is a developer feature, staging-only, that lets you log in as a school_admin or teacher with no password stored or required, reachable only through a path that clears several layered gates (isProdLikeEnv, APP_ENV==='staging', a gate key). Its purpose is to reuse the teacher and school-administrator accounts already in the staging DB, so the experience matches operating on real data.

findExistingSchoolAdminUid handles that account resolution, taking exactly one row with users.role = 'school_admin' and is_active. Filtering with WHERE and adding LIMIT 1 guarantees that exactly one row comes back, but which row that is is not guaranteed unless you write ORDER BY. PostgreSQL is free to vary the row order a SELECT without ORDER BY returns, depending on the execution plan, page cache state, concurrent updates and so on.

Why

The staging DB this function runs against can hold test data for several schools (tenants) side by side. When rows that are school_admin and is_active exist across several schools, which school’s row a LIMIT 1 with no explicit ordering actually returns depends on the table’s physical state at that moment. Add test data, or let another batch update the same table, and the returned row can change.

The function’s docstring states the concern outright.

To avoid falling onto “whichever school was created first” when staging holds multi-tenant test data, prefer school_admin rows under the dev-login test school (DEVLOGIN_TEST) above all else

For what dev-login is for, leaving this alone would show up as “today I logged in as A school’s admin; tomorrow the same steps make me B school’s admin”. dev-login is a path developers use for verification, so there is no direct customer impact, but a feature used to verify behaviour that assumes tenant isolation standing on a premise where the tenant it returns can vary run to run is precarious. Review flagged it, and it was made deterministic in the same PR, before staging accumulated production-like data.

Fixing it

We dropped the premise “any school’s row will do” and gave ORDER BY a deterministic order: “always prefer the dev-login test school (DEVLOGIN_TEST)”.

// packages/db/src/queries/dev-login-accounts.ts:93-103
const rows = await tx
  .select({ id: users.id })
  .from(users)
  .where(and(eq(users.role, "school_admin"), eq(users.isActive, true)))
  // Prefer rows under the DEVLOGIN_TEST school (0=test school / 1=other).
  // Ties stabilised by createdAt/id.
  .orderBy(
    sql`case when ${users.schoolId} = ${DEVLOGIN_TEST_SCHOOL_ID} then 0 else 1 end`,
    asc(users.createdAt),
    asc(users.id),
  )
  .limit(1);

Ordering rows whose schoolId matches the dev-login test school ID as 0 and everything else as 1 with a CASE expression means the test school’s admin is always chosen as long as one exists. The ties remaining as a fallback when there is no test-school admin (all the 1s) are stabilised by ascending createdAt and id, so the same data state always returns the same row. If you want a specific real school’s admin, a separate path lets the caller state it explicitly with the DEV_LOGIN_CONFIG.admin.uid hint; this fix makes the default behaviour deterministic for when that hint is absent.

Preventing a repeat

A missing ORDER BY never surfaces as long as tests are written and run against a single tenant with little data. Looking at the LIMIT clause alone tells you only “this is narrowed to one row”; whether the same one row keeps coming back is a separate question you have to be conscious of.

Generalised: when writing a SELECT ... LIMIT N to fetch “a representative row”, ORDER BY has to be treated as the place where you state what gets preferred. A LIMIT with ORDER BY omitted looks accidentally stable in a test environment with little data and a single tenant, and only shows its symptom in an environment with several tenants or a lot of data. For work like dev-login where you want a representative row chosen deterministically, writing the preferred condition (here, whether it is the dedicated test school) as a CASE expression and writing out the tie-break keys (createdAt, id) in ORDER BY too gives a result independent of tenant count and data volume.