Skip to content

fix(search): answer CJK and Japanese queries by substring - #3810

Merged
thedotmack merged 2 commits into
thedotmack:mainfrom
ntdatt812:fix/3801-cjk-search
Sep 11, 2026
Merged

thedotmack merged 2 commits into
thedotmack:mainfrom
ntdatt812:fix/3801-cjk-search

Conversation

@ntdatt812

Copy link
Copy Markdown
Contributor

Fixes #3801.

⚠️ The suggested trigram fix does not fix the reported case

Before writing anything I built the trigram index the issue proposes and queried it:

trigram  "用户身份"  -> 1
trigram  "用户"      -> 0     <- the issue's own primary example (826 rows contain it)
trigram  "数据"      -> 0
trigram  "db"        -> 0     <- regression: unicode61 answers this today
trigram  "database"  -> 1

Trigram indexes 3-character substrings, so it cannot answer a 2-character query at all. 用户 is exactly the query the issue opens with, and db is an English query that works today. Trigram would also require detecting and rebuilding the FTS tables on every existing install, since they are created with CREATE VIRTUAL TABLE IF NOT EXISTS and would otherwise silently keep the old tokenizer.

So I took suggestion 3 instead, which is accurate for every query length and needs no migration.

The fix

unicode61 has no delimiter to split Chinese or Japanese on, so a whole run of ideographs folds into one token — and every query in those scripts is a substring of some longer run, which can never match a token. Route those queries to a substring predicate over the same columns the FTS table indexes.

This is not a new mechanism in this class. searchUserPrompts has always answered every query this way, escaping included:

const escapedQuery = query.replace(/[\%_]/g, '\$&');
baseConditions.push("up.prompt_text LIKE ? ESCAPE '\'");

The new helper is that line, generalised over a column list. Queries FTS5 can tokenize keep the FTS path untouched — same SQL, same rank ordering.

One correction to the issue: user_prompts_fts needs nothing, because prompt search never used it. The affected paths are searchObservations and searchSessions.

Scope and cost

The predicate scans observations / session_summaries rather than an index. That is the price of a correct answer here — there is no index in the schema that can represent these queries. It runs only when the query contains Hiragana, Katakana, or a CJK ideograph, so no English query changes plan. If you would rather bound it further, the natural next step is a generated bigram column, and I am happy to send that instead.

Tests

tests/services/sqlite/search-cjk-fallback.test.ts, 8 tests: Chinese inside a longer run, a 2-character Chinese query, Japanese, session summaries, project and type filters still applied on the new path (the parameter order is easy to get wrong), %/_ in a query treated literally, and English unchanged for both observations and summaries.

Reverting the change proves they hold it:

mutation result
dispatch never fires 6 fail
drop the wildcard escaping 1 fail
bun test tests/services/sqlite/search-cjk-fallback.test.ts     8 pass, 0 fail
bun test tests/services/sqlite/ tests/worker/search/ \
         tests/sqlite/ tests/storage/sqlite/                   204 pass, 4 fail
tsc --noEmit                                                   clean

The 4 failures are the pre-existing Windows EBUSY: resource busy or locked teardown failures in session-store-orphan-fk-repair.test.ts. I confirmed they are identical on a clean tree — same four names, before and after.

FTS5's unicode61 tokenizer has no delimiter to split Chinese or Japanese on,
so a whole run of ideographs folds into a single token. Every query in those
scripts is a substring of some longer run, and a substring of a token can
never match it -- so observation and session search returned 0 rows for any
multi-character CJK keyword, however many rows contained it.

Route queries containing an unsegmented script to a substring predicate over
the same columns the FTS table indexes. searchUserPrompts has always answered
this way, escaping included, so the semantics already exist in this class;
this extends them to the two tables that go through FTS.

Queries FTS5 can tokenize are untouched.

Fixes thedotmack#3801
@greptile-apps

greptile-apps Bot commented Aug 31, 2026 •

Copy link
Copy Markdown
Contributor

Greptile Summary

Japanese searches containing half-width Katakana or Katakana Phonetic Extensions do not retrieve matching observations or session summaries. Extend the unsegmented-script detector so these queries use the escaped substring search path.

Confidence Score: 4/5

Not ready to merge until the Japanese substring fallback recognizes the omitted Katakana ranges.

A verified search correctness failure remains: matching records are not returned for half-width Katakana and Katakana Phonetic Extension queries.

Files Needing Attention: src/services/sqlite/SessionSearch.ts

T-Rex T-Rex Logs

What T-Rex did

  • Generated an in-memory Katakana fallback reproduction to test the P1 finding.
  • Validated an additional P1 finding using a separate proof.
  • Ran a general-contract-validation-proof to compare current-code output and range-corrected output, including the authored source and both outputs.

