Bug #24912 Submitted: problems with bigint in abs() ceiling() ruond() truncate() mod() 8 Dec 2006 14:41 4 May 2007 18:05 Martin Friebe (Gold Quality Contributor) (OCA) Closed None MySQL Server: Data Types S3 (Non-critical) 4.1.22, 5.0.27, 5.0.32-BK Linux (Linux, freebsd) Alexey Kopytov Any 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.```