MySQL Bugs: #19955: unsigned bigint treated as signed by MOD

Bug #19955	unsigned bigint treated as signed by MOD
Submitted:	19 May 2006 21:06	Modified:	11 Dec 2006 10:11
Reporter:	Michael Del Monte
Status:	Closed
Category:	Server	Severity:	S2 (Serious)
Version:	5.0.22-BK, 5.0.20	OS:	Linux (Linux, Windows XP)
Assigned to:	Bugs System	Target Version:

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.

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)

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.

Looks good to me.

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.