From: Larry Martell
Date: November 8 2010 8:11pm
Subject: Re: numeric comparisons
List-Archive: http://lists.mysql.com/mysql/223524
Message-Id: <AANLkTinQkDWKzs6=m9e0KfxnttmY7nqh_Ux=k66-W8Tp@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

On Mon, Nov 8, 2010 at 1:01 PM, Michael Satterwhite <michael@stripped> 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
>
> =C2=A0 =C2=A0 =C2=A0 =C2=A0x =3D 4.3
> =C2=A0 =C2=A0 =C2=A0 =C2=A0if(abs(x - 4.3) .lt. <some tolerance>)
>
> The tolerance is how close the values needed to be for me to consider the=
m
> equal. An example might be
>
> =C2=A0 =C2=A0 =C2=A0 =C2=A0if(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
>> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0(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.
>> =C2=A0I 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
>> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0(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.