Division by zero handled differently

Question

I am not sure if this has been answered before in S.O, but I couldn't find any answers googling to explain this behaviour in various databases. so I thought I would clarify this here from experts.

When I run this query in Oracle, Postgresql, mysql and sql-server, I get varying results.

Oracle 11g

select count(1/0) from dual; 

ORA-01476: divisor is equal to zero

http://sqlfiddle.com/#!4/e0ee9e/751

SQL-Server 2017

select count(1/0) ;

1

but select 1/0 gives error.

Divide by zero error encountered

http://sqlfiddle.com/#!18/2be41/2

http://sqlfiddle.com/#!18/185a6/7

PostgreSQL 9.6

ERROR: division by zero

http://sqlfiddle.com/#!17/340e0/80

Mysql 5.6

select count(1/0) ;

0

http://sqlfiddle.com/#!9/2be41/10

Why these differences in implementations?

Answer 1

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.

Answer 2

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