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
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
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?