Description:
If database has table with decimal (12, 2) column and there are sequence of numbers starting from zero and continuing to negative side of zero e.g 0, -0.01, -0.02, -0.03 and now create select with less than operator (<) and querying with negative constant that has more significant fractional digits than column's type e.g -0.001 the operator adjust is not done right. Operator is adjusted to less than or equal (<=), and this included the constant truncation causes the line with zero to be add in to query results and that is not right becouse the query was asking lines below zero that are less than -0.001.
How to repeat:
create table decimal_test (id bigint auto_increment not null primary key, decval decimal (12, 2) not null);
insert into decimal_test (decval) values (0), (-0.01), (-0.02), (-0.03);
select * from decimal_test where decval < -0.001;
+----+--------+
| id | decval |
+----+--------+
| 1 | 0.00 |
| 2 | -0.01 |
| 3 | -0.02 |
| 4 | -0.03 |
explain analyze select * from decimal_test where decval < -0.001;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (decimal_test.decval <= 0.00) (cost=0.65 rows=1.33) (actual time=0.419..0.437 rows=4 loops=1)
-> Table scan on decimal_test (cost=0.65 rows=4) (actual time=0.405..0.422 rows=4 loops=1)
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
Suggested fix:
the operator adjust should not be done due to truncation in this case.