Bug #83702 Math operations with JSON extracted values
Submitted: 5 Nov 2016 1:58 Modified: 26 Oct 2017 11:01
Reporter: gz — Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: JSON Severity:S2 (Serious)
Version:5.7.15, 5.7.16 OS:Any (Win, Ubuntu)
Assigned to: CPU Architecture:Any
Tags: json

[5 Nov 2016 1:58] gz —
Description:
Math operations with fractional numbers in string (single values, not array) extracted from JSON looks strange.

How to repeat:
Server version: 5.7.15-log MySQL Community Server (GPL)

mysql> SET @json := '{
    '>  "a":"0.5",
    '>  "b":"0.75"
    '> }';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT JSON_EXTRACT(@json, '$.b') - JSON_EXTRACT(@json, '$.a') AS value;
+-------+
| value |
+-------+
|    0. |
+-------+
1 row in set (0.00 sec)

mysql> SELECT AVG(JSON_EXTRACT(@json, '$.b') - JSON_EXTRACT(@json, '$.a')) AS value;
+--------+
| value  |
+--------+
| 0.2500 |
+--------+
1 row in set (0.00 sec)

mysql> SELECT
    ->  @b := JSON_EXTRACT(@json, '$.b'),
    ->  @a := JSON_EXTRACT(@json, '$.a');
+----------------------------------+----------------------------------+
| @b := JSON_EXTRACT(@json, '$.b') | @a := JSON_EXTRACT(@json, '$.a') |
+----------------------------------+----------------------------------+
| "0.75"                           | "0.5"                            |
+----------------------------------+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT @a, @b, @b - @a AS value;
+-------+--------+-------+
| @a    | @b     | value |
+-------+--------+-------+
| "0.5" | "0.75" |     0 |
+-------+--------+-------+
1 row in set (0.00 sec)

Same results with values extracted from tables.

Even if in JSON used numbers:
mysql> SET @json := '{
    '>  "a":0.5,
    '>  "b":0.75
    '> }';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT JSON_EXTRACT(@json, '$.b') - JSON_EXTRACT(@json, '$.a') AS value;
+-------+
| value |
+-------+
|    0. |
+-------+
1 row in set (0.00 sec)

mysql> SET @json := '[0.5, 0.75]';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT JSON_EXTRACT(@json, '$[1]'), JSON_EXTRACT(@json, '$[0]'), JSON_EXTRACT(@json, '$[1]') - JSON_EXTRACT(@json, '$[0]') AS value;
+-----------------------------+-----------------------------+-------+
| JSON_EXTRACT(@json, '$[1]') | JSON_EXTRACT(@json, '$[0]') | value |
+-----------------------------+-----------------------------+-------+
| 0.75                        | 0.5                         |    0. |
+-----------------------------+-----------------------------+-------+
1 row in set (0.00 sec)

AVG works fine.
And CAST can help:
mysql> SET @json := '{
    '>  "a":0.5,
    '>  "b":0.75
    '> }';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CAST(JSON_EXTRACT(@json, '$.b') AS decimal (3,2)) - CAST(JSON_EXTRACT(@json, '$.a') AS decimal (3,2)) AS value;
+-------+
| value |
+-------+
|  0.25 |
+-------+
1 row in set (0.00 sec)
[7 Nov 2016 8:01] MySQL Verification Team
Hello!

Thank you for the report.

Thanks,
Umesh
[26 Oct 2017 11:01] Knut Anders Hatlen
Bug#84935 seems to have fixed this bug too. Closing the bug report.