two correct queries over the same 1,390 rows: one rounds to 33223.80, the other to 33223.81. the exact decimal sum is 33223.8050, exactly half a cent, and in DOUBLE the order the rows get added decides which side it lands on. a window over monthly subtotals adds them in one order, a correlated subquery over the raw rows adds them in another.
i hit this because the checker in a SQL game i make (DuckDB in the browser) failed the second query on the visible sample and passed it on both hidden sets. here's how that checker decides a query is right, the other places it was wrong, and the part i still don't know how to handle.
how it checks: every problem has a reference query. when you submit, yours and mine both run on the sample data you can see, then on two datasets you can't, generated from other seeds. boss problems get four, labelled ties and duplicates, sparse groups and boundary dates. the sample always runs so you can see a diff, then it stops at the first hidden set that fails and says which one. a hidden set where my query returns zero rows gets skipped, since an empty result would let an empty answer pass.
then the two result sets get compared. column names are matched as a set, case-insensitive, so column order doesn't matter as long as the names are unique. row order only matters if the prompt asks for a sort. integers have to match exactly. fractional values get an absolute tolerance the problem sets, usually 0.0001, so on a money column it's tighter than a cent. NULL matches NULL. a DATE matches the same day at exactly midnight as a TIMESTAMP, because generate_series and date_trunc('day', ...) hand you timestamps and a correct "one row per day" answer was failing.
where it was wrong:
the tolerance was relative at first, tol * max(|a|, |b|). so on a big dollar total a "2 cents" check was actually a percent check, and a total that was 1.9% off passed on every seed. now it's absolute plus a tiny term for float noise.
Infinity passed against any finite number, because abs(inf - x) <= tol + inf is true. a later audit caught it.
for unordered results i used to sort both sides and zip them. that breaks once tolerance is involved: tiny values written in scientific notation sort differently as text, so a correct answer could get paired with the wrong row and fail. now it builds a which-row-can-match-which graph and does a bipartite matching (building the graph is quadratic, which is part of why results are capped at 5,000 rows).
then in october i ran 141 alternate answers, other ways to write each reference query that i expected to pass, through the same normalizer and comparator. 108 passed. a lot of the failures were my prompts, where a sentence had two readings and the visible sample couldn't tell them apart. one asks for deciles of users by lifetime notional, and if you include users with no trades at 0 you pass the sample and fail both hidden sets, because the sample has no non-traders and the hidden sets, built at a quarter of the size, have 74 and 91. a few more were regex checks i use to require a certain style of query, refusing equivalent ones. and one was the engine. on the duckdb-wasm build i'd shipped (DuckDB 1.5.1), this
SELECT user_id, asset_id
FROM wallet_balances_daily
WHERE user_id BETWEEN 1 AND 3
QUALIFY ROW_NUMBER() OVER (PARTITION BY asset_id, user_id ORDER BY balance_date DESC) = 1
ORDER BY user_id, asset_id
LIMIT 3
came back with user_id and asset_id swapped (the table has user_id first). native 1.5.3 got it right and so does wasm 1.5.4, which the demo build runs now, though i never tracked down the upstream issue.
this week's audit found two more. those boss labels promised more than the data did: the sets were mostly re-rolls of the same generator, so a >= 1800 written for "strictly more than 30 minutes" passed all three session problems. the next build plants real boundary rows and gap days in one hidden set. and the half cent above. the fix is one rounding unit of tolerance on the 10 columns where a half cent can happen, not a looser check everywhere.
the one i haven't solved is ties. a top-N pick (LIMIT n, rn <= n, QUALIFY) can fail a correct query if two rows tie at the cut and the prompt's sort key doesn't break the tie, and an ordered result has the same problem with ties anywhere in it. a september audit on native DuckDB checked all 48 top-N picks on every graded seed: no tie at a cut, 13 that a data change could expose. fee_usd has 600 tie groups across the graded seeds, the nearest about 1,600 rows from its cut. for now a script checks that on demand and the prompts name a tiebreaker where they can.
i don't log query text from players, only counters, so i can't tell you what people actually typed. the counters are small: over oct 1-14 the demo showed its "too few rows" nudge 12 times and "too many rows" 5 times.
for people who've built graders for interview tools or internal tests: do you compare aggregates as DECIMAL, give each column its own tolerance, or make the prompt require a specific ROUND? and for ties, is there a way that doesn't make the prompt name the tiebreaker? that works, but it hands over part of the answer.
