MySQL’s ROUND has different behavior for DECIMALs than it does for FLOATs and DOUBLEs.

This is documented. The reason for this is not discussed but it’s important. ROUND operates by altering the type of the expression to have the number of decimal places that it was passed. And this matters because the type information associated with a DOUBLE will bleed… it taints the rest of the expression:

We’re going to start with some simple SQL:

mysql> SELECT 2.5 * 605e-2;
+--------------+
| 2.5 * 605e-2 |
+--------------+
|       15.125 |
+--------------+
1 row in set (0.00 sec)

Here 2.5 is a DECIMAL(2,1) and 605e-2 a DOUBLE, and the result is a DOUBLE. That’s all well and good…

But let’s try rounding 605e-2.

mysql> SELECT 2.5 * ROUND(605e-2,2);
+-----------------------+
| 2.5 * ROUND(605e-2,2) |
+-----------------------+
|                 15.12 |
+-----------------------+
1 row in set (0.00 sec)

So… what’s going on here? The round part of the expression shouldn’t have changed its value. And in fact, it hasn’t, calling ROUND(605e-2,2) returns 6.05 as expected. The problem here is that the type of ROUND(605e-2,2) is DOUBLE(19,2) and when that’s multiplied by 2.5 the resulting expression is still DOUBLE(19,2). But the number of decimals on a float is for display purposes only– internally MySQL keeps full precision… we can prove that this way:

mysql> SELECT ROUND(2.5 * ROUND(605e-2,2),3);
+--------------------------------+
| ROUND(2.5 * ROUND(605e-2,2),3) |
+--------------------------------+
|                         15.125 |
+--------------------------------+
1 row in set (0.00 sec)

So yeah… MySQL let’s you increase precision with ROUND– Postgres is looking mighty fine right now.