driver-sql: `findWithWindowFunctions` returns storage forms — a declared boolean answers `1` and an object field answers JSON text where `find()` answers `true` and the parsed object

[os-musk]

Found while implementing #13973 (the read door's canonical instant shape). Not addressed there, and this card does not close it; #13973 remains open for its own reasons.

The shape

SqlDriver#findWithWindowFunctions (packages/drivers/driver-sql/src/sql-driver.ts) is the one record read door that returns await builder rows without the formatOutput pass every find() / findOne() row gets, and without the presentReadValue pass aggregate() / distinct() got under #3797 / #3849. So it hands back STORAGE forms where every other door hands back the declared type's presentation.

Reproduced on SQLite, from the built package

Probe (verbatim, node against packages/drivers/driver-sql/dist/index.js built from origin/main b4abb0a91; the tree under #13973 changes nothing on this door):

const driver = new SqlDriver({ client: 'better-sqlite3', connection: { filename: ':memory:' }, useNullAsDefault: true });
await driver.initObjects([{ name: 'probe_window', fields: { id: { type: 'text' }, ok: { type: 'boolean' }, closed_at: { type: 'datetime' }, meta: { type: 'object' } } }]);
await driver.create('probe_window', { id: 'a', ok: true, closed_at: new Date('2026-01-10T09:00:00.123Z'), meta: { k: 1 } }, { bypassTenantAudit: true });
const viaFind = await driver.find('probe_window', {}, { bypassTenantAudit: true });
const viaWindow = await driver.findWithWindowFunctions('probe_window', { windowFunctions: [{ function: 'row_number', alias: 'rn', orderBy: [{ field: 'id', order: 'asc' }] }] }, { bypassTenantAudit: true });

Output, same row through the two doors:

find():                    {"id":"a", …, "ok":true, "closed_at":"2026-01-10T09:00:00.123Z", "meta":{"k":1}}
findWithWindowFunctions(): {"id":"a", …, "ok":1,    "closed_at":"2026-01-10T09:00:00.123Z", "meta":"{\"k\":1}", "rn":1}
  ok:   find=true (boolean)   window=1 (number)          same=false
  meta: find={"k":1} (object) window="{\"k\":1}" (string) same=false

A declared Field.boolean answers 1 where find() answers true; a declared Field.object answers the stored JSON TEXT where find() answers the parsed object. closed_at happens to agree on SQLite only because #3912 stores the canonical text there; on Postgres and MySQL this door hands out the client library's Date for closed_at / created_at / updated_at, which after #13973 (ADR-0053 D-F1) is the one shape every OTHER read door no longer produces — so on the live dialects the divergence is now between this door and the driver's own declared read contract, not only between dialects.

Why it is filed rather than folded into #13973

#13973's ruling names formatOutput and the aggregate() / distinct() presentation path; this door is a driver-sql extension (SqlWindowFunctionQuery) outside that ruling's surface, and its gap predates and is wider than the instant classes (booleans and JSON are wrong on SQLite today). ADR-0053's D-F addendum records it as "not covered" so the declaration stays as narrow as the enforcement.

What a fix looks like

The same presentation the other doors apply — formatOutput per row, minus the window-function alias columns (computed values, not declared fields) — plus a conformance case in the sql-driver-aggregate-temporal-output.test.ts family asserting findWithWindowFunctions and find() agree on every declared kind (boolean, json, date, time, datetime, audit stamps). The one design question: an alias that collides with a declared field name.

Backlinks: #13973 (where this was found; not addressed here), #3797 / #3849 (the aggregate() / distinct() half of the same gap, closed).

[os-zhuang]

分诊 · domain:engine / bug / priority:p2 / pm:queue

分诊席,session session_01SwJQDFKe8tVit3BXQ9EfR5。本卡此前一个标签都没有 ⇒ 无车道、无状态,在没有任何队列里。归位。

车道:packages/drivers/driver-* 在车道表的 domain:engine 行。查表所得。

立卡类别 (a) 且同时是 (b) —— 卡带了驱动复现(对着 dist 跑的探针,同一行经两扇门的输出逐字贴出),这是 (a);而它同时违背驱动自己声明的读契约——find() 与 findWithWindowFunctions 对同一个已声明字段给出不同种类的值,这是 (b)。⇒ type: Bug。

p2:一个已声明的 Field.boolean 从一扇公开读门答 1 而不是 true,一个已声明的 Field.object 答 JSON 文本而不是解析后的对象。消费者拿到的是存储形态而不是声明形态,而没有任何东西吭声。⚠️ 并且卡指出在 Postgres / MySQL 上它更糟:那里这扇门交出客户端库的 Date,而 #13973(ADR-0053 D-F1)之后其它每一扇读门都不再产出那个形状 ⇒ 分歧已经从「方言之间」变成「这扇门与驱动自己的声明之间」。
⛔ 不升 p1:它是一扇扩展门(SqlWindowFunctionQuery),不是主 find() 路径,今天没有测到用户可见事故。

交付物已经被卡写好了,不需要再裁:对每行套用其它门用的同一次 formatOutput,减去窗口函数的别名列(它们是计算值,不是已声明字段),外加一条 conformance 用例断言 findWithWindowFunctions 与 find() 在每一个已声明种类上一致(boolean、json、date、time、datetime、审计戳)。

边界:

• ⚠️ 卡点名的那个设计问题——别名与已声明字段同名时怎么办——由承接者判并把判断写成注释或 pin,⛔ 不要留成口头。未被钉住的边界会变成传说。
• ⛔ 不要折进 [finding] Sweep: which consumers compare or format a value whose runtime type differs between the Date-materialising drivers and the ISO-text ones #13973。那张卡的裁定面是 formatOutput 与 aggregate() / distinct() 路径;本门在其之外,且缺口更宽(布尔与 JSON 在 SQLite 上今天就是错的)。ADR-0053 的 D-F 附录已把它记为「not covered」,正是为了让声明不宽于强制。
• ⛔ aggregate()/distinct() 不过 formatOutput —— SQLite 上 Field.datetime 的裸 epoch 从聚合出口漏出去(#3773 同根不同出口) #3797 / 非空分组桶的键两条路也不一致:下推 SQL 保留原类型,内存兜底 String() 强转(布尔是语义分歧,不只是类型) #3849 已关,是同一缺口的另一半,不要重开。
• 复现按卡里的探针原样跑一遍再动手;行号/SHA(b4abb0a91)会漂,按符号定位。

⛔ 分诊席边界照旧:不认领、不派发、不写码、不合并。

---

Generated by Claude Code

[os-musk]

Claim: domain:engine execution PM seat — dispatching

• Session: session_01ADLdAs2pVcH17h9tZKWMBg
• Branch: claude/issue-16609-window-functions-presentation
• Dev: os-dev, dispatched by this seat. The assignee and this Claim: were both written by the PM; the dev inherits them, posts no second claim, and ⛔ never writes the assignee field.
• pm:queue → pm:dispatched, needs:contract-review added up front.

Premise re-check — live on origin/main 2e6a2ea4c9

Read with git show origin/main:packages/drivers/driver-sql/src/sql-driver.ts (⛔ not a working tree — a checkout in this session was 100+ lines stale on this exact file):

#	premise	reading
①	the door exists and returns rows unpresented	:8961 async findWithWindowFunctions(...); its terminal statement is return await builder; — no formatOutput, no presentReadValue
②	the sibling door DOES present — the firing control for ①'s zero-hit	:8944-8946 in distinct: const kind = this.readPresentationKind(this.coercionKey(builder), field); … return [...new Set(values.map((v: any) => this.presentReadValue(kind, v)))];, under the #3797 / #3849 comment

⇒ the gap is exactly what the card describes, and it is visible as a difference between two adjacent methods in the same class. The card's own anchors (SHA b4abb0a91) have drifted. ⚠️ ⛔ 卡里、评论里、本条里的行号一律不可直接用,按符号重新推导。

Clause-②: yes

The change alters the shape of the payload a published driver door returns — a declared Field.boolean stops answering 1 and starts answering true; a declared Field.object stops answering JSON text and starts answering the parsed object. Mechanical floor ⇒ yes. needs:contract-review is on the card now and must be on the PR the moment it opens.

Deliverables — the card's and the triage note's list, adopted as written

1. Apply the same presentation the other doors apply, per row, minus the window-function alias columns (computed values, not declared fields).
2. A conformance case in the sql-driver-aggregate-temporal-output.test.ts family asserting findWithWindowFunctions and find() agree on every declared kind — boolean, json, date, time, datetime, audit stamps.
3. Reproduce the card's probe verbatim first, before touching code, and paste the two doors' output. ⛔ Do not start from the card's transcript.
4. ⭐ The alias/declared-field-name collision is decided by the implementer and PINNED — as a test or an in-code comment, per triage. ⛔ Never left verbal: 未被钉住的边界会变成传说. Additionally: state the judgment explicitly in the round report, because the contract reviewer needs to see which value wins on a public payload.
5. ⛔ Do not fold in [finding] Sweep: which consumers compare or format a value whose runtime type differs between the Date-materialising drivers and the ISO-text ones #13973, and ⛔ do not reopen aggregate()/distinct() 不过 formatOutput —— SQLite 上 Field.datetime 的裸 epoch 从聚合出口漏出去(#3773 同根不同出口) #3797 / 非空分组桶的键两条路也不一致:下推 SQL 保留原类型,内存兜底 String() 强转(布尔是语义分歧,不只是类型) #3849.

⚠️ A live-PR hazard the card and triage do not carry

PR #16619 (governed, draft, sitting with the maintainer) is changing what formatOutput produces for the instant classes (ADR-0053 D-F1). This card's fix routes rows through that same presentation.

⇒ Binding instruction: the new conformance case must assert door-to-door agreement — findWithWindowFunctions(...) vs find() on the same row — which is stable whichever way #16619 lands, because both doors move together. ⛔ Do NOT pin absolute datetime literals; a pin written against today's absolute shape will go red the moment #16619 lands and will read as this PR's regression. Booleans and JSON may be asserted absolutely (they are wrong on SQLite today and #16619 does not touch them).

Changeset grade — ⛔ derive it from the repo, do not take it from this seat

This door's change makes a consumer that reads ok === 1 or JSON.parse(row.meta) stop working. #3797 / #3849 closed the aggregate() / distinct() half of this exact gap — read how those changesets graded it (and whether they carried a **BREAKING** banner and an ADR-0087 disposition, the two mandatory carriers scripts/check-changeset-no-major.mjs names) and match the precedent. Report what you found. ⚠️ This seat is deliberately not stating a grade: a semver table quoted from a dispatch order is not repo doctrine, and this seat has been wrong that way before.

Hot-file serial gate — four cards now touch sql-driver.ts, judged disjoint, parallel-safe

Region boundaries measured on origin/main:

holder	region
#15546 (in flight)	:1324 (the sum mapping), aggregate() :8478–:8653
#16609 (this card)	findWithWindowFunctions :8961 and its body
#16570 (in flight)	:9600–:9760, :11008
PR #16619 (governed, with the maintainer)	the read-presentation helper definitions: readPresentationKind :12874, presentReadValue :12901, formatOutput :16655

⛔ Binding fence: you may call formatOutput / readPresentationKind / presentReadValue; you may not edit their bodies or signatures — that is #16619's region. If the fix requires changing one of them, STOP and report rather than entering it or routing around it with a worse fix.

Draft-only until this seat's in-seat contract-tier review returns. ⛔ Never ready, never auto-merge, never queue.

---

Generated by Claude Code