Bug #7036 converting from string to unsigned doesn't work properly
Submitted: 6 Dec 2004 12:58 Modified: 29 Apr 2005 13:56
Reporter: Gleb Paharenko Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.7 OS:Linux (Linux)
Assigned to: Michael Widenius

[6 Dec 2004 12:58] Gleb Paharenko
Description:
mysql> select cast(18446744073709551615 as char);
+------------------------------------+
| cast(18446744073709551615 as char) |
+------------------------------------+
| 18446744073709551615               |
+------------------------------------+

mysql> select cast('18446744073709551615' as unsigned);
+------------------------------------------+
| cast('18446744073709551615' as unsigned) |
+------------------------------------------+
|                      9223372036854775807 |
+------------------------------------------+

I think this select should return 18446744073709551615. Thanks Robin Bryce 
for founding this gap.

How to repeat:
mysql> select cast(18446744073709551615 as char);
+------------------------------------+
| cast(18446744073709551615 as char) |
+------------------------------------+
| 18446744073709551615               |
+------------------------------------+

mysql> select cast('18446744073709551615' as unsigned);
+------------------------------------------+
| cast('18446744073709551615' as unsigned) |
+------------------------------------------+
|                      9223372036854775807 |
+------------------------------------------+
[6 Dec 2004 13:28] Miguel Solorzano
Verified on 4.1.8 Bk source.
[6 Dec 2004 13:28] Hartmut Holzgraefe
CAST(18446744073709551613 AS UNSIGNED) -> 18446744073709551613
CAST('18446744073709551613' AS UNSIGNED) ->   9223372036854775807

i would expect the same result for both
[8 Mar 2005 0:45] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/22760
[11 Mar 2005 1:37] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/22924
[12 Mar 2005 1:53] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/22958
[29 Apr 2005 13:56] Michael Widenius
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Fix should be in 4.1.12 and 5.0.6

The fix also adds warnings for most wrong CAST(string as SIGNED / UNSIGNED) conversions
[6 Sep 2005 16:07] Jason Garber
The new change violates the documentation on hexadecimal values here:

http://dev.mysql.com/doc/mysql/en/hexadecimal-values.html

This works fine in 4.1.7, but in 4.1.12 it fails:

mysql> select CAST(0xB8 AS UNSIGNED INTEGER);
+--------------------------------+
| CAST(0xB8 AS UNSIGNED INTEGER) |
+--------------------------------+
|                              0 |
+--------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: '?' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)
[21 Sep 2005 14:28] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30157