numeric comparisons

numeric comparisons

am 08.11.2010 19:50:16 von Larry.Martell

I have a client that asked me to look into a situation where they were
seeing different behavior with the same data and same sql on 2
different servers.

The have some sql that was comparing a double to a varchar in a where
clause - something like this:

where (doubleCol > varcharCol and some other conditions) or
(doubleCol < varcharCol and some other conditions)

Let's take an example where the varcharCol has a string in like
'4.5000' and the doubleCol has 4.5.

On their 5.0.45 server this was working as 'expected' (i.e. the
comparisons in the where clause were false since they are numerically
equal). But on their 5.0.18 server the > clause was resolving to true.
I changed the query to cast both columns to binary, so the query now
reads:

where (cast(doubleCol as binary) > cast(varcharCol as binary) and some
other conditions) or
(cast(doubleCol as binary) < cast(varcharCol as binary) and
some other conditions)

And now on both servers the < clause of the query is resolving to true.

I know I can strip off the trailing zeros from the varchar, but there
must be a generic way to cast these so they compare properly as
numbers.

TIA
-larry

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

Re: numeric comparisons

am 08.11.2010 21:01:02 von Michael Satterwhite

While I don't know why the behavior changed, the comparison of floating point
values has been an issue since the day computers were created. In the last 10
- 15 years, the comparisons have worked better, but it's still an issue. The
problem is that most decimal fractions do not convert well to binary.
Actually, I'd expect the example of 4.5 to work fine as 0.5 does convert well
to a binary floating point - but that is academic.

In the 1970's I got used to writing floating point comparisons as

x = 4.3
if(abs(x - 4.3) .lt. )

The tolerance is how close the values needed to be for me to consider them
equal. An example might be

if(abs(x - 4.3) .lt. 0.00001)

Someone else might be able to shed some light on why the behavior changed.

On Monday, November 08, 2010 12:50:16 pm Larry Martell wrote:
> I have a client that asked me to look into a situation where they were
> seeing different behavior with the same data and same sql on 2
> different servers.
>
> The have some sql that was comparing a double to a varchar in a where
> clause - something like this:
>
> where (doubleCol > varcharCol and some other conditions) or
> (doubleCol < varcharCol and some other conditions)
>
> Let's take an example where the varcharCol has a string in like
> '4.5000' and the doubleCol has 4.5.
>
> On their 5.0.45 server this was working as 'expected' (i.e. the
> comparisons in the where clause were false since they are numerically
> equal). But on their 5.0.18 server the > clause was resolving to true.
> I changed the query to cast both columns to binary, so the query now
> reads:
>
> where (cast(doubleCol as binary) > cast(varcharCol as binary) and some
> other conditions) or
> (cast(doubleCol as binary) < cast(varcharCol as binary) and
> some other conditions)
>
> And now on both servers the < clause of the query is resolving to true.
>
> I know I can strip off the trailing zeros from the varchar, but there
> must be a generic way to cast these so they compare properly as
> numbers.
>
> TIA
> -larry

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

Re: numeric comparisons

am 08.11.2010 21:11:07 von Peter Brawley

--------------060504010402060507070007
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

>I know I can strip off the trailing zeros from the varchar, but there
>must be a generic way to cast these so they compare properly as
>numbers.

Cast() may happen to fix some rounding errors, but the only way to be
sure of getting rid of such rounding errors in float or double values is
to change the /storage type/ to decimal.

PB

----

On 11/8/2010 12:50 PM, Larry Martell wrote:
> I have a client that asked me to look into a situation where they were
> seeing different behavior with the same data and same sql on 2
> different servers.
>
> The have some sql that was comparing a double to a varchar in a where
> clause - something like this:
>
> where (doubleCol> varcharCol and some other conditions) or
> (doubleCol< varcharCol and some other conditions)
>
> Let's take an example where the varcharCol has a string in like
> '4.5000' and the doubleCol has 4.5.
>
> On their 5.0.45 server this was working as 'expected' (i.e. the
> comparisons in the where clause were false since they are numerically
> equal). But on their 5.0.18 server the> clause was resolving to true.
> I changed the query to cast both columns to binary, so the query now
> reads:
>
> where (cast(doubleCol as binary)> cast(varcharCol as binary) and some
> other conditions) or
> (cast(doubleCol as binary)< cast(varcharCol as binary) and
> some other conditions)
>
> And now on both servers the< clause of the query is resolving to true.
>
> I know I can strip off the trailing zeros from the varchar, but there
> must be a generic way to cast these so they compare properly as
> numbers.
>
> TIA
> -larry
>

--------------060504010402060507070007--

Re: numeric comparisons

am 08.11.2010 21:11:41 von Larry.Martell

