Bug #83954 JSON handeling of DECIMAL values, JSON from JSON string
Submitted: 24 Nov 2016 12:42 Modified: 1 Dec 2016 13:32
Reporter: sinai yoktan Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: JSON Severity:S3 (Non-critical)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any
Tags: JSON DECIMAL

[24 Nov 2016 12:42] sinai yoktan 
Description:
it seems that mysql is inconsistent when handeling / displaying bug decimal values in the context of JSON object.

JSON will encode big DECIMAL values as floating point number in some cases, and not in others,

this appears to happen when MySQL process the JSON object as a string.

MySQL often converts JSON objects to string implicitly
and might cause unexpected behavior.
this might also be caused by the users explicitly (casting JSON to TEXT and vice versa) however without knowledge of the expected effect

no warning is issued on the conversion (might setting dependent though).

How to repeat:
SELECT 
@j0 := JSON_OBJECT('a',123456789012345678901234567890) json_object_,
CAST(CAST(JSON_OBJECT('a',123456789012345678901234567890) AS CHAR) AS JSON) json_object_from_char_,
CAST(@j0 AS JSON) json_object_from_variable_,
JSON_EXTRACT(JSON_OBJECT('a',123456789012345678901234567890),'$.a') json_value_,
JSON_EXTRACT(@j0,'$.a') json_value_from_variable
[1 Dec 2016 13:32] Chiranjeevi Battula 
Hello sinai yoktan,

Thank you for the bug report.
Verified based on internal discussion with dev's.

Thanks,
Chiranjeevi.
[28 Aug 2023 13:34] Dmitriy Kostunin 
Dear developers, are there any updates on the bug resolution? 

On version 8.0.32 I have the similar behavior:

create table t1(t json);
insert into t1 values ('{"value": 212765.700000000010000}');
insert into t1 values ('{"value": 4.5338270940840284e+18}');
select * from t1\G
*************************** 1. row ***************************
t: {"value": 212765.7}
*************************** 2. row ***************************
t: {"value": 4.533827094084029e18}
[11 Apr 2024 14:27] Fenfang Li Fenfang Li 
I also encountered a similar problem. Has it been fixed in version 8.0?
MySQL [sbtest]> CREATE TABLE `test` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `name` varchar(50) DEFAULT NULL,
    ->   `age` int(11) DEFAULT NULL,
    ->   `extrainfo` json DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.029 sec)

MySQL [sbtest]> insert into test(name,age,extrainfo) values('jon3',300,'{"type": 3.0}');
Query OK, 1 row affected (0.003 sec)

# MySQL 5.7.44

MySQL [sbtest]> select * from test;
+----+------+------+------------------+
| id | name | age  | extrainfo        |
+----+------+------+------------------+
|  5 | jon3 |  300 | {"type": 3} |
+----+------+------+------------------+
1 row in set (0.001 sec)

#MySQL8.0.35
MySQL [sbtest]> select * from test;
+----+------+------+--------------------+
| id | name | age  | extrainfo          |
+----+------+------+--------------------+
|  5 | jon3 |  300 | {"type": 3.0} |
+----+------+------+--------------------+
1 row in set (0.001 sec)