Repository navigation
Result digests match across all three systems #20
Description
Every query produces a digest of its canonicalised result and the digests are compared. A row that does not match is marked incorrect and excluded from every aggregate.
The motivating example from the fair benchmarking literature is a prototype that was faster because it used hardcoded group counts and types too small to hold the aggregate. It was, genuinely, faster. A digest catches that class of thing without anyone having to suspect it first.
Done when digests agree across all three drivers on all 43 queries, and every disagreement is resolved rather than tolerated.
Activity
tamnd commentedon Sep 6, 2026
The workload has now been run end to end against the real corpus, all three drivers, on the class B Intel Core i9-13900K workstation. Both engines answered all forty three, which is the first time that has happened, and the reference reader recorded forty three unsupported rows as it is built to. Thirty three of the forty three agree. Ten do not.
Every one of the ten returns exactly ten rows on both sides, and every one of them has a LIMIT sitting on top of an ordering that does not determine which rows fall inside it. That is not a guess about the cause, because eight of the ten are also cases where an engine disagreed with itself across its own three runs of the same query on the same file.
q17, q24, q31, q32, q38, q39, q40 and q41 are the eight. DuckDB gave three different digests for six of them and DataFusion gave three different digests for three of them, on runs that differ in nothing but which run they were. A query that answers itself differently twice in a row cannot be said to disagree with another engine, because it does not agree with itself, and the comparison already reports that separately rather than folding it into the disagreement.
Their shapes are the two that were expected. q17 is GROUP BY UserID, SearchPhrase LIMIT 10 with no ORDER BY on it at all, so ten groups out of many millions are picked by whatever the plan happened to produce. q31 and q32 group by WatchID and ClientIP and order by COUNT(*) descending, where nearly every group has a count of one, so the top ten is a choice among equals. q38 through q41 are worse than that: they order by a count and then take LIMIT 10 OFFSET 1000, OFFSET 100 and OFFSET 10000, which lands the window deep in the tail where every remaining group has the same tiny count. q24 orders by EventTime, which is a second and not a row identity.
The other two, q21 and q23, are stable within each engine and still differ between them, and stability is worth being careful about here. A single engine's plan is deterministic for fixed data, so it will break a tie the same way every time and look steady while still having made an arbitrary choice. Instability proves a tie. Steadiness does not disprove one.
q23 is the one I expect to be different in kind. It is SELECT * FROM hits WHERE URL LIKE '%google%' ORDER BY EventTime LIMIT 10, and SELECT * means the answer carries EventTime itself. The two published setups do not convert that column the same way. DuckDB's load turns it into a timestamp before any query sees it and DataFusion's create.sql leaves it as raw seconds. So the two engines can return the same ten rows and still render one column as a timestamp and as an integer, and no digest over the rendered rows can match. If that is what is happening it is a direct consequence of the fairness decision in #99 to keep each entry's own setup, it is not a fault in either engine, and it needs writing down rather than fixing.
I am not going to close this on that reasoning, because it is still reasoning. The digests were kept and the answers were thrown away, which is why I am reduced to inferring what a difference is from the shape of the SQL. A benchmark that can tell you two systems differ but never what about is missing the tool you want at exactly the moment you want it, so the next change adds
clickbench answer, which takes the table in the same way a run does, runs only the queries you name, and prints the rows the digests were taken over. It records no timings and needs no gate.Then each of the ten gets a verdict backed by looking at it, and the exit condition here says resolved rather than tolerated, so a verdict of the query has no single answer will have to come with the rule that says which queries are allowed to claim that and the evidence for each one that did.
tamnd commentedon Sep 6, 2026
Resolved by #104.
The three drivers have now been run end to end on the real corpus and their records compared. Thirty three of the forty three agree between DuckDB and DataFusion, the reference reader answers none of them so it contributes nothing to agreement, and the other ten are resolved rather than tolerated.
The resolution is that those ten do not have one answer to agree about. Nine are ties under a LIMIT, where the ordering key does not tell the rows inside the window apart from the rows just outside it, so two correct systems return a different arbitrary ten. The tenth is q23, which is SELECT * over two setups that do not build the same table out of the same corpus.
Every one of the ten was run on both engines and the rows were read before it went on the list. For the six grouped ones the sequence of ordering key values is identical between the two engines and tied across the whole window, so both computed the same aggregate and cut it differently. For q23 the two returned the same ten rows with a hundred and two of the hundred and five columns matching exactly, differing only in where EventDate sits and in whether three timestamps are written as instants or as raw seconds.
The list lives in
bench_workload::clickbench::undeterminedwith the reason attached per query, a test pins it to exactly these ten so it cannot grow quietly,iris-bench clickbench answerreproduces the evidence for any of them, and everything not on the list is still a hard failure that stops the timings being published.