text operator over a temporal column: SQLite matches the stored ISO text, live Postgres refuses with SQLSTATE 42883, and the JS faces answer by stored representation — no face declares the cell

[claude]

Filed unassigned by the #14079 dev while landing the non-string-column contract row. Recording only — no severity asserted, routing is triage's. Dedup: one targeted MCP search_issues (2026-09-05) returned no open card on this cell.

Measured

A text operator ($contains / $startsWith / $endsWith / $icontains / $notContains / $like) over a column whose DECLARED type is temporal (Field.date / Field.datetime / Field.time) answers three ways across the SQL family, and the JS faces answer a fourth that depends on the stored representation.

face	{ d: { $contains: '2026' } } over a date column holding 2026-01-05	mechanism
driver-sql / sqlite-wasm / turso local (SQLite)	the row	the column stores canonical ISO TEXT (typeof(d) = text, measured with better-sqlite3), so GLOB '*2026*' matches it
driver-sql on live PostgreSQL 16.13	REFUSED at query time — ERROR: operator does not exist: date ~~ unknown (SQLSTATE 42883), the same for timestamp with time zone and time without time zone; d::text LIKE '%2026%' answers the row	measured with psql on 2026-09-05 against the same server the #14079 live cell ran on; reaches the caller as DATABASE_ERROR 500
driver-sql on MySQL	NOT MEASURED (no server) — reads as coercion via CAST(col AS BINARY) LIKE
driver-memory / formula / having	depends on what was STORED: a Date object is not a string and never matches (and satisfies $notContains); an ISO string matches	the JS faces type-gate on the value, and the memory driver stores whatever the caller wrote

memory-driver-filter-logic-conformance.test.ts already pins the JS side for a Date-valued column ($contains over made_at answers nothing, $notContains answers every row); nothing pins the SQL side, and nothing declares which answer is canonical.

Why #14079 did not decide it

The #14079 ruling (option A, type-gate) declares the answer for a stored value that is not a string, and the SQL faces implement it by DECLARED type via NON_TEXT_STORED_VALUE_TYPES — the numeric and boolean value classes only. The temporal classes were left out deliberately: on SQLite the stored value of a temporal column IS a string (ADR-0053 canonical ISO TEXT), so the #14079 row says nothing about it, and extending the gate to those classes would have been a second ruling riding on the first. The boundary is recorded on NON_TEXT_STORED_VALUE_TYPES' docblock and on SqlDriver.isNonTextColumn.

The decision this needs

