Bug #99127 Incorrect result when comparing an UNSIGNED INT with a floating-point number
Submitted: 31 Mar 2020 11:49 Modified: 31 May 2021 8:42
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

[31 Mar 2020 11:49] Manuel Rigger 
Description:
Consider the following statements:

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

Unexpectedly, the row is not fetched, even though the predicate should evaluate to TRUE:

SELECT 0.9 > t0.c0 FROM t0; -- 1
[31 Mar 2020 12:00] MySQL Verification Team 
Hello Manuel Rigger,

Thank you for the report.

regards,
Umesh
[7 Apr 2020 13:23] Catalin Besleaga 
Posted by developer:
 
Likely related to WL#11935: Add folding of constants when compared to fields
[7 Apr 2020 14:14] Catalin Besleaga 
Posted by developer:
 
duplicate for Bug#31110614
[31 May 2021 8:42] MySQL Verification Team 
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.