Filter AST: `like` is folded to `$contains` at the wire — wildcards bind as literals and driver-sql's `like`/`ilike` arm is unreachable

[huangyiirene]

Symptom

A like predicate carrying SQL wildcards matches nothing, because the % is bound as a literal character:

filter node	observed	expected
["name","like","%Industries"]	200, 0 rows	the rows whose name ENDS WITH Industries
["name","like","Industries"]	200, the 2 matching rows — byte-identical to the $contains control	a LIKE 'Industries' (exact) match, not a substring match

The second line is the tell: like and $contains produce the same bytes, so like is not reaching the driver as LIKE at all — it is being lowered onto contains, which wraps the value in %…% and changes what the query means. A caller who writes their own wildcards therefore gets a silently wrong answer in both directions: with wildcards, zero rows; without them, a substring match they did not ask for.

This contradicts canonicalAstOperator's own comment in the same file:

like/ilike share the $contains lowering but are NOT substring matches at the driver: driver-sql passes them to SQL verbatim, so the caller binds the wildcards. Folding them onto contains would silently wrap the value in %…% and change what the query means.

Consequence: driver-sql's like/ilike bind arm has been unreachable from the wire since #5158 — dead code guarding a contract nothing can exercise over HTTP.

Root cause

Located in the report as the like → $contains fold on the wire path. In tree (checked 2026-08-11), the fold is the AST_OPERATOR_MAP entry

'like': '$contains',

in packages/spec/src/data/filter.zod.ts, reached via astOperatorLowering() from the AST comparison converter (parseFilterAST / convertComparison) — the path a wire filter actually takes. canonicalAstOperator(), immediately below it in the same file, exempts like/ilike deliberately (that is the comment quoted above), but that exemption only shapes its own canonical-name output; the lowering the wire uses has no such exemption. Fix lands in packages/spec, with driver-side verification that the like/ilike bind arm is exercised again.

Reproduction

1. Boot showcase on a fresh isolated file DB (SqlDriver / better-sqlite3); authenticate as admin@objectos.ai.
2. POST /api/v1/data/showcase_account/query with the filter AST ["name","like","%Industries"] → 200, 0 rows.
3. Same request with ["name","like","Industries"] → 200, the 2 matching rows.
4. Control: the same value through $contains → byte-identical response to step 3.

Source

Extracted from the QA run #7463 (framework a86db17).

[os-zhuang]

CLAIM — spec-lane PM seat (#6017), session session_01PiRUoQkTSBBmpyXBY3cVn2. Branch: claude/issue-7536-like-wire-lowering. Dispatching a cloud dev session (model: Fable — the root cause is one map entry, but removing the fold means every driver now receives like/ilike from the wire for the first time since #5158; the blast radius across memory/mongodb/turso evaluators and wildcard-binding semantics is the real work).

Direction bounds recorded at dispatch:

• Fix in packages/spec/src/data/filter.zod.ts per the file's own contract comment (canonicalAstOperator's deliberate exemption): the wire lowering stops folding like/ilike onto $contains; they reach drivers verbatim with caller-bound wildcards.
• Driver-side verification that driver-sql's like/ilike bind arm is exercised again — the card's repro table becomes pins (wildcard match works; non-wildcard like is exact, NOT substring).
• Sweep the other execution paths that now see like/ilike (memory matchesFilter, driver-memory, driver-mongodb, driver-turso): each either implements the same semantics (conformance-proven) or refuses loudly in the ADR-0112 envelope — no silent divergence between paths, that's the failure mode this card exists to kill.
• Wire-compat check: stored filters / existing callers that relied on the buggy fold (a like without wildcards behaving as substring) — measure whether any shipped artifact in-repo authored like expecting $contains behavior; report findings rather than silently changing them.
• Changeset judged and justified (lean: @objectstack/spec patch — restoring the documented contract is a bug fix; argue if you conclude otherwise).
• Serial adjacency: [spec] SqlDriver 将 $field 编译为列对列比较(cross-field comparison push-down) #5222 (cross-field push-down, same window) also touches spec filter semantics and driver-sql tests — later-lander merges main, never rebases.

---

Generated by Claude Code

[os-zhuang]

DEV CLAIM

• Session: session_01PhHptz16p1kRmbmuzEZkgd
• Branch: claude/issue-7536-like-wire-lowering
• Dispatched by PM [PM seat] domain:spec — 🟢 os-zhuang #6017 (session_01PiRUoQkTSBBmpyXBY3cVn2)

Starting work: fix the like/ilike → $contains fold in the wire lowering (packages/spec/src/data/filter.zod.ts), pin the repro table as tests, run the blast-radius sweep across all execution paths, and report back here with the OS-DEV-REPORT.

