json cast for enum is not same with MySQL

[xiekeyi98]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do? If possible, provide a recipe for reproducing the error.

drop table if exists t,blobs,tinyblobs,mediumblobs,longblobs,year,varbin,bin,enum,sett,varchar_binary;
create table t(c varchar(30) not null, j json, key(c));
create table blobs(b blob); insert into blobs values(x'cafebabe');
create table tinyblobs(b tinyblob); insert into tinyblobs values(x'cafebabe');
create table mediumblobs(b mediumblob); insert into mediumblobs values(x'cafebabe');
create table longblobs(b longblob); insert into longblobs values(x'cafebabe');
create table year(y year); insert into year values('1992');
create table varbin(b varbinary(40)); insert into varbin values(x'cafebabe');
create table bin(b binary(40)); insert into varbin values(x'cafebabe');
create table enum(e enum('a', 'b', 'c')); insert into enum values ('b');
create table sett(e set('a', 'b', 'c')); insert into sett values ('b,c');
create table varchar_binary(c varchar(30) character set 'binary'); insert into varchar_binary values ('foo');
insert into t(c,j) (select 'opaque_mysql_type_enum', cast(e as json) from enum);

2. What did you expect to see?

In MySQL

mysql> create table t(c varchar(30) not null, j json, key(c));
Query OK, 0 rows affected (0.02 sec)

mysql> create table blobs(b blob); insert into blobs values(x'cafebabe');
Query OK, 0 rows affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

mysql> create table tinyblobs(b tinyblob); insert into tinyblobs values(x'cafebabe');
Query OK, 0 rows affected (0.02 sec)

Query OK, 1 row affected (0.00 sec)

mysql> create table mediumblobs(b mediumblob); insert into mediumblobs values(x'cafebabe');
Query OK, 0 rows affected (0.03 sec)

Query OK, 1 row affected (0.01 sec)

mysql> create table longblobs(b longblob); insert into longblobs values(x'cafebabe');
Query OK, 0 rows affected (0.03 sec)

Query OK, 1 row affected (0.00 sec)

mysql> create table year(y year); insert into year values('1992');
Query OK, 0 rows affected (0.01 sec)

Query OK, 1 row affected (0.01 sec)

mysql> create table varbin(b varbinary(40)); insert into varbin values(x'cafebabe');
Query OK, 0 rows affected (0.01 sec)

Query OK, 1 row affected (0.01 sec)

mysql> create table bin(b binary(40)); insert into varbin values(x'cafebabe');
Query OK, 0 rows affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

mysql> create table enum(e enum('a', 'b', 'c')); insert into enum values ('b');
Query OK, 0 rows affected (0.01 sec)

Query OK, 1 row affected (0.01 sec)

mysql> create table sett(e set('a', 'b', 'c')); insert into sett values ('b,c'); 
Query OK, 0 rows affected (0.02 sec)

Query OK, 1 row affected (0.00 sec)

mysql> create table varchar_binary(c varchar(30) character set 'binary'); insert into varchar_binary values ('foo');
Query OK, 0 rows affected (0.02 sec)

Query OK, 1 row affected (0.01 sec)

mysql> insert into t(c,j) (select 'opaque_mysql_type_enum', cast(e as json) from enum);
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> 
mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.7.25-0ubuntu0.18.04.2 |
+-------------------------+
1 row in set (0.00 sec)

3. What did you see instead?

In TiDB

Database changed
mysql> drop table if exists t,blobs,tinyblobs,mediumblobs,longblobs,year,varbin,bin,enum,sett,varchar_binary;
Query OK, 0 rows affected (0.99 sec)

mysql> create table t(c varchar(30) not null, j json, key(c));
Query OK, 0 rows affected (0.07 sec)

mysql> create table blobs(b blob); insert into blobs values(x'cafebabe');
Query OK, 0 rows affected (0.06 sec)

Query OK, 1 row affected (0.01 sec)

mysql> create table tinyblobs(b tinyblob); insert into tinyblobs values(x'cafebabe');
Query OK, 0 rows affected (0.11 sec)

Query OK, 1 row affected (0.01 sec)

mysql> create table mediumblobs(b mediumblob); insert into mediumblobs values(x'cafebabe');
Query OK, 0 rows affected (0.07 sec)

Query OK, 1 row affected (0.01 sec)

mysql> create table longblobs(b longblob); insert into longblobs values(x'cafebabe');
Query OK, 0 rows affected (0.07 sec)

Query OK, 1 row affected (0.01 sec)

mysql> create table year(y year); insert into year values('1992');
Query OK, 0 rows affected (0.10 sec)

Query OK, 1 row affected (0.00 sec)

mysql> create table varbin(b varbinary(40)); insert into varbin values(x'cafebabe');
Query OK, 0 rows affected (0.09 sec)

Query OK, 1 row affected (0.01 sec)

mysql> create table bin(b binary(40)); insert into varbin values(x'cafebabe');
Query OK, 0 rows affected (0.04 sec)

Query OK, 1 row affected (0.01 sec)

mysql> create table enum(e enum('a', 'b', 'c')); insert into enum values ('b');
Query OK, 0 rows affected (0.03 sec)

Query OK, 1 row affected (0.00 sec)

mysql> create table sett(e set('a', 'b', 'c')); insert into sett values ('b,c'); 
Query OK, 0 rows affected (0.03 sec)

Query OK, 1 row affected (0.01 sec)

mysql> create table varchar_binary(c varchar(30) character set 'binary'); insert into varchar_binary values ('foo');
Query OK, 0 rows affected (0.02 sec)

Query OK, 1 row affected (0.01 sec)

mysql> insert into t(c,j) (select 'opaque_mysql_type_enum', cast(e as json) from enum);
ERROR 3140 (22032): Invalid JSON text: invalid character 'b' looking for beginning of value
mysql> 
mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v3.0.0-beta-323-gad752611c
Git Commit Hash: ad752611c86940a6e5c8f7afd7bb2184b2d7777b
Git Branch: master
UTC Build Time: 2019-04-02 09:01:54
GoVersion: go version go1.12.1 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false
1 row in set (0.00 sec)

mysql> 

[xiekeyi98]

 insert into t(c,j) (select 'opaque_mysql_type_bit' ,cast(x'cafe' as json));
 insert into t(c,j) (select 'opaque_mysql_type_blob' ,cast(b as json) from blobs);
 insert into t(c,j) (select 'opaque_mysql_type_longblob',cast(b as json) from longblobs);
 insert into t(c,j) (select 'opaque_mysql_type_mediumblob',cast(b as json) from mediumblobs);  
 insert into t(c,j) (select 'opaque_mysql_type_tinyblob' ,cast(b as json) from tinyblobs);
insert into t(c,j) (select 'opaque_mysql_type_varchar' ,cast(c as json) from varchar_binary);

Also should be tested.

[winkyao]

@bb7133 Please take a look

[ghost]

Confirming this issue can still be reproduced:

..
mysql> insert into t(c,j) (select 'opaque_mysql_type_enum', cast(e as json) from enum);
ERROR 3140 (22032): Invalid JSON text: The document root must not be followed by other values.
mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-859-gccfc9b2ad
Edition: Community
Git Commit Hash: ccfc9b2ad0dcf8e447210de5f559d7fc208db968
Git Branch: master
UTC Build Time: 2020-07-29 09:37:45
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

[xiongjiwei]

MySQL has confirmed it is a bug. see https://bugs.mysql.com/bug.php?id=108357