Bug #99145 Incorrect result when comparing a floating-point number with an integer
Submitted: 1 Apr 2020 9:00 Modified: 19 May 2020 14:25
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
Tags: regression

[1 Apr 2020 9:00] Manuel Rigger 
Description:
Consider the following statements:

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

Unexpectedly, the SELECT does not fetch any rows. Using "0.9 <= t0.c0" as a predicate also results in no rows being fetched. The first predicate should evaluate to TRUE, as demonstrated by the following statement:

SELECT 0.9 > t0.c0 FROM t0; -- 1
[1 Apr 2020 10:25] MySQL Verification Team 
Hello Manuel Rigger,

Thank you for the report.

regards,
Umesh
[7 Apr 2020 14:13] Catalin Besleaga 
Posted by developer:
 
WL#11935: Add folding of constants when compared to fields
[19 May 2020 14:25] Jon Stephens 
Documented fix as follows in the MySQL 8.0.21 changelog:

    A logical error in the constant folding code for the case in
    which a constant of type DECIMAL or FLOAT was the left-hand
    operand and an integer column value was the right-hand operand
    yielded an incorrect result.
      
Closed.