Summary
LOCATE() (and by extension POSITION()) returns a byte offset instead of a character position on multi-byte (e.g. Arabic, CJK) strings. MySQL defines LOCATE as multibyte-safe: "returns the position of the first occurrence", counted in characters.
Repro (dolt 2.2.2, any database)
SELECT LOCATE('ب', 'ااااب'), CHAR_LENGTH('ااااب');
- MySQL 8:
5, 5
- Dolt:
9, 5 — the substring's first byte is at byte offset 9 (each Arabic char is 2 bytes in utf8), reported as if it were a character position.
A returned position can thus exceed CHAR_LENGTH of the searched string, and any arithmetic combining LOCATE with SUBSTRING/CHAR_LENGTH (which are character-based) silently corrupts. Found in production while byte-verifying text containment: LOCATE(...) = 10761 inside a string whose CHAR_LENGTH = 6282.
Fix area: sql/expression/function/locate.go — strings.Index(strings.ToLower(str[position-1:]), strings.ToLower(substr)) returns byte offsets, treats the start argument as bytes (can slice mid-rune), and folds case even for binary arguments. See the follow-up comment below for the verified semantics matrix (MySQL 8.4 manual + MariaDB 12.2): positions and start argument are counted in characters; matching is simple case folding for nonbinary arguments only — full collation weights are not applied (accent-insensitive collations still don't fold é/e), so no ICU-level search is needed. Instr in this repo already uses the right rune-based position approach (it has separate defects — #3650).
Summary
LOCATE()(and by extensionPOSITION()) returns a byte offset instead of a character position on multi-byte (e.g. Arabic, CJK) strings. MySQL defines LOCATE as multibyte-safe: "returns the position of the first occurrence", counted in characters.Repro (dolt 2.2.2, any database)
5, 59, 5— the substring's first byte is at byte offset 9 (each Arabic char is 2 bytes in utf8), reported as if it were a character position.A returned position can thus exceed
CHAR_LENGTHof the searched string, and any arithmetic combiningLOCATEwithSUBSTRING/CHAR_LENGTH(which are character-based) silently corrupts. Found in production while byte-verifying text containment:LOCATE(...)= 10761 inside a string whoseCHAR_LENGTH= 6282.Fix area:
sql/expression/function/locate.go—strings.Index(strings.ToLower(str[position-1:]), strings.ToLower(substr))returns byte offsets, treats the start argument as bytes (can slice mid-rune), and folds case even for binary arguments. See the follow-up comment below for the verified semantics matrix (MySQL 8.4 manual + MariaDB 12.2): positions and start argument are counted in characters; matching is simple case folding for nonbinary arguments only — full collation weights are not applied (accent-insensitive collations still don't fold é/e), so no ICU-level search is needed.Instrin this repo already uses the right rune-based position approach (it has separate defects — #3650).