Bug #24911 sum with cast as unsigned, wrong result

• Submitted: 8 Dec 2006 13:52
• Modified: 7 Feb 2008 12:45
• Reporter: Martin Friebe (Gold Quality Contributor) (OCA)
• Status: Won't fix
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 4.1.22
• OS: Any
• Assigned to: Gleb Shchepa
• CPU Architecture: Any
• Tags: cast, qc, sum, UNSIGNED

[8 Dec 2006 13:52] Martin Friebe

Description:
Casting a negative number as unsigned, gives you a positive integer.

SUM() does not regochnize this, and returns the negative result

mysql 5.0.27 works correct

select sum( cast( -2 as unsigned) ) from (select 1 union select 2) x;

gives 36893488147419103228 on mysql 5 (correct)
gives -4 on mysql 4.1.22 (wrong)

How to repeat:
select sum( cast( -2 as unsigned) ) from (select 1 union select 2) x;

Suggested fix:
-

[8 Dec 2006 13:56] Martin Friebe

max(), min(), bit_and() are fine

avg() is affected too

std / variance seems affected:
 select std( cast( a as unsigned) ) from (select -2 a union select 0) x;

[8 Dec 2006 14:00] Martin Friebe

same thing the other way round:

select  cast( 18446744073709551614 as signed);
# is -2
select sum( cast( 18446744073709551614 as signed)  ) from (select 1 union select 2) x;
# is 36893488147419103232 on 4.1.22

[8 Dec 2006 14:48] Hartmut Holzgraefe

mysqltest test case

Attachment: bug24911.tgz (application/x-gtar, text), 797 bytes.