Bug #24912 problems with bigint in abs() ceiling() ruond() truncate() mod()
Submitted: 8 Dec 2006 14:41 Modified: 4 May 2007 18:05
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:4.1.22, 5.0.27, 5.0.32-BK OS:Linux (Linux, freebsd)
Assigned to: Alexey Kopytov CPU Architecture:Any
Tags: BIGINT

[8 Dec 2006 14:41] Martin Friebe 
Description:
I have tested a whole range of mathematical functions with bigint and cast_as_bigint values, and got a lot of unexpected results.

I have only checked the functions listed, I dont know about other functions...

18446744073709551614 = cast(-2 as unsigned)

under each line, there are 3 values listed:
"expected", "got 4.1", "got 5.0"

 select abs( cast( -2 as unsigned ) );
# 18446744073709551614 / 2 / 2
 select abs(18446744073709551614);
# 18446744073709551614 / 2 / 2

 select ceiling( cast( -2 as unsigned ) ); # works 5.0
# 18446744073709551614 / -2 
 select ceiling(18446744073709551614);
# 18446744073709551614 / -9223372036854775808

 select floor( cast( -2 as unsigned ) ); # works 5.0
# 18446744073709551614 / -2 
 select floor(18446744073709551614);
# 18446744073709551614 / -9223372036854775808

 select format( cast( -2 as unsigned ), 2 ); # works 5.0
#  18,446,744,073,709,551,616.00 / -2.00
 select format(18446744073709551614, 2);
# works fine in both

 select sqrt( cast( -2 as unsigned ) );
# 4294967296 / null / null
# null as error indicator, but the below works...
 select sqrt(18446744073709551614);
# works fine in both (actually not checked the result)

 select round( 18446744073709551614, 1 );
# works fine in both
 select round( 4, 18446744073709551614  );
# ?? / 0 / 0
# documentation specifies no uper limit for 2nd arg
# in case of error, null would be documented, but 0?
 select round( cast( -2 as unsigned ), 1 ); # works 5.0
# 18446744073709551616.0 / -2.0
 select round( 4, cast( -2 as unsigned )  );
# ?? / 0 / 0
# documentation specifies no uper limit for 2nd arg
# in case of error, null would be documented, but 0?

# truncate behaves like round

 select mod( 18446744073709551614, 3 );
# 2 / 18446744073709551614 / -2
 select mod( 5, 18446744073709551614  );
# 5 / 1 / 1
 select mod( cast( -2 as unsigned ), 3 );
# 2 / 18446744073709551614 / -2
 select mod( 5, cast( -2 as unsigned )  );
# 5 / 1 / 1

How to repeat:
 select abs( cast( -2 as unsigned ) );
 select abs(18446744073709551614);

 select ceiling( cast( -2 as unsigned ) );
 select ceiling(18446744073709551614);

 select floor( cast( -2 as unsigned ) );
 select floor(18446744073709551614);

 select format( cast( -2 as unsigned ), 2 );
 select format(18446744073709551614, 2);

 select sqrt( cast( -2 as unsigned ) );
 select sqrt(18446744073709551614);

 select round( 18446744073709551614, 1 );
 select round( 4, 18446744073709551614  );
 select round( cast( -2 as unsigned ), 1 );
 select round( 4, cast( -2 as unsigned )  );

 select truncate( 18446744073709551614, 1 );
 select truncate( 4, 18446744073709551614  );
 select truncate( cast( -2 as unsigned ), 1 );
 select truncate( 4, cast( -2 as unsigned )  );

 select mod( 18446744073709551614, 3 );
 select mod( 5, 18446744073709551614  );
 select mod( cast( -2 as unsigned ), 3 );
 select mod( 5, cast( -2 as unsigned )  );

Suggested fix:
-
[8 Dec 2006 15:12] Martin Friebe 
pow() is also affected
[8 Dec 2006 16:32] Valeriy Kravchuk 
Thank you for a detailed bug report. Verified just as described with latest 5.0.32-BK on 32-bit Linux:

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

mysql> select cast( -2 as unsigned );
+------------------------+
| cast( -2 as unsigned ) |
+------------------------+
|   18446744073709551614 |
+------------------------+
1 row in set (0.01 sec)

...

and so on.
[24 Jan 2007 16:53] 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/18730

ChangeSet@1.2608, 2007-01-24 08:53:24-08:00, igreenhoe@ra.greendragongames.com +4 -0
  Fix for bug #24912 (misc functions having trouble with unsigned)
  
  This patch is for 4.1 version of the server.
  
  Problem:  Needed to check/set unsigned_flag in a number of places.  Also need to
  have the variable being assigned to from "val_int" be a longlong type or casted
  to ulonglong.
[24 Jan 2007 16:54] 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/18731

ChangeSet@1.2391, 2007-01-24 08:53:35-08:00, igreenhoe@ra.greendragongames.com +4 -0
  Fix for bug #24912 (misc functions having trouble with unsigned)
  
  This patch is for 5.0/5.1 version of the server. This is *not* cumulative with the 4.1
  patch.
  
  Problem:  Needed to check/set unsigned_flag in a number of places.  Also need to
  have the variable being assigned to from "val_int" be a longlong type or casted
  to ulonglong.
