Bug in calculations of UNSIGNED int

[Eduard Boer]

Hi,

I have 2 databases. One on a 32 bit system, and one on AMD64.

mysql behaves different on both. Example:

create table SPS (
ID CHAR(32) NOT NULL UNIQUE PRIMARY KEY,
S91 TINYINT UNSIGNED NOT NULL DEFAULT 0
);

INSERT INTO SPS (ID,S91) VALUES
('4f2271284f2271284f2271284f227128',222);

SELECT S91,S91-1,S91-222,POW(S91-223,2) FROM SPS WHERE
ID='4f2271284f2271284f2271284f227128';

The result on my AMD64 machine is:
"1";"222";"221";"0";"A really big number"

On my 32 bit machine the (correct!) result is:
"1";"222";"221";"0";"1"

I think this is a bug on my AMD64.


On my 32 bit machine I think I can also reproduce a bug:

SELECT S91,S91-1,S91-222,S91-223 FROM SPS WHERE
ID='4f2271284f2271284f2271284f227128';


The result:
"1";"222";"221";"0";"A really big number"

But the correct answer should be:
"1";"222";"221";"0";"-1"

I quess the work around is not setting the unsigned value. A 'signed smallint' instead of a 'unsigned tinyint' would probably help. But that would take twice as much harddisk space :( So IMHO this is a bug.

Best Regards,
Eduard