drivers(sql family): 文本算子的大小写折叠是「方言的」而非「契约的」—— `$contains` 在 SQLite 过折叠、`$icontains` 在 PG/MySQL 过折叠

[os-zhuang]

Part of #4706(裁决 Q1=A / Q2=A)。从 #5702 实施期实测拆出 —— #5702 交付了 $regex 拒收与 $icontains 实现,没有交付这一条,原因见下「为什么不能在 #5702 里顺手做」。

两个方向的同一个缺陷

SqlDriver.applyLike 发的 SQL 里,大小写由方言决定,不由契约决定。两个算子各在一半方言上跑偏:

算子	契约(#4706)	SQLite / turso / sqlite-wasm	Postgres	MySQL
$contains / $notContains / $startsWith / $endsWith	大小写敏感(Q2=A)	❌ LIKE 自带 ASCII 折叠	✅ 恰好正确	⚠️ 随 collation
$icontains	折叠 ASCII only(Q1=A)	✅ SQLite lower() 只折 ASCII	❌ LOWER() 折全 Unicode	❌ 同左

两格都是过匹配(返回了不该返回的行),不是漏匹配。

实测(better-sqlite3,FILTER_TEXT_ROWS 九行,origin/main + PR of #5702)

$contains  'acme'  → ["1","2"]     契约要求 ["2"]      (行 1 = "ACME Corp")
$contains  'ACME'  → ["1","2"]     契约要求 ["1"]
$icontains 'acme'  → ["1","2"]     ✅
$icontains 'café'  → ["4"]         ✅(É 不折)

顺带一条对分诊有用的读数:在 SQLite 上 $contains 与 $icontains 对任意比较数返回完全相同的行 —— 因为 LIKE 已经折了 ASCII,外面再套一层 LOWER() 是幂等的。也就是说这条不修,$icontains 在 SQLite 上是行为不可观测的(它的正确性只能靠编译出的 SQL 文本和 PG 见证)。#5702 的 remote-transport-text-predicates.test.ts / sql-driver-icontains-and-retired-operators.test.ts 已把这两条如实钉住。

为什么不能在 #5702 里顺手做(这是本单独立的理由)

修 $contains 的大小写需要在 SQLite 上换构造(GLOB / instr() / 二进制 collation —— LIKE 不受 collation 影响,只能靠 PRAGMA case_sensitive_like,而那是连接级全局开关)。这个下译在三处并存,必须同批移动:

1. packages/drivers/driver-sql/src/sql-driver.ts 的 applyLike;
2. packages/drivers/driver-turso/src/remote-transport.ts 的 pushLike(remote 不走 knex,自带一份);
3. RLS / 分析面的孪生实现 —— read-scope-sql 与 packages/services/service-analytics/src/like-pattern.ts(后者的注释自陈「held to THIS expression, character for character」)。

只改 driver 不改第 3 组,等于一条权限规则编出两个行集 —— #3948 那一类 P0,比现状更糟。所以它不是 domain:drivers 单车道能闭环的改动。

验收

• 五个后端对 FILTER_TEXT_CASES 的 5 条大小写用例($contains ×2 / $startsWith / $endsWith / $notContains)一致判绿;
• $icontains 的 ASCII-only 边界在 live PG / MySQL 格上也成立(live-dialect-matrix.testkit.ts 已有基建,driver-sql 的 LIKE 转义(自标 P0 的过滤器旁路)只在 SQLite 上验证过 —— live PG + MySQL 矩阵已存在、testkit 也已存在,LIKE 族一个用例都没接进去 #5589 的方言矩阵加算子轴即可);
• scripts/check-driver-conformance.mjs 的 FILTER_TEXT_CASES DEBT 行随之清偿(drivers: $regex 响亮拒收 + $icontains 各后端实现(#4706 裁决 B 案 · 驱动半边) #5702 已把 $regex / $icontains 两个半边从这些行里划掉,剩下的就是本单)。

[os-zhuang]

Triage: pm:queue + domain:drivers + target:v17.

Anchors verified on origin/main @ 3510e4a: sql-driver.ts applyLike (doc at :971, call at :6990), driver-turso/src/remote-transport.ts pushLike (:1849/:1861 — its own lowering, not knex), and packages/services/service-analytics/src/like-pattern.ts present as the twin the body names. The contract is already ruled (#4706 Q1=A / Q2=A), so this is execution of a made decision, not a new ruling — a concrete over-matching defect with a scripted repro on FILTER_TEXT_ROWS.

Cross-domain exception path, designated by this seat: the LIKE lowering exists in three synchronized places (driver-sql, turso remote transport, and the RLS/analytics twins), and the body is right that fixing the driver alone would make one permission rule compile two row-sets — worse than the status quo. The drivers lane leads as the single claimant; the claim comment MUST declare the full file surface including service-analytics/src/like-pattern.ts and the read-scope SQL twin, and run the targeted in-flight check for that surface.

target:v17 on ground ② (declared ≠ enforced): the ruled case-sensitivity contract is published while SQLite/turso/wasm silently over-match $contains and PG/MySQL over-fold $icontains today — wrong rows on a shipped filter surface. Maintainer may strike.

Serialization note for the lane: #5702's PR is in flight on the same files (sql-driver.ts, the conformance DEBT rows) — land #5702 first. File collision, not Blocked-by: (the case-folding fix does not substantively depend on it; only the DEBT-clearing acceptance line references #5702's halves). Dedup: family checked (#4706 / #5702 / #5814 / #6337 / #6520) — no duplicate; this facet was deliberately split out of #5702.

本评论来自分诊座位 Routine（#5474 试点），不构成认领。

---

Generated by Claude Code

[os-zhuang]

Claim: PM loop round 1 (drivers seat)
Session: session_01Hg9Pkg5nDedCRihRsdeCdX
Branch: claude/issue-6518-contains-case-folding
Worktree: cloud dispatch (mode:cloud, fresh exclusive container via create_session; outcome branch as above)
Domain: domain:drivers — cross-domain exception path, designated by the triage seat (its 2026-08-08 03:56Z comment above: drivers lane leads as single claimant)
File surface: packages/drivers/driver-sql/src/sql-driver.ts (applyLike + text-operator tests), packages/drivers/driver-turso/src/remote-transport.ts (pushLike + tests), packages/drivers/driver-sqlite-wasm/** (inherited-behaviour witness tests), packages/services/service-analytics/src/like-pattern.ts + read-scope-sql.ts + the third compiler like-pattern serves (+ their tests), scripts/check-driver-conformance.mjs (the five DEBT rows pointing here), plus repo-wide test pin flips for the ruled $contains case semantics. ⛔ packages/spec source: zero changes expected — the dev stops and reports if measurement says otherwise (spec surfaces belong to the spec seat). (stop on breach; explain in the report)
Serial constraints cleared: #5702 → PR #6549 MERGED 2026-08-08 06:43Z (same files — triage's "land #5702 first" note satisfied); #6320 → PR #6517 MERGED 03:38Z; all 9 open PRs checked — none touches the declared surface; targeted in-flight check for the cross-domain surface: domain:services carries zero pm:dispatched issues; engine-core's in-flight PR #6655 touches packages/objectql/packages/runtime only, outside this surface.
Container verdict: L (multi-face lowering + conformance + live dialect matrix; verification radius spans 4+ packages) ⇒ mode:cloud exclusive container.

---

Generated by Claude Code

[os-zhuang]

Dispatched — mode:cloud, exclusive container: session session_01AYvVzTaCWDCC32e9Le9zXn (model claude-opus-5), outcome branch claude/issue-6518-contains-case-folding.

Expected deliverables per the dispatch order: a draft PR ("Fixes #6518") carrying the per-face verdict table, plus a terminal report comment here under the heading "os-dev report". The PM subscribes to the PR the moment it exists; collection otherwise sweeps this thread.

---

Generated by Claude Code

[os-zhuang]

os-dev report

{
  "issue": 6518,
  "status": "done",
  "branch": "claude/issue-6518-contains-case-folding",
  "pr": "https://github.com/objectstack-ai/objectstack/pull/6706",
  "premise_still_valid": true,
  "summary": "Re-verified the premise on post-#6549 origin/main (d13f627) — it held: applyLike still emitted one LIKE…ESCAPE for every dialect and LOWER() for the fold, and the five FILTER_TEXT_CASES case rows were still the sole reason the five DEBT rows survived. The text-predicate construct is now chosen per dialect in one emitter: GLOB on the SQLite family (LIKE's ASCII fold cannot be turned off per statement, and CAST(col AS BLOB) LIKE ? was measured to match nothing), LIKE unchanged on Postgres with the $icontains fold moved from Unicode-folding LOWER() to an explicit ASCII-only translate(), and LIKE over CAST(… AS BINARY) on MySQL; turso's independent remote compiler moved in the same batch and is pinned to the local face by a new local/remote parity suite. Faces 3/4 (service-analytics) measured 本就合规 — both compilers emit Postgres-shaped SQL ($N placeholders, quoted identifiers) where LIKE already IS the ruled semantics, so no RLS fork opens; that dependency is now written down and pinned rather than implied. driver-sql / -sqlite-wasm / -turso now import and answer the whole case-set, clearing three DEBT cells; driver-memory and driver-mongodb stay honestly DEBT under the #5499 freeze, re-pointed at newly-filed #6682.",
  "tests": "pnpm test (whole repo): 135 successful / 135 total, 0 ELIFECYCLE. pnpm lint: exit 0. turbo run typecheck (packages/*, packages/*/*, apps/*): 120/120. pnpm --filter @objectstack/spec exec tsc --noEmit: exit 0. check:driver-conformance: OK — 28 covered, 2 DEBT, 0 exempt. check:query-options-erasure: OK (test surface 263, at the ceiling). check:type-check-debt: OK — 34 entries re-measured, none above its number. check:engine-double-contract: OK. check:nul-bytes: OK (6228 files). check:adr-0087-registration: OK — no declared-breaking changeset. check:empty-changeset: OK — 1 declaring changeset added. Every other check:* step enumerated from .github/workflows/lint.yml was run one by one and passed (full list in the PR body). LIVE CELLS: a PostgreSQL 16.13 was provisioned in-container and the FILTER_TEXT_CASES matrix RAN against it on an ICU-locale database — all 17 cases plus the fixture control green (42 passed / 1 skipped). The live MySQL cell could NOT run (no mysqld, no working Docker daemon) and is a declared skip by name via declareUnprovisionedCell — never a faked green; CI's 'Temporal Conformance (live PG + MySQL)' job already sets both URLs, and the MySQL arm additionally carries an in-process compiled-SQL pin. REVERSE VERIFICATION, direction predicted before each run: (1) sqlite arm GLOB→LIKE — predicted exactly the 5 case-sensitivity rows red on sqlite and nothing red on postgres; measured exactly that. (2) drop the column-side lower() — predicted the fold becomes observable in rows, retiring #5702's 'unobservable on SQLite' caveat; measured $icontains 'acme'/'ACME' ['1','2']→['2'] and 'CAFÉ' ['3']→[]. (3) postgres translate()→LOWER() — predicted exactly the 2 ASCII-only rows red on the LIVE postgres cell and nothing red on sqlite; measured exactly that ('café' answered ['3','4'] where the case-set demands ['4']). The rejected CAST(col AS BLOB) candidate was measured before being written off.",
  "open_questions": [
    "translate() chosen over lower(x COLLATE \"C\") on Postgres — both measured ASCII-only on live PG 16 (C.utf8 and ICU); translate() wins because its ASCII-only-ness is structural (the 26-char map is in the emitted SQL) rather than a property of a collation a reader must look up. Adjudicated, not blocking.",
    "MySQL's fold is 26 REPLACEs per operand (~52 per predicate) — verbose, accepted: every alternative is wrong rather than merely uglier (LOWER() over-folds; LOWER() on a binary string is documented ineffective; CONVERT(… USING ascii) collides café with cafÉ), the predicate was a full scan either way, and byte-wise ASCII lowering is provable by reading because UTF-8 is self-synchronising.",
    "The 'unknown' dialect (any client outside sqlite/pg/mysql) keeps the pre-#6518 LIKE/LOWER() shape — GLOB is a syntax error there and CAST(… AS BINARY) means something else, so it is the only form that runs. Named as residue in the conformance ledger and pinned in the matrix rather than left silent.",
    "service-analytics was deliberately NOT given a dialect seam. Measured: it has no dialect input at all, and its compilers emit Postgres-shaped SQL where the ruled semantics already hold — so a seam would be speculative surface for zero behaviour change. The dependency is now written down in like-pattern.ts (the file is wrong the day those compilers emit for SQLite or MySQL) and the Postgres-shape assumption is pinned so it fails rather than rots."
  ],
  "out_of_scope_findings": [
    "https://github.com/objectstack-ai/objectstack/issues/6682 — filed unassigned (finding posture, search-first dedup): driver-memory and driver-mongodb still fold case on their query paths (RegExp 'i' / hard-coded $options:'i'), and driver-memory's reference matcher disagrees with its own query path. Out of scope by the #5499 freeze restated in the dispatch; filed so the two surviving FILTER_TEXT_CASES DEBT rows point at a live successor rather than at this issue, which closes here. Both rows now state that #6682 AND #6520 must both land before the cell can import the case-set.",
    "check:objectui-pin-fresh is red on repo state (the objectui pin is stale versus objectui main). NOT in .github/workflows/lint.yml — it gates the release / Version-Packages path — and refreshing it would be exactly the pin-bump rider this change must not carry. Left untouched and reported."
  ]
}

---

Generated by Claude Code

[os-zhuang]

Progress checkpoint (not the formal report — that one is already above).

The checkpoint's premise reads as accurate at the time it was taken but stale by ~15 minutes: branch, draft PR and report all landed at 13:12–13:13Z. Nothing was lost, and nothing is spinning.

• Branch claude/issue-6518-contains-case-folding — pushed, head 8013514.
• Draft PR — fix(drivers): text-operator case folding is the contract's answer, not the dialect's (#6518) #6706 ("Fixes drivers(sql family): 文本算子的大小写折叠是「方言的」而非「契约的」—— $contains 在 SQLite 过折叠、$icontains 在 PG/MySQL 过折叠 #6518"), carrying the per-face verdict table, reverse-verification evidence, pin-sweep inventory, real gate outputs and the 必答项 answers.
• os-dev report — drivers(sql family): 文本算子的大小写折叠是「方言的」而非「契约的」—— $contains 在 SQLite 过折叠、$icontains 在 PG/MySQL 过折叠 #6518 (comment) (13:13Z), status: done, premise_still_valid: true.

Since then: main advanced nine commits (d13f627 → 2c2a212), so I merged it in and pushed. It merges cleanly, but one of those commits (#6665, the plugin-auth contains witness migrated onto sqlite) sits directly under this change — verified it still passes, and corrected its head note, which described the case gap as still open. A full repo pnpm test on the merged tree is running now; ETA ~10 minutes. If it is green there is nothing further to deliver — the report above stands as final. If it goes red I will fix, push and amend the report rather than open a new one.

No blockers, no decision points pending PM adjudication. The four open choices are adjudicated in the report's open_questions per the dispatch's three axes.

---

Generated by Claude Code

[os-zhuang]

Merged-tree verification finished — green. The os-dev report above stands as final; nothing in it needs amending.

Re-run on 8013514 (this branch merged with main @ 2c2a212, nine commits ahead of where the report's numbers were taken):

pnpm test (whole repo)                       135 successful / 135 total, 0 ELIFECYCLE
pnpm lint                                    OK
turbo run typecheck (packages/*, apps/*)     120 successful / 120 total
check:driver-conformance                     OK — 28 covered, 2 DEBT, 0 exempt
check:nul-bytes / query-options-erasure /
engine-double-contract / adr-0087-registration /
empty-changeset / type-check-coverage        OK

driver-sql, driver-sqlite-wasm and driver-turso read ok on FILTER_TEXT; driver-memory and driver-mongodb stay honest DEBT under the #5499 freeze, re-pointed at #6682.

The one merge-adjacent risk was #6665 (the plugin-auth contains witness migrated onto sqlite, landed while this was in flight) — it sits directly under this change. Verified individually: all 9 of its cases still pass, because every fixture's comparand matches its row's case exactly. Its head note claimed the case gap was still open, which this PR closes, so the note was corrected rather than left stale beside a passing test.

Working tree clean, local HEAD == remote head 8013514, draft PR #6706 up to date. Terminal state reached; nothing pending.

---

Generated by Claude Code

[os-zhuang]

ACCEPT — PR #6706 passed the step-7 review; marking it ready and adding it to the merge queue.

What shipped: one per-dialect text-predicate emitter (textMatchPredicate) replacing the dialect-blind LIKE/LOWER() lowering — GLOB with its own escape class on the SQLite family, translate() ASCII-only fold on Postgres, binary-cast LIKE + 26-REPLACE fold on MySQL; turso's independent remote compiler moved in the same batch and is pinned to the local face by a new parity suite; service-analytics faces measured already-compliant with the Postgres-shape assumption now written down and pinned. Frozen memory/mongodb faces untouched, DEBT rows re-pointed at #6682. Verified during review: complete per-face verdict table, pre-registered reverse verification with disjoint failure sets, live PG cell executed (MySQL cell executed in CI's Temporal Conformance job — conclusion success), all gate jobs green as job conclusions, pin flips bear load, changeset grading sound.

check:objectui-pin-fresh red is repo-state (release path), correctly left out of scope.

---

Generated by Claude Code