Arithmetic Bug in 4.0.12/13-max

am 16.06.2003 23:04:16 von Rich Schramm

------=_NextPart_000_002E_01C33429.524912D0
Content-Type: text/plain;
charset="US-ASCII"
Content-Transfer-Encoding: 7bit

I have tried the following SQL script on 4.0.12-max-nt on Windows XP and
4.0.13-max on Red Hat Linux 9. In both cases, the 4th and 5th select
statements (and only those statements) return false (0). They return
true in Oracle and Access:

select (0.040000 + 0.040000)/2 = 0.040;
select (0.041000 + 0.039000)/2 = 0.040;
select (0.042000 + 0.038000)/2 = 0.040;
select (0.043000 + 0.037000)/2 = 0.040;
select (0.044000 + 0.036000)/2 = 0.040;
select (0.045000 + 0.035000)/2 = 0.040;
select (0.046000 + 0.034000)/2 = 0.040;
select (0.047000 + 0.033000)/2 = 0.040;
select (0.048000 + 0.032000)/2 = 0.040;
select (0.049000 + 0.031000)/2 = 0.040;
select (0.050000 + 0.030000)/2 = 0.040;

The selects with 0.043 and 0.044 as the first number return false for
some unknown reason. I discovered this in code as I am trying to select
records whose average of two columns equals the number I pass to it.

I have worked around this by modifying my select statement to multiply
each number by 1000, essential making each a whole number. When this is
done, it work as it should.

Anyone have any ideas?

Thanks,

Richard Schramm
Director, Strategic Technology Services
Aerospace International Materials, Inc.

------=_NextPart_000_002E_01C33429.524912D0--

Re: Arithmetic Bug in 4.0.12/13-max

am 16.06.2003 23:51:07 von Alexander Keremidarski

Hello,

Rich Schramm wrote:
> I have tried the following SQL script on 4.0.12-max-nt on Windows XP and
> 4.0.13-max on Red Hat Linux 9. In both cases, the 4th and 5th select
> statements (and only those statements) return false (0). They return
> true in Oracle and Access:
>
> select (0.040000 + 0.040000)/2 = 0.040;
> select (0.041000 + 0.039000)/2 = 0.040;
> select (0.042000 + 0.038000)/2 = 0.040;
> select (0.043000 + 0.037000)/2 = 0.040;
> select (0.044000 + 0.036000)/2 = 0.040;
> select (0.045000 + 0.035000)/2 = 0.040;
> select (0.046000 + 0.034000)/2 = 0.040;
> select (0.047000 + 0.033000)/2 = 0.040;
> select (0.048000 + 0.032000)/2 = 0.040;
> select (0.049000 + 0.031000)/2 = 0.040;
> select (0.050000 + 0.030000)/2 = 0.040;
>
> The selects with 0.043 and 0.044 as the first number return false for
> some unknown reason. I discovered this in code as I am trying to select
> records whose average of two columns equals the number I pass to it.

MySQL uses floating point arithmetic in this case which as you know is not precise.

To see what happens modify your query as:

select (0.040000 + 0.040000)/2 = 0.040, round((0.040000 + 0.040000)/2, 20);
select (0.041000 + 0.039000)/2 = 0.040, round((0.041000 + 0.039000)/2, 20);
....

Possible way to do above comparison is to use ROUND() function on one or both columns.

select (0.040000 + 0.040000)/2 = 0.040, round((0.040000 + 0.040000)/2, 3) = 0.040;

You will get trues in right column for obvious reason.

However most precise way is to do convert = to as a Range comparison 0.400+/-
tolerance

select (0.040000 + 0.040000)/2 BETWEEN 0.040 - 0.000001 AND 0.040 + 0.000001;

This is well docummented in Manual:

A.5.6 Solving Problems with No Matching Rows

....

# If you are comparing FLOAT or DOUBLE columns with numbers that have decimals,
you can't use '='. This problem is common in most computer languages because
floating-point values are not exact values. In most cases, changing the FLOAT to a
DOUBLE will fix this. See section A.5.7 Problems with Floating-Point Comparison.

Read also whole A.5.7 Problems with Floating-Point Comparison

> I have worked around this by modifying my select statement to multiply
> each number by 1000, essential making each a whole number. When this is
> done, it work as it should.

Yes as then MySQL uses integer arithmetics which is precise.

> Anyone have any ideas?
>
> Thanks,
>
> Richard Schramm
> Director, Strategic Technology Services
> Aerospace International Materials, Inc.

Best regards

--
Are you MySQL certified? -> http://www.mysql.com/certification
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

RE: Arithmetic Bug in 4.0.12/13-max

am 18.06.2003 15:06:45 von Rich Schramm

OK, I have been working on this for the last day. I read the manual
sections that were recommended on floating point calculations and I am
still seeing some weird things.

I simply want to see if the average value of two columns named upper and
lower (both are decimal(20,8) unsigned) is within the range of two
decimal numbers I pass to the statement.

