orWhere() appends a bare OR with no parenthesised group, so any where() already on the query stops applying. SQL binds AND tighter than OR, so A AND B OR C parses as (A AND B) OR C — every row matching C comes back regardless of A.
Nothing warns, the query is valid SQL, and the result set is a superset of what was asked for. That last part is what makes it dangerous: it fails toward MORE rows, so it looks like data rather than an error.
Found on 0.2.26.
Repro, with row counts
const T = 'migrations' // 155 rows
await db.selectFrom(T).selectAll()
.where('id', '<=', 10)
.execute() // 10 rows
await db.selectFrom(T).selectAll()
.where('id', '<=', 10) // <- the bounding filter
.where('migration', 'like', '%create%')
.orWhere('migration', 'like', '%table%')
.execute() // 118 rowsEmitted SQL:
SELECT * FROM migrations WHERE id <= ? AND migration like ? OR migration like ?id <= 10 is silently gone. 10 rows → 118.
whereAny gets it right, which is what makes the asymmetry clear:
.where('id', '<=', 10).whereAny(['migration'], 'like', '%table%')
// SELECT * FROM migrations WHERE id <= ? AND (migration like ? OR migration like ?)
// 10 rowsAlso affects .where() chained AFTER .orWhere()
.where('title', 'like', '%a%').orWhere('content', 'like', '%b%').where('status', '=', 'published')
// SELECT … WHERE title like ? OR content like ? AND status = ?which parses as title OR (content AND status) — the trailing status filter binds only to the OR's right arm. So the bug is not just "the earlier filter is lost"; the grouping is wrong in both directions depending on chain order.
whereIn(...).orWhere(...) and whereNull(...).orWhere(...) emit the same flat shape and will mis-group as soon as a second AND-term exists.
Why this is worth a fix rather than a doc note
It bit a moderation queue in a review app. The admin list filters by status and then supports a text search over two columns, written the obvious way:
q = q.where('status', '=', status)
q = q.where('title', 'like', like).orWhere('content', 'like', like)A moderator on the "Pending" tab searching a common word got 18 rows back instead of 3, 15 of them already published. Both the list query and the count query were built identically, so total agreed with the wrong rows and nothing looked inconsistent — the queue simply showed already-published content as pending, and the backlog figure was 6× too high.
Suggested fix
Track OR-terms as a group and parenthesise on emit, so
.where(a).where(b).orWhere(c)produces a AND (b OR c) — matching what every other query builder in this space does with a chained orWhere, and what whereAny already does here.
If changing the emitted SQL is too breaking, the alternative is to make it impossible to write by accident: have orWhere throw when the query already has a where, and point at whereAny/a callback-group API. Silently emitting a valid query with different semantics than the chain reads is the worst of the three options.
Two smaller things found alongside
whereAny([], op, value) silently drops the predicate.
.where('status', '=', 'published').whereAny([], 'like', '%x%')
// SELECT * FROM judge_reviews WHERE status = ?An empty column list is almost always a bug at the call site (a .filter() that removed everything), and the consequence is again more rows than intended. Throwing, or emitting a false-y predicate, would both be safer than dropping it.
whereIn(col, []) emits IN ().
SELECT * FROM judge_reviews WHERE id IN ()SQLite accepts this and matches nothing, which is the sane reading. MySQL and PostgreSQL both reject IN () as a syntax error, so the same code is portable-looking and engine-dependent. Emitting a constant-false predicate instead would behave identically on SQLite and stop it exploding elsewhere. (I only executed this against SQLite.)