Bug #90423 JSON_TABLE returns wrong datatype when INT-value is grater than (2^31-1)
Submitted: 13 Apr 3:17 Modified: 13 Apr 5:20
Reporter: tsubasa tanaka (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: JSON Severity:S2 (Serious)
Version:8.0.4 OS:CentOS (7.4)
Assigned to: CPU Architecture:x86

[13 Apr 3:17] tsubasa tanaka
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)
[13 Apr 5:20] Umesh Shastry
Hello Tanaka-San,

Thank you for the report and feedback!

Thanks,
Umesh