MySQL Bugs: #101048: The result is inconsistent between join and group by

Bug #101048	The result is inconsistent between join and group by
Submitted:	4 Oct 2020 1:38	Modified:	20 Dec 2023 19:26
Reporter:	zetao wei	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: JSON	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Examples of json:
{ "t" : -999}
{ "t" : -999.0}

The value of '$.t':
Is considered to be the same value by `GroupBY`.
Is considered to be different value by `JOIN`.

The same value is in different operators, and the results are inconsistent.

How to repeat:
CREATE TABLE t1(id INT PRIMARY KEY AUTO_INCREMENT, j JSON);
insert into t1(j) values 
('{ "t" : -999}'), 
('{ "t" : -999.0}');

CREATE TABLE t2(id INT PRIMARY KEY AUTO_INCREMENT, j JSON);
insert into t2(j) values 
('{ "t" : -999.0}');

mysql> select j->'$.t' from t1 group by 1;
+----------+
| j->'$.t' |
+----------+
| -999     |
+----------+

mysql> select * from t1,t2 where t1.j->'$.t' = t2.j->'$.t';
+----+---------------+----+---------------+
| id | j             | id | j             |
+----+---------------+----+---------------+
|  2 | {"t": -999.0} |  1 | {"t": -999.0} |
+----+---------------+----+---------------+

Suggested fix:
Whether integer and floating-point types do forced type conversion is expected to keep consistent behavior in different operators.

Thank you for the bug report.

Posted by developer:
 
We join strings, and create hash values for "-999" and "-999.0".

Documented fix as follows in the MySQL 8.3.0 changelog:

    JOIN and GROUP BY handled JSON column values differently.

Closed.

This is flagged as fixed in 8.3.0, but also as affecting 8.0.

Will this be fixed in a future 8.0 release.

Also fixed in MySQL 8.0.37 and 8.4.0.

Changelog entry as above.