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)