Inconsistent results from `REGEXP_REPLACE` on a `CACHE` table
- 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
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