| Bug #84935 | The result of SUM on JSON_EXTRACT lost the decimal part | ||
|---|---|---|---|
| Submitted: | 10 Feb 2017 5:08 | Modified: | 1 Mar 2017 12:01 |
| Reporter: | Milton Lai | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: JSON | Severity: | S3 (Non-critical) |
| Version: | 5.7.14, 5.7.17 | OS: | CentOS (Centos6.8) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | sum | ||
[10 Feb 2017 5:38]
MySQL Verification Team
Hello Milton Lai, Thank you for the report and test case. Thanks, Umesh
[10 Feb 2017 5:41]
MySQL Verification Team
-- root@localhost [test]> SELECT json_extract(vals, '$.amount') FROM voucher WHERE id='1'; +--------------------------------+ | json_extract(vals, '$.amount') | +--------------------------------+ | 5.45 | +--------------------------------+ 1 row in set (0.00 sec) root@localhost [test]> SELECT SUM(json_extract(vals, '$.amount')) FROM voucher WHERE id='1'; +-------------------------------------+ | SUM(json_extract(vals, '$.amount')) | +-------------------------------------+ | 5 | +-------------------------------------+ 1 row in set (0.00 sec) root@localhost [test]> create view v1 as SELECT json_extract(vals, '$.amount') FROM voucher WHERE id='1'; Query OK, 0 rows affected (0.00 sec) root@localhost [test]> desc v1; +--------------------------------+------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------------------+------+------+-----+---------+-------+ | json_extract(vals, '$.amount') | json | YES | | NULL | | +--------------------------------+------+------+-----+---------+-------+ 1 row in set (0.00 sec) root@localhost [test]> create view v2 as SELECT SUM(json_extract(vals, '$.amount')) FROM voucher WHERE id='1'; Query OK, 0 rows affected (0.00 sec) root@localhost [test]> desc v2; +-------------------------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------------------------+--------------+------+-----+---------+-------+ | SUM(json_extract(vals, '$.amount')) | double(17,0) | YES | | NULL | | +-------------------------------------+--------------+------+-----+---------+-------+ 1 row in set (0.00 sec) root@localhost [test]> SELECT SUM(5.45); +-----------+ | SUM(5.45) | +-----------+ | 5.45 | +-----------+ 1 row in set (0.00 sec)
[1 Mar 2017 12:01]
Jon Stephens
Documented fix in the MySQL 8.0.1 changelog as follows:
The SUM() function truncated decimal values extracted from JSON
documents and produced an integer result.
Closed.
[1 Mar 2017 12:04]
Jon Stephens
Workaround for 5.7: Use JSON_UNQUOTE() on the extracted value prior to applying SUM(), e.g. mysql> select SUM(vals->"$.amount") from voucher; +-----------------------+ | SUM(vals->"$.amount") | +-----------------------+ | 9 | +-----------------------+ 1 row in set (0.00 sec) mysql> select SUM(vals->>"$.amount") from voucher; +------------------------+ | SUM(vals->>"$.amount") | +------------------------+ | 9.440000000000001 | +------------------------+ 1 row in set (0.00 sec)
Description: I found this when tracking a bug in the system I am recently working on. It seems the SUM() over JSON_EXTRACT didn't recognize the data type correctly and covert it into integer. The decimal part is missing. How to repeat: -- Prepare the table and populate it with records CREATE TABLE `voucher` ( `id` varchar(32) NOT NULL COMMENT 'ID', `vals` mediumtext NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `voucher` VALUES ('1', '{\"period\":90,\"amount\":5.45}'); INSERT INTO `voucher` VALUES ('2', '{\"period\":90,\"amount\":3.99}'); -- Execute queries mysql> SELECT vals FROM voucher WHERE id='1'; +-----------------------------+ | vals | +-----------------------------+ | {"period":90,"amount":5.45} | +-----------------------------+ 1 row in set (0.00 sec) mysql> SELECT json_extract(vals, '$.amount') FROM voucher WHERE id='1'; +--------------------------------+ | json_extract(vals, '$.amount') | +--------------------------------+ | 5.45 | +--------------------------------+ 1 row in set (0.00 sec) -- The result of this one is not correct mysql> SELECT SUM(json_extract(vals, '$.amount')) FROM voucher WHERE id='1'; +-------------------------------------+ | SUM(json_extract(vals, '$.amount')) | +-------------------------------------+ | 5 | +-------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT ROUND(SUM(json_extract(vals, '$.amount')), 2) FROM voucher WHERE id='1'; +-----------------------------------------------+ | ROUND(SUM(json_extract(vals, '$.amount')), 2) | +-----------------------------------------------+ | 5.45 | +-----------------------------------------------+ 1 row in set (0.00 sec) Suggested fix: Need to fix it.