Bug #99122 A predicate that compares 0 with -0 yields an incorrect result
Submitted: 31 Mar 2020 9:11 Modified: 3 Apr 2020 13:10
Reporter: Manuel Rigger Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.19 OS:Any
Assigned to: CPU Architecture:Any

[31 Mar 2020 9:11] Manuel Rigger 
Description:
Consider the following test case:

How to repeat:
CREATE TABLE t0(c0 INT);
CREATE TABLE t1(c0 DOUBLE);
INSERT INTO t0 VALUES(0);
INSERT INTO t1 VALUES('-0');
SELECT * FROM t0, t1 WHERE t0.c0 = t1.c0; -- expected: {0, -0}, actual: {}

Unexpectedly, the SELECT does not fetch a row. That the predicate should evaluate to TRUE can be verified with the following statement:

SELECT t0.c0 = t1.c0 FROM t0, t1; -- 1

I found this bug in MySQL version 8.0.19.
[31 Mar 2020 10:01] MySQL Verification Team 
Hello Manuel Rigger,

Thank you for the report.

regards,
Umesh
[3 Apr 2020 13:07] Jon Stephens 
Documented fix as follows in the MySQL 8.0.21 changelog:

    A query whose predicate compared 0 with -0 where at least one of
    these was a flaoting-point value returned incorrect results.

Closed.
[3 Apr 2020 13:10] Manuel Rigger 
Thanks a lot for fixing!