MySQL Bugs: #118941: Bug Report: DECIMAL(22,2) Column Comparison with -0.001 Excludes 0.00 Row

Bug #118941	Bug Report: DECIMAL(22,2) Column Comparison with -0.001 Excludes 0.00 Row
Submitted:	5 Sep 8:19	Modified:	10 Sep 14:45
Reporter:	fan alan	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.0.x, 8.4.x	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When running a WHERE a > -0.001 filter on a DECIMAL(22,2) column, rows with the exact value 0.00 are not returned—even though 0.00 > -0.001 should evaluate to true.

How to repeat:
Steps to Reproduce
Create a test table and insert sample data:

sql
DROP TABLE IF EXISTS bug_decimal;
CREATE TABLE bug_decimal (
  a DECIMAL(22,2) NOT NULL
);
INSERT INTO bug_decimal VALUES (0.00), (1.23), (-0.01), (-0.10);

Run the following query:

sql
SELECT * 
FROM bug_decimal 
WHERE a > -0.001;

Actual Result
The query returns:

+-------+
| a     |
+-------+
|  1.23 |
+-------+
Rows with a = 0.00 and a = -0.01 are not returned.

Both the > and >= operators exhibit the same behavior. For example:

SELECT * FROM bug_decimal WHERE a >= -0.001;

also fails to return the 0.00 row.This indicates the issue is not specific to strict versus non-strict comparison, nor simply a one-time precision truncation of the literal—rather it points to an underlying inconsistency in the DECIMAL comparison logic when the literal has more fractional digits than the column’s scale.

Hi,

This is not a bug. decimal(22,2) -0.001 is 0 so neither of "(0.00), (-0.01), (-0.10)" are > 0

However, using the query:

SELECT * 
FROM bug_decimal 
WHERE a >= -0.001;

also fails to return the row where a = 0.00

Moreover, this query works correctly on MySQL 5.7, and no other database systems exhibit this issue. It only fails on MySQL 8.0 and later versions.

Works as expected on 9

mysql> SELECT * 
    -> FROM bug_decimal 
    -> WHERE a >= -0.001;
+------+
| a    |
+------+
| 0.00 |
| 1.23 |
+------+
2 rows in set (0.001 sec)

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 9.4.0     |
+-----------+
1 row in set (0.000 sec)

mysql>

Hi,

You are correct. There is a definete weird behavior with 8.4. 9.x works as expected.

mysql> SELECT * 
    -> FROM bug_decimal 
    -> WHERE a >= -0.001;
+------+
| a    |
+------+
| 1.23 |
+------+
1 row in set (0.00 sec)

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.4.6     |
+-----------+
1 row in set (0.00 sec)

Bug is verified, thank you for your test case and your patience.

Bug #118967 marked as duplicate of this one

Will this bug be fixed in versions 8.0 and 8.4? 
Also, is it possible to support filtering with > -0.001? In a CASE WHEN statement, matching with > -0.001 is supported, but the result is not entirely consistent with filtering in the WHERE clause. 
For example:

select a,(case when a>-0.001 then true else false end) ca
FROM bug_decimal;

+-------+-----+
| a     | ca   |
+-------+-----+
| 0.00  | 1   |
| 1.23  | 1   |
| -0.01 | 0   |
| -0.10 | 0   |
+-------+-----+

"Update the affected versions：8.0.x, 8.4.x