[MDEV-26023] JSON handing of DECIMAL is odd Created: 2021-06-26  Updated: 2022-06-01

Status: Open
Project: MariaDB Server
Component/s: JSON
Affects Version/s: 10.6.1
Fix Version/s: 10.6

Type: Bug Priority: Major
Reporter: Anders Karlsson Assignee: Rucha Deodhar
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Linux CentOS 7.7


 Description   

When a DECIMAL value is passed as an argument to create a JSON object, it is handled differently in the generated JSON from, say, a DOUBLE, despite DECIMAL not being a datatype in JSON and is treated like DOUBLE in other cases. In particular, trailing zeros are retained.

CREATE TABLE tab1(c1 INTEGER NOT NULL PRIMARY KEY, c1 DECIMAL(10,5) NOT NULL);
 
INSERT INTO tab1 VALUES(1, 10);
 
INSERT INTO tab1 VALUES(1, 10.8);
 
SELECT JSON_OBJECT('c1', c1, 'c2', c2) FROM tab1;
 
+---------------------------------+
 
| JSON_OBJECT('c1', c1, 'c2', c2) |
 
+---------------------------------+
 
| {"c1": 1, "c2": 10.00000}       |
 
| {"c1": 2, "c2": 10.80000}       |
 
+---------------------------------+

Note that the trailing zeros are retained, which is meaningless in JSON, somehow the notion that DOUBLE is different from DECIMAL, which makes sense in SQL, is retained in JSON, where it really doesn't make sense. Also

SELECT JSON_VALUE(JSON_OBJECT('c1', c1, 'c2', c2), '$.c2') value,
 
   JSON_TYPE(JSON_VALUE(JSON_OBJECT('c1', c1, 'c2', c2), '$.c2')) type FROM tab1;
 
+----------+--------+
 
| value    | type   |
 
+----------+--------+
 
| 10.00000 | DOUBLE |
 
| 10.80000 | DOUBLE |
 
+----------+--------+


Generated at Thu Feb 08 09:42:10 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.