| Bug #90423 | JSON_TABLE returns wrong datatype when INT-value is grater than (2^31-1) | ||
|---|---|---|---|
| Submitted: | 13 Apr 2018 3:17 | Modified: | 10 Aug 2018 7:19 |
| Reporter: | Tsubasa Tanaka (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: JSON | Severity: | S2 (Serious) |
| Version: | 8.0.4 | OS: | CentOS (7.4) |
| Assigned to: | CPU Architecture: | x86 | |
[13 Apr 2018 5:20]
MySQL Verification Team
Hello Tanaka-San, Thank you for the report and feedback! Thanks, Umesh
[13 Apr 2018 5:21]
MySQL Verification Team
--
[umshastr@hod03]/export/umesh/server/binaries/Trunk/mysql-8.0.12: bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.12-commercial MySQL Enterprise Server - Commercial
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SELECT id FROM JSON_TABLE('[{"id":"2147483647"}]', '$[*]' COLUMNS (id BIGINT UNSIGNED PATH '$.id')) AS json;
+------------+
| id |
+------------+
| 2147483647 |
+------------+
1 row in set (0.01 sec)
mysql> SELECT id FROM JSON_TABLE('[{"id":"2147483648"}]', '$[*]' COLUMNS (id BIGINT UNSIGNED PATH '$.id')) AS json;
+-------------+
| id |
+-------------+
| -2147483648 |
+-------------+
1 row in set (0.00 sec)
mysql> SELECT id FROM JSON_TABLE('[{"id":"2147483648"}]', '$[*]' COLUMNS (id REAL PATH '$.id')) AS json;
+------------+
| id |
+------------+
| 2147483648 |
+------------+
1 row in set (0.00 sec)
[10 Aug 2018 7:19]
Jon Stephens
Documented fix in the 8.0.13 changelog as follows:
The JSON_TABLE() function subjected integer values greater than
or equal to 2³¹ to wraparound. For example, the query SELECT id
FROM JSON_TABLE('[{"id":"2147483648"}]', '$[*]' COLUMNS (id
BIGINT UNSIGNED PATH '$.id')) AS json returned -2147483648.
Closed.
Description: JSON_TABLE returns wrong datatype when INT-value is grater than (2^31-1). Unexpected overflowed INT SIGNED is returned. See "How to repeat" in detail. How to repeat: While "id" < (2^31 -1), JSON_TABLE returns "id" correctly. mysql80 32> SELECT id FROM JSON_TABLE('[{"id":"2147483647"}]', '$[*]' COLUMNS (id BIGINT UNSIGNED PATH '$.id')) AS json; +------------+ | id | +------------+ | 2147483647 | +------------+ 1 row in set (0.00 sec) When "id" >= (2^31 -1), "id" get to be arithmetic overflow. mysql80 32> SELECT id FROM JSON_TABLE('[{"id":"2147483648"}]', '$[*]' COLUMNS (id BIGINT UNSIGNED PATH '$.id')) AS json; +-------------+ | id | +-------------+ | -2147483648 | +-------------+ 1 row in set (0.00 sec) Suggested fix: Be honest for datatype which is specified in COLUMNS clause. Workaround is picking AS REAL(NUMERIC) in COLUMNS clause. mysql80 32> SELECT id FROM JSON_TABLE('[{"id":"2147483648"}]', '$[*]' COLUMNS (id REAL PATH '$.id')) AS json; +------------+ | id | +------------+ | 2147483648 | +------------+ 1 row in set (0.00 sec)