Bug #114196 Incorrect query result caused by comparing decimal and float type value
Submitted: 4 Mar 2024 2:49 Modified: 11 Jun 2024 17:58
Reporter: Ye Shiyang Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:8.0.35 OS:Any
Assigned to: CPU Architecture:Any

[4 Mar 2024 2:49] Ye Shiyang 
Description:
The Query #2 should return nothing insteand of 0 because t0 = 0 > - 0.8.

**Query #1**
select * from t0;

| c0  |
| --- |
| 0   |

**Query #2**

SELECT t0.c0 FROM t0 WHERE t0.c0 <= - 0.8;

| c0  |
| --- |
| 0   |

**Query #3**

SELECT (t0.c0 <= - 0.8) IS TRUE AS flag FROM t0;

| flag |
| ---- |
| 0    |

How to repeat:
CREATE TABLE IF NOT EXISTS t0(c0 DECIMAL);
REPLACE INTO t0(c0) VALUES(0.1);

SELECT t0.c0 FROM t0 WHERE t0.c0 <= - 0.8;

SELECT (t0.c0 <= - 0.8) IS TRUE AS flag FROM t0;

Suggested fix:
The Query #2 should return nothing instead of 0;
[4 Mar 2024 4:06] MySQL Verification Team 
Hello Ye Shiyang,

Thank you for the report and feedback

regards,
Umesh
[11 Jun 2024 17:58] Jon Stephens 
Documented fix as follows in the MySQL 9.0.0 changelog:

    In some cases, DECIMAL 0 was treated as less than a FLOAT value
    between 0 and -1.

Closed.