The manual says in BIG CAPITAL LETTERS that you should NOT round and the
only precise way of getting this to go is to do use the abs(a - b) <
some tolerance, such as .00001. Having read that, I changed my code so
that as follows:

and
(
(
abs(upper - 0.039) < 0.00001
)
or
(
abs(lower - 0.037) < 0.00001
)
or
(
(
(upper + lower) / 2
)
between 0.039 and 0.037
)
)

Which I assume would find anything where the upper column is .039, the
lower column is .037 or the average between the columns is between .039
and .037.

However, all I am returned is one record where the average ((upper +
lower)/2) = 0.040 !!! I have plenty of records where the average is
0.038. Why is it not finding them??

Also, when my range is 0.038 - 0.038 so that the query looks like:

and
(
(
abs(upper - 0.038) < 0.00001
)
or
(
abs(lower - 0.038) < 0.00001
)
or
(
(
(upper + lower) / 2
)
between 0.038 and 0.038
)
)

It brings back ALL columns were the average is 0.038 AND it brings back
records where the average is .040 and .036.

In short, I am seeing exactly the OPPOSITE behavior I would expect from
this.

Any help would be much appreciated!

Thanks,

Rich
-----Original Message-----
From: Alexander Keremidarski [mailto:salle@mysql.com]
Sent: Monday, June 16, 2003 5:51 PM
To: Rich Schramm
Cc: bugs@lists.mysql.com
Subject: Re: Arithmetic Bug in 4.0.12/13-max

Hello,

Rich Schramm wrote:
> I have tried the following SQL script on 4.0.12-max-nt on Windows XP
> and 4.0.13-max on Red Hat Linux 9. In both cases, the 4th and 5th
> select statements (and only those statements) return false (0). They
> return true in Oracle and Access:
>
> select (0.040000 + 0.040000)/2 = 0.040;
> select (0.041000 + 0.039000)/2 = 0.040;
> select (0.042000 + 0.038000)/2 = 0.040;
> select (0.043000 + 0.037000)/2 = 0.040;
> select (0.044000 + 0.036000)/2 = 0.040;
> select (0.045000 + 0.035000)/2 = 0.040;
> select (0.046000 + 0.034000)/2 = 0.040;
> select (0.047000 + 0.033000)/2 = 0.040;
> select (0.048000 + 0.032000)/2 = 0.040;
> select (0.049000 + 0.031000)/2 = 0.040;
> select (0.050000 + 0.030000)/2 = 0.040;
>
> The selects with 0.043 and 0.044 as the first number return false for
> some unknown reason. I discovered this in code as I am trying to
> select records whose average of two columns equals the number I pass
> to it.

MySQL uses floating point arithmetic in this case which as you know is
not precise.

To see what happens modify your query as:

select (0.040000 + 0.040000)/2 = 0.040, round((0.040000 + 0.040000)/2,
20); select (0.041000 + 0.039000)/2 = 0.040, round((0.041000 +
0.039000)/2, 20); ...

Possible way to do above comparison is to use ROUND() function on one or
both columns.

select (0.040000 + 0.040000)/2 = 0.040, round((0.040000 + 0.040000)/2,
3) = 0.040;

You will get trues in right column for obvious reason.

However most precise way is to do convert = to as a Range comparison
0.400+/-
tolerance

select (0.040000 + 0.040000)/2 BETWEEN 0.040 - 0.000001 AND 0.040 +
0.000001;

This is well docummented in Manual:

A.5.6 Solving Problems with No Matching Rows

....

# If you are comparing FLOAT or DOUBLE columns with numbers that have
decimals,
you can't use '='. This problem is common in most computer languages
because
floating-point values are not exact values. In most cases, changing the
FLOAT to a
DOUBLE will fix this. See section A.5.7 Problems with Floating-Point
Comparison.

Read also whole A.5.7 Problems with Floating-Point Comparison

> I have worked around this by modifying my select statement to multiply

> each number by 1000, essential making each a whole number. When this
> is done, it work as it should.

Yes as then MySQL uses integer arithmetics which is precise.

> Anyone have any ideas?
>
> Thanks,
>
> Richard Schramm
> Director, Strategic Technology Services
> Aerospace International Materials, Inc.

Best regards

--
Are you MySQL certified? -> http://www.mysql.com/certification
For technical support contracts, visit
https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski

/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:
http://lists.mysql.com/bugs?unsub=richard.schramm@aim-intl.c om

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

RE: Arithmetic Bug in 4.0.12/13-max

am 18.06.2003 15:06:45 von Rich Schramm

RE: Arithmetic Bug in 4.0.12/13-max

am 18.06.2003 18:42:53 von Rich Schramm

I figured it out. The between clause needs lower then upper, and I had
upper then lower. The extraneous values I found must have been
artifacts from the abs comparison, which was clearly not working anyway.
I am just going to use the between clause. It seems to return the
results I expect.

Rich

-----Original Message-----
From: Rich Schramm [mailto:richard.schramm@aim-intl.com]
Sent: Wednesday, June 18, 2003 9:07 AM
To: 'Alexander Keremidarski'; bugs@lists.mysql.com;
mysql@lists.mysql.com
Subject: RE: Arithmetic Bug in 4.0.12/13-max

