Bug #90423 JSON_TABLE returns wrong datatype when INT-value is grater than (2^31-1)

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)

Hello Tanaka-San,

Thank you for the report and feedback!

Thanks,
Umesh

--
[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)

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.