List:General Discussion« Previous MessageNext Message »
From:Steve Edberg Date:February 1 2002 11:21pm
Subject:Suggestion re: floating point comparison (was Re: Problem with
where clause)
View as plain text  
...perhaps a NEAR function could be added; as a config file or compile-time 
option, you could define an accuracy range. Say,

         ./config --with-epsilon=0.0001

(if memory of my numerical analysis classes serves, the 'fudge factor' was 
conventionally symbolized by epsilon; I suppose you could make it 
--with-fudge, but this ain't no bakery) so that

         select 1.0 near 1.00009 => 1
and
         select 1.0 near 0.9995 => 0

The default would be current behavior (epsilon=0).

This would avoid statements like

         select * where x > 0.99999999 and x < 1.00000001

...just my too sense -

         -steve

At 02:33 PM 2/1/02 , James Montebello <jamesm@stripped> wrote:

>Yes, but you need to use the decimal (fixed-point) type, not the floating
>point type.  Any program that's directly comparing FP numbers for exact
>matches is simply wrong, and certainly won't be portable, even if it works
>in one particular environment.  Fixed-point numbers CAN be compared for
>exact matches, and they can include fractional values.
>
>james montebello
>
>On Fri, 1 Feb 2002, Jim Dickenson <dickenson@stripped> wrote:
>
> > Am I to assume that based on your response that one should never use a 
> float
> > field type if you ever want to select the data?
> >
> > This causes a big problem for the way MyODBC 3.51 has been implemented. I
> > was actually debugging a problem I had in MyODBC when I ran across 
> this. The
> > way MyODBC works is that is generates a native SQL statement. In my 
> case the
> > statement was:
> >
> > UPDATE `junk` SET `record`=  91 WHERE record=1 AND title='This is item one'
> > AND num1=12.3 AND num2=134 AND num3=0.100 AND code='abc' AND
> > sdate='1991-11-30' AND stime='17:45:00' LIMIT 1
> >
> > Since the float compare did not work (field num1), the record I wanted
> > changed did not get changed.
> >
> > The way our software works is that it fixes the variable number (num1) to
> > the number of decimals in the constant number before the compare is done.
> > There are ways for compares to be programmed so they do work. We are using
> > computers after all.
> >
> >
> > On 2/1/2002 2:06 PM, "Gerald Clark" <gerald_clark@stripped>
> > wrote:
> >
> > > A floating point number can never be equal to 12.3.
> > > It can be close, and with rounding display as 12.3, but it
> > > won't actually be equal to 12.3.
> > > Use a decimal type instead.
> > >
> > >
> > > Jim Dickenson wrote:
> > >
> > >> I am running mysql  Ver 11.15 Distrib 3.23.47, for pc-linux-gnu (i686)
> > >> installed from a binary RPM file. This is using RedHat Linux 7.2.
> > >>
> > >> I have a table described as:
> > >> mysql> describe junk;
> > >> +--------+---------------+------+-----+---------+-------+
> > >> | Field  | Type          | Null | Key | Default | Extra |
> > >> +--------+---------------+------+-----+---------+-------+
> > >> | record | decimal(3,0)  | YES  |     | NULL    |       |
> > >> | title  | varchar(250)  | YES  |     | NULL    |       |
> > >> | num1   | float         | YES  |     | NULL    |       |
> > >> | num2   | decimal(6,0)  | YES  |     | NULL    |       |
> > >> | num3   | decimal(10,3) | YES  |     | NULL    |       |
> > >> | code   | char(3)       | YES  |     | NULL    |       |
> > >> | sdate  | date          | YES  |     | NULL    |       |
> > >> | stime  | time          | YES  |     | NULL    |       |
> > >> +--------+---------------+------+-----+---------+-------+
> > >> 8 rows in set (0.00 sec)
> > >>
> > >> It has the following data:
> > >> mysql> select record,num1 from junk;
> > >> +--------+----------+
> > >> | record | num1     |
> > >> +--------+----------+
> > >> |      1 |     12.3 |
> > >> |      2 |   17.785 |
> > >> |      3 |  138.981 |
> > >> |      4 |   -34.12 |
> > >> |      5 | -12.7365 |
> > >> |      6 |    -0.34 |
> > >> +--------+----------+
> > >> 6 rows in set (0.00 sec)
> > >>
> > >> The following command does not update the row I would like it to:
> > >> mysql> update junk set num1=12.4 where num1=12.3;
> > >> Query OK, 0 rows affected (0.01 sec)
> > >> Rows matched: 0  Changed: 0  Warnings: 0
> > >>
> > >>
> > >> Can I get some insight as to what the problem might be?
> > >>
> > >> Thanks,
> > >>


+------------------------------------------------------------------------+
| Steve Edberg                                      sbedberg@stripped |
| Database/Programming/SysAdmin                            (530)754-9127 |
| University of California, Davis             http://pgfsun.ucdavis.edu/ |
+---------------------- Gort, Klaatu barada nikto! ----------------------+

Thread
Re: Problem with where clauseShankar Unni1 Feb
RE: Problem with where clauseMichael Fochtman4 Feb