OK, I have been working on this for the last day. I read the manual
sections that were recommended on floating point calculations and I am
still seeing some weird things.

I simply want to see if the average value of two columns named upper and
lower (both are decimal(20,8) unsigned) is within the range of two
decimal numbers I pass to the statement.

The manual says in BIG CAPITAL LETTERS that you should NOT round and the
only precise way of getting this to go is to do use the abs(a - b) <
some tolerance, such as .00001. Having read that, I changed my code so
that as follows:

and
(
(
abs(upper - 0.039) < 0.00001
)
or
(
abs(lower - 0.037) < 0.00001
)
or
(
(
(upper + lower) / 2
)
between 0.039 and 0.037
)
)

Which I assume would find anything where the upper column is .039, the
lower column is .037 or the average between the columns is between .039
and .037.

However, all I am returned is one record where the average ((upper +
lower)/2) = 0.040 !!! I have plenty of records where the average is
0.038. Why is it not finding them??

Also, when my range is 0.038 - 0.038 so that the query looks like:

and
(
(
abs(upper - 0.038) < 0.00001
)
or
(
abs(lower - 0.038) < 0.00001
)
or
(
(
(upper + lower) / 2
)
between 0.038 and 0.038
)
)

It brings back ALL columns were the average is 0.038 AND it brings back
records where the average is .040 and .036.

In short, I am seeing exactly the OPPOSITE behavior I would expect from
this.

Any help would be much appreciated!

Thanks,

Rich
-----Original Message-----
From: Alexander Keremidarski [mailto:salle@mysql.com]
Sent: Monday, June 16, 2003 5:51 PM
To: Rich Schramm
Cc: bugs@lists.mysql.com
Subject: Re: Arithmetic Bug in 4.0.12/13-max

Hello,

Rich Schramm wrote:
> I have tried the following SQL script on 4.0.12-max-nt on Windows XP
> and 4.0.13-max on Red Hat Linux 9. In both cases, the 4th and 5th
> select statements (and only those statements) return false (0). They
> return true in Oracle and Access:
>
> select (0.040000 + 0.040000)/2 = 0.040;
> select (0.041000 + 0.039000)/2 = 0.040;
> select (0.042000 + 0.038000)/2 = 0.040;
> select (0.043000 + 0.037000)/2 = 0.040;
> select (0.044000 + 0.036000)/2 = 0.040;
> select (0.045000 + 0.035000)/2 = 0.040;
> select (0.046000 + 0.034000)/2 = 0.040;
> select (0.047000 + 0.033000)/2 = 0.040;
> select (0.048000 + 0.032000)/2 = 0.040;
> select (0.049000 + 0.031000)/2 = 0.040;
> select (0.050000 + 0.030000)/2 = 0.040;
>
> The selects with 0.043 and 0.044 as the first number return false for
> some unknown reason. I discovered this in code as I am trying to
> select records whose average of two columns equals the number I pass
> to it.

MySQL uses floating point arithmetic in this case which as you know is
not precise.

To see what happens modify your query as:

select (0.040000 + 0.040000)/2 = 0.040, round((0.040000 + 0.040000)/2,
20); select (0.041000 + 0.039000)/2 = 0.040, round((0.041000 +
0.039000)/2, 20); ...

Possible way to do above comparison is to use ROUND() function on one or
both columns.

select (0.040000 + 0.040000)/2 = 0.040, round((0.040000 + 0.040000)/2,
3) = 0.040;

You will get trues in right column for obvious reason.

However most precise way is to do convert = to as a Range comparison
0.400+/-
tolerance

select (0.040000 + 0.040000)/2 BETWEEN 0.040 - 0.000001 AND 0.040 +
0.000001;

This is well docummented in Manual:

A.5.6 Solving Problems with No Matching Rows

....

# If you are comparing FLOAT or DOUBLE columns with numbers that have
decimals,
you can't use '='. This problem is common in most computer languages
because
floating-point values are not exact values. In most cases, changing the
FLOAT to a
DOUBLE will fix this. See section A.5.7 Problems with Floating-Point
Comparison.

Read also whole A.5.7 Problems with Floating-Point Comparison

> I have worked around this by modifying my select statement to multiply

> each number by 1000, essential making each a whole number. When this
> is done, it work as it should.

Yes as then MySQL uses integer arithmetics which is precise.

> Anyone have any ideas?
>
> Thanks,
>
> Richard Schramm
> Director, Strategic Technology Services
> Aerospace International Materials, Inc.

Best regards

--
Are you MySQL certified? -> http://www.mysql.com/certification
For technical support contracts, visit
https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski

/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:
http://lists.mysql.com/bugs?unsub=richard.schramm@aim-intl.c om

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=richard.schramm@aim-intl. com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

RE: Arithmetic Bug in 4.0.12/13-max

am 18.06.2003 18:42:53 von Rich Schramm