MySQL Bugs: #99183: Comparison on FLOAT(9, 9) gives an incorrect result

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

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.

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

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