matrixorigin / matrixorigin/matrixone

[Compatibility]: SUBSTRING_INDEX truncates fractional DECIMAL counts

Open
#28,401 0 comments 0 reactions 1 assignee Claimed by @ck89119 View on GitHub
area/compatibility kind/bug severity/s0
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.