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).