Bug #8663 cant use bgint unsigned as input to cast
Submitted: 21 Feb 2005 22:29 Modified: 4 Jun 2007 18:30
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1 OS:Any (*)
Assigned to: Alexey Botchkov CPU Architecture:Any

[21 Feb 2005 22:29] Martin Friebe
Description:
trying to cast a number bigger than 18446744073709551615 to unsigned returns the maximum signed bigint (9223372036854775808). 
I expected the cutoff at the maximum value for the data type (18446744073709551615 )

in other functions those numbers are used treaded as float, and handled currect, I tried to force float inside cast (+0.0 or just ..999.0) but it didnt help

How to repeat:
select cast(17999999999999999999 as unsigned);
+----------------------------------------+
| cast(17999999999999999999 as unsigned) |
+----------------------------------------+
|                   17999999999999999999 |
+----------------------------------------+

select cast(19999999999999999999 as unsigned);
+----------------------------------------+
| cast(19999999999999999999 as unsigned) |
+----------------------------------------+
|                    9223372036854775808 |
+----------------------------------------+
# should be 18446744073709551615 instead.

Suggested fix:
-
[7 Dec 2005 15:34] Alexander Nozdrin
Not 'In progress' actually.
[6 Jun 2006 9:26] Alexander Barkov
MySQL-5.0.22 demonstrates wrong behaviour as well,
but with a different result:

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

mysql> select cast(19999999999999999999 as unsigned);
+----------------------------------------+
| cast(19999999999999999999 as unsigned) |
+----------------------------------------+
|                            19999999999 |
+----------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+---------------------------------------+
| Level | Code | Message                               |
+-------+------+---------------------------------------+
| Error | 1292 | Truncated incorrect DECIMAL value: '' |
+-------+------+---------------------------------------+
1 row in set (0.00 sec)
[6 Jun 2006 9:28] Alexander Barkov
5.1.10 works the same as 5.0.22 does.
[14 Jun 2006 8:43] 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/commits/7606
[3 Oct 2006 20:03] Chad MILLER
Available in 5.0.26.
[3 Oct 2006 20:11] Chad MILLER
Available in 5.1.12-beta.
[4 Oct 2006 11:26] Alexander Barkov
Settting back to "in progress" as it needs additional fix for 5.0.
[4 Oct 2006 13:55] Chad MILLER
First patch is available in 4.1.22.
[12 Oct 2006 13:48] Paul DuBois
Noted in 4.1.22 changelog.

The result for CAST() when casting a value to UNSIGNED was limited to
the maximum signed BIGINT value (9223372036854775808), not the
maximum unsigned value (18446744073709551615).

Leaving report set to In Progress pending fix in 5.0/5.1 trees.
[30 Oct 2006 5:22] 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/commits/14529

ChangeSet@1.2289, 2006-10-30 09:52:50+04:00, holyfoot@mysql.com +4 -0
  Bug #8663 (cant use bigint as input to CAST)
  
  decimal->ulong conversion fixed to assign max possible ULONG if decimal
  is bigger
  Item_func_unsigned now handles DECIMAL parameter separately as we can't
  rely on decimal::val_int result here.
[1 Nov 2006 11:11] Alexander Barkov
The patch  "bk commit into 5.0 tree (holyfoot:1.2289) BUG#8663" is ok to push.
[27 Nov 2006 17:12] Georgi Kodinov
Pushed in 5.0.32/5.1.14-beta
[27 Nov 2006 19:13] Paul DuBois
Noted in 5.0.32, 5.1.14 changelogs.
[3 May 2007 20:07] Evgeny Potemkin
mysql> select cast(19999999999999999999 as signed);
+--------------------------------------+
| cast(19999999999999999999 as signed) |
+--------------------------------------+
|                          19999999999 | 
+--------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.42-debug | 
+--------------+
[15 May 2007 7:49] 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/commits/26672

ChangeSet@1.2489, 2007-05-15 11:48:54+05:00, holyfoot@mysql.com +3 -0
  Bug #8663 cant use bgint unsigned as input to cast
  decimal -> signed int conversion fixed to return LONGLONG_MAX (MIN)
  when we get huge decimal to convert.
[15 May 2007 15:33] 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/commits/26714

ChangeSet@1.2489, 2007-05-15 19:33:19+05:00, holyfoot@mysql.com +3 -0
  bug #8663 cant use bigint unsigned as input to cast
  in the case of the overflow in the decimal->integer conversion
  we didn't return the proper boundary value, but just the result
  of the conversion we calculated on the moment of the error
[16 May 2007 6:13] 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/commits/26761

ChangeSet@1.2489, 2007-05-16 10:12:49+05:00, holyfoot@mysql.com +3 -0
  bug #8663 cant use bigint unsigned as input to cast
  in the case of the overflow in the decimal->integer conversion
  we didn't return the proper boundary value, but just the result
  of the conversion we calculated on the moment of the error
[20 May 2007 17:11] Bugs System
Pushed into 5.0.44
[20 May 2007 17:13] Bugs System
Pushed into 5.1.19-beta
[4 Jun 2007 18:30] Paul DuBois
Moved 5.0 changelog entry from 5.0.32 to 5.0.44.
Moved 5.1 changelog entry from 5.1.14 to 5.1.20.
Removed entry from 5.0.33.