by Serguey Shinder
The ticket said an invoice was on two pages at once. A customer working back through eighteen months of billing history had found the same invoice number on page one and again on page three, and further down had noticed that another invoice she was certain existed did not appear anywhere at all. Both complaints were true, and both had the same cause.
The list was ordered by invoice date, most recent first, twenty rows to a page, each page a separate query with an offset. That is an entirely ordinary arrangement and it had worked for years.
What I had not thought about was ties. Most of that customer's invoices came from a monthly batch that stamped every row in the run with the same date. Forty invoices, one date, and no further instruction about how to arrange them. So the database arranged them however it liked, and it was under no obligation to like the same thing twice. Page one asked for the first twenty of forty equal rows and got a set. Page three asked for a later slice, on a different connection, some seconds afterwards, and the ordering underneath had shifted. One invoice landed in both slices. One fell into the gap between them and appeared in neither.
The part I want to be honest about is that nothing had changed. No deploy caused this. The behaviour had been available since the afternoon I wrote the query and had simply not surfaced until a customer had enough same-day invoices and enough patience to page through them. For years I had been reading correct output as evidence of a correct instruction, when it was really evidence of a system that happened to choose consistently while the data stayed small.
I had written a partial order and read it as a total one. The query said arrange these by date. In my head it said arrange these by date in a stable and repeatable way, which is a different sentence and one I had never actually typed. Everything downstream, including the entire idea of a page number, quietly rested on the sentence I had imagined rather than the one I had written.
The fix took four minutes. Every ordering in that codebase now ends with the primary key as a final tiebreaker, whether or not ties look possible, because looking possible is a judgement about today's data.
The habit I took from it is wider than sorting. Wherever a system hands me something that appears ordered, grouped or arranged, I now ask which part of that arrangement I actually requested and which part I am receiving as a gift. Gifts get withdrawn, usually on the day a customer is watching.
– Serguey Asael Shinder
Leave a Reply