The Query That Makes Tom Foley Governor

Here is the most obvious query in election data: total the votes, group them by party, sort descending. It is the first thing anyone writes against a returns table, and in most of the country it is correct. Run it against Connecticut's certified 2010 gubernatorial results and it makes Tom Foley governor of Connecticut.

Foley was not governor of Connecticut. Dan Malloy was. Malloy finished with 567,278 votes and 49.51 percent to Foley's 560,874, a margin of 6,404. The query never sees 567,278, because that number is not in the file. What is in the file is 540,970 votes for Malloy on the Democratic line and 26,308 votes for Malloy on the Working Families line, sitting on two separate rows.

Nothing goes wrong on the way to the wrong answer. Every row is read once. Every vote is added once. No constraint is violated, no null appears, no type is coerced, no sum comes out short. The arithmetic is flawless; the grouping key is wrong. That combination is the most dangerous shape an error can take in this discipline, because a mistake that is not arithmetic will never be caught by arithmetic.

The query that makes Foley governor

Read down that column and the story looks unambiguous. The Republican line leads the Democratic line by 19,904 votes. That is a real number and a true one: Foley's Republican line genuinely outpolled Malloy's Democratic line by nearly twenty thousand votes. It simply is not the election result, because the office is not awarded to a line.

Malloy's Democratic line finished 19,904 votes behind Foley's Republican line. Malloy won the election by 6,404. Both facts come out of the same certified file.

Party is a property of the ballot line, not of the result

In most states a returns row can be described as contest, jurisdiction, candidate, votes, with party carried along as a harmless label. That works because party and candidate are one-to-one inside a contest: one candidate, one line, so the label is a fact about the candidate. Everyone denormalizes on that assumption, usually without noticing they made one. Fusion voting breaks the assumption, and it is currently in force in New York and Connecticut.

Under fusion, more than one party may nominate the same candidate, and that candidate appears on more than one line of the same ballot. Votes cast on each line are certified separately and summed for the candidate. So the true grain of the row is contest, jurisdiction, candidate, ballot line. Party hangs off the line. The candidate hangs off the row. A candidate total is a rollup across lines, and a margin is a quantity that does not exist until the rollup has happened.

The failure is symmetric, which is worth saying plainly: any candidate who appears on more than one line is undercounted by the same query, whatever letter follows the name. The bug has no politics. It has a bad key.

That contrast is the second trap. A pipeline tested against New York 2022 passes: even at the line level, Hochul's Democratic line is on top, and the missing 261,323 votes only move the share. The same pipeline, pointed at Connecticut 2010, reports the loser as the winner. The bug is invisible in the comfortable race and decisive in the close one, which is precisely backwards from the order in which anyone checks their work.

The line is not a synonym for the candidate

The tempting patch is a crosswalk: hardcode each minor line to the major-party candidate it usually cross-endorses, add the votes, move on. It is fast, it is one dictionary, and it is wrong in exactly the cases that matter.

New York's 17th district in 2024 is the counterexample. The Working Families line there carried Anthony Frascone with 7,530 votes, not Mondaire Jones. A crosswalk keyed on the party's name and habits would have shifted 7,530 votes onto a candidate who did not receive them, and, as always in this family of errors, raised nothing while doing it. The only safe join key is the candidate certified on that specific line in that specific contest.

What we check, because nothing throws

Since no exception will ever be raised, the guard has to be written by hand and run every load. Ours are deliberately dull:

• Keep the line-level rows. Party is never collapsed into the candidate at load time. A rollup you cannot re-derive from what was certified is a claim, not a record.
• Flag every contest in which one candidate appears on more than one line, and refuse to compute any share, margin, or winner before the rollup has run.
• Join minor lines by the candidate named on that line in that contest as certified, never by which party normally endorses whom.
• Treat the party's vote and the party's candidate's vote as two different columns, because in Connecticut in 2010 they were 26,308 votes apart and the difference was the governorship.

Connecticut has a habit of breaking keys. The same state also retired all eight of its counties as statistical county-equivalents in a Federal Register notice dated 2022-06-06, replacing them with nine planning regions, which is why the national county-equivalent count moved from 3,143 to 3,144. Those counties had stopped functioning as governments back in 1960; the paperwork simply caught up. The regions do not nest inside the old counties either, so there is no crosswalk to hide behind: Shelton moved out of Fairfield County into the Naugatuck Valley Planning Region, and Litchfield County has no single successor at all. The 169 towns, notably, never moved.

One state, two broken assumptions: the key you use for place and the key you use for party. Both fail quietly, both produce plausible tables, and both are fixed the same way. Store the thing that was actually certified, roll it up in public, and let the reader check the seam. The certified Connecticut file was never wrong about who won. The query was.