Bug #117093 The comparison results of DECIMAL type data do not meet expectations.
Submitted: 2 Jan 5:51 Modified: 14 Jan 15:59
Reporter: wang jack Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:8.4.1, 8.0.40 OS:Windows (windows 11)
Assigned to: CPU Architecture:x86 (x86_64)

[2 Jan 5:51] wang jack 
Description:
In the WHERE condition, I compared a floating-point number with a column of DECIMAL type, and the query results are inconsistent with expectations. 
I believe this query should not return any results.

mysql> SELECT * FROM t0;
+------+
| c0   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

mysql> SELECT TRUE FROM t0 WHERE ((- (0.8957012519606671))) >= (t0.c0);
+------+
| TRUE |
+------+
|    1 |
+------+
1 row in set (0.01 sec)
mysql> SELECT ((- (0.8957012519606671))) >= (t0.c0) FROM t0;
+---------------------------------------+
| ((- (0.8957012519606671))) >= (t0.c0) |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set (0.00 sec)

I found two similar bug reports, #112826 and #114218. I'm not sure if they are duplicates.

How to repeat:
DROP TABLE IF EXISTS t0;
CREATE TABLE t0(c0 DECIMAL);
INSERT INTO t0 VALUES (0);

SELECT TRUE FROM t0 WHERE ((- (0.8957012519606671))) >= (t0.c0); -- 1 row
SELECT ((- (0.8957012519606671))) >= (t0.c0) FROM t0;            -- {0}
[2 Jan 7:40] MySQL Verification Team 
Hello wang jack,

Thank you for the report and test case.

regards,
Umesh
[14 Jan 15:59] Jon Stephens 
Fixed in MySQL 9.0.0 by BUG#114196.

See same for more info.

Closed.
[17 Feb 21:30] Jean-François Gagné 
Copying what I wrote in Bug#114196:

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