MySQL Bugs: #115541: The query result does not match the decimal type in and or query.

Bug #115541	The query result does not match the decimal type in and or query.
Submitted:	8 Jul 2024 9:57	Modified:	8 Jul 2024 18:10
Reporter:	lei yue	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
For in, the left side is decimal and the right side is string, which are converted to real for comparison. Real is double, so the result is abnormal; mainly because the precision of double is only 15 digits, and here there are 20 digits;

For = value, the left side is decimal, and the string on the right side is converted to decimal, and the decimal of the left value is used for comparison. So the result is correct;

How to repeat:
create table t1(id int primary key, test decimal(20,0));
insert into t1 values(1,10112102010000126011);
insert into t1 values(2,10112102010000126017);
insert into t1 values(3,10112102010000126013);

-- Abnormal results:
select * from t1 where test in ('10112102010000126017','10112102010000126013');
+----+----------------------+
| id | test                 |
+----+----------------------+
|  1 | 10112102010000126011 |
|  2 | 10112102010000126017 |
|  3 | 10112102010000126013 |
+----+----------------------+
3 rows in set (0.00 sec)

-- normal results:
select * from t1 where test = '10112102010000126017' or test = '10112102010000126013';
+----+----------------------+
| id | test                 |
+----+----------------------+
|  2 | 10112102010000126017 |
|  3 | 10112102010000126013 |
+----+----------------------+
2 rows in set (0.00 sec)

Hi Mr. vue,

Thank you for your bug report.

We managed to reproduce it in 8.0.38, 8.4.1 and 9.0.0.

Verified as reported.

Thanks again.

Posted by developer:
 
This is not a bug.
MySQL is documented to convert string values to double precision values when comparing them to integer or decimal values,
and thus rounding errors may impact the result.
The problem can be worked around with a CAST operator on the string value.