JSON data type does not support custom types (i.e. MySQL types)

[xiekeyi98]

Description

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.

select json_type(cast(cast('2014-11-25 18:00' as datetime) as json));
select json_type(cast(cast('2014-11-25' as date) as json));
select json_type(cast(cast('18:00:59' as time) as json));

2. What did you expect to see?

In MySQL

mysql> select json_type(cast(cast('2014-11-25 18:00' as datetime) as json));
+---------------------------------------------------------------+
| json_type(cast(cast('2014-11-25 18:00' as datetime) as json)) |
+---------------------------------------------------------------+
| DATETIME                                                      |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_type(cast(cast('2014-11-25' as date) as json));
+-----------------------------------------------------+
| json_type(cast(cast('2014-11-25' as date) as json)) |
+-----------------------------------------------------+
| DATE                                                |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_type(cast(cast('18:00:59' as time) as json));
+---------------------------------------------------+
| json_type(cast(cast('18:00:59' as time) as json)) |
+---------------------------------------------------+
| TIME                                              |
+---------------------------------------------------+
1 row in set (0.01 sec)

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

mysql> 

3. What did you see instead?

In TiDB

mysql> select json_type(cast(cast('2014-11-25 18:00' as datetime) as json));
+---------------------------------------------------------------+
| json_type(cast(cast('2014-11-25 18:00' as datetime) as json)) |
+---------------------------------------------------------------+
| STRING                                                        |
+---------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> select json_type(cast(cast('2014-11-25' as date) as json));
+-----------------------------------------------------+
| json_type(cast(cast('2014-11-25' as date) as json)) |
+-----------------------------------------------------+
| STRING                                              |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_type(cast(cast('18:00:59' as time) as json));
+---------------------------------------------------+
| json_type(cast(cast('18:00:59' as time) as json)) |
+---------------------------------------------------+
| STRING                                            |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v3.0.0-beta-321-gc6a757b24
Git Commit Hash: c6a757b2435965a72ada9d61ea8b7a367bd89b5f
Git Branch: master
UTC Build Time: 2019-04-02 02:38:04
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> 

SIG slack channel

#sig-exec

Score

300

Mentor

• @lzmhhh123

[xiekeyi98]

Others may be tested too:

select json_type(cast(127 as json));
select json_type(cast(255 as json));
select json_type(cast(32767 as json));
select json_type(cast(65535 as json));
select json_type(cast(8388607 as json));
select json_type(cast(16777215 as json));
select json_type(cast(2147483647 as json));
select json_type(cast(4294967295 as json));
select json_type(cast(9223372036854775807 as json));
select json_type(cast(18446744073709551615 as json));
select json_type(cast(true as json));
select json_type(cast(b'10101' as json));
select json_type(cast(cast(3.14 as decimal(5,2)) as json));
select json_type(cast(3.14 as json));
select json_type(cast(3.14E30 as json));

[ghost]

Confirming this still exists in master. I've noted which values are correct:

select json_type(cast(cast('2014-11-25 18:00' as datetime) as json));
select json_type(cast(cast('2014-11-25' as date) as json));
select json_type(cast(cast('18:00:59' as time) as json));
select json_type(cast(127 as json));
select json_type(cast(255 as json));
select json_type(cast(32767 as json));
select json_type(cast(65535 as json));
select json_type(cast(8388607 as json));
select json_type(cast(16777215 as json));
select json_type(cast(2147483647 as json));
select json_type(cast(4294967295 as json));
select json_type(cast(9223372036854775807 as json));
select json_type(cast(18446744073709551615 as json));
select json_type(cast(true as json));
select json_type(cast(b'10101' as json));
select json_type(cast(cast(3.14 as decimal(5,2)) as json));
select json_type(cast(3.14 as json));
select json_type(cast(3.14E30 as json));
select tidb_version()\G


..


mysql> select json_type(cast(cast('2014-11-25 18:00' as datetime) as json));
+---------------------------------------------------------------+
| json_type(cast(cast('2014-11-25 18:00' as datetime) as json)) |
+---------------------------------------------------------------+
| STRING                                                        |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_type(cast(cast('2014-11-25' as date) as json));
+-----------------------------------------------------+
| json_type(cast(cast('2014-11-25' as date) as json)) |
+-----------------------------------------------------+
| STRING                                              |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_type(cast(cast('18:00:59' as time) as json));
+---------------------------------------------------+
| json_type(cast(cast('18:00:59' as time) as json)) |
+---------------------------------------------------+
| STRING                                            |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_type(cast(127 as json)); # correct
+------------------------------+
| json_type(cast(127 as json)) |
+------------------------------+
| INTEGER                      |
+------------------------------+
1 row in set (0.00 sec)

