Funny differences between Mysql and Postgresql
I hope someone can explain these funny differences between Mysql and Postgresql. (Yes, see update below.)
Here’s an easy one… What is 11/5?
select 11/5;
What should a SQL engine answer? Anyone knows? I could check as I used to be a member of the “SQL” ISO committee, but I’m too lazy and the ISO specs are too large. Mysql gives me 2.20 whereas Postgresql gives me 2 (integer division). It seems to me like Postgresql is more in line with most programming language (when dividing integers, use integer division).
It gets even weirder… how do you round 0.5? I was always taught that the answer is 1.
select round(0.5);
Mysql gives me 0 (which I feel is wrong) and Postgresql gives me 1 (which I feel is right).
On both counts, Mysql gives me an unexpected answer.
(The color scheme above for SQL statements shows I learned to program with Turbo Pascal.)
Update: Scott gave me the answer regarding Mysql rounding rule. It will alternate rounding up with rounding down, so
select round(1.5);
gives you 2 under Mysql. The idea is that rounding should not, probabilistically speaking, favor “up” over “down”. Physicists know this principle well. Joseph Scott also gave me the answer, and in fact he gave me quite a detailed answer on his blog. I think Joseph’s answer is slightly wrong. I don’t think Mysql uses the standard C librairies because the following code:
#include <cmath>
#include <iostream>
using namespace std;
int main() {
cout << round(0.5) << endl;
cout << round(1.5) <<endl;
}
outputs 1 and 2 on my machine (not what Mysql gives me).
Follow me on twitter!
MySQL’s Funny Math
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 likely to expect the kin…
Trackback by Joseph Scott's Blog — 8/11/2004 @ 21:40
Regarding the rounding of 0.5, I was also taught that it should round to 1. But what about 1.5? I would guess that Daniel was taught that it rounds to 2; I was taught that it also rounds to 1. I learned this in lab courses in undergraduate physics. The general rule was (is) that if the digit to the immediate left of the positition being rounded is even, round up; otherwise round down. The rationale is that otherwise, on average, you will end up
rounding up more often than down, skewing results slightly.
Comment by Scott — 9/11/2004 @ 12:20
Hmm. Should have tried it BEFORE posting the last comment so as to avoid having to post another. It looks to me like MySQL follows a similar rounding rule, but with even/odd reversed: if the preceding digit is odd, round up; otherwise round down. So, for example, 1.5 and 2.5 both round to 2.
Comment by Scott — 9/11/2004 @ 12:23
As to why MySQL rounds the way it does, that was directly from the MySQL docs. I didn’t really but it either, but it looks like that is their “official” explanation.
Comment by Joseph Scott — 10/11/2004 @ 11:30