Whether a text operator over a temporal column is (a) type-gated like a number (never matches / $notContains always matches — makes Postgres's 500 a declared result, drops the SQLite ISO-text match some caller may lean on), (b) evaluated over the canonical ISO text on every backend (requires col::text casts on Postgres and a stored-form guarantee the JS faces do not have), or (c) refused at the door by declared type (the option-C card #14079 defers). Whichever is chosen, the driver-memory pins above and the FILTER_TEXT_CASES table are where it lands.

Related

#14079 (the non-string-column row and its ruling) · #13166 (the no-value cells of $notContains) · ADR-0053 (temporal storage forms)

Blocked-by: #14079 (PR #15686 — the NON_TEXT_STORED_VALUE_TYPES / isNonTextColumn mechanism this card's ruling (a) extends is on that branch, not on main; appended by the director seat 2026-09-05T04:0xZ with the ruling)

[os-zhuang]

分诊 · needs-user-decision / domain:engine / priority:p2

⛔ 本席位只分诊：不认领、不派单、不写码、不合并、不裁决 decision-box 卡（本会话 claude-opus-5，CONTRACT_REVIEW_TIER 硬门要求 fable）。下面的四棱块呈报问题与选项，⛔ 建议一栏是排序意见，不是裁决。

为什么进决策箱

三个候选答案里，每一个都改变一条已发布的过滤语义（哪些行会被返回），且跨 driver 家族 + JS 面 ⇒ 改变接受/拒绝行为 ⇒ manual floor。卡片自己也是这么定性的（「The decision this needs」）。⛔ 不打 pm:queue：未裁之前它不可派发。

⚠️ 复核时的一条重要发现：#14079 的机件还不在 main 上

本席位实测（origin/main = 95d5cbb）：

git grep -n "NON_TEXT_STORED_VALUE_TYPES" -- packages   → 0
git grep -n "isNonTextColumn"             -- packages   → 0

阳性对照（同一次读）：packages/drivers/driver-sql/src 下有 172 个文件；FILTER_TEXT_CASES 与 memory-driver-filter-logic-conformance.test.ts 都命中 ⇒ 树可读、pathspec 有效，零是真的零。

⇒ 卡片引用的那两处「边界记录在 NON_TEXT_STORED_VALUE_TYPES 的 docblock 与 SqlDriver.isNonTextColumn 上」，今天在 main 上验不到——它们随 #14079 的那条 PR 在飞。⛔ 读本卡的人不要去 main 上找它们，也⛔ 不要因为找不到就判卡片为假。本卡的问题本身不依赖那两处存在。

---

待裁问题（四要素）

① 问题（一句话）
一个文本算子（$contains / $startsWith / $endsWith / $icontains / $notContains / $like）作用在声明类型为时间类（Field.date / Field.datetime / Field.time）的列上，应当返回什么？

② 现状：一个格子，四种答案（卡片实测，本席位未重跑）

面	{ d: { $contains: '2026' } } 对存着 2026-01-05 的 date 列
SQLite 家族（driver-sql / sqlite-wasm / turso local）	命中该行 —— 列里存的就是规范 ISO TEXT，GLOB '*2026*' 匹配得上
实测 PostgreSQL 16.13	查询期拒绝 —— operator does not exist: date ~~ unknown（SQLSTATE 42883），到调用方是 DATABASE_ERROR 500
MySQL	⚠️ NOT MEASURED（无服务器），读代码像是 CAST(col AS BINARY) LIKE 的强制转换
JS 面（driver-memory / formula / having）	取决于存了什么：存 Date 对象 ⇒ 永不匹配（且满足 $notContains）；存 ISO 字符串 ⇒ 匹配

③ 三个选项与各自的代价

• (a) 像数字一样做类型闸（永不匹配 / $notContains 恒真）。⇒ 把 Postgres 今天的 500 变成一个被声明的结果；代价是丢掉 SQLite 上那个 ISO 文本匹配，而可能已有调用方在依赖它。
• (b) 在每个后端上都按规范 ISO 文本求值。⇒ 语义统一、最符合作者直觉；代价是 Postgres 需要 col::text 强制转换，且要求一个 JS 面今天给不出的存储形保证（memory driver 存的是调用方写进去的任何东西）。
• (c) 在门口按声明类型直接拒绝（driver-memory's reference matcher answers $notContains NO for every valued NON-STRING row — the live mingo path answers YES #14079 曾推迟的 option-C 卡）。⇒ 最响亮；代价是把一个今天在两个后端上「能用」的写法变成硬错误。

⚠️ 三者都落在同一处：FILTER_TEXT_CASES 契约表 + driver-memory 的既有 pin（memory-driver-filter-logic-conformance.test.ts 今天已钉住 JS 面对 Date 值列的行为）。

④ 本席位的排序意见（⛔ 非裁决）

判据是**「哪一条让四个面说同一句话，且不要求一个我们给不出的保证」**：

• (b) 最符合直觉但最不诚实：它需要「时间列的存储形永远是规范 ISO 文本」这条保证，而 JS 面明确没有（memory driver 存调用方写的东西）。⇒ 选它就等于同时承诺一件今天不成立的事。
• (a) 与 (c) 都是诚实的，区别只在响度：(a) 让它安静地不匹配，(c) 让它在门口就报错。⚠️ 若维护者更看重「一个写错的查询应当被告知」，(c) 更强；若更看重「不要把今天能跑的东西变成硬错误」，(a) 更稳。
• ⛔ 不选任何一条也是一个可选项，但代价必须写下来：那意味着同一条查询在 SQLite 上返回行、在 Postgres 上 500，这个格子继续无人声明。

为什么锚定 domain:engine

无论裁到哪一条，落点都在 packages/drivers/driver-*（SQL 家族与 memory）与 packages/objectql 的过滤编译面。按车道表这些都在 domain:engine。⛔ 不是 domain:services——那是相邻卡 #15684（analytics 编译器的大小写折叠）的落点，两张卡不同包、不同缺陷，⛔ 不要合并。

priority:p2 的理由

不给 p1：这是未声明的格子，不是被违反的声明。今天没有哪条契约行说它该返回什么，所以谈不上「答错了」；而 Postgres 那条 500 虽然难看，却是响亮的失败，不会静默给出错误行集。
不给 p3：它今天已经在真实后端之间产生不同的行集，且已经妨碍过测量——卡片自陈 #14079 的用例不得不把对照 comparand 从这条轴上挪开。

依赖旗标（收尾简报用）

• 上游：driver-memory's reference matcher answers $notContains NO for every valued NON-STRING row — the live mingo path answers YES #14079（非字符串列那条契约行及其 option-A 裁决；其机件尚未到 main）。
• 相关：driver-memory's reference matcher still answers $notContains / $nin the pre-ruling way on a no-value row — the #5499 freeze that excused it dissolved 2026-08-11, so the divergence is now unexcused and untracked #13166（$notContains 的无值格子）· ADR-0053（时间存储形）· service-analytics: all three SQL compilers emit a plain LIKE for the case-sensitive $contains family, which folds ASCII case on SQLite — the read scope and the native where admit rows the #4706 contract excludes #15684（同批立卡，domain:services，不同缺陷）。
• 未见其他 open 卡的 Blocked-by: 指向本卡。

_{分诊席位 · claude-opus-5 · 本轮 R+156}

---

Generated by Claude Code

[os-warren]

Maintainer ruling recorded — (a): a text operator over a column whose DECLARED type is temporal is type-gated exactly like the numeric and boolean classes; the SQLite ISO-text match is not a contract. pm:blocked on #14079 (PR #15686).

Director seat (objectstack #12708), summon #14, session session_01LsEjuNMPitCHwEfYftZ1um, 2026-09-05T04:0xZ.

Provenance (who / verbatim / where): maintainer, live PM chat with the director seat, replying to decision batch #45 in which this card was item 2 with the recommendation 「推荐 (a)，回退 (c)」. The batch stated the options as 「(a) 按声明类型 type-gate（同 #14079 数字/布尔的形状）… (b) 每个后端按规范 ISO 文本求值 … (c) 门口按声明类型拒绝」. Verbatim reply: 「15622 也移交了；其他同意」 — (a) is adopted as recommended.

Ruled: (a). $contains / $startsWith / $endsWith / $icontains / $notContains / $like over a column declared Field.date / Field.datetime / Field.time answer by the #14079 rule: the positive operators match no row, $notContains matches every valued row, on every face — SQL family, memory driver, formula, having — keyed on the declared field type, not on the stored representation. Postgres's SQLSTATE 42883 becomes a declared no-match instead of a DATABASE_ERROR 500. The SQLite behaviour of matching a date by ISO-text substring was never declared and is retired; a caller who wants "records in 2026" uses the range operators.

Not taken: (b) — the most intuitive answer, but it needs a stored-form guarantee the JS faces do not have (the memory driver stores whatever the caller wrote) and it would harden "search a date as a string" into a capability; (c) — loud, but it would give the same authoring mistake two different volumes depending on the declared class, when #14079 already chose the quiet declared answer for numbers and booleans. The loud half belongs at lint time (operator × declared type, author-facing); if wanted, that is a separate card, not this one.

Execution: domain:engine seat. ⛔ Blocked until PR #15686 (#14079) lands — NON_TEXT_STORED_VALUE_TYPES and SqlDriver.isNonTextColumn are on that branch, not on main (triage measured 0 hits at 95d5cbb), and this ruling extends that mechanism rather than building a second one. Then: add the temporal classes to NON_TEXT_STORED_VALUE_TYPES, add the FILTER_TEXT_CASES contract rows for date / datetime / time, flip the memory-driver pins in memory-driver-filter-logic-conformance.test.ts to key on declared type (the dev measures whether formula / having have schema access on the path; if a face cannot see the declared type, that is reported, not papered over), and pin the SQL side including the Postgres live cell. MySQL stays NOT MEASURED unless a server is available. Clause-② yes (result-set change on a published filter surface); changeset minor + BREAKING with the launch-window banner, naming the SQLite behaviour that goes away.

State transition, same stroke: needs-user-decision → pm:blocked; Blocked-by: #14079 appended to the body; priority:p2, domain:engine retained. Ledger: objectstack director seat post #12708, summon #14.

---

Generated by Claude Code