Bug #100567 Unexpected cast to float from attribute of a JSON datum

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.

Hello Marcos,

Thank you for the report and test case.

Thanks,
Umesh

- 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)

- 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)

- 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)

# Related Bug #88230

Bug #100587 marked as duplicate of this one

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