Bug #17175 Results different for bigint operation on SCO OpenServer platform
Submitted: 7 Feb 2006 1:26 Modified: 7 Jun 2006 20:27
Reporter: Alexey Stroganov Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.17a-cert OS:Other (SCO OpenServer 6.0)
Assigned to: CPU Architecture:Any

[7 Feb 2006 1:26] Alexey Stroganov
Description:
We got different results for test case below(see how to repeat section) for binaries on 
regular Linux and on SCO OpenServer box:

[Linux box]mysql test < bug.sql
fn3(1.84e+17)
184000000000000000

[SCO OpenServer box]
fn3(1.84e+17)
0

How to repeat:
DROP FUNCTION IF EXISTS fn3;

delimiter |

CREATE FUNCTION fn3( f1 bigint unsigned zerofill) returns bigint unsigned zerofill
BEGIN
   set f1 = (f1 / 2);
   set f1 = (f1 * 2);
   set f1 = (f1 - 10);
   set f1 = (f1 + 10);
   return f1;
END|

delimiter ;

SELECT fn3(1.84e+17);
[7 Feb 2006 2:53] Brian Aker
Results like this are platform dependent. Use DECIMAL if you want exact results.
[7 Feb 2006 13:59] Alexey Stroganov
I still believe that there is platform specific bug in code that deal with BIGINT:

SCO box:

MAX BIGINT (1):
Requested value(2):

(1)9223372036854775807
(2)184000000000000000

But:

mysql> select  CAST(1.84E+17 AS SIGNED INT) ;
+------------------------------+
| CAST(1.84E+17 AS SIGNED INT) |
+------------------------------+
|          9223372036854775807 |
+------------------------------+
1 row in set (0.00 sec)

mysql> select  CAST(1.84E+17 AS UNSIGNED INT) ;
+--------------------------------+
| CAST(1.84E+17 AS UNSIGNED INT) |
+--------------------------------+
|            9223372036854775807 |
+--------------------------------+
1 row in set (0.00 sec)

The same statements on the linux box:

mysql> select CAST(1.84E+17 as SIGNED INT);
+------------------------------+
| CAST(1.84E+17 as SIGNED INT) |
+------------------------------+
|           184000000000000000 |
+------------------------------+
1 row in set (0.00 sec)

mysql> select CAST(1.84E+17 as UNSIGNED INT);
+--------------------------------+
| CAST(1.84E+17 as UNSIGNED INT) |
+--------------------------------+
|             184000000000000000 |
+--------------------------------+
1 row in set (0.00 sec)

So even without any arithmetic operations  value 1.84E+17 was interpreter as MAX BIGINT.

Is it expected behaviour? If yes then I failed to find it in our manual.
[8 Feb 2006 15:05] Alexey Stroganov
Overflow happens for cast operations for any value bigger than 2^32. 

mysql>  select  CAST(4.295E+9 AS SIGNED INT) ;
+--------------------------------------+
| CAST(4.295E+9 AS SIGNED INT) |
+--------------------------------------+
|              9223372036854775807 |
+---------------------------------------+
1 row in set (0.00 sec)

So it looks that binary was compiled with max 4 bytes for BIGINT  field.
[8 Feb 2006 15:08] Alexey Stroganov
Possible  that some macros or defines on SCO box work in a bit different way.

Found that in config.log we have ac_cv_sizeof_long_long=8 so it should work in right way but it doesn't.  Investigating...
[9 Feb 2006 17:21] Alexey Stroganov
Further investigation of this issue showed that problem is in casting of long long value to double in the  'sql/item.cc:Item_float::val_int()'. The problem arised only in case when code below was compiled with /opt/K/SCO/unixds/6.0.0Ni/usr/ccs/bin/CC compiler. When code was compiled with /opt/K/SCO/Unix/6.0.0Ni/usr/ccs/bin/cc all looks ok.

It is possible to verify with following small C program:

#include <stdio.h>
main()
{
  printf("Value |%lg|\n",(double)(unsigned long long)0x7FFFFFFFFFFFFFFFLL);
}

Further investigation requried.
[9 Feb 2006 17:23] Alexey Stroganov
Result in case of cc compiler(correct) - 9.22337e+18
Result in case of CC compiler(wrong) - 4.29497e+09
[14 Feb 2006 13:59] Alexey Stroganov
Further investigantion shows that problem is in CC(c++) compiler that cast long long to double incorrectly.
[6 Jun 2006 15:06] Alexey Stroganov
The issue was fixed with compiler update from SCO in the end of February. Final 5.0.17a-cert binaries for SCO were built with proper compilers. I've just forget to update bug entry with this information.