List: General Discussion « Previous MessageNext Message » From: Jani Tolonen Date: May 11 1999 9:15am Subject: Re: Error comparing equality with numeric(18,0) View as plain text
```Fred Read writes:
> Jani Tolonen wrote:
> >
> > > Float comparisons don't work.
> >
> > Hello!
> >
> > The problem is that numeric is a floating point number,
> > which are never accurate. One should never compare floating
> > pint numbers with '='. But you can use 'like' instead, which
> > will work.
>
> But not how you might expect!

Hello!

Actually, it is. Using 'like' will change comparison to strings, in
other words, it will check if the numbers you can 'see' by doing
'select', are the same.  Then you can trust on what you 'see'. If you
do comparison with '=' you can't trust what you 'see', because the
computer is then comparing the numbers as they really are, and they
are not _necessarily_ the ones you can see, because a floating point
number is not accurate. We will anyway try to fix this in future, so
that '=' could be used too.

> For example if you wanted to compare a floating point value
> to 10, for example, you could say "... like '10.%' ..." but
> this wouldn't help as the actual value is more likely to be
> 10.0 +/- 0.00001 [for argument's sake] so would fail where
> the value was actually 9.999999.
>
>
> Perhaps we need a "roughly equals" operator, eg:
>
>     "... where value ~= 10 ..."
>
> which would do a floating point comparison with a known degree
> of tolerance which defaults to a "sensible" value but could be
> set as large or as small as required.

Yes, but you can do that already.
Just use : ...where value between (value - 0.00001) and (value + 0.00001)

Just an example:

I have a table 'f' described as:

mysql> show fields from f;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| f     | float(10,6) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

I have the following values inserted:

mysql> select * from f;
+-------------+
| f           |
+-------------+
|   10.000001 |
| 1000.000610 |
+-------------+

Then I try:

mysql> select * from f where f = 1000.000610;
Empty set (0.00 sec)

But if I try:

mysql> select * from f where f between (1000.000610 - 0.00001) and (1000.000610
+ 0.000001);
+-------------+
| f           |
+-------------+
| 1000.000610 |
+-------------+
1 row in set (0.01 sec)

Which will, again, be fast, as the comparison is done with numbers,
not as strings.

> The above was written slightly "tongue in cheek" but the more
> I look at it, the more useful it seems to be...

Not bad ;) The only problem is that what would the accurancy
be, if you use ~= ..? Of course it could be some default, but..

Thanks for the idea anyway!

>
> If it ain't opinionated, it ain't Rich Teer.

Yours,

- Jani

--
+---------------------------------------------------------------------+
| TcX  ____  __     _____   _____  ___       ==  mysql@stripped         |
|     /*/\*\/\*\   /*/ \*\ /*/ \*\ |*|       Jani Tolonen             |
|    /*/ /*/ /*/   \*\_   |*|   |*||*|       mailto: jani@stripped      |
|   /*/ /*/ /*/\*\/*/  \*\|*|   |*||*|       Helsinki                 |
|  /*/     /*/  /*/\*\_/*/ \*\_/*/ |*|_____  Finland                  |
|  ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^                           |
|             /*/             \*\                   Developers Team   |
+---------------------------------------------------------------------+
```