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