[7 Feb 2007 16:30] Chad MILLER 
Review from Brian: Follow code standards, e.g., correct spacing around assignment "=".
[7 Mar 2007 17:26] 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/21389

ChangeSet@1.2626, 2007-03-07 09:25:09-08:00, igreenhoe@ra.greendragongames.com +4 -0
  Fix for bug #24912: problems with bigint in abs() ceiling() ruond() truncate() mod()
  
  Changed code to look for unsigned_flag in appropriate places.
[2 Apr 2007 13:25] 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/23545

ChangeSet@1.2403, 2007-04-02 17:25:07+04:00, kaa@polly.local +7 -0
  Fix for bug #24912 "problems with bigint in abs() ceiling() round() truncate() mod()" and a number of related problems:
  
  - unsigned flag was not handled correctly for a number of mathematical funcions, which led to incorrect results
  - passing large values as the number of decimals to ROUND() resulted in incorrect results and even server crashes in some cases
  - reverted the fix and the testcase for bug #10083 as it violates the manual
  - fixed some testcases which relied on broken ROUND() behavior
[26 Apr 2007 8:39] 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/25487

ChangeSet@1.2403, 2007-04-26 12:39:14+04:00, kaa@polly.local +7 -0
  Fix for bug #24912 "problems with bigint in abs() ceiling() round() truncate() mod()" and a number of related problems:
  
  - unsigned flag was not handled correctly for a number of mathematical funcions, which led to incorrect results
  - passing large values as the number of decimals to ROUND() resulted in incorrect results and even server crashes in some cases
  - reverted the fix and the testcase for bug #10083 as it violates the manual
  - fixed some testcases which relied on broken ROUND() behavior
[27 Apr 2007 11:59] 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/25601

ChangeSet@1.2403, 2007-04-27 15:58:45+04:00, kaa@polly.local +7 -0
  Fix for bug #24912 "problems with bigint in abs() ceiling() round() truncate() mod()" and a number of related problems:
  
  - unsigned flag was not handled correctly for a number of mathematical funcions, which led to incorrect results
  - passing large values as the number of decimals to ROUND() resulted in incorrect results and even server crashes in some cases
  - reverted the fix and the testcase for bug #10083 as it violates the manual
  - fixed some testcases which relied on broken ROUND() behavior
[27 Apr 2007 12:44] 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/25605

ChangeSet@1.2403, 2007-04-27 16:44:24+04:00, kaa@polly.local +7 -0
  Fix for bug #24912 "problems with bigint in abs() ceiling() round() truncate() mod()" and a number of related problems:
  
  - unsigned flag was not handled correctly for a number of mathematical funcions, which led to incorrect results
  - passing large values as the number of decimals to ROUND() resulted in incorrect results and even server crashes in some cases
  - reverted the fix and the testcase for bug #10083 as it violates the manual
  - fixed some testcases which relied on broken ROUND() behavior
[28 Apr 2007 11:39] 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/25666

ChangeSet@1.2402, 2007-04-28 15:35:23+04:00, kaa@polly.local +7 -0
  Fix for bug #24912 "problems with bigint in abs() ceiling() round() truncate() mod()" and a number of related problems:
  
  - unsigned flag was not handled correctly for a number of mathematical funcions, which led to incorrect results
  - passing large values as the number of decimals to ROUND() resulted in incorrect results and even server crashes in some cases
  - reverted the fix and the testcase for bug #10083 as it violates the manual
  - fixed some testcases which relied on broken ROUND() behavior
[28 Apr 2007 16:01] 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/25673

ChangeSet@1.2402, 2007-04-28 20:01:01+04:00, kaa@polly.local +7 -0
  Fix for bug #24912 "problems with bigint in abs() ceiling() round() truncate() mod()" and a number of related problems:
  
  - unsigned flag was not handled correctly for a number of mathematical funcions, which led to incorrect results
  - passing large values as the number of decimals to ROUND() resulted in incorrect results and even server crashes in some cases
  - reverted the fix and the testcase for bug #10083 as it violates the manual
  - fixed some testcases which relied on broken ROUND() behavior
[28 Apr 2007 19:25] 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/25678

ChangeSet@1.2465, 2007-04-28 23:25:31+04:00, kaa@polly.local +1 -0
  Avoid compiler warnings in Windows builds introduced by the patch for bug #24912 "problems with bigint in abs() ceiling() ruond() truncate() mod()"
[1 May 2007 20:58] Bugs System 
Pushed into 5.1.18-beta
[1 May 2007 20:59] Bugs System 
Pushed into 5.0.42
[4 May 2007 18:05] Paul DuBois 
Noted in 5.0.42, 5.1.18 changelogs.

Several math functions produced incorrect results for large unsigned
values. ROUND() produced incorrect results or a crash for a large
number-of-decimals argument.