4 messages in com.mysql.lists.bugsRe: bug with CAST in WHERE-Clause
FromSent OnAttachments
Georg Richter24 Dec 2002 15:04 
Sinisa Milivojevic25 Dec 2002 09:28 
Georg Richter25 Dec 2002 09:57 
Sinisa Milivojevic25 Dec 2002 11:00 
Subject:Re: bug with CAST in WHERE-Clause
From:Sinisa Milivojevic (sin@mysql.com)
Date:12/25/2002 09:28:29 AM
List:com.mysql.lists.bugs

Georg Richter writes:

Hi,

Version: bk 4.1 latest

how-to-repeat:

mysql> create table a (a float); Query OK, 0 rows affected (0.00 sec)

mysql> insert into a values(1.1); Query OK, 1 row affected (0.00 sec)

mysql> select cast(a as signed) from a; +-------------------+ | cast(a as signed) | +-------------------+ | 1 | +-------------------+ 1 row in set (0.00 sec)

mysql> select a from a where a=cast(a as signed); +------+ | a | +------+ | 1.1 | +------+ 1 row in set (0.00 sec)

should return an empty result set

Regards

Georg

Hi!

I have taken a look at the above and it is actually not a bug.

Difference in behaviour result from the fact that , unlike in select list, when it comes to comparisons (like in WHERE expressions), real and int's are always compared as real numbers. As CAST does not change a field type, then cast is actually not applied at all.

To force comparisons without decimal, you have to use CEIL() or FLOOR() or ROUND() functions.

But you can use CAST to force comparisons with integers, like this:

select a from a where cast((a+0.7) as signed)=1; +------+ | a | +------+ | 1.1 | +------+

But I think that we need to document this behaviour much better.

-- __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic <sin@mysql.com> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus <___/ www.mysql.com

Join MySQL Users Conference and Expo: http://www.mysql.com/events/uc2003/