pingcap / pingcap/tidb

information_schema LIKE with a custom ESCAPE returns rows that fail the predicate (memtable extractor ignores ESCAPE)

Open
#69,653 1 comment 0 reactions 0 assignees View on GitHub
affects-9.0 found-by-ai severity/major sig/planner type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

### 1. Minimal reproduce step (Required)

A `LIKE ... ESCAPE` predicate on an `information_schema` table can return a row that **fails** the predicate and omit a row that **satisfies** it. The memtable predicate extractor pushes the `LIKE` pattern down but ignores the custom `ESCAPE` character, then removes the original scalar predicate, so the wrong (default-escape) match becomes authoritative.

```sql
CREATE TABLE `abc_def`(a INT);
CREATE TABLE `abc#x`(a INT);

-- SQL semantics: with ESCAPE '#', '#_' is a literal underscore, so only 'abc_def' matches:
SELECT 'abc_def' LIKE '%#_%' ESCAPE '#', -- 1
'abc#x' LIKE '%#_%' ESCAPE '#'; -- 0

-- extractor fast path returns the WRONG row (abc#x, which fails its own predicate):
SELECT table_name, table_name LIKE '%#_%' ESCAPE '#' AS self_true
FROM information_schema.tables
WHERE table_schema = DATABASE() AND table_name LIKE '%#_%' ESCAPE '#';
-- returns: abc#x | 0

-- scalar reference (CASE keeps the predicate) returns the correct row:
SELECT table_name, table_name LIKE '%#_%' ESCAPE '#' AS self_true
FROM information_schema.tables
WHERE table_schema = DATABASE() AND table_name LIKE '%'
AND (CASE WHEN table_name LIKE '%#_%' ESCAPE '#' THEN 1 ELSE 0 END) = 1;
-- returns: abc_def | 1
```

### 2. What did you expect to see? (Required)

The `information_schema.tables` query with `ESCAPE '#'` should return `abc_def` (the row that satisfies the predicate) and every returned row should evaluate the same `LIKE ... ESCAPE` predicate as true — matching ordinary scalar SQL semantics and the CASE-wrapped reference above.

### 3. What did you see instead (Required)

The fast path returns `abc#x`, whose own `LIKE '%#_%' ESCAPE '#'` value is `0` (it fails the predicate it was selected by), and it drops `abc_def`, which should match. `EXPLAIN` confirms the predicate is consumed into `table_name_pattern:[%#_%]` on the `MemTableScan` with no remaining `Selection`, while the CASE reference keeps a scalar `Selection`. The default-escape control (`LIKE '%\_%'` with the default backslash escape) is correct, isolating the bug to non-default `ESCAPE`.

> The same root cause affects `information_schema.cluster_log` for both `LIKE ... ESCAPE` and `REGEXP_LIKE(msg, pat, 'i')` (the extractor also ignores the regexp `match_type`); those are harder to give a self-contained repro for because they need matching log content, but they share the "extractor models only part of the operator" defect below.

### 4. What is your TiDB version? (Required)

```
Release Version: v9.0.0-beta.2.pre-1774-g81ec977cb8
Git Commit Hash: 81ec977cb8bf97e0c9805dfc0be8ffcbd4b0bbeb
UTC Build Time: 2026-05-28 03:35:30
Edition: Community
Store: tikv
```

Likely root cause — the memtable LIKE extractor drops the ESCAPE argument and hardcodes the default escape

In `pkg/planner/core/memtable_predicate_extractor.go`, the `LIKE` extractor reads only the column and the pattern constant; it does not preserve the third `ESCAPE` argument. The extracted pattern is compiled via `stringutil.CompileLike2Regexp` (`pkg/util/stringutil/string_util.go`), which uses the default backslash escape (`CompilePattern(str, '\\')`). Because the original scalar predicate is then removed from the remaining filters, the default-escape regexp becomes authoritative and diverges from the user's custom escape. The fix is to either preserve and pass the actual `ESCAPE` character into the regex compiler, or only extract `LIKE` when the escape is the default and otherwise keep the scalar recheck. The `REGEXP_LIKE` `match_type` case on `cluster_log` is the same shape (extractor records `column + pattern` only, ignoring `match_type`).

Contributor guide

Open the contributing guide

Research direction

Reproduce the information_schema.tables queries and inspect the MemTableScan plan first. Read pkg/planner/core/memtable_predicate_extractor.go alongside pkg/util/stringutil/string_util.go, focusing on how LIKE arguments reach CompileLike2Regexp. Done means custom ESCAPE semantics are preserved or rechecked, the query returns abc_def with self_true=1, and the default-escape control 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
Quiet
Clarity
Clearly specified
Newbie friendliness
63/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.