From: Peter Brawley
Date: November 8 2010 8:11pm
Subject: Re: numeric comparisons
List-Archive: http://lists.mysql.com/mysql/223523
Message-Id: <4CD8595B.5010700@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------060504010402060507070007"

--------------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--