View all artifacts

T-Rex Ran code and verified through T-Rex

Comments Outside Diff (1)

  1. General comment

    P1 Katakana extension queries bypass substring fallback

    • Bug
      • searchObservations('ユーザー') does not return stored 開始ユーザー認証完了, and searchObservations('ㇰㇱ') does not return stored 開始ㇰㇱ確認完了. The same misses occur in searchSessions.
    • Cause
      • UNSEGMENTED_SCRIPT at src/services/sqlite/SessionSearch.ts:253 includes standard Katakana only through U+30FF and omits half-width Katakana U+FF66–U+FF9D and Katakana Phonetic Extensions U+31F0–U+31FF. Therefore the fallback branches at lines 304 and 383 are not selected.
    • Fix
      • Extend the regex character class at line 253 with \u31F0-\u31FF and \uFF66-\uFF9D (for example: /[\u3040-\u30FF\u3100-\u318F\u31F0-\u31FF\u3400-\u4DBF\u4E00-\u9FFF\uAC00-\uD7AF\uF900-\uFAFF\uFF66-\uFF9D]/), and retain the focused regression coverage.

    T-Rex Ran code and verified through T-Rex

Reviews (2): Last reviewed commit: "fix(search): widen the unsegmented-scrip..." | Re-trigger Greptile

