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.
8 replies on “MySQL’s Funny Math”
Why are programmers more likely to expect the answer that PostgreSQL gives for division than MySQL? I would expect the MySQL answer myself, because it ‘more accurate’ in the sense that it contains more significant digits. It’s also the answer I get whenn using the ‘bc’ command line calculator. (I’m a programmer who prefers PostgreSQL).
Really? I tried out bc with 11/5 and got 2. With 11%5 I got 1, just like PostgreSQL did. Again, the MySQL isn’t wrong, for this case, just perhaps not what some would expect. It is also inconsistent with their DIV() function, which I would expect to work the same way as the math operator of the same name.
I have got such problems while trying to calculate the varege of a class, ans so I were obliged to do it with PHP code directly, now it seems that after two years I’ve found the answer, I thought that I was a bug, but it’s nothing else an other limit of MySql, postgresql is great anyway !
In answer to Mark Stosberg’s comment on why its not the expected result is because integer maths normally always return an integer, and if your expecting an integer and get a floating point number problems can occur. If you want a real answer then you use floating point maths. That rounding issue is rather more scary to me…viva la piggy-squeal (pgsql)
Hi
I am in big problem since many days. Any kind of help would be appeiciated.
I want some help to enter data into mysql databasefrom a flash form using coldfusion components ?
i’m using web services and simply want to pass data from my flash form to my cfc while staying in flash…
any actionscript (or coldfusion code ‘cfc’) that anyone could provide or even links to other ressources on this specific topic would be awesome…
if someone could help me with this process i would be greatful…..
thank you in advance…
Imran Hashmi
http://www.visionstudio.co.uk
just a quick note re MySQL’s rounding: it’s not wrong at all; MySQL uses the perfectly valid ’round to even’ method of rounding. For more info: http://en.wikipedia.org/wiki/Rounding
George – I’m going to leave the comment, I think the additional reference to rounding methods is worthwhile.
I don’t think ’select 11/5;’ should be considered Integer math and yes, I would expect 2.2. Yes, 11 and 5 are integers but that doesn’t mean anything, even floats are integers sometimes. Now, if you typed ‘select `col_a` / `col_b`;’ and both col_a and col_b were integer types then sure, the result should be 2. Likewise if one uses a function which is defined as being an integer function then I would expect an integer.
The round behavior is odd though, I would expect a default of rounding to the nearest with an option to specify round up/down if preffered.