I was sitting in #postgresql today (no not the twitter, the irc) talking to some of the community peeps and I came across this tidbit. MySQL casts integers to float before division[1]. Say what?

mysql> SELECT 3/5;
        -> 0.60

To be honest, I can't fault MySQL for this behavior. It falls in line with the MySQL mantra of make it easy, not "necessarily" correct. A division of 3/5 in a numeric or float would return 0.60. It makes the math easy and normal human consumable.

PostgreSQL and Python on the other hand would give you this:

postgres=# select 3/5;
 ?column? 
----------
        0
(1 row)

Python 2.6.5 (r265:79063, Apr 16 2010, 13:57:41) 
[GCC 4.4.3] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> 3/5;
0
>>> 

To get the similar human consumable response you would want:

postgres=# select 3/5.0;
        ?column?        
------------------------
 0.60000000000000000000
(1 row)

Python 2.6.5 (r265:79063, Apr 16 2010, 13:57:41) 
[GCC 4.4.3] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> 3/5.0;
0.59999999999999998

Python is using float versus numeric here which explains the disparity. However, MySQL does do something that violates a very basic, as in elementary school math mistake. MySQL defines division by zero as NULL. Yes, you read that correctly.

mysql> SELECT 102/(1-1);
        -> NULL

What should happen is:

postgres=# select 102/(1-1);
ERROR:  division by zero

>>> 102/(1-1);
Traceback (most recent call last):
  File "", line 1, in 
ZeroDivisionError: integer division or modulo by zero

That's correct, an ERROR or EXCEPTION 1. http://dev.mysql.com/doc/refman/5.0/en/arithmetic-functions.html#operator_divide 2. http://en.wikipedia.org/wiki/Division_by_zero