Bug #99183 Comparison on FLOAT(9, 9) gives an incorrect result
Submitted: 5 Apr 2020 16:01 Modified: 7 May 2020 12:37
Reporter: Manuel Rigger Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.0.19 OS:Any
Assigned to: CPU Architecture:Any

[5 Apr 2020 16:01] Manuel Rigger
Description:
Consider the following statements:

How to repeat:
CREATE TABLE t0(c0 FLOAT(9, 9) UNIQUE) ;
INSERT IGNORE INTO t0(c0) VALUES(1);
SELECT * FROM t0 WHERE t0.c0 >= 1; -- expected: {1.000000000}, actual: {}

Unexpectedly, the row contained in t0 is not fetched. The query works as expected when removing the UNIQUE constraint. Also the following indicates that the predicate should evaluate to TRUE:

SELECT t0.c0 >= 1 FROM t0; -- {1}

While I'm aware that floating-point numbers are inaccurate and of https://dev.mysql.com/doc/refman/8.0/en/no-matching-rows.html, I still believe that this is a bug, since I would expect a consistent result irrespective of an index. This invariance has been holding in all the other DBMS that I have been testing.
[6 Apr 2020 13:10] MySQL Verification Team
Hi Mr. Rigger,

Thank you for your bug report.

I can not reproduce what you are reporting.

I get no results regardless if the table is created with or without UNIQUE index.

I can't repeat the behaviour you reported.

Did you use our binary and did you use any special settings that would help us reproduce this behaviour ??
[7 May 2020 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".