MySQL Bugs: #24913: cast(float as signed) equlas both: orig float and int

Bug #24913	cast(float as signed) equlas both: orig float and int
Submitted:	8 Dec 2006 15:04	Modified:	29 Jan 2008 10:30
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	4.1.22, 5.0.27, 5.0.32-BK	OS:	Linux (Linux, freebsd)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	BIGINT, cast, qc

Description:
cast as signed/unsigned, is expected to return an integer.

however the result of this cast (with a float) as input, equals both: the original float and the integer.

I was first thinking, if pi() in integer contect eqals 3; but:
select pi()=3; # return false

on mysql 4.1 and 5.0 both of the following return true
 select cast(pi() as signed) = pi();
 select cast(pi() as signed) = 3;

the same aplies to those two on mysql 4.1 (but works correct on 5.0)
 select cast(3.141593 as signed) = 3.141593;
 select cast(3.141593 as signed) = 3;

also found in some cases with sin() / probably with other functions to

How to repeat:
select pi()=3;

select cast(pi() as signed) = pi();
select cast(pi() as signed) = 3;
select cast(pi() as signed) = 3.141593;

select cast(pi() as unsigned) = pi();
select cast(pi() as unsigned) = 3;
select cast(pi() as unsigned) = 3.141593;

select cast(3.141593 as signed) = 3.141593;
select cast(3.141593 as signed) = 3;

select cast(3.141593 as unsigned) = 3.141593;
select cast(3.141593 as unsigned) = 3;

select cast(sin(22) as signed) = sin(22);
select cast(sin(22) as unsigned) = sin(22);

Suggested fix:
-

Thank you for a bug report. Verified just as described with 5.0.32-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.32-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select pi();
+----------+
| pi()     |
+----------+
| 3.141593 |
+----------+
1 row in set (0.00 sec)

mysql> create table tpi as select pi() as c1;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table tpi;
+-------+-----------------------------------------------------------------------
-------------------------------------+
| Table | Create Table
                                     |
+-------+-----------------------------------------------------------------------
-------------------------------------+
| tpi   | CREATE TABLE `tpi` (
  `c1` double(8,6) NOT NULL default '0.000000'
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------
-------------------------------------+
1 row in set (0.00 sec)

mysql> select pi()=3;
+--------+
| pi()=3 |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)

mysql> select cast(pi() as signed);
+----------------------+
| cast(pi() as signed) |
+----------------------+
|                    3 |
+----------------------+
1 row in set (0.01 sec)

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

This looks like a bug for me, but may be "explained" by the following quote from the manual (http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html):

" Note that if either operand is a floating-point value, the result is a floating-point value and is not affected by the preceding rule. (In this context, DECIMAL column values are regarded as floating-point values.)"

Anyway, having both above and following:

mysql> select cast(pi() as signed) = 3;
+--------------------------+
| cast(pi() as signed) = 3 |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set (0.00 sec)

true at the same time is inconsistent and, thus, is a bug.

mysql> select cast(pi() as signed) = 3.141593;
+---------------------------------+
| cast(pi() as signed) = 3.141593 |
+---------------------------------+
|                               0 |
+---------------------------------+
1 row in set (0.00 sec)

The above is a different story...

Not reproducible on recent 5.0, 5.1 and 6.0 trees. Fixed in 5.0+ by the changeset for bug #24912.