List:General Discussion« Previous MessageNext Message »
From:James Harvard Date:December 24 2005 12:59am
Subject:Re: Strange behavior with integer unsigned type...
View as plain text  
I don't think that this behaviour is very surprising. If you carry out a mathmaticical
operation that returns a result outside the data type's range then it _must_ give you an
incorrect result. The only alternative would be to throw an error.

I know that the manual documents that after an auto_increment column hits its maximum
value it will 'roll over' to the lowest value that column will store (i.e. 0 for an
unsigned int).

Probably if you add 1 to 2^32 (or 2^16 for a SMALLINT, for example) you will get 0.

BTW, 18446744073709551615 is the maximum value for a BIGINT (64 bit number), and IIRC
MySQL uses 64 bit maths.

FWIW my preferred web app middleware - Lasso - does the same thing (only with signed 64
bit numbers).

HTH,
James Harvard

At 11:17 pm +0200 23/12/05, Gleb Paharenko wrote:
>On both 4.1.16 and 5.0.17 I've got the same results, however not 2^32,
>but 18446744073709551615. 4.0 is deprecated and its results could be
>different. Please provide exact SQL statement which you're using if you
>still think that MySQL behaves weirdly with unsigned integers. In the
>manual we have:
>
>  mysql> SELECT CAST(1-2 AS UNSIGNED)
>        -> 18446744073709551615
>
>Marko Domanovic wrote:
>> I noticed rather interesting thing... If you deduct 1 from the 0 which is
>> stored in integer unsigned field, you get 2^32, not 0. I think that's how
>> things are not working with version 4, and want to ask is this behavior bug
> > or feature in mysql version 5, and is it customizable?
Thread
Strange behavior with integer unsigned type...Marko Domanovic23 Dec
  • Re: Strange behavior with integer unsigned type...Gleb Paharenko23 Dec
    • Re: Strange behavior with integer unsigned type...James Harvard24 Dec
Re: Strange behavior with integer unsigned type...Marko Domanovic24 Dec
  • Re: Strange behavior with integer unsigned type...Gleb Paharenko30 Dec
    • Re: Strange behavior with integer unsigned type...Stephen Cook31 Dec