MySQL Bugs: #27483: Casting 'scientific notation type' to 'unsigned bigint' fails on windows.

Bug #27483	Casting 'scientific notation type' to 'unsigned bigint' fails on windows.
Submitted:	27 Mar 2007 20:28	Modified:	28 Jan 2009 21:49
Reporter:	Kristofer Pettersson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0BK/5.1BK	OS:	Windows (Windows)
Assigned to:	Alexey Kopytov	CPU Architecture:	Any

Description:
1.84e+19 converted to unsigned bigint should be converted to
18400000000000000000 < 18446744073709551615.

Instead it is displayed as 9223372036854775808 which is the upper bound for a signed bigint +1.

This only happens on Windows.

This bug is slightly similar to bug17412, but only with numbers in scientific notation.

How to repeat:
delimiter |;
create function bug20777(f1 bigint unsigned) returns bigint unsigned
begin
set f1 = (f1 - 10); set f1 = (f1 + 10);
return f1;
end|
delimiter ;|
select bug20777(1.84e+19);
> 9223372036854775808
select bug20777(18400000000000000000);
> 18400000000000000000
select bug20777(18900000000000000000);
> 18446744073709551615

create table t1 (bigint_col bigint unsigned);
insert into t1 values (1.84e+19);
insert into t1 values (18400000000000000000);
insert into t1 values (18900000000000000000);
select * from t1;
> 9223372036854775808
> 18400000000000000000
> 18446744073709551615

Thank you for a problem report. What exact version(s) of MySQL server you had checked?

Thank you for the report.

Verified with a bit different results:

create function bug20777(f1 bigint unsigned) returns bigint unsigned
begin
set f1 = (f1 - 10); set f1 = (f1 + 10);
return f1;
end|
select bug20777(1.84e+19);
bug20777(1.84e+19)
-9223372036854775808
select bug20777(18400000000000000000);
bug20777(18400000000000000000)
-46744073709551616
select bug20777(18900000000000000000);
bug20777(18900000000000000000)
-1
Warnings:
Warning 1264    Out of range value adjusted for column 'f1' at row 1
create table t1 (bigint_col bigint unsigned);
insert into t1 values (1.84e+19);
insert into t1 values (18400000000000000000);
insert into t1 values (18900000000000000000);
Warnings:
Warning 1264    Out of range value adjusted for column 'bigint_col' at row 1
select * from t1;
bigint_col
9223372036854775808
18400000000000000000
18446744073709551615

Verified with 5.0.37 and 5.0.36 binaries

Just to clarify the original bug report. The testcase for this bug demonstrates 2 independent bugs:

1. Item_func_sp handles BIGINT UNSIGNED values incorrectly.
2. Large float/double values are incorrectly converted to BIGINT UNSIGNED.

The first one is fixed separately as bug #20777 by Kristofer.
The latter is a Windows-specific one. I cannot reproduce it on Linux, and don't have a suitable Windows environment to debug this, so I'm putting this bug back to Verified.

A shorter test case:

create table t1(a bigint unsigned);
insert into t1 values (1.84e19);
select * from t1;

On Linux the result is 18400000000000000000 (correct). On Windows it is 9223372036854775808 (wrong).

Root cause analysis:
--------------------

The actual conversion from DOUBLE values to BIGINT UNSIGNED ones is performed by the following code in Field_longlong::store(double):

      res=(longlong) (ulonglong) nr; 

It turns out the above code does always work as one would expect on Windows.

Background:
-----------

On x86 hardware the conversion from double to longlong/ulonglong is done via the FISTP instruction. The problem is that FISTP can only work with _signed_ integers as its destination format. That is, when the input double value is outside of the longlong range (which is the case for any double value x such that x < -2^63 or x > 2^63 - 1), an invalid-arithmetic-operand-condition is signaled and the value of 0x80000000 is stored to the destination address (Intel docs say 'undefined value' but in reality it is always 0x80000000).

GCC bypasses this hardware limitation by implementing the following workaround (below is abbreviated assembly code generated for "double d; unsigned long long u = d;"):

LC0:
        .long   0
        .long   1138753536
_main:
        pushl   %ebp
        movl    %esp, %ebp
        subl    $40, %esp
        call    L5
"L00000000001$pb":
L5:
        popl    %ecx
        fldl    -24(%ebp)
        leal    LC0-"L00000000001$pb"(%ecx), %eax
        fldl    (%eax)
        fxch    %st(1)
        fucomip %st(1), %st
        fstp    %st(0)
        jae     L2
        fldl    -24(%ebp)
        fistpll -16(%ebp)
        jmp     L3
L2:
        fldl    -24(%ebp)
        leal    LC0-"L00000000001$pb"(%ecx), %eax
        fldl    (%eax)
        fsubrp  %st, %st(1)
        fistpll -16(%ebp)
        movl    -16(%ebp), %eax
        xorb    $0, %ah
        movl    -12(%ebp), %edx
        xorl    $-2147483648, %edx
        movl    %eax, -16(%ebp)
        movl    %edx, -12(%ebp)

That is, if the input double number is greater than (double)0x80000000ULL, this constant is subtracted from the input number, then the FISTP instruction is called to convert the resulting number to a _signed_ 8-byte integer, then 0x80000000 is added back to the resulting integer by flipping the highest-order bit.

Microsoft Visual Studio utilizes a CRT function _ftol() (in VS 6) or an optimized version, _ftol2() (in later versions) to perform double -> longlong/ulonglong conversions. Both of those function do not implement any workarounds for unsigned conversions. That is, if the input double value is outside of the signed longlong range, the result of the conversion will always be 0x80000000ULL = 9223372036854775808ULL = -9223372036854775808LL which is not what one would expect.

Possible solution:
------------------

It seems the only possible solution to this problem is to implement our own workaround on Windows in the way GCC does it on all other platforms. I am not sure if the code in Field_longlong::store(double) is the only place to fix, there may be more.

there's ulonglong2double() function in config-win.h (on other platforms it's #define'd as (double) (ulonglong) (A)).

perhaps windows needs double2ulonglong() too ?

Bug #37745 marked as a duplicate

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/60510

2732 Alexey Kopytov	2008-12-03
      Fix for bug #27483: Casting 'scientific notation type' to 'unsigned 
                          bigint' fails on windows.
      
      Visual Studio does not take into account some x86 hardware limitations
      which leads to incorrect results when converting large DOUBLE values
      to BIGINT UNSIGNED ones.
      
      Fixed by adding a workaround for double->ulonglong conversion on
      Windows.

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/61003

2736 Alexey Kopytov	2008-12-09
      Fixed type_float failures in --ps-protocol mode introduced by the test case for bug #27483.
      
      The reason for the failures was bug #21205 (fixed in 6.0 by dtoa, but still present in 5.0/5.1).

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/61026

2737 Alexey Kopytov	2008-12-09
      Added a missing bit from the original patch for bug #27483 which was lost when re-applying
      the patch manually to another tree.

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/61209

2739 Alexey Kopytov	2008-12-10
      Fix for a test failure on Solaris/x86/gcc introduced by the patch for bug #27483.
      Removed values with more than 15 significant digits from the test case. Results of 
      reading/printing such values using system library functions depend on implementation 
      and thus are not portable.

Pushed into 5.0.76 (revid:joro@sun.com-20090105160414-8q9j4bi1klkfwiup) (version source revid:azundris@mysql.com-20081230114734-nmsc37ak330zlygn) (merge vers: 5.0.76) (pib:6)

Noted in 5.0.76 changelog.

On Windows, Visual Studio does not take into account some x86
hardware limitations, which led to incorrect results converting large
DOUBLE values to BIGINT UNSIGNED values. 

Setting report to NDI pending push into 5.1.x/6.0.x.

Pushed into 5.1.31 (revid:joro@sun.com-20090115053147-tx1oapthnzgvs1ro) (version source revid:azundris@mysql.com-20081230114838-cn52tu180wcrvh0h) (merge vers: 5.1.31) (pib:6)

Noted in 5.1.31 changelog.

Setting report to NDI pending push into 6.0.x.

Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090115073240-1wanl85vlvw2she1) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)

Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)

Setting status back to NDI pending merge to 6.0 tree.

Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)

Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:azundris@mysql.com-20081230114916-c290n83z25wkt6e4) (merge vers: 6.0.9-alpha) (pib:6)

Noted in 6.0.10 changelog.