Thursday, February 02, 2006

Is this a MySQL bug?

Today, I received a message from a friend who is also a MySQL DBA and recently upgraded to MySQL 4 from MySQL 3.23:

I thought this might be useful for you:

I had two fields, say "a" and "b", both of which were unsigned int. For one calculation, I need to subtract b from a. Before upgrading, if b>a, the result would be negative as expected. After upgrading, if b>a, the result is something like 18446744073709551615. Of course, this sort of thing happens in c, but MySql apparently used to automatically cast so that the result of such calculations would come out as expected. Of course, changing the fields to signed int fixed the problem. Interesting, eh? Seems like this kind of thing could cause huge problems for some people when upgrading.


My question to MySQL gurus here: Is this a bug in MySQL 4? Why is MySQL 4 not "casting" it properly?

Thanks
Frank

3 comments:

Markus Popp said...

Hi,

this is not really a bug, but certainly a gotcha which, however, doesn't occur anymore in MySQL 5.0 when SQL mode is enables.

Here's where this behaviour's described in the manual:

http://dev.mysql.com/doc/refman/4.1/en/cast-functions.html

Markus

Markus Popp said...

Oups - have to correct myself ... it's still there in 5.0 with SQL mode enabled.

Frank said...

Thanks Markus

I was very happy to learn from your first post that it was no longer there. However, your second post woke me up.

I guess I'll go read the manual "that no one reads" ;)


Frank


Frank