Google+ Followers

donderdag 26 juni 2014

Why I like PostgreSQL more than MySQL: UNSIGNED and ABS()

Let's say that you have a table with unsigned values:

CREATE TEMPORARY TABLE f (d INTEGER UNSIGNED, e INTEGER UNSIGNED);
INSERT INTO f VALUES (0,1);

You want to know the difference between the values of d and e, so you use abs()

SELECT abs(d-e) FROM f;

And you get:

ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`f`.`d` - `f`.`e`)'

I know that 0-1 is -1, which is negative and yes, that would not fit in an USIGNED BIGINT, but where does the UNSIGNED BIGINT come from?

Apparently MySQL is smart enough to know that substracting two INTEGER values will require a BIGINT to store the result, but for some bizar reason it copies the UNSIGNED bit from the source values, which is odd for a function that is used specifically to work with negative values. It works if you change the column types of d and e to SIGNED, but that's not acceptable because that changes the allowed values, both by allowing negative numbers and by halving the range of values that can be stored.

The only thing you can do about this, apparently, is to cast d and e to SIGNED when putting them into the ABS(). I will let you guess how I feel about this.


PostgreSQL, of course, does not have this problem. PostgreSQL does not do UNSIGNED in the first place, but they also test their functions before publishing them.