Bug #92331 Document JSON numeric types and conversion rules
Submitted: 7 Sep 2018 6:29 Modified: 7 Sep 2018 6:54
Reporter: Alexey Kopytov Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:8.0.12 OS:Any
Assigned to: CPU Architecture:Any
Tags: json

[7 Sep 2018 6:29] Alexey Kopytov
Description:
Below is a demonstration of behavior that, in my opinion, is not
explained anywhere in the user manual:

mysql> create table t(a json);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t values ('{"a": 127}'), ('{"a": 255}'), ('{"a": 32767}'), ('{"a": 32768}'), ('{"a": 8388607}'), ('{"a": 16777215}'), ('{"a": 9223372036854775807}'), ('{"a": 18446744073709551615}'), ('{"a": 18446744073709551616}'), ('{"a": 111.111}'), (JSON_OBJECT('a', 1.0));
Query OK, 11 rows affected (0.04 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> select a->'$.a', json_type(a->'$.a'), json_storage_size(a->'$.a') from t;
+-----------------------+---------------------+-----------------------------+
| a->'$.a'              | json_type(a->'$.a') | json_storage_size(a->'$.a') |
+-----------------------+---------------------+-----------------------------+
| 127                   | INTEGER             |                           3 |
| 255                   | INTEGER             |                           3 |
| 32767                 | INTEGER             |                           3 |
| 32768                 | INTEGER             |                           5 |
| 8388607               | INTEGER             |                           5 |
| 16777215              | INTEGER             |                           5 |
| 9223372036854775807   | UNSIGNED INTEGER    |                           9 |
| 18446744073709551615  | UNSIGNED INTEGER    |                           9 |
| 1.8446744073709552e19 | DOUBLE              |                           9 |
| 111.111               | DOUBLE              |                           9 |
| 1.0                   | DECIMAL             |                           7 |
+-----------------------+---------------------+-----------------------------+
11 rows in set (0.00 sec)

https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html#function_json-type
lists various supported JSON value types, including the numeric
ones.

However, that page does not:

- mention their storage sizes;

- mention UNSIGNED INTEGER;

- explain how the types and their storage sizes are inferred from string
  values when parsing a string into JSON;

- explain why 127 and 32767 have the same storage size;

- explain why 9223372036854775807 is stored as an UNSIGNED INTEGER

- explain if there is a way to parse and store a string value into a
  JSON DECIMAL value, rather than using JSON_OBJECT()

How to repeat:
See above.
[7 Sep 2018 6:51] Umesh Shastry
Hello Alexey,

Thank you for the report!

regards,
Umesh