Round (floating point precision) bug

Subject: Round (floating point precision) bug

>Description:

select if(round(0.1,1) = round(0.1,1),'ok','buggs!');

returns a result of "buggs". If 0.1 is replaced by 0.5 this returns ok,
but 0.25 also generates a bug. It is also interesting to note that
0.1 = round(0.1,1) gives a different result from round(0.1,1) = 0.1

>How-To-Repeat:

select if(round(0.1,1) = round(0.1,1),'ok','buggs!');:

>Fix:

Compile item_func.cc without optimisation i.e. -O0 instead of -O3.

My guess is that the optimiser is keeping an intermediate result
on a floating point register, where it is held in extended format
rather than bringing it back to memory where it would be changed
back to a true C double. My best guess as to which variable is
being "bugged" by the optimiser would be tmp in Item_func_round::val(),

That should be enough to get you going, let me know if you need
more but be warned that I'm an application developer not a
software engineer.

You should note that this also fixes the problems with round
described in manual section A.5.7 where it is claimed that
the floating point comparison problem "cannot be solved by using
ROUND() (or similar function), because the result is still a
floating-point number".

More to come on the mailing list about this so-called "floating point
comparison problem" and why the only proper solution is decimal rounding
BY MYSQL as arithmetic errors are generated!

>Submitter-Id: george@intellnet.net.uk
>Originator: george@intellnet.net.uk
>Organization:

>MySQL support: [none]
>Synopsis: round producing inconsistent results on same operands.
>Severity: serious
>Priority:
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.49 (Source distribution)

>Environment:
i686, Linux 2.4.8-26mdk,gcc version 2.96 20000731 (Mandrake Linux 8.1
2.96-0.62mdk)

System: Linux roi-47a 2.4.8-26mdk #1 Sun Sep 23 17:06:39 CEST 2001 i686
unknown
Architecture: i686

Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i586-mandrake-linux-gnu/2.96/specs
gcc version 2.96 20000731 (Mandrake Linux 8.1 2.96-0.62mdk)




------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13383@lists.mysql.com
To unsubscribe, e-mail

Re: Round (floating point precision) bug

On Tuesday 31 December 2002 00:23, George Whiffen wrote:
> Subject: Round (floating point precision) bug
>
> >Description:
>
> select if(round(0.1,1) = round(0.1,1),'ok','buggs!');
>
> returns a result of "buggs". If 0.1 is replaced by 0.5 this
> returns ok, but 0.25 also generates a bug. It is also
> interesting to note that 0.1 = round(0.1,1) gives a different
> result from round(0.1,1) = 0.1
>
> >How-To-Repeat:
>
> select if(round(0.1,1) = round(0.1,1),'ok','buggs!');:
> >Fix:
>
> Compile item_func.cc without optimisation i.e. -O0 instead of
> -O3.
>
> My guess is that the optimiser is keeping an intermediate
> result on a floating point register, where it is held in
> extended format rather than bringing it back to memory where
> it would be changed back to a true C double. My best guess as
> to which variable is being "bugged" by the optimiser would be
> tmp in Item_func_round::val(),
>

Dear George,

Thank you for your bug report.
This is not the bug but sort of expected behavior - you should
not use "=" with floating point if you would like to get stable
results. Different layout of data in FPU register and in memory
is only one issue. Others are - the same value could be
represented differently by floating point, also the same
expression can be optimized differently in different parts of
code, leading to such result.

If you compare numbers of fixed scale, you can use
abs(a-b) different scale you might need to adjust epsilon.

We're planing to target this issue in MySQL two ways:

- Implementing some fuzzy comparison functions for floating
types.
- Adding fixed type support which would be accurate withing
domain.

In this specific case you can use convertion to string as
workaround, however I should warn it would not work for every
case.

mysql> select concat(round(0.1,1))=concat(round(0.1,1));
+-------------------------------------------+
| concat(round(0.1,1))=concat(round(0.1,1)) |
+-------------------------------------------+
| 1 |
+-------------------------------------------+
1 row in set (0.27 sec)


mysql> select round(0.1,1) like round(0.1,1);
+--------------------------------+
| round(0.1,1) like round(0.1,1) |
+--------------------------------+
| 1 |
+--------------------------------+
1 row in set (0.00 sec)




--
MySQL 2003 Users Conf. -> http://www.mysql.com/events/uc2003/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Peter Zaitsev
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Moscow, Russia
<___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13396@lists.mysql.com
To unsubscribe, e-mail