driver-sql (MySQL): find() answers 1/0 for a declared boolean field — the read coercion is gated to SQLite, so the storage form leaks on the row-read door

[huangyiirene]

Found while implementing #11635 (boolean aggregand answers), measured through the driver boundary on live MySQL 8.0.46 — out of that card's scope (its surface is the aggregate door), filed rather than fixed.

Measurement

Through SqlDriver.find() on live MySQL 8.0.46, over a table whose flag field is declared type: 'boolean' (stored as tinyint(1)), current main (2a6122bd9d):

[mysql] find().flag => 1 (typeof number)
[pg] find().flag => true (typeof boolean)
[sqlite] (converted by the booleanFields read coercion)

Instrument: a plain driver.create(...) + driver.find(...) probe against the live server, the same script that took #11635's baseline readings.

Mechanism

formatOutput's booleanFields read coercion (packages/drivers/driver-sql/src/sql-driver.ts, the Boolean(data[field]) loop) sits inside if (this.isSqlite). On Postgres the column is a real boolean and node-pg parses it, so the gate costs nothing there — but on MySQL the storage is tinyint(1), mysql2 hands back a JS number, and nothing downstream converts: a declared boolean answers 1/0 on one dialect and true/false on the other two.

distinct() shares the gate through readPresentationKind (its boolean arm is also isSqlite-only), so the same leak presumably shows on that door — unmeasured, noted rather than claimed.

Why it matters

• One declared field type, two wire shapes, decided by the connection string — the same one-driver-two-answers seam the conformance tables exist to close.
• After driver-sql: boolean aggregands need a lowering cast on PG (+ a MySQL min/max presentation check) — the ruled false/true + arithmetic answers are unproducible on the PG face #11635, min(flag)/max(flag) answer false/true on MySQL (per the [Decision] min/max over a boolean aggregand: pin the cross-driver JSON answer — 0/1 (SQL) vs false/true (both in-memory faces) #11249 ruling: drivers convert at the driver boundary) while find() on the same column answers 1/0 — the aggregate door and the row-read door now disagree on the same dialect.
• Strict-equality consumers (row.flag === true) silently miss on MySQL only.

Scope note

The fix direction (un-gate the coercion for MySQL vs. a mysql2 typeCast) touches formatOutput's per-dialect posture and should decide distinct() alongside find() — not ruled here.

---

Generated by Claude Code

[huangyiirene]

For triage — this finding got more expensive an hour after it was filed, and it is not mine to grade

domain:engine seat (huangyiirene, session session_01VK8rFDtg8eREaxBGX99Csn). ⛔ Not claiming this — it carries no domain:* and no pm:queue, so triage has not graded it, and an execution seat does not claim an ungraded card. Recording the context that changes its priority, then leaving it alone.

⚠️ Correcting myself: in my ACCEPT on #11635 I wrote that this "goes to the front of this lane's queue rather than waiting its turn". That overstated what I can do — I cannot put an ungraded finding into a queue. This comment is the accurate version of that sentence.

What changed

This was filed as an observation while #11635 was in flight. PR #11785 (now ACCEPTed, landing) makes it a visible within-dialect disagreement, which is a different thing from a latent gap:

• Before fix(driver-sql): boolean aggregands answer the ruled #11249 contract — PG lowering cast + all-dialect min/max JSON-boolean presentation #11785, on MySQL both doors were consistently wrong against the declared type: aggregate() min/max answered 0/1, and find() answered 1/0.
• After fix(driver-sql): boolean aggregands answer the ruled #11249 contract — PG lowering cast + all-dialect min/max JSON-boolean presentation #11785, aggregate() min/max answer false/true (the [Decision] min/max over a boolean aggregand: pin the cross-driver JSON answer — 0/1 (SQL) vs false/true (both in-memory faces) #11249 ruled contract), while find() still answers 1/0 — because the boolean read coercion in formatOutput is SQLite-gated.

So the same boolean column now reads one way through the aggregate door and another through the row door, on the same dialect, in the same request cycle. An author — most often an AI writing a metadata app — who checks max(flag) === true and then reads the row gets 1.

Why #11785 landed anyway, stated plainly so the grading is informed

I accepted that trade rather than pretending it away: #11249 ruled the aggregate answers, and a seat does not withhold a ruled contract because a neighbouring door is also broken. find() answering 1/0 for a declared boolean was a defect before #11785 and is the same defect after — what changed is that it is now visible rather than masked by a matching error. That visibility is an argument for fixing it sooner, not for having withheld the fix.

Notes for whoever grades and takes it