Comment thread src/services/sqlite/SessionSearch.ts Outdated
* folds into a single token and no substring of that run can match it (#3801) — and every
* query in these scripts is a substring of some longer run.
*/
private static readonly UNSEGMENTED_SCRIPT = /[\u3040-\u30FF\u3400-\u4DBF\u4E00-\u9FFF\uF900-\uFAFF]/;

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

P1 Katakana bypasses substring fallback

UNSEGMENTED_SCRIPT excludes half-width Katakana (U+FF66–U+FF9D) and Katakana Phonetic Extensions (U+31F0–U+31FF). Queries containing either block therefore use the unicode61 FTS5 path rather than the escaped LIKE substring fallback. Searching for ユーザー in 開始ユーザー認証完了 and ㇰㇱ in 開始ㇰㇱ確認完了 returned no rows through SessionSearch.searchObservations, while the equivalent fallback returned the stored row in both cases. Include these ranges in the detector so these Japanese queries receive substring-search behavior.

Artifacts

Self-contained Bun validation source for half-width Katakana and phonetic-extension search routing

  • The exact executable source seeds an in-memory SQLite database and compares the current SessionSearch path with the expected escaped LIKE fallback, confirming both omitted Unicode blocks need fallback routing.

Current search behavior for omitted Katakana Unicode blocks

  • Executed `SessionSearch.searchObservations` against longer stored Japanese text; both omitted-block substring queries took FTS5 MATCH and returned zero rows, confirming the failure.

Expected fallback behavior for omitted Katakana Unicode blocks

  • Executed the equivalent escaped SQLite LIKE fallback against the same seeded data; both substring queries returned their one stored row, confirming the required behavior.

Focused existing CJK fallback test output

  • Ran the repository's existing CJK fallback test file with Bun; all 8 tests passed, showing the existing coverage does not exercise the omitted Katakana blocks.

View artifacts

T-Rex Ran code and verified through T-Rex

@stantheman0128

Copy link
Copy Markdown
Contributor

Support. Trigram cannot answer a 2-character query. Routing those scripts through the same LIKE ... ESCAPE helper searchUserPrompts already uses is the right height. The new tests go red if the dispatch never fires.

Two sibling scripts sit next to this regex. Bopomofo (\u3100-\u312F) and Hangul (\uAC00-\uD7AF) are delimiter-free the same way. unicode61 will fold them into one token too. A 注音 query would still miss.

Not a blocker for the reported Chinese/Japanese hole.

Review on thedotmack#3810 pointed out two scripts sitting beside the ones this branch
already covered. Confirmed against tokenize='unicode61' rather than taken on
faith:

  설정   inside 설정을            -> 0 rows
  설정을 as a whole token         -> 1 row
  ㄓㄨ   inside ㄓㄨㄛ            -> 0 rows
  项目   inside 修改了项目配置    -> 0 rows   (the reported case)
  project inside "the project"    -> 1 row    (Latin, unaffected)

unicode61 breaks on Unicode whitespace and punctuation, and none of these
scripts put either between the characters of a run, so a run is one token and no
substring of it can match.

Korean is the interesting one: it does space its words, so only the sub-word
case is affected. That case is still every partial-word query a user types, and
the measurement above is exactly it.

The blocks are adjacent, so \u3100-\u318F picks up Bopomofo together with the
Hangul compatibility jamo beside it, and \uAC00-\uD7AF covers the syllables.

Two tests, one per script. Narrowing the class back to the previous ranges fails
both.
@ntdatt812

Copy link
Copy Markdown
Contributor Author

Both are in, in 95c92fee. I measured them before adding rather than reasoning from the block names, since the Korean case is not obviously the same shape:

설정   inside 설정을            -> 0 rows
설정을 as a whole token         -> 1 row
ㄓㄨ   inside ㄓㄨㄛ            -> 0 rows
项目   inside 修改了项目配置    -> 0 rows   (the reported case)
project inside "the project"    -> 1 row    (Latin, unaffected)

Run against a bare CREATE VIRTUAL TABLE t USING fts5(body, tokenize='unicode61'), so it is the tokenizer talking and not this repo's schema.

Korean is worth stating explicitly in the comment, which I did: it does space its words, so unlike Chinese and Japanese only the sub-word case breaks. That case is still every partial-word query someone types, and the first two lines above are exactly it — the whole token matches, the prefix does not.

The blocks turned out to be adjacent, so it is one range rather than two: ㄀-㆏ picks up Bopomofo together with the Hangul compatibility jamo next to it, and 가-힯 covers the syllables.

Two tests, one per script, seeded next to the existing Chinese and Japanese fixtures. Narrowing the character class back to the previous ranges fails both, so they are pinned to the change rather than to the dispatch in general.

Thanks — you were right that a 注音 query would still have missed, and it would not have been obvious from the issue.

* \u3100-\u318F covers Bopomofo together with the Hangul compatibility jamo beside it.
*/
private static readonly UNSEGMENTED_SCRIPT =
/[\u3040-\u30FF\u3100-\u318F\u3400-\u4DBF\u4E00-\u9FFF\uAC00-\uD7AF\uF900-\uFAFF]/;

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

P1 Katakana extension queries bypass substring fallback

UNSEGMENTED_SCRIPT includes standard Katakana only through U+30FF, so queries containing half-width Katakana (U+FF66–U+FF9D) or Katakana Phonetic Extensions (U+31F0–U+31FF) bypass the escaped LIKE fallback. searchObservations('ユーザー') does not return stored 開始ユーザー認証完了, and searchObservations('ㇰㇱ') does not return stored 開始ㇰㇱ確認完了; the same misses occur in searchSessions. Include both ranges in this detector so these substring queries use the fallback.

Artifacts

In-memory Katakana fallback reproduction source

  • The authored Bun script creates an in-memory SQLite store, inserts both requested strings, and calls observation and session searches; it exercises the exact affected path.

Current-code Katakana fallback reproduction output

  • Running the authored script against the current source returned empty observation and session arrays for both required queries, proving the fallback is not reached.

Temporary range-correction Katakana fallback output

  • Running the same script after temporarily adding the two omitted Unicode ranges returned every stored observation and session row, proving the ranges resolve the defect.

View artifacts

T-Rex Ran code and verified through T-Rex

@stantheman0128

Copy link
Copy Markdown
Contributor

Thanks. Measuring the tokenizer is the right way. \u3100-\u318F plus \uAC00-\uD7AF closes the 注音 hole.

Half-width Katakana (\uFF66-\uFF9D) and phonetic extensions (\u31F0-\u31FF) still skip the fallback. Same miss if someone searches ユーザー. Not a blocker for the Chinese / Hangul / Bopomofo cases you pinned.

@thedotmack

Copy link
Copy Markdown
Owner

All-night ship gate: PASS

  1. RCA logged — FTS5 unicode61 folds CJK/Japanese runs into one token, so substring queries such as 用户 never match (Fixes [BUG] Chinese (CJK) phrase search always returns 0 — FTS5 unicode61 tokenizer can't segment CJK; not covered by #3610 #3801 / plan-25). Trigram cannot answer 2-character queries.
  2. No second-system — routes those scripts through the same escaped LIKE helper searchUserPrompts already uses; English FTS path unchanged.
  3. Narrow / not opinionated — dispatch + helper + tests. Half-width Katakana remains a follow-up, not a second system.

Rebased onto 7335467f locally (fork branch update blocked). Focused tests: bun test tests/services/sqlite/search-cjk-fallback.test.ts — 10 pass. This head has no CI runs; opening a rebased ship branch to verify CI before merge. No version bump.

@thedotmack
thedotmack merged commit bfe4694 into thedotmack:main Sep 11, 2026
@thedotmack

Copy link
Copy Markdown
Owner

Update: GitHub accepted the squash merge of this head onto current main after the local rebase onto 7335467f stayed clean and the 10 focused tests passed. No separate ship branch needed.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

[BUG] Chinese (CJK) phrase search always returns 0 — FTS5 unicode61 tokenizer can't segment CJK; not covered by #3610

3 participants