cockroachdb / cockroachdb/cockroach
sql: collated LIKE with a wildcard ignores accents due to NFD normalization
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
**Describe the problem**
Under an accent-sensitive collation, `LIKE` with a wildcard silently ignores an accent at the wildcard boundary, returning `true` when it should return `false`. This is a silently-wrong result, not an error.
The cause is NFD normalization in the collated `LIKE` evaluation path. `matchStringFromDatum` (`pkg/sql/sem/eval/match.go`) NFD-normalizes a collated string's contents and then matches rune-wise. NFD decomposes a precomposed accented character (e.g. `ë` U+00EB → `e` U+0065 + combining diaeresis U+0308). A literal character in the pattern then matches the decomposed base letter, and a following wildcard (`%`/`_`) absorbs the leftover combining mark — so the accent, significant everywhere else under the collation, stops mattering right at a wildcard boundary.
NFD normalization was added in #147623 to make collated `LIKE` agree with `=` for canonically-equivalent strings (NFC `é` vs NFD `é`). That goal is correct, but applying NFD and then doing codepoint-based wildcard matching on the decomposed form has this unintended side effect.
**To Reproduce**
```sql
CREATE TABLE t (s STRING COLLATE en_US);
INSERT INTO t VALUES ('Strëët' COLLATE en_US); -- ë = U+00EB; en_US is accent-sensitive
-- Accent is correctly significant for = and wildcard-free LIKE:
SELECT s = 'Street' COLLATE en_US FROM t; -- false (ë ≠ e)
SELECT s LIKE 'Street' FROM t; -- false (ë ≠ e)
-- But a trailing % makes the accent stop mattering:
SELECT s LIKE 'Stre%' FROM t; -- true <- wrong; should be false
```
**Expected behavior**
`'Strëët' COLLATE en_US LIKE 'Stre%'` should return `false`: under an accent-sensitive collation the 4th character `ë` ≠ the pattern's literal `e`, so the string does not start with `Stre`. PostgreSQL returns `false` here. The core issue is the inconsistency: the same accent is significant for `=` and wildcard-free `LIKE`, but insignificant when a wildcard follows the position where it decomposes.
**Additional data**
- The leak is normally masked because deterministic collations are case-sensitive: `LIKE 'stre%'` (lowercase) fails on `s ≠ S` before the accent matters. It surfaces when the pattern's case matches (`'Stre%'`), and would surface readily for a case-insensitive collation.
- Related: #149791 (CITEXT `LIKE` support).
**Environment:**
- CockroachDB version: master (collated-string `LIKE` is not in a released version).
**Additional context**
Impact is currently latent — collated `LIKE` is only on master and the leak is masked by case-sensitivity in most cases — but it should be fixed before/as collated `LIKE` (including case-insensitive collations) ships, since case-insensitivity removes the masking. Likely fix direction: don't expose the NFD-decomposed form to codepoint-based wildcard splitting — compare through the collator (which normalizes internally for the comparison), or match `_`/`%` on grapheme clusters rather than codepoints.
Epic CRDB-52348
Jira issue: CRDB-68161
Contributor guide
Research direction
Start in pkg/sql/sem/eval/match.go at matchStringFromDatum and run the SQL reproduction to observe the wildcard-boundary mismatch. Trace how NFD normalization and rune-wise wildcard matching interact with the collator. Done means an accent-sensitive collated LIKE such as 'Strëët' LIKE 'Stre%' returns false while canonical-equivalent matching remains correct.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 68/100