Division by zero handled differently Division by zero handled differently oracle oracle

Division by zero handled differently


count() counts the number of rows or the non-NULL values of the expression.

When the expression is constant, then it returns the number of rows.

What you are seeing is the question of when and whether the constant is evaluated, and the different way that the databases handle this situation.

Oracle and Postgres are clearly trying to evaluate the constant. To be honest, I'm not sure if these are run-time or compile-time errors.

SQL Server postpones the evaluation until needed -- and it is never needed. So, it counts the number of rows.

MySQL is the strange one. It returns NULL for divide-by-zero. This is not standard behavior, but also not entirely unreasonable. COUNT(NULL) returns 0.


In mysql there is a specific mode (enabled or disable)

ERROR_FOR_DIVISION_BY_ZERO

that manage this situation and in your case seems disable (you should check for proper mode value) so you apparently see a wrong (or unexpected result) but is simply a configuration for error handling

https://dev.mysql.com/doc/refman/5.7/en/precision-math-expressions.html