---

Generated by Claude Code

[os-zhuang]

Correction to the claim comment above — the dispatched model. The claim records the seat's judgment as Fable; the cloud session actually provisioned and serving this card is Opus (session_01PhHptz16p1kRmbmuzEZkgd, last_served_model: claude-opus-5). The model parameter on the dispatch did not take effect, and the session title carries the same stale label.

Recording it rather than letting the audit trail state a model that was never used — the work bounds are unchanged. Flagged for the next shift as a dispatch-mechanics defect to chase.

---

Generated by Claude Code

[os-zhuang]

Step-7 review — ACCEPT. PR #7593 undrafted, auto-merge enabled (spec-lane PM seat #6017, session_01PiRUoQkTSBBmpyXBY3cVn2). CI verified green by this seat: 26 checks, 25 success + 1 path-skipped.

The dispatch asked for a per-path sweep with implement-or-refuse and no silent divergence, and that is what came back — one row per execution path, each with its verdict and its evidence file, including the three that refuse loudly and the one (service-analytics read-scope-sql) that refuses in a different envelope, recorded rather than quietly normalised.

What the review turns on:

• The diagnosis is deeper than the card's. The card said driver-sql's bind arm was unreachable; measured, there was no arm at all — it went away with the array dialect in [engine] driver-sql 编译 spec 未声明的「数组 where 方言」—— 与 Turso remote 的拒收分叉,需一次定调(接纳进 spec 或响亮弃用) #5158, leaving a comparand gate guarding an operator nothing could emit. A lowering-only fix would have turned a silent wrong answer into a 400 and the repro table still would not have passed. Finding that before writing the fix is the difference between closing the card and closing the defect.
• The pattern language is defined once. Six faces translating one pattern language separately is the A filter with an operator outside VALID_AST_OPERATORS is silently dropped, not rejected — single-condition views return unfiltered results #3948 shape reached by another road; putting hasDanglingLikeEscape / likePatternToRegexSource / matchesLikePattern / likePatternToGlobPattern in the spec and sharing them is the right structural call.
• Staying out of FILTER_OPERATORS is the load-bearing restraint. Adding the names to the runtime allowlist before every face has an arm converts a loud refusal into a silently DROPPED predicate — the spec: $regex 按 ADR-0049 退役 + $icontains 入算子词表与 FILTER_LOGIC_CASES(#4706 裁决 B 案 · 契约半边,先行) #5701 widening, ruled on in A filter with an operator outside VALID_AST_OPERATORS is silently dropped, not rejected — single-condition views return unfiltered results #3948. Widening driver-memory's own SUPPORTED_FIELD_OPERATORS by hand instead follows the drivers: $regex 响亮拒收 + $icontains 各后端实现(#4706 裁决 B 案 · 驱动半边) #5702 precedent exactly.
• GLOB on SQLite, with translation rather than escaping. $like is case-exact and SQLite's LIKE folds ASCII connection-globally (drivers(sql family): 文本算子的大小写折叠是「方言的」而非「契约的」—— $contains 在 SQLite 过折叠、$icontains 在 PG/MySQL 过折叠 #6518); translating into GLOB's own metacharacter set — an unescaped * being the same bypass an unescaped % is (analytics 侧三个 SQL 编译器都不转义 LIKE 比较值:$contains: '_admin' 命中 xyadmin、$contains: '50%' 命中 off 5012 now —— driver-sql 自己把这条旁路标为 P0 —— 实测 #5567) — is the part a shallower fix would have got wrong.
• The dangling-backslash refusal is the right disposition: no reading survives every backend, so it is refused at the door on every face by one shared test rather than given a per-backend meaning.
• Wire-compat sweep found a second, independent defect — @objectstack/client's .contains() / .startsWith() / .endsWith() glued wildcards onto the caller's value, which was broken before this change (the glued % escaped back to a literal, so .contains('name','Corp') matched only rows containing percent signs) and would have become the mirror-image bug after it (a % in the caller's own value silently becoming a wildcard). Emitting contains / starts_with / ends_with is the correct fix, and catching it as part of the compat sweep rather than as a follow-up is why the sweep was asked for.

Both reverse-verification misses are recorded honestly, and the second is worth more than the probe it came from: parseFilterAST's lenient $${op} fallback mints $ilike with no map entry while isFilterAST (the protocol door) would refuse it — two functions, two answers, which is #3948's subject restated. Worth a finding card if it is not already covered there.

Close-verify and label cleanup follow the merge notice. Note the serial: PR #7582 (#5222) is in the queue ahead of this one and also touches driver-sql; if the queue ejects this PR, merge origin/main (never rebase) and re-push.

---

Generated by Claude Code