| Bug #100567 | Unexpected cast to float from attribute of a JSON datum | ||
|---|---|---|---|
| Submitted: | 19 Aug 2020 3:37 | Modified: | 19 Aug 2020 5:44 |
| Reporter: | Marcos Albe (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: JSON | Severity: | S3 (Non-critical) |
| Version: | 8.0, 8.0.21, 8.0.11 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | regression | ||
[19 Aug 2020 5:44]
MySQL Verification Team
Hello Marcos, Thank you for the report and test case. Thanks, Umesh
[19 Aug 2020 5:44]
MySQL Verification Team
- 5.7
bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.31 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test
Database changed
mysql> create table test (id int, data json NOT NULL);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test values(1, '{"id": 1}');
Query OK, 1 row affected (0.01 sec)
mysql> select CAST(sum(id) as JSON), sum(id), CAST(sum(JSON_EXTRACT(data, '$.id')) AS JSON), sum(JSON_EXTRACT(data, '$.id')) from test;
+-----------------------+---------+-----------------------------------------------+---------------------------------+
| CAST(sum(id) as JSON) | sum(id) | CAST(sum(JSON_EXTRACT(data, '$.id')) AS JSON) | sum(JSON_EXTRACT(data, '$.id')) |
+-----------------------+---------+-----------------------------------------------+---------------------------------+
| 1 | 1 | 1 | 1 |
+-----------------------+---------+-----------------------------------------------+---------------------------------+
1 row in set (0.02 sec)
[19 Aug 2020 5:44]
MySQL Verification Team
- 8.0.21
bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.21 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database test;
Query OK, 1 row affected (0.01 sec)
mysql> use test
Database changed
mysql> create table test (id int, data json NOT NULL);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test values(1, '{"id": 1}');
Query OK, 1 row affected (0.01 sec)
mysql> select CAST(sum(id) as JSON), sum(id), CAST(sum(JSON_EXTRACT(data, '$.id')) AS JSON), sum(JSON_EXTRACT(data, '$.id')) from test;
+-----------------------+---------+-----------------------------------------------+---------------------------------+
| CAST(sum(id) as JSON) | sum(id) | CAST(sum(JSON_EXTRACT(data, '$.id')) AS JSON) | sum(JSON_EXTRACT(data, '$.id')) |
+-----------------------+---------+-----------------------------------------------+---------------------------------+
| 1 | 1 | 1.0 | 1 |
+-----------------------+---------+-----------------------------------------------+---------------------------------+
1 row in set (0.01 sec)
[19 Aug 2020 5:45]
MySQL Verification Team
- This is not new, seen at least since 8.0.11
bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 8.0.11 MySQL Community Server - GPL
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database test;
Query OK, 1 row affected (0.06 sec)
mysql> use test
Database changed
mysql> create table test (id int, data json NOT NULL);
Query OK, 0 rows affected (0.10 sec)
mysql> insert into test values(1, '{"id": 1}');
Query OK, 1 row affected (0.01 sec)
mysql> select CAST(sum(id) as JSON), sum(id), CAST(sum(JSON_EXTRACT(data, '$.id')) AS JSON), sum(JSON_EXTRACT(data, '$.id')) from test;
+-----------------------+---------+-----------------------------------------------+---------------------------------+
| CAST(sum(id) as JSON) | sum(id) | CAST(sum(JSON_EXTRACT(data, '$.id')) AS JSON) | sum(JSON_EXTRACT(data, '$.id')) |
+-----------------------+---------+-----------------------------------------------+---------------------------------+
| 1 | 1 | 1.0 | 1 |
+-----------------------+---------+-----------------------------------------------+---------------------------------+
1 row in set (0.00 sec)
[19 Aug 2020 5:51]
MySQL Verification Team
# Related Bug #88230
[20 Aug 2020 13:53]
MySQL Verification Team
Bug #100587 marked as duplicate of this one
[27 Aug 2020 11:24]
Marcelo Altmann
5.7 stores 1.0 as double, but omit the floating part when displaying .0:
mysql> SELECT *, JSON_TYPE(JSON_EXTRACT(data, '$.id')) FROM test;
+------+-------------+---------------------------------------+
| id | data | JSON_TYPE(JSON_EXTRACT(data, '$.id')) |
+------+-------------+---------------------------------------+
| 1 | {"id": 1} | INTEGER |
| 2 | {"id": 1} | DOUBLE |
| 3 | {"id": 1.5} | DOUBLE |
+------+-------------+---------------------------------------+
3 rows in set (0.00 sec)
While on 8.0, the representation of ID 2 comes with the floating-point:
mysql> SELECT *, JSON_TYPE(JSON_EXTRACT(data, '$.id')) FROM test;
+------+-------------+---------------------------------------+
| id | data | JSON_TYPE(JSON_EXTRACT(data, '$.id')) |
+------+-------------+---------------------------------------+
| 1 | {"id": 1} | INTEGER |
| 2 | {"id": 1.0} | DOUBLE |
| 3 | {"id": 1.5} | DOUBLE |
+------+-------------+---------------------------------------+
3 rows in set (0.00 sec)
So, 8.0 is still storing them correctly and displaying them correctly (what is not correct is how 5.7 display a double with .0).
If we check the CAST test case provided on this report and check what data type it returns we can see:
#5.7
mysql> select * from test;
+------+-----------+
| id | data |
+------+-----------+
| 1 | {"id": 1} |
+------+-----------+
1 row in set (0.00 sec)
mysql> select CAST(sum(JSON_EXTRACT(data, '$.id')) AS JSON), JSON_TYPE(CAST(sum(JSON_EXTRACT(data, '$.id')) AS JSON)) from test WHERE id = 1;
+-----------------------------------------------+----------------------------------------------------------+
| CAST(sum(JSON_EXTRACT(data, '$.id')) AS JSON) | JSON_TYPE(CAST(sum(JSON_EXTRACT(data, '$.id')) AS JSON)) |
+-----------------------------------------------+----------------------------------------------------------+
| 1 | DOUBLE |
+-----------------------------------------------+----------------------------------------------------------+
1 row in set (0.00 sec)
# 8.0
mysql> select * from test;
+------+-----------+
| id | data |
+------+-----------+
| 1 | {"id": 1} |
+------+-----------+
1 row in set (0.00 sec)
mysql> select CAST(sum(JSON_EXTRACT(data, '$.id')) AS JSON), JSON_TYPE(CAST(sum(JSON_EXTRACT(data, '$.id')) AS JSON)) from test WHERE id = 1;
+-----------------------------------------------+----------------------------------------------------------+
| CAST(sum(JSON_EXTRACT(data, '$.id')) AS JSON) | JSON_TYPE(CAST(sum(JSON_EXTRACT(data, '$.id')) AS JSON)) |
+-----------------------------------------------+----------------------------------------------------------+
| 1.0 | DOUBLE |
+-----------------------------------------------+----------------------------------------------------------+
1 row in set (0.00 sec)
So I don't think there is nothing wrong with 8.0 here. CAST operation is returning a DOUBLE on 5.7 and 8.0.
8.0 display it with the decimal point which is correct, while 5.7 on the other hand is omitting the floating-point, which is wrong and is what https://github.com/mysql/mysql-server/commit/0b8663708e24aff8c12bcf79886784c1ccf859f2 / https://bugs.mysql.com/bug.php?id=88230 fixed.
Botton line is that 5.7 is wrong and https://bugs.mysql.com/bug.php?id=88230 was only fixed on 8.0
Description: In 5.7 doing CAST(sum(JSON_EXTRACT(data, '$.id')) where the id attribute is an int would return an int, but in 8.0 it comes up as a float: [PROD-SER] [marcos.albe@bm-support01 msb_5_7_30]$ ./use Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.30-log MySQL Community Server (GPL) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql [localhost:5731] {msandbox} ((none)) > use test; Database changed mysql [localhost:5731] {msandbox} (test) > create table test (id int, data json NOT NULL); Query OK, 0 rows affected (0.00 sec) mysql [localhost:5731] {msandbox} (test) > insert into test values(1, '{"id": 1}'); Query OK, 1 row affected (0.01 sec) mysql [localhost:5731] {msandbox} (test) > select CAST(sum(id) as JSON), sum(id), CAST(sum(JSON_EXTRACT(data, '$.id')) AS JSON), sum(JSON_EXTRACT(data, '$.id')) from test; +-----------------------+---------+-----------------------------------------------+---------------------------------+ | CAST(sum(id) as JSON) | sum(id) | CAST(sum(JSON_EXTRACT(data, '$.id')) AS JSON) | sum(JSON_EXTRACT(data, '$.id')) | +-----------------------+---------+-----------------------------------------------+---------------------------------+ | 1 | 1 | 1 | 1 | +-----------------------+---------+-----------------------------------------------+---------------------------------+ And in 8.0 [PROD-SER] [marcos.albe@bm-support01 msb_8_0_19]$ ./use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.19-debug MySQL Community Server - GPL - Debug Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql [localhost:8019] {msandbox} (test) > create table test (id int, data json NOT NULL); Query OK, 0 rows affected (0.06 sec) mysql [localhost:8019] {msandbox} (test) > insert into test values(1, '{"id": 1}'); Query OK, 1 row affected (0.02 sec) mysql [localhost:8019] {msandbox} (test) > select CAST(sum(id) as JSON), sum(id), CAST(sum(JSON_EXTRACT(data, '$.id')) AS JSON), sum(JSON_EXTRACT(data, '$.id')) from test; +-----------------------+---------+-----------------------------------------------+---------------------------------+ | CAST(sum(id) as JSON) | sum(id) | CAST(sum(JSON_EXTRACT(data, '$.id')) AS JSON) | sum(JSON_EXTRACT(data, '$.id')) | +-----------------------+---------+-----------------------------------------------+---------------------------------+ | 1 | 1 | 1.0 | 1 | +-----------------------+---------+-----------------------------------------------+---------------------------------+ 1 row in set (0.01 sec) Happens in 8.0.20 as well How to repeat: Run these on 5.7 and 8.0; See outputs. create table test (id int, data json NOT NULL); insert into test values(1, '{"id": 1}'); select CAST(sum(id) as JSON), sum(id), CAST(sum(JSON_EXTRACT(data, '$.id')) AS JSON), sum(JSON_EXTRACT(data, '$.id')) from test; Suggested fix: Make it an int if there are no signs of the value being a float in the JSON document.