matrixorigin / matrixorigin/matrixone
[Compatibility]: SUBSTRING_INDEX truncates fractional DECIMAL counts
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
### Is there an existing issue for the same bug?
- [x] I have checked the existing issues.
No issue was found for fractional DECIMAL count handling in `SUBSTRING_INDEX()`.
### Branch Name
main
### Commit ID
`f72ca9efbeb3a7c673701e4a135cc6670c33cfde`
### Other Environment Information
- Local standalone MatrixOne
- Reference: MySQL 8.3.0
### Actual Behavior
`SUBSTRING_INDEX(str, delim, count)` truncates a DECIMAL `count` toward zero in MatrixOne. MySQL converts the DECIMAL count to the nearest integer, so fractional counts at and above `.5` select a different number of delimited components.
```sql
SELECT
SUBSTRING_INDEX('a,b,c,d', ',', CAST(1.4 AS DECIMAL(4,1))),
SUBSTRING_INDEX('a,b,c,d', ',', CAST(1.5 AS DECIMAL(4,1))),
SUBSTRING_INDEX('a,b,c,d', ',', CAST(1.9 AS DECIMAL(4,1))),
SUBSTRING_INDEX('a,b,c,d', ',', CAST(2.5 AS DECIMAL(4,1)));
```
```text
MatrixOne: a, a, a, a,b
MySQL 8.3: a, a,b, a,b, a,b,c
```
The negative direction is affected in the same way:
```sql
SELECT
SUBSTRING_INDEX('a,b,c,d', ',', CAST(-1.5 AS DECIMAL(4,1))),
SUBSTRING_INDEX('a,b,c,d', ',', CAST(-1.9 AS DECIMAL(4,1))),
SUBSTRING_INDEX('a,b,c,d', ',', CAST(-2.5 AS DECIMAL(4,1)));
```
```text
MatrixOne: d, d, c,d
MySQL 8.3: c,d, c,d, b,c,d
```
The difference also occurs for DECIMAL table columns and for a server-side prepared statement executed with a DECIMAL session variable:
```sql
CREATE TABLE t(id INT, n DECIMAL(4,1));
INSERT INTO t VALUES (1,1.4),(2,1.5),(3,1.9),(4,-1.5);
SELECT id, SUBSTRING_INDEX('a,b,c,d', ',', n) FROM t ORDER BY id;
PREPARE stmt FROM 'SELECT SUBSTRING_INDEX("a,b,c,d", ",", ?)';
SET @v=CAST(1.5 AS DECIMAL(4,1));
EXECUTE stmt USING @v;
```
For the prepared execution, MatrixOne returns `a`; MySQL returns `a,b`.
Each scalar form was repeated three times.
### Expected Behavior
A DECIMAL `count` should use the same integer conversion as MySQL before `SUBSTRING_INDEX()` selects components. Values at and above the half boundary should not be truncated toward zero.
### Controls and scope
- Integer counts, including negative, zero, one, two, and counts larger than the number of delimiters, agree with MySQL.
- `NULL` remains `NULL`.
- DECIMAL `1.4` and `-1.4`, which remain one after nearest-integer conversion, agree with MySQL.
- MySQL 8.3 shows context-dependent behavior for explicitly cast DOUBLE constants versus DOUBLE columns. That ambiguous DOUBLE behavior is deliberately excluded from this issue; the reproduction relies only on DECIMAL, whose constant, column, and prepared paths are consistent.
### Root cause
`SUBSTRING_INDEX` routes noninteger numeric input through its FLOAT64 overload. `getCount()` converts FLOAT64 with Go's `int64(v)`, which truncates toward zero.
Contributor guide
Assessment
This issue has not been assessed yet.