bug with CAST in WHERE-Clause

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

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13354@lists.mysql.com
To unsubscribe, e-mail

Re: bug with CAST in WHERE-Clause

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
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

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


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13361@lists.mysql.com
To unsubscribe, e-mail

Re: bug with CAST in WHERE-Clause

On Wednesday 25 December 2002 18:28, Sinisa Milivojevic wrote:

Hello Sinia,
>
> 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.

Sorry, but I don't agree: Cast should change/return values datatype without
changing it's internal representation.
Comparison should be done with datatypes and not with internal
representation.

This works without problems in MSSQL, Postgres, Oracle, DB2 and Sybase. And
it should be also the preferred way to detect if a float-value is even (cause
the expression is closer to an sargable ideal, than using floor/ceil on the
left side)

Regards

Georg

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13362@lists.mysql.com
To unsubscribe, e-mail

Re: bug with CAST in WHERE-Clause

Georg Richter writes:
> On Wednesday 25 December 2002 18:28, Sinisa Milivojevic wrote:
>
> Hello Sinia,
>
> Sorry, but I don't agree: Cast should change/return values datatype without
> changing it's internal representation.
> Comparison should be done with datatypes and not with internal
> representation.
>
> This works without problems in MSSQL, Postgres, Oracle, DB2 and Sybase. And
> it should be also the preferred way to detect if a float-value is even (cause
> the expression is closer to an sargable ideal, than using floor/ceil on the
> left side)
>
> Regards
>
> Georg

The crux of the issue is that in MySQL, when it comes to compare
floats and integers, the comparison is done in floats.

So, any cast on float dissapears ....

That is why the same cast works with integer, as then integers are compared.

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

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


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13363@lists.mysql.com
To unsubscribe, e-mail