| Bug #101945 | JSON Unsigned Integer out of range for Unsigned Bigint column | ||
|---|---|---|---|
| Submitted: | 9 Dec 2020 22:03 | Modified: | 10 Dec 2020 5:01 |
| Reporter: | Morgan Tocker | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: JSON | Severity: | S3 (Non-critical) |
| Version: | 5.7.31, 8.0.22 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[10 Dec 2020 1:40]
Morgan Tocker
This is another variant that is useful to show the issue. I would expect that both cases should work the same:
DROP TABLE IF EXISTS t3;
CREATE TABLE t3 (a bigint unsigned as (b->"$.a"), b json);
INSERT INTO t3 (b) VALUES ('{"a":9223372036854775808}'); # fails
INSERT INTO t3 (b) VALUES ('{"a":"9223372036854775808"}'); # fails
SELECT * FROM t3;
DROP TABLE IF EXISTS t4;
CREATE TABLE t4 (a bigint unsigned as (b->>"$.a"), b json);
INSERT INTO t4 (b) VALUES ('{"a":9223372036854775808}'); # works
INSERT INTO t4 (b) VALUES ('{"a":"9223372036854775808"}'); # works
SELECT * FROM t4;
[10 Dec 2020 5:01]
MySQL Verification Team
Hello Morgan, Thank you for the report and feedback. regards, Umesh
Description: I tried inserting the JSON value of 9223372036854775808 into a bigint unsigned column. It failed with an error: mysql [localhost:8022] {msandbox} (test) > insert into tb8 (a) values (cast(9223372036854775808 as json)); -- fails ERROR 1264 (22003): Out of range value for column 'a' at row 1 How to repeat: Here is the complete testsuite: drop table if exists tb8; create table tb8 (a bigint unsigned); insert into tb8 (a) values (9223372036854775808); -- works insert into tb8 (a) values (cast(9223372036854775808 as json)); -- fails insert into tb8 (a) values (cast(cast(9223372036854775808 as json) as unsigned)); -- works SELECT JSON_TYPE( cast(9223372036854775808 as json) ); SELECT cast(9223372036854775808 as json) INTO @a; insert into tb8 (a) values (@a); -- works SELECT * FROM tb8; Suggested fix: I thought this might be an issue because JSON itself has strange numeric values. That's not the case though, I can create a json type and represent this value no problems: mysql [localhost:8022] {msandbox} (test) > drop table if exists tjson; Query OK, 0 rows affected (0.01 sec) mysql [localhost:8022] {msandbox} (test) > create table tjson (a json); Query OK, 0 rows affected (0.00 sec) mysql [localhost:8022] {msandbox} (test) > INSERT INTO tjson VALUES ('{"a": 9223372036854775808}'); Query OK, 1 row affected (0.00 sec) mysql [localhost:8022] {msandbox} (test) > SELECT * FROM tjson; +----------------------------+ | a | +----------------------------+ | {"a": 9223372036854775808} | +----------------------------+ 1 row in set (0.00 sec)