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

Published by

Daniel Lemire

A computer science professor at the Université du Québec (TELUQ).

4 thoughts on “Funny differences between Mysql and Postgresql”

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax