Bug #100587 Unexpected cast to FLOAT from INT attribute from a JSON data

• Submitted: 20 Aug 2020 13:32
• Modified: 20 Aug 2020 13:53
• Reporter: Sveta Smirnova (OCA)
• Status: Duplicate
• Category: MySQL Server: JSON
• Severity: S3 (Non-critical)
• Version: 8.0.21
• OS: Any
• CPU Architecture: Any
• Tags: regression

[20 Aug 2020 13:32] Sveta Smirnova

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 value.

This is regression, introduced in version 8.0.4, most likely, after fix for https://bugs.mysql.com/bug.php?id=88230

How to repeat:
Version 5.7:

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;
CAST(sum(id) as JSON)	sum(id)	CAST(sum(JSON_EXTRACT(data, '$.id')) AS JSON)	sum(JSON_EXTRACT(data, '$.id'))
1	1	1	1

Version 8.0.21:

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

Suggested fix:
Use proper data type.

[20 Aug 2020 13:53] MySQL Verification Team

Hello Sveta,

Thank you for the report and feedback.
Imho this is duplicate of Bug #100567, please see Bug #100567

Sincerely,
Umesh