Bug #108357 inconsistent behavior about the cast function and modify column

• Submitted: 1 Sep 2022 4:10
• Modified: 5 Sep 2022 4:22
• Reporter: x j
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 8.0.26, 5.7.39, 8.0.30
• OS: Any
• CPU Architecture: Any

[1 Sep 2022 4:10] x j

Description:
I assume the cast function and modify column should be consistent, but for enum to json, it does not.

How to repeat:
create table t(e enum('a'), c char(20));
insert into t values ('a', 'a');

when using the cast function, it is ok.
mysql root@localhost:test> select cast(e as json) from t;
+-----------------+
| cast(e as json) |
+-----------------+
| "a"             |
+-----------------+

but if I modify the column to JSON type, it reports an error
mysql> alter table t modify column e json;
ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 0 in value for column '#sql-176_18.e'.

well, for comparison, it is consistent for char type, it always reports an error

mysql> select cast(c as json) from t;
ERROR 3141 (22032): Invalid JSON text in argument 1 to function cast_as_json: "Invalid value." at position 0.

mysql> alter table t modify column c json;
ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 0 in value for column '#sql-176_18.c'.

[1 Sep 2022 4:25] MySQL Verification Team

Hello x j,

Thank you for the report and test case.

regards,
Umesh

[3 Sep 2022 9:06] huahua xu

I think that it may be not a bug. for example:

mysql> create table t(e enum('a'), c char(20));

mysql> insert into t values ('a', '"a"');

mysql> select cast(c as json) from t;
+-----------------+
| cast(c as json) |
+-----------------+
| "a"             |
+-----------------+

or other example:

mysql>  create table t(e enum('a'), c char(20));

mysql> insert into t values ('a', '10');

mysql> select cast(c as json) from t;
+-----------------+
| cast(c as json) |
+-----------------+
| 10              |
+-----------------+

[3 Sep 2022 9:22] huahua xu

The function Item_typecast_json works according to these principles:
1. The string is parsed into a JSON value, and it will report syntax error if the string value is not valid JSON.
2. All other types(except character types、NULL、Geometry types) results in a JSON document consisting of a single scalar value.

[5 Sep 2022 4:22] x j

I can accept cast and modify columns both report error or not, but now, one reports an error another one does not