MySQL Bugs: #13817: ERROR_FOR_DIVISION_BY_ZERO handling and documentation inconsistent

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

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.

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.