Insert Json loss accuracy

[H-ZeX]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

create table tb1
(
    a json
);
insert into tb1 (a)
values (cast('{
  "a": 9223372036854775809
}' as json));

insert into tb1 (a)
values ('{
  "a": 9223372036854775809
}');

select *
from tb1;

2. What did you expect to see?

{"a": 9223372036854775809}
{"a": 9223372036854775809}

3. What did you see instead?

{"a": 9223372036854776000}
{"a": 9223372036854776000}

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

$ ./bin/tidb-server -V                                                                                                  
Release Version: v3.0.0-rc.1-300-gbd4130d1f
Git Commit Hash: bd4130d1f34944691126c29d7d72246ca5cbd36c
Git Branch: master
UTC Build Time: 2019-07-09 11:46:41
GoVersion: go version go1.12 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false

[H-ZeX]

@bb7133

[bb7133]

Got it

[H-ZeX]

add these statements as the first two statements ConvertJSONToFloat

	fmt.Println("----------------call convert json to float----------------")
	fmt.Println(j.Type(), j.TypeCode == json.TypeCodeUint64)

case one

drop table tb4;
create table `tb4`
(
    `a` json,
    `b` double
);
insert into tb4 (a, b)
values ('9223372036854775808', 9223372036854775808);

select *
from tb4
where a = b;

The log TiDB print is

----------------call convert json to float----------------
DOUBLE false

case two

drop table tb4;
create table `tb4`
(
    `a` json,
    `b` double
);
insert into tb4 (a, b)
values ('9223372036854775807', 9223372036854775807);

select *
from tb4
where a = b;

The log TiDB print is

----------------call convert json to float----------------
INTEGER false

[crazycs520]

@H-ZeX Can we close this issue now?

[H-ZeX]

@crazycs520 no, the bug has not fixed, this pr(#11355) is not to fix this issue

[ghost]

Here is a pasteable testcase showing the issue is still present in master:

DROP TABLE IF EXISTS t4;
CREATE TABLE t4 (a json);
INSERT INTO t4 (a) values (cast('{"a": 9223372036854775809}' as json));
INSERT INTO t4 (a) values ('{"a": 9223372036854775809}');
SELECT * FROM t4;
SELECT tidb_version()\G

..

tidb> SELECT * FROM t4;
+----------------------------+
| a                          |
+----------------------------+
| {"a": 9223372036854776000} |
| {"a": 9223372036854776000} |
+----------------------------+
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)

See also #11489 -- a different set of type conversion problems with JSON, that should probably be fixed at the same time.

[morgo]

This is a duplicate of #21619 - I have verified it will be fixed in #21619