Daniel Lemire came across some funny math in MySQL. He ran ‘select 11/5;’ and had MySQL give him 2.20, where PostgreSQL gave him 2. There is nothing wrong with MySQL’s answer, but it might throw off most programmers who be more likely to expect the kind of result that PostgreSQL gives. The second query he ran was ‘select round(0.5);’ and MySQL returns 0 and PostgreSQL returns 1. As far as I can tell MySQL is just completely wrong here. Those were about all the details Daniel gave, so I thought I’d try this out for myself.
I was able to duplicate all of the results on MySQL 4.0.20 and PostgreSQL 7.4.3 systems. I thought I would take this one step further and try ‘select 11%5;’ on PostgreSQL, it gave me the correct answer of 1. This is nicely documented under Mathematical Functions and Operators in the PostgreSQL docs. Interestingly enough MySQL also returns 1 when running ‘select 11%5;’. MySQL does support integer division, but only by using the DIV() function. As for rounding, the MySQL docs for round() claim that:
… the behavior of ROUND() when the argument is halfway between two integers depends on the C library implementation. Different implementations round to the nearest even number, always up, always down, or always toward zero. If you need one kind of rounding, you should use a well-defined function such as TRUNCATE() or FLOOR() instead.
This explanation seems very odd to me since both the PostgreSQL and MySQL tests I ran were on the same system. Perhaps PostgreSQL implements round() independently of the c libraries in order to achieve more consistent (and correct) results? If so MySQL should do the same and not simply throw its arms up in the air if the result of round() comes out incorrectly. If Microsoft had done something like this in Excel the screaming Slashdot hoards would be all over them.
Note to MySQL developers: just fix this and move on.