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

[wjhuang2016]

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.

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).

[vismaytiwari]

Opened #69670 for this. I went with the conservative option from your write-up — only push the LIKE pattern down when the escape is the default backslash, and otherwise keep the predicate so it's rechecked as a scalar Selection — plus a regression test for the ESCAPE '#' case. Your diagnosis made it easy to trace, thanks for the detailed write-up. Would appreciate a review whenever someone has a moment, and happy to take any feedback.