Bug #114581 Constant-Folding optimization negative constant truncate gives wrong operator
Submitted: 8 Apr 2024 11:24 Modified: 8 Apr 2024 12:35
Reporter: Simo Hiltunen Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.0.36 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[8 Apr 2024 11:24] Simo Hiltunen
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.
[8 Apr 2024 12:35] MySQL Verification Team
Hello Simo Hiltunen,

Thank you for the report and test case.
Verified as described.

regards,
Umesh