MySQL Bugs: #8663: cant use bgint unsigned as input to cast

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

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:
-

Not 'In progress' actually.

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)

5.1.10 works the same as 5.0.22 does.

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

Available in 5.0.26.

Available in 5.1.12-beta.

Settting back to "in progress" as it needs additional fix for 5.0.

First patch is available in 4.1.22.

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.

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.

The patch  "bk commit into 5.0 tree (holyfoot:1.2289) BUG#8663" is ok to push.

Pushed in 5.0.32/5.1.14-beta

Noted in 5.0.32, 5.1.14 changelogs.

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 | 
+--------------+

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.

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

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

Pushed into 5.0.44

Pushed into 5.1.19-beta

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.