tidb icon indicating copy to clipboard operation
tidb copied to clipboard
verified publisher icon pingcap

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

Open xiekeyi98 opened this issue 7 years ago • 6 comments

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));
  1. 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>
  1. 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 avatar Apr 02 '19 05:04 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));

xiekeyi98 avatar Apr 02 '19 05:04 xiekeyi98

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)

ghost avatar Jul 13 '20 19:07 ghost

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

lzmhhh123 avatar Dec 08 '20 05:12 lzmhhh123

image image

The difference json type between MySQL 8 and TiDB.

lzmhhh123 avatar Dec 08 '20 06:12 lzmhhh123

reference:

jianzhiyao avatar May 18 '21 12:05 jianzhiyao

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

morgo avatar Aug 09 '21 18:08 morgo

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 avatar Sep 01 '22 08:09 YangKeao

It's quite amazing that it doesn't have the problem for https://github.com/pingcap/tidb/issues/37443, the json_type keeps to be DATETIME even after saving to disk.

YangKeao avatar Sep 01 '22 08:09 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.

YangKeao avatar Sep 02 '22 10:09 YangKeao