matrixorigin / matrixorigin/matrixone
[Compatibility]: QUOTE and SOUNDEX infer the wrong string domain for VARBINARY input
- 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
Assessment
This issue has not been assessed yet.