mysql> select json_type(cast(255 as json)); # correct
+------------------------------+
| json_type(cast(255 as json)) |
+------------------------------+
| INTEGER                      |
+------------------------------+
1 row in set (0.00 sec)

mysql> select json_type(cast(32767 as json)); # correct
+--------------------------------+
| json_type(cast(32767 as json)) |
+--------------------------------+
| INTEGER                        |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select json_type(cast(65535 as json)); # correct
+--------------------------------+
| json_type(cast(65535 as json)) |
+--------------------------------+
| INTEGER                        |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select json_type(cast(8388607 as json)); # correct
+----------------------------------+
| json_type(cast(8388607 as json)) |
+----------------------------------+
| INTEGER                          |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select json_type(cast(16777215 as json));  # correct
+-----------------------------------+
| json_type(cast(16777215 as json)) |
+-----------------------------------+
| INTEGER                           |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> select json_type(cast(2147483647 as json));  # correct
+-------------------------------------+
| json_type(cast(2147483647 as json)) |
+-------------------------------------+
| INTEGER                             |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> select json_type(cast(4294967295 as json));  # correct
+-------------------------------------+
| json_type(cast(4294967295 as json)) |
+-------------------------------------+
| INTEGER                             |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> select json_type(cast(9223372036854775807 as json));  # correct
+----------------------------------------------+
| json_type(cast(9223372036854775807 as json)) |
+----------------------------------------------+
| INTEGER                                      |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_type(cast(18446744073709551615 as json));  # correct
+-----------------------------------------------+
| json_type(cast(18446744073709551615 as json)) |
+-----------------------------------------------+
| UNSIGNED INTEGER                              |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_type(cast(true as json));  # correct
+-------------------------------+
| json_type(cast(true as json)) |
+-------------------------------+
| BOOLEAN                       |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select json_type(cast(b'10101' as json));
ERROR 3140 (22032): Invalid JSON text: The document root must not be followed by other values.
mysql> select json_type(cast(cast(3.14 as decimal(5,2)) as json));
+-----------------------------------------------------+
| json_type(cast(cast(3.14 as decimal(5,2)) as json)) |
+-----------------------------------------------------+
| DOUBLE                                              |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_type(cast(3.14 as json));
+-------------------------------+
| json_type(cast(3.14 as json)) |
+-------------------------------+
| DOUBLE                        |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select json_type(cast(3.14E30 as json)); # correct
+----------------------------------+
| json_type(cast(3.14E30 as json)) |
+----------------------------------+
| DOUBLE                           |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-750-g8a661044c
Edition: Community
Git Commit Hash: 8a661044cedf8daad1de4fbf79a390962b6f6c3b
Git Branch: master
UTC Build Time: 2020-07-10 10:52:37
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)

[lzmhhh123]

We didn't fully support json type. I convert the issue to type/feature.

[lzmhhh123]

The difference json type between MySQL 8 and TiDB.

[jianzhiyao]

reference:

• usage of json_type
• json type of mysql

[morgo]

I am assigning this to myself. We have an intern looking into it.

[YangKeao]

This behavior has further influence:

drop table t;
create table t(
    i INT, j JSON, da DATE
    );
insert into t values (0, '"1596-03-31"', '1596-03-31');
select i from t where cast(json_unquote(j) as DATE) = da; -- should output 0
select i from t where cast(da as json) != j; -- should output 0

In TiDB, cast(da as json) is equal with the j, which in MySQL it's not.

[YangKeao]

It's quite amazing that it doesn't have the problem for #37443, the json_type keeps to be DATETIME even after saving to disk.

[YangKeao]

I'm working on this issue. It can be split into two problems:

• Merge the json and types pkg, so that the json pkg can use the types.Time and types.Duration.
• Implement the json time and duration types, and the cast between the json and Date/Datetime/Timestamp/Time.