Bug #44478 bigint mod bigint munges least significant byte
Submitted: 25 Apr 2009 20:21 Modified: 25 Apr 2009 21:59
Reporter: Anthon Pang Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.0.75 OS:Linux (Ubuntu 9.04)
Assigned to: MySQL Verification Team CPU Architecture:Any

[25 Apr 2009 20:21] Anthon Pang
Description:
mysql  Ver 14.12 Distrib 5.0.75, for debian-linux-gnu (i486) using readline 5.2

The result of a bigint modulo another bigint is faulty in MySQL 5.0.75.  It appears that the least significant byte is munged.

These work as expected:

mysql> select conv('8E2EE04D',16,10) % pow(2,32);
+------------------------------------+
| conv('8E2EE04D',16,10) % pow(2,32) |
+------------------------------------+
|                         2385436749 | 
+------------------------------------+
1 row in set (0.00 sec)

mysql> select conv('1FFFF8E2EE04D',16,10) % pow(2,32);
+-----------------------------------------+
| conv('1FFFF8E2EE04D',16,10) % pow(2,32) |
+-----------------------------------------+
|                              2385436749 | 
+-----------------------------------------+
1 row in set (0.00 sec)

How to repeat:
The use of conv() in my examples is only for demonstration purposes, i.e., to show that the low order 32 bits is the same throughout (i.e., 8E2EE04D in base 16).

Here's the oddness:

mysql> select conv('2000008E2EE04D',16,10) % pow(2,32);
+------------------------------------------+
| conv('2000008E2EE04D',16,10) % pow(2,32) |
+------------------------------------------+
|                               2385436748 | 
+------------------------------------------+
1 row in set (0.00 sec)

mysql> select conv('F000008E2EE04D',16,10) % pow(2,32);
+------------------------------------------+
| conv('F000008E2EE04D',16,10) % pow(2,32) |
+------------------------------------------+
|                               2385436752 | 
+------------------------------------------+
1 row in set (0.00 sec)

mysql> select conv('FFFFFFF8E2EE04D',16,10) % pow(2,32);
+-------------------------------------------+
| conv('FFFFFFF8E2EE04D',16,10) % pow(2,32) |
+-------------------------------------------+
|                                2385436800 | 
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select conv('FFFFFFFF8E2EE04D',16,10) % pow(2,32);
+--------------------------------------------+
| conv('FFFFFFFF8E2EE04D',16,10) % pow(2,32) |
+--------------------------------------------+
|                                 2385436672 | 
+--------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
In each of the above cases, the expected result is 2385436749.

It appears from the anomalies above that the divisor is limited to 47 bits.
[25 Apr 2009 21:59] MySQL Verification Team
Thank you for the bug report. Please start you mysql client with the option -T to see the data type DOUBLE returned by these queries (see below):

miguel@txg:~/dbs/5.0$ bin/mysql -uroot -T
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1                            
Server version: 5.0.82-debug Source distribution         

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select conv('2000008E2EE04D',16,10) % pow(2,32);
Field   1:  `conv('2000008E2EE04D',16,10) % pow(2,32)` 
Catalog:    `def`                                      
Database:   ``                                         
Table:      ``                                         
Org_table:  ``
Type:       DOUBLE
Collation:  binary (63)
Length:     23
Max_length: 10
Decimals:   31
Flags:      BINARY NUM

+------------------------------------------+
| conv('2000008E2EE04D',16,10) % pow(2,32) |
+------------------------------------------+
|                               2385436748 |
+------------------------------------------+
1 row in set (0.04 sec)

Then using cast():

miguel@txg:~/dbs/5.0$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2                            
Server version: 5.0.82-debug Source distribution         

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select cast(conv('2000008E2EE04D',16,10) as unsigned) % cast(pow(2,32) as unsigned);
+------------------------------------------------------------------------------+           
| cast(conv('2000008E2EE04D',16,10) as unsigned) % cast(pow(2,32) as unsigned) |           
+------------------------------------------------------------------------------+           
|                                                                   2385436749 |           
+------------------------------------------------------------------------------+           
1 row in set (0.00 sec)                                                                    

mysql> select cast(conv('F000008E2EE04D',16,10) as unsigned) % cast(pow(2,32) as unsigned);
+------------------------------------------------------------------------------+
| cast(conv('F000008E2EE04D',16,10) as unsigned) % cast(pow(2,32) as unsigned) |
+------------------------------------------------------------------------------+
|                                                                   2385436749 |
+------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select cast(conv('FFFFFFF8E2EE04D',16,10) as unsigned) % cast(pow(2,32) as unsigned);
+-------------------------------------------------------------------------------+
| cast(conv('FFFFFFF8E2EE04D',16,10) as unsigned) % cast(pow(2,32) as unsigned) |
+-------------------------------------------------------------------------------+
|                                                                    2385436749 |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select cast(conv('FFFFFFFF8E2EE04D',16,10) as unsigned) % cast(pow(2,32) as unsigned);
+--------------------------------------------------------------------------------+
| cast(conv('FFFFFFFF8E2EE04D',16,10) as unsigned) % cast(pow(2,32) as unsigned) |
+--------------------------------------------------------------------------------+
|                                                                     2385436749 |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>