On Mon, Nov 8, 2010 at 1:01 PM, Michael Satterwhite w=
rote:
> While I don't know why the behavior changed, the comparison of floating p=
oint
> values has been an issue since the day computers were created. In the las=
t 10
> - 15 years, the comparisons have worked better, but it's still an issue. =
The
> problem is that most decimal fractions do not convert well to binary.
> Actually, I'd expect the example of 4.5 to work fine as 0.5 does convert =
well
> to a binary floating point - but that is academic.
>
> In the 1970's I got used to writing floating point comparisons as
>
>        x =3D 4.3
>        if(abs(x - 4.3) .lt. )
>
> The tolerance is how close the values needed to be for me to consider the=
m
> equal. An example might be
>
>        if(abs(x - 4.3) .lt. 0.00001)
>
> Someone else might be able to shed some light on why the behavior changed=
..
>
> On Monday, November 08, 2010 12:50:16 pm Larry Martell wrote:
>> I have a client that asked me to look into a situation where they were
>> seeing different behavior with the same data and same sql on 2
>> different servers.
>>
>> The have some sql that was comparing a double to a varchar in a where
>> clause - something like this:
>>
>> where (doubleCol > varcharCol and some other conditions) or
>>            (doubleCol < varcharCol and som=
e other conditions)
>>
>> Let's take an example where the varcharCol has a string in like
>> '4.5000' and the doubleCol has 4.5.
>>
>> On their 5.0.45 server this was working as 'expected' (i.e. the
>> comparisons in the where clause were false since they are numerically
>> equal). But on their 5.0.18 server the > clause was resolving to true.
>>  I changed the query to cast both columns to binary, so the query n=
ow
>> reads:
>>
>> where (cast(doubleCol as binary) > cast(varcharCol as binary) and some
>> other conditions) or
>>            (cast(doubleCol as binary) < ca=
st(varcharCol as binary) and
>> some other conditions)
>>
>> And now on both servers the < clause of the query is resolving to true.
>>
>> I know I can strip off the trailing zeros from the varchar, but there
>> must be a generic way to cast these so they compare properly as
>> numbers.
>>
>> TIA
>> -larry


Prompted by a reply I got off-list, I changed the cast from casting to
binary to casting to decimal(10,5) and it seems to work properly on
both servers.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: numeric comparisons

am 08.11.2010 21:57:48 von Jerry Schwartz

The two different versions of MySQL might perform the calculations in a
slightly different order.

By the way, it isn't just comparing numbers that can cause misleading results.
Consider the following.


10000000000 + 1.5 + 7 - 10000000000

What is the result of that calculation?

It depends upon the native precision of the machine, how the language chooses
to cast the operands, and how (or if) the compiler chooses to reorder the
operations.

I would not rely on the result being the same if you changed software
versions, let alone languages.


Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com

>-----Original Message-----
>From: Larry Martell [mailto:larry.martell@gmail.com]
>Sent: Monday, November 08, 2010 3:12 PM
>To: Michael Satterwhite
>Cc: mysql@lists.mysql.com
>Subject: Re: numeric comparisons
>
>On Mon, Nov 8, 2010 at 1:01 PM, Michael Satterwhite
>wrote:
>> While I don't know why the behavior changed, the comparison of floating
>> point
>> values has been an issue since the day computers were created. In the last
>> 10
>> - 15 years, the comparisons have worked better, but it's still an issue.
>> The
>> problem is that most decimal fractions do not convert well to binary.
>> Actually, I'd expect the example of 4.5 to work fine as 0.5 does convert
>> well
>> to a binary floating point - but that is academic.
>>
>> In the 1970's I got used to writing floating point comparisons as
>>
>> x = 4.3
>> if(abs(x - 4.3) .lt. )
>>
>> The tolerance is how close the values needed to be for me to consider them
>> equal. An example might be
>>
>> if(abs(x - 4.3) .lt. 0.00001)
>>
>> Someone else might be able to shed some light on why the behavior changed.
>>
>> On Monday, November 08, 2010 12:50:16 pm Larry Martell wrote:
>>> I have a client that asked me to look into a situation where they were
>>> seeing different behavior with the same data and same sql on 2
>>> different servers.
>>>
>>> The have some sql that was comparing a double to a varchar in a where
>>> clause - something like this:
>>>
>>> where (doubleCol > varcharCol and some other conditions) or
>>> (doubleCol < varcharCol and some other conditions)
>>>
>>> Let's take an example where the varcharCol has a string in like
>>> '4.5000' and the doubleCol has 4.5.
>>>
>>> On their 5.0.45 server this was working as 'expected' (i.e. the
>>> comparisons in the where clause were false since they are numerically
>>> equal). But on their 5.0.18 server the > clause was resolving to true.
>>> I changed the query to cast both columns to binary, so the query now
>>> reads:
>>>
>>> where (cast(doubleCol as binary) > cast(varcharCol as binary) and some
>>> other conditions) or
>>> (cast(doubleCol as binary) < cast(varcharCol as binary) and
>>> some other conditions)
>>>
>>> And now on both servers the < clause of the query is resolving to true.
>>>
>>> I know I can strip off the trailing zeros from the varchar, but there
>>> must be a generic way to cast these so they compare properly as
>>> numbers.
>>>
>>> TIA
>>> -larry
>
>
>Prompted by a reply I got off-list, I changed the cast from casting to
>binary to casting to decimal(10,5) and it seems to work properly on
>both servers.
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=jerry@gii.co.jp





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