pingcap / pingcap/tidb

Grouped `REPEAT` over a `WITH` CTE returns a string; the same query on the table returns NULL

Open
#70,493 3 comments 0 reactions 0 assignees View on GitHub
affects-25.10 affects-26.3 affects-7.5 affects-8.1 affects-8.5 component/expression contribution severity/major sig/execution type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

`REPEAT(name, 5592406)` on VARCHAR `'abc'` is 16 777 218 bytes, one past
`mysql.MaxBlobWidth` (16 777 216). Vectorized `REPEAT`
(`builtin_string_vec.go`) NULLs that call via `len(str) > Flen/num`. Scalar
`REPEAT` (`builtin_string.go` `evalString`) only checks `max_allowed_packet`
(64 MiB here) and **produces the string**.

On a plain table, `GROUP BY name` plus `HAVING MAX(id) <= (SELECT COUNT(*) FROM t)`
takes the vectorized path: `CAST(… AS CHAR(255))` is NULL. Replace the table with
the identity CTE

### 1. Minimal reproduce step (Required)
```sql
CREATE TABLE t__base (id BIGINT, name VARCHAR(255));
INSERT INTO t__base VALUES (2, 'abc'), (42, '');
CREATE VIEW t AS WITH t__base_cte_1 AS (SELECT * FROM t__base) SELECT * FROM t__base_cte_1;

SELECT name,
CAST(IF(name = name, REPEAT(name, 5592406), REGEXP_INSTR(name, '.')) AS CHAR(255)) IS NULL AS is_null,
CHAR_LENGTH(CAST(IF(name = name, REPEAT(name, 5592406), REGEXP_INSTR(name, '.')) AS CHAR(255))) AS clen
FROM t
GROUP BY name
HAVING MAX(id) <= (SELECT COUNT(*) FROM t);
-- Expected: ('abc', 1, NULL)
-- Actual: ('abc', 0, 255) -- WRONG
```

This is correct, selecting from a table instead of a view:
```sql
CREATE TABLE t__base (id BIGINT, name VARCHAR(255));
INSERT INTO t__base VALUES (2, 'abc'), (42, '');
CREATE TABLE t LIKE t__base;
INSERT INTO t SELECT * FROM t__base;

SELECT name,
CAST(IF(name = name, REPEAT(name, 5592406), REGEXP_INSTR(name, '.')) AS CHAR(255)) IS NULL AS is_null,
CHAR_LENGTH(CAST(IF(name = name, REPEAT(name, 5592406), REGEXP_INSTR(name, '.')) AS CHAR(255))) AS clen
FROM t
GROUP BY name
HAVING MAX(id) <= (SELECT COUNT(*) FROM t);
-- Expected: ('abc', 1, NULL)
-- Actual: ('abc', 1, NULL)
```

### 2. What did you expect to see? (Required)
| Query | Expected | Actual |
|---|---|---|
| table, distilled `IF`/`REPEAT`/`REGEXP_INSTR` + `HAVING` | NULL (`IS NULL` = 1) | NULL |
| CTE view, same query | NULL (same rows) | **255-char string** |
| query-level `WITH t AS (SELECT * FROM t__base)`, same query | NULL | **255-char string** |
| identity `VIEW` (no `WITH`) | NULL | NULL |
| `REPEAT(name, 5592405)` (3·n = 16 777 215 ≤ MaxBlobWidth) | 255 on both | 255 on both |
| `IF(TRUE, REPEAT(…), REGEXP_INSTR(…))` | NULL on both | NULL on both |
| `ELSE NULL` | NULL on both | NULL on both |
| no `HAVING` | `'abc'` NULL and `''` length 0 on both | both agree |

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

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

TiDB v9.0.0-beta.2.pre @ 3bea8196a5

Contributor guide

Open the contributing guide

Research direction

Run the minimal CTE/view and plain-table queries, then compare the vectorized path in builtin_string_vec.go with scalar REPEAT handling in builtin_string.go evalString. Trace how GROUP BY, HAVING, and the CTE affect evaluation; done means the CTE and table forms both return the documented NULL result for the oversized REPEAT cases.

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
Mostly clear
Newbie friendliness
48/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.