cockroachdb / cockroachdb/cockroach

sql: collated LIKE with a wildcard ignores accents due to NFD normalization

Open
#175,009 1 comment 0 reactions 1 assignee Assigned to @BryanWangAtCRL View on GitHub
A-sql-execution C-bug O-agent T-sql-queries
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.