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