MySQL Bugs: #111142: Float calculate result changes when adding brakets

Bug #111142	Float calculate result changes when adding brakets
Submitted:	24 May 2023 15:03	Modified:	25 May 2023 14:21
Reporter:	Siyang Weng	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.18	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	bug;float;calculation

Description:
The select result changes when adding brakets in predicate.

select * from t0 where a=0.001+1e-1-1e-1;
select * from t0 where a=0.001+(1e-1-1e-1);

return different result sets. The first one returns empty set and the second one returns 0.001;

How to repeat:
initalize:

create table t0 (a decimal(5,3));
insert into t0 values(0);
insert into t0 values(0.001);

Then execute three queries:

select * from t0 where a=0.001+1e-1-1e-1;
select * from t0 where a=0.001+(1e-1-1e-1); -- these two are the same as mentioned above
select * from t0 where a=0+1e-1-1e-1; -- return 0

Suggested fix:
These two queries should return same result, i.e, 0.001.

Hi MR. Weng,

Thank you for your bug report.

However, it is not a bug.

First of all, you are using an ancient release of MySQL. Current release is 8.0.33.

But, the crux of your problem is that your data domain is DECIMAL, while you are using the constants from the FLOAT / DOUBLE domain. Try using just 0.001 ....... Also, leave the blanks between the numbers and operations in your arithmetics ........

Not a bug.

Thanks for your reply and advice!

So is it expected that adding brakets to FLOAT / DOUBLE expression may change its result? If so, I will avoid using float and double in the future.

Hi,

You may use FLOAT and DOUBLE as much as you wish, but don't mix them with DECIMAL.

Those are two totally different data types.

Also, write expressions differently. Instead of:

a=0.001+1e-1-1e-1;

do the following:

a=0.001 + 1e-1 - 1e-1;  // when 'a' is FLOAT or DOUBLE 

and

a=0.001 + 0.1 - 0.1; // when 'a' is DECIMAL

Simply, DECIMAL do not use scientific notation.

This is all described in our Reference Manual.

Got it.

Thanks a lot！

You are truly welcome .....