...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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]>
> > 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 [EMAIL PROTECTED] |
| Database/Programming/SysAdmin (530)754-9127 |
| University of California, Davis http://pgfsun.ucdavis.edu/ |
+---------------------- Gort, Klaatu barada nikto! ----------------------+
---------------------------------------------------------------------
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
