matrixorigin / matrixorigin/matrixone

[Compatibility]: string-consuming functions reject JSON operands

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

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.