MySQL Bugs: #114196: Incorrect query result caused by comparing decimal and float type value

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

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;

Hello Ye Shiyang,

Thank you for the report and feedback

regards,
Umesh

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.

Related: Bug#117093.

I confirm this is fixed in 9.2.0, but it is not fixed in 8.4.4 and 8.0.41.  Can we expect a fix in 8.0 and 8.4 ?

sql="select version();
       drop table IF EXISTS t0;
       CREATE TABLE t0(c0 DECIMAL);
       REPLACE INTO t0(c0) VALUES(0.1);
       SELECT count(*) FROM t0 WHERE t0.c0 <= - 0.8;"

./use test -N <<< "$sql"
9.2.0
0

./use test -N <<< "$sql"
8.4.4
1

./use test -N <<< "$sql"
8.0.41
1