pingcap / pingcap/tidb

Inconsistent results from `REGEXP_REPLACE` on a `CACHE` table

Open
#70,458 3 comments 0 reactions 0 assignees View on GitHub
contribution severity/moderate sig/execution type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

Reading a CACHE table flips REGEXP_REPLACE's NULL short-circuit off, turning a correct per-row NULL into a spurious "Empty pattern is invalid" error, for the same query against the same unchanging data, on repeat execution.

### 1. Minimal reproduce step (Required)

```sql
CREATE TABLE t_tbl (c_pk BIGINT, c_txt VARCHAR(255), c_big BIGINT, c_date DATE);
INSERT INTO t_tbl VALUES (1, '', NULL, '2030-06-01'), (2, 'abc', 5, '2024-01-15');
ALTER TABLE t_tbl CACHE;
CREATE VIEW t AS SELECT * FROM t_tbl;

SELECT c_date FROM t WHERE REGEXP_REPLACE(647356755, c_txt, c_big);
-- Call 1 (sometimes call 2 as well): 1 row, (2024-01-15,). -- CORRECT
-- Call 2 (or 3) onward, forever after, from ANY connection:
-- ERROR 1139 (HY000): Got error 'Empty pattern is invalid' from regexp
```

Without caching the table, the error doesn't occur
```sql
CREATE TABLE a_tbl (c_pk BIGINT, c_txt VARCHAR(255), c_big BIGINT, c_date DATE);
INSERT INTO a_tbl VALUES (1, '', NULL, '2030-06-01'), (2, 'abc', 5, '2024-01-15');
CREATE VIEW a AS SELECT * FROM a_tbl;
SELECT c_date FROM a WHERE REGEXP_REPLACE(647356755, c_txt, c_big); -- always 1 row, clean.
```

### 2. What did you expect to see? (Required)
Please see the repro
### 3. What did you see instead (Required)
Please see the repro
### 4. What is your TiDB version? (Required)
`tidb 8.0.11-TiDB-v9.0.0-beta.2.pre-2051-g3bea8196a5 @ 3bea8196a5`

Contributor guide

Open the contributing guide

Research direction

Start by running the provided SQL reproduction against TiDB 8.0.11 to confirm the behavior with and without ALTER TABLE ... CACHE. Trace the interaction between cached-table reads and REGEXP_REPLACE NULL handling; done means repeated executions from any connection consistently return the expected row without the spurious empty-pattern error.

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
52/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.