Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6.1
-
None
-
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 | |
+----------+--------+ |