Bug #13817 ERROR_FOR_DIVISION_BY_ZERO handling and documentation inconsistent
Submitted: 6 Oct 2005 20:00 Modified: 24 Jan 2014 15:46
Reporter: Kristian Koehntopp Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.0.13 OS:Linux (Suse Linux 9.3)
Assigned to: Paul Dubois CPU Architecture:Any

[6 Oct 2005 20:00] Kristian Koehntopp
Description:
SQL Mode ERROR_FOR_DIVISION_BY_ZERO promises "Produce an error in strict mode (otherwise a warning) when we encounter a division by zero (or MOD(X,0)) during an INSERT or UPDATE, or in any expression (for example, in a select list or WHERE clause) that involves table data and a division by zero". No error is given, a warning is produced instead.

How to repeat:
mysql> select version() as version\G
*************************** 1. row ***************************
version: 5.0.13-rc-max
1 row in set (0.00 sec)

mysql> select @@sql_mode\G
*************************** 1. row ***************************
@@sql_mode: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
1 row in set (0.00 sec)

mysql> select 1/0;
+------+
| 1/0  |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.01 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Error
   Code: 1365
Message: Division by 0
1 row in set (0.00 sec)

Suggested fix:
When STRICT_TRANS_TABLES or STRICT_ALL_TABLES is in effect, handle 1/0 as an error, not as a warning.
[20 Oct 2005 19:18] Paul Dubois
Further information:

The test case does not qualify for the conditions described in the
manual.  SELECT 1/0 is not an INSERT or UPDATE, and it doesn't
involve table data.  So the documentation does not "promise" an 
error in this case.

However, after further investigation, we also do not throw an error
for either of the following cases when col_name is 0, and both cases 
*do* involve table data:

SELECT 1/col_name FROM t;
SELECT * FROM t WHERE 1/col_name;

So the manual was indeed not correct.  I'll amend it to delete "or
in any expression (for example, in a select list or WHERE clause)
that involves table data and a division by zero."

See also Bug#8423, Bug#6105.