Convert json to uint different from mysql

[H-ZeX]

Description

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

difference between mysql and TiDB

drop table tb8;
create table tb8
(
    a bigint(64) unsigned
);
insert into tb8 (a)
values (9223372036854775808);

select *
from tb8;

insert into tb8 (select cast(a as json) as aj
                 from tb8);

when running it in mysql, the insert into tb8 (select cast(a as json) as aj from tb8); will report out of range error. But in TiDB, it will not report error. This seems like a bug of mysql.

something not ok in TiDB's implement

in types/convert.go::ConvertJSONToInt

	case json.TypeCodeInt64, json.TypeCodeUint64:
		return j.GetInt64(), nil

when the type is json.TypeCodeUint64, it doesn't check whether the number extract from json bigger than i64::MAX if signed.

error msg is not clear

In this function ConvertJSONToInt, when report overflow error msg, the error msg don't contain whether the type is unsigned.

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

Release Version: v3.0.0-rc.1-415-g54056faf4-dirty
Git Commit Hash: 54056faf44d800268d67cf9968427c1d8017fd66
Git Branch: fix-ConvertJSONToInt-error-msg-error
UTC Build Time: 2019-07-29 03:47:56
GoVersion: go version go1.12.6 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false

SIG slack channel

#sig-exec

Score

300

Mentor

• @lzmhhh123

[crazycs520]

I think there is some compatible problem between TiDB and MySQL.
In TiDB:

mysql>create table t (a bigint as (json_extract(b,'$.a')), b json);
Query OK, 0 rows affected
Time: 0.006s
mysql>insert into t set b='{"a":"9223372036854775808"}'
(1690, u"BIGINT value is out of range in '9223372036854775808'")
mysql>insert into t set b='{"a":9223372036854775808}'
Query OK, 1 row affected
Time: 0.004s
mysql>select * from t;
+---------------------+----------------------------+
| a                   | b                          |
+---------------------+----------------------------+
| 9223372036854775807 | {"a": 9223372036854776000} |
+---------------------+----------------------------+
1 row in set
Time: 0.007s
mysql>create table tu (a bigint unsigned as (json_extract(b,'$.a')), b json);

Query OK, 0 rows affected
Time: 0.006s
mysql>insert into tu set b='{"a":9223372036854775808}'
Query OK, 1 row affected
Time: 0.004s
mysql>insert into tu set b='{"a":"9223372036854775808"}';
(1690, u"BIGINT value is out of range in '9223372036854775808'")
mysql>select * from t;
+---------------------+----------------------------+
| a                   | b                          |
+---------------------+----------------------------+
| 9223372036854775807 | {"a": 9223372036854776000} |
+---------------------+----------------------------+
1 row in set
Time: 0.006s

In MySQL

mysql>create table t (a bigint as (json_extract(b,'$.a')), b json);
Query OK, 0 rows affected
Time: 0.096s
mysql>insert into t set b='{"a":"9223372036854775808"}'
Query OK, 1 row affected
Time: 0.082s
mysql>insert into t set b='{"a":9223372036854775808}'
Query OK, 1 row affected
Time: 0.063s
mysql>select * from t;
+----------------------+------------------------------+
| a                    | b                            |
+----------------------+------------------------------+
| -9223372036854775808 | {"a": "9223372036854775808"} |
| -9223372036854775808 | {"a": 9223372036854775808}   |
+----------------------+------------------------------+
2 rows in set
Time: 0.007s
mysql>create table tu (a bigint unsigned as (json_extract(b,'$.a')), b json);
Query OK, 0 rows affected
Time: 0.075s
mysql>insert into tu set b='{"a":9223372036854775808}'
(1264, u"Out of range value for column 'a' at row 1")
mysql>insert into tu set b='{"a":"9223372036854775808"}'
(1264, u"Out of range value for column 'a' at row 1")

[crazycs520]

@XuHuaiyu PTAL

[ghost]

Here is a pasteable test case confirming all 3 issues still present in master:

DROP TABLE IF EXISTS t1, t2, t3;
CREATE TABLE t1 (a bigint(64) unsigned);
CREATE TABLE t2 (a bigint as (json_extract(b,'$.a')), b json);
CREATE TABLE t3 (a bigint unsigned as (json_extract(b,'$.a')), b json);

INSERT INTO t1 (a) VALUES (9223372036854775808);
INSERT INTO t1 (select cast(a as json) as aj FROM tb8);

INSERT INTO t2 (b) VALUES ('{"a":"9223372036854775808"}');
INSERT INTO t2 (b) VALUES ('{"a":9223372036854775808}');


INSERT INTO t3 (b) VALUES ('{"a":9223372036854775808}');
INSERT INTO t3 (b) VALUES ('{"a":"9223372036854775808"}');

SELECT * FROM t1;
SELECT * FROM t2;
SELECT * FROM t3;

SELECT tidb_version()\G

The output of the select statements only:

mysql8020> SELECT * FROM t1;
+---------------------+
| a                   |
+---------------------+
| 9223372036854775808 |
+---------------------+
1 row in set (0.00 sec)

mysql8020> SELECT * FROM t2;
+----------------------+------------------------------+
| a                    | b                            |
+----------------------+------------------------------+
| -9223372036854775808 | {"a": "9223372036854775808"} |
| -9223372036854775808 | {"a": 9223372036854775808}   |
+----------------------+------------------------------+
2 rows in set (0.00 sec)

mysql8020> SELECT * FROM t3;
Empty set (0.00 sec)

tidb> SELECT * FROM t1;
+---------------------+
| a                   |
+---------------------+
| 9223372036854775808 |
| 9223372036854775808 |
| 9223372036854775808 |
+---------------------+
3 rows in set (0.01 sec)

tidb> SELECT * FROM t2;
+---------------------+----------------------------+
| a                   | b                          |
+---------------------+----------------------------+
| 9223372036854775807 | {"a": 9223372036854776000} |
+---------------------+----------------------------+
1 row in set (0.00 sec)

tidb> SELECT * FROM t3;
+---------------------+------------------------------+
| a                   | b                            |
+---------------------+------------------------------+
| 9223372036854775808 | {"a": 9223372036854776000}   |
| 9223372036854775808 | {"a": "9223372036854775808"} |
+---------------------+------------------------------+
2 rows in set (0.00 sec)

tidb> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-745-g2b0b34b88
Edition: Community
Git Commit Hash: 2b0b34b88e43ad20f4e5ab1a0b5daf7ae6ff6046
Git Branch: master
UTC Build Time: 2020-07-09 10:12:32
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)

[morgo]

There are a few different bugs here:

1. Inserting an integer larger than int64.MaxValue but less than uint64.MaxValue leads to incorrect values (you can see this in the t2 table result above). Forked as Cast int string to JSON results in wrong type #21619

2. Inserting bigint unsigned values into json tables that produce bigint signed virtual columns is permitted in MySQL. The value is expected to wrap to the signed value (don't ask me if this is a good idea, that's the behavior in this case, yet in other cases like character length it will produce an error). Forked as generated columns do not wrap unsigned values to signed #21654 - possible MySQL bug.

There is also what I believe to be two non bugs which I have reported as https://bugs.mysql.com/bug.php?id=101945

[morgo]

The MySQL team has verified the bugs reported. I will leave this issue open for a while in case the status changes, but my intention is to close this as a 'duplicate'. The forked issues better isolate the issues discovered.

[XuHuaiyu]

@morgo If this is a 'duplicate', I think we can close it.

[morgo]

I agree, I am going to close this issue now. The remaining issues are forked into #21619 and #21654.