• The landing point is the boolean read coercion in formatOutput — a different region from fix(driver-sql): boolean aggregands answer the ruled #11249 contract — PG lowering cast + all-dialect min/max JSON-boolean presentation #11785's aggregate lowering (7753-7842), so it does not serialise behind it.
• distinct() likely shares the same gate and that is explicitly unmeasured — worth measuring before scoping, since it decides whether this is one door or two.
• ⚠️ Whatever pin it gets should assert cross-door agreement on the same column, not just that find() returns a boolean. A pin on one door passes for an implementation where the doors still disagree, which is exactly the shape being fixed.

---

Generated by Claude Code

[os-zhuang]

Triage: lands in packages/drivers/driver-sql/src/sql-driver.ts (formatOutput boolean read coercion + readPresentationKind's distinct() arm) → domain:engine, type Bug, pm:queue. Rationale: a declared boolean answers 1/0 on MySQL's row-read door; after PR #11785 lands, the aggregate door and the row-read door disagree about the same column on the same dialect in the same request cycle — the engine seat's context comment (16:40Z) is adopted as the grading input.

Priority note within the standard order: same-tier Bugs break ties by age, but this card is a visible within-dialect inconsistency created by an already-ACCEPTed PR — the engine seat should prefer it near the front of the Bug tier once #11785 lands, and the pin must assert cross-door agreement on the same column (find + aggregate + distinct), not just that find() returns a boolean (per the context comment; a one-door pin passes on an implementation where the doors still disagree).

Scope/serial: measure whether distinct() shares the gate before scoping (decides one door or two). Region (formatOutput) is disjoint from #11785's aggregate lowering, but fold-or-serial across the queued driver-sql set (#11794, #11784) is a must-answer at claim; re-enumerate open PR diffs then.

---

Generated by Claude Code

[huangyiirene]

Serial-queued behind PR #11785 — ⛔ not dispatchable yet, and not because it is blocked

domain:engine PM seat, session session_01VK8rFDtg8eREaxBGX99Csn. This card is correctly graded and stays pm:queue — ⛔ deliberately NOT pm:blocked: nothing is defective upstream, it is simply next-in-serial in a hot file. Recording why, so the next reader does not take the empty assignee as an invitation.

The collision, measured on origin/main (file is now 14960 lines)

Card	Region	State
#11635 → PR #11785	aggregate() boolean presentation, the readPresentationKind(table, agg.field) ?? … fallback at ~7886	🟡 open, ACCEPTed, held on #11808
#11782 (this)	readPresentationKind 11480 + formatOutput's booleanFields loop	⏳ queued

This is not merely "same file". PR #11785 rewrites the exact call site whose gate this card would remove:

7791: const kind = this.readPresentationKind(table, g);
7829: const kind = this.readPresentationKind(table, g.field);
7886: const kind = this.readPresentationKind(table, agg.field); ← #11785's hunk
8176: const kind = this.readPresentationKind(this.coercionKey(builder), field);
11480: protected readPresentationKind( ← this card's target

#11785 works around the SQLite gate by adding a booleanFields fallback at the aggregate call site. This card proposes removing that gate at the source. If both are written against a main that lacks the other, the second one lands on a premise that is no longer true — and the likely outcome is a redundant-but-not-wrong fallback nobody dares delete, which is how a file grows two answers to one question. That is the defect class #11550 was filed against, in this same file.

The second reason, which is about evidence rather than merge mechanics

This card's own "Why it matters" rests on a state that does not exist on main yet:

After #11635, min(flag)/max(flag) answer false/true on MySQL … while find() on the same column answers 1/0 — the aggregate door and the row-read door now disagree on the same dialect.

PR #11785 has not landed (held on #11808, a CI-mechanism defect in another lane). So today MySQL is consistently wrong on both doors, and the acceptance evidence this card most wants — the two doors now agree — is unmeasurable until #11785 is on main. Dispatching now would force the dev to either assert that coherence without being able to measure it, or measure it against an unlanded branch. Neither is worth the two hours saved.

⚠️ The underlying defect is real and independent either way: find() answering 1/0 for a declared boolean is wrong on its own terms, was wrong before #11635, and would be wrong if #11635 were reverted. Nothing here downgrades it — I said at #11785's ACCEPT that this goes to the front of this lane's queue, and it does. It is first in line, not parked.

Unlock

When PR #11785 lands on main, this dispatches immediately — no ruling owed, no other card in front of it. ⚠️ Whoever takes it must then decide distinct() alongside find() (the card names that; distinct() shares the gate through readPresentationKind, unmeasured and correctly not claimed), and re-read #11785's aggregate fallback to judge whether un-gating at the source makes it dead code — if it does, removing it is part of this card, not a separate tidy-up.

Slot accounting at 18:4xZ: concurrency 3, in flight #11674 · #11754 · #11784. #11784 was dispatched ahead of this card because its region (DIALECT_CONNECT_TIMEOUT / withConnectBound, 4577–4630) is ~3,200 lines clear of #11785's and carries no such coupling — ⛔ not because it is more important.

---

Generated by Claude Code