Bug #15098 CAST(column double TO signed int), wrong result
Submitted: 21 Nov 2005 13:17 Modified: 4 Apr 2006 13:52
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1 OS:
Assigned to: Alexander Barkov CPU Architecture:Any

[21 Nov 2005 13:17] Matthias Leich
Description:
CREATE TABLE t1 (f1 double, f2 BIGINT);
INSERT INTO t1 SET f1 = -1.0e+30 ;
INSERT INTO t1 SET f1 = +1.0e+30 ;
SELECT f1 AS double_val, CAST(f1 AS   SIGNED INT) AS cast_val FROM t1;
double_val	cast_val
-1e+30	-9223372036854775808
1e+30	-9223372036854775808   <-- Wrong, the value has to be
                                           (positive) 9223372036854775807

Some statements for comparison
# explicit CAST of double constant
SELECT +1.0e+30 AS double_val, CAST(+1.0E+30 AS   SIGNED INT) as cast_val;
double_val	cast_val
1e+30	9223372036854775807      <-- correct
# implicit CAST of double column content
UPDATE t1 SET f2 = f1;
Warnings:
Warning	1264	Out of range value adjusted for column 'f2' at row 1
Warning	1264	Out of range value adjusted for column 'f2' at row 2
SELECT f1 AS double_val, f2 AS impl_cast_val FROM t1;
double_val	impl_cast_val
-1e+30	-9223372036854775808
1e+30	9223372036854775807       <-- correct

My environment:
   - Intel PC with Linux(SuSE 9.3)
   - MySQL compiled from source
        Version 4.1 ChangeSet@1.2465.1.1, 2005-11-10
        Version 5.0 ChangeSet@1.1971.4.1, 2005-11-19

Please have a look on the fixed Bug #13344.

How to repeat:
Please execute the statements above or use the
attached testscript.
  copy it to mysql-test/t
  touch r/ml002.result     # Produce a dummy file with 
                                    # expected results
  ./mysql-test-run ml002
  inspect r/ml002.reject
[21 Nov 2005 13:19] Matthias Leich
test case

Attachment: ml002.test (application/test, text), 561 bytes.

[21 Nov 2005 13:19] Matthias Leich
my test result

Attachment: ml002.reject (application/octet-stream, text), 800 bytes.

[6 Dec 2005 12:57] 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/internals/33070
[28 Mar 2006 12:14] 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/4231
[29 Mar 2006 14:38] 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/4278
[31 Mar 2006 16:40] Alexander Barkov
Fixed in 4.1.19, 5.0.21, 5.1.9
[4 Apr 2006 13:52] Paul Dubois
Noted in 4.1.19, 5.0.21, 5.1.9 changelogs.

<literal>CAST<replaceable>double</replaceable> AS SIGNED
INT)</literal> for large <replaceable>double</replaceable>
values outside the signed integer range truncates the result
to be within range, but the result sometimes had the wrong
sign, and no warning was generated. (Bug #15098)

(a warning occurs only in 5.0 and up)