matrixorigin / matrixorigin/matrixone
[Compatibility]: string-consuming functions reject JSON operands
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
### Problem
`CONCAT`, `CONCAT_WS`, and `ELT` reject JSON operands that MySQL stringifies. This prevents direct queries as well as View/CTAS and prepared SQL over JSON columns.
### Reproduction
```sql
drop database if exists concat_json_repro;
create database concat_json_repro;
use concat_json_repro;
create table src(id int primary key,js json,v varchar(20));
insert into src values
(1,'{"b":2,"a":1}','Aé中a'),
(2,'null',''),
(3,null,null);
select id,
concat('',js),
concat(js,''),
concat_ws('|','x',js,'y'),
concat_ws('|',js,v)
from src order by id;
select elt(2,'fallback',js,'other') from src order by id;
create view v_json as
select id,concat_ws('|','x',js,'y') c from src;
prepare p from
'select id,concat_ws(''|'',''x'',js,''y'') from src order by id';
execute p;
```
### MatrixOne result
- Two-argument `CONCAT` returns `invalid argument function concat, bad value [CHAR JSON]`.
- `CONCAT_WS` returns `invalid argument function concat_ws, bad value [VARCHAR VARCHAR JSON VARCHAR]`.
- `ELT` returns `invalid argument function elt, bad value [BIGINT VARCHAR JSON VARCHAR]`.
- View creation and prepared compilation are rejected on the same JSON operand.
### MySQL 8.0.45 result
MySQL renders the JSON value using its normal JSON text representation:
```text
{"a": 1, "b": 2}
null
SQL NULL
```
`CONCAT` preserves SQL NULL. `CONCAT_WS` includes the JSON literal `null` but skips SQL NULL, matching its ordinary NULL rules. `ELT` returns the selected JSON text (`1` for a selected numeric JSON scalar). View, CTAS, and prepared execution retain those values.
### Control and scope
Explicit `CAST(js AS CHAR)` makes `CONCAT`, `CONCAT_WS`, and `ELT` work in MatrixOne and produces the same JSON text as MySQL. Canonical object output, numeric scalar output, JSON null, SQL NULL, mixed JSON/VARCHAR inputs, View/CTAS, and prepared SQL are covered. Three independent executions produced identical results on each database.
### Code location
The `builtInConcatCheck`, `concatWsCheck`, and `eltCheck` argument conversion paths use the generic implicit cast to `VARCHAR`; JSON has no accepted implicit conversion there even though explicit JSON-to-character conversion already supplies the required representation.
### 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.