SELECT describes a result set; it does not promise row order unless ORDER BY makes that order complete. Filters use three-valued logic—true, false, and unknown—because NULL means missing or inapplicable, not zero or an empty string. Correct reading also requires explicit columns, tenant scope, and bounded pagination.
A query is a pipeline that conceptually chooses source rows, filters them, forms groups, projects expressions, sorts, and limits. The planner may execute it differently while preserving SQL semantics.
The ID tie-breaker makes ordering deterministic when timestamps match. SELECT * is useful interactively but makes application contracts brittle and can fetch large/sensitive columns unnecessarily.
NULL = NULL is unknown, not true. Use:
For null-safe equality, PostgreSQL provides:
NOT IN can surprise when its set contains null. Prefer NOT EXISTS for anti-joins when nullability is possible.
Offset pagination is simple but grows more expensive and shifts when rows are inserted. Keyset pagination continues after the last sort key:
The cursor contains the last created_at and id. Direction and null handling must match the ordering.
Retrieval and model tools need bounded, deterministic context. An unbounded query can exceed token, latency, or privacy budgets. Explicit projections prevent hidden columns from entering prompts. Stable pagination and “as of” timestamps make evaluations reproducible; a changing result set otherwise makes model comparisons misleading.
Insert two rollbackable tickets with the same created_at. Prove ordering is unstable in meaning without a tie-breaker, then add id. Create rows with null and non-null optional fields and produce a truth table for =, <>, IS NULL, and IS DISTINCT FROM.
Build an inbox query for open/pending tickets ordered by urgent-first, then oldest creation, then ID. Add keyset continuation for the composite order.
Acceptance criterion: two pages contain no duplicate or missing IDs on a fixed snapshot, and ties resolve deterministically.