Bug #50567 CAST from large double to integer produces inconsistent results
Submitted: 23 Jan 2010 15:46 Modified: 23 Jan 2010 16:59
Reporter: Alexey Kopytov Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.1+ OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[23 Jan 2010 15:46] Alexey Kopytov
Description:
mysql> select cast(1e308 as signed);
+-----------------------+
| cast(1e308 as signed) |
+-----------------------+
|   9223372036854775807 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select cast(1e308 + 0 as signed);
+---------------------------+
| cast(1e308 + 0 as signed) |
+---------------------------+
|      -9223372036854775808 |
+---------------------------+
1 row in set (0.00 sec)

mysql> select cast(1e308 as unsigned);
+-------------------------+
| cast(1e308 as unsigned) |
+-------------------------+
|     9223372036854775807 |
+-------------------------+
1 row in set (1.68 sec)

mysql> select cast(1e308 + 0 as unsigned);
+-----------------------------+
| cast(1e308 + 0 as unsigned) |
+-----------------------------+
|         9223372036854775808 |
+-----------------------------+
1 row in set (0.94 sec)

How to repeat:
select cast(1e308 as signed);
select cast(1e308 + 0 as signed);
select cast(1e308 as unsigned);
select cast(1e308 + 0 as unsigned);
[23 Jan 2010 16:59] Valeriy Kravchuk
Verified just as described:

77-52-24-143:6.0-codebase openxs$ 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 2
Server version: 6.0.14-alpha-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

mysql> select cast(1e308 + 0 as signed);
+---------------------------+
| cast(1e308 + 0 as signed) |
+---------------------------+
|      -9223372036854775808 |
+---------------------------+
1 row in set (0.00 sec)

mysql> select cast(1e308 as unsigned);
+-------------------------+
| cast(1e308 as unsigned) |
+-------------------------+
|     9223372036854775807 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select cast(1e308 + 0 as unsigned);
+-----------------------------+
| cast(1e308 + 0 as unsigned) |
+-----------------------------+
|         9223372036854775808 |
+-----------------------------+
1 row in set (0.00 sec)