Bug #19955 unsigned bigint treated as signed by MOD
Submitted: 19 May 2006 19:06 Modified: 11 Dec 2006 9:11
Reporter: Michael Del Monte Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.22-BK, 5.0.20 OS:Linux (Linux, Windows XP)
Assigned to: Ian Greenhoe CPU Architecture:Any

[19 May 2006 19:06] Michael Del Monte
Description:
Unsigned BIGINTs are used as signed with the MOD function.

How to repeat:
select cast(10000002383263201056 as unsigned) mod 50

returns -10.

create table b (id bigint unsigned) engine=myisam;
insert b values (10000002383263201056);

select id mod 50 from b;

will return -10.

Suggested fix:
Thorough check of bigint unsigned behavior.  There appear to be a variety of problems using unsigned bigints, all related to them being treated as signed.
[19 May 2006 19:44] Valeriy Kravchuk
Verified just as described with 5.0.22-BK on Linux:

mysql> select cast(10000002383263201056 as unsigned) mod 50;
+-----------------------------------------------+
| cast(10000002383263201056 as unsigned) mod 50 |
+-----------------------------------------------+
|                                           -10 |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> drop table b;
Query OK, 0 rows affected (0.01 sec)

mysql> create table b (id bigint unsigned) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> insert b values (10000002383263201056);
Query OK, 1 row affected (0.01 sec)

mysql> select id mod 50 from b;
+-----------+
| id mod 50 |
+-----------+
|       -10 |
+-----------+
1 row in set (0.00 sec)
[10 Sep 2006 9:31] 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/11661

ChangeSet@1.2272, 2006-09-10 02:30:39-07:00, igreenhoe@anubis.greendragongames.com +3 -0
  Fix for bug #19955 (unsigned bigint used as signed with MOD function)
  
  Problem:  When we have a really large number (between 2^63 and 2^64)
  as the left side of the mod operator, it gets improperly corerced
  into a signed value.
  
  Solution:  Added check to see if the "negative" number is really
  positive, and if so, cast it.
[27 Sep 2006 16:07] Chad MILLER
Looks good to me.
[11 Dec 2006 9:11] Jon Stephens
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 bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix for 5.0.32 and 5.1.15.