matrixorigin / matrixorigin/matrixone

[Compatibility]: QUOTE and SOUNDEX infer the wrong string domain for VARBINARY input

Open
#28,912 0 comments 0 reactions 1 assignee Claimed by @XuPeng-SH View on GitHub
area/compatibility kind/bug needs-triage
Dominant language
Go
Stars
1.9k
Forks
311
Avg merge
1d 3h
Merged PRs (30d)
768

Description

### Problem

`QUOTE(VARBINARY)` and `SOUNDEX(VARBINARY)` expose the opposite string domain from MySQL:

- MatrixOne keeps `QUOTE` in the binary domain, while MySQL returns a nonbinary `utf8mb4` string.
- MatrixOne converts `SOUNDEX` to a nonbinary `utf8mb4` string, while MySQL preserves the binary domain.

The difference is visible through View/CTAS metadata. For `QUOTE`, it also changes execution semantics for invalid UTF-8 bytes: MatrixOne returns a binary quoted value containing the invalid byte, whereas MySQL rejects conversion to `utf8mb4`.

### Reproduction

```sql
CREATE DATABASE quote_soundex_binary;
USE quote_soundex_binary;

CREATE TABLE src(id INT PRIMARY KEY, b VARBINARY(64));
INSERT INTO src VALUES
(1, CAST('Ashcraft' AS BINARY)),
(2, UNHEX('410042')),
(3, UNHEX('C3A9')),
(4, UNHEX('41FF42')),
(5, ''),
(6, NULL);

CREATE VIEW vquote AS SELECT QUOTE(b) q FROM src;
CREATE VIEW vsoundex AS SELECT SOUNDEX(b) s FROM src;
CREATE TABLE cquote AS SELECT QUOTE(b) q FROM src WHERE id IN (1,3,5,6);
CREATE TABLE csoundex AS SELECT SOUNDEX(b) s FROM src;

SELECT table_name,column_name,data_type,column_type,
character_maximum_length,character_set_name,collation_name
FROM information_schema.columns
WHERE table_schema=DATABASE()
AND table_name IN ('vquote','vsoundex','cquote','csoundex')
ORDER BY table_name;

SELECT HEX(QUOTE(b)) FROM src WHERE id=4;
```

### MatrixOne result

```text
vquote/cquote.q VARBINARY(130), binary/binary
vsoundex/csoundex.s VARCHAR(65535), utf8mb4/utf8mb4_general_ci

HEX(QUOTE(X'41FF42')) = 2741FF4227
```

### MySQL 8.0.45 result

```text
vquote/cquote.q VARCHAR(130), utf8mb4/utf8mb4_0900_ai_ci
vsoundex/csoundex.s VARBINARY(64), binary/binary

QUOTE(X'41FF42') -> ERROR 3854:
Cannot convert string 'A\xFFB' from binary to utf8mb4
```

### Controls and scope

- ASCII binary input, valid UTF-8 binary input, embedded NUL, invalid UTF-8, empty string, and SQL NULL were covered.
- For inputs accepted by both systems, `QUOTE` escaping and `SOUNDEX` values match.
- Direct table evaluation, View, CTAS, and prepared parameter reuse were covered.
- Each database run was repeated three times with identical results.
- The difference is not the known collation-comparison defect: `information_schema.columns` reports different binary/nonbinary result domains before any comparison is evaluated.

### Code location

`QUOTE` uses `quoteReturnType`, which preserves the source binary domain in MatrixOne. `SOUNDEX` resolves VARBINARY through its VARCHAR overload and `soundexReturnType` produces a nonbinary result. Both choices differ from MySQL's observable result metadata.

### Environment

- MatrixOne: official `main` commit `0c3a04f390adaf6281fd592a49778ea5b1155e67`
- MySQL control: 8.0.45
- Local single-node MatrixOne deployment

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.