MySQL Bugs: #17175: Results different for bigint operation on SCO OpenServer platform

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

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);

Results like this are platform dependent. Use DECIMAL if you want exact results.

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.

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.

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...

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.

Result in case of cc compiler(correct) - 9.22337e+18
Result in case of CC compiler(wrong) - 4.29497e+09

Further investigantion shows that problem is in CC(c++) compiler that cast long long to double incorrectly.

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.