Bug #5929 Traditional: MOD with 0 divisor should return error
Submitted: 6 Oct 2004 17:24 Modified: 4 Jan 2007 0:16
Reporter: Trudy Pelzer Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Errors Severity:S3 (Non-critical)
Version:5.0.2-alpha-debug OS:Linux (SuSE 9.1)
Assigned to: CPU Architecture:Any

[6 Oct 2004 17:24] Trudy Pelzer
Description:
When sql_mode='traditional', any use of the MOD function 
where the divisor is zero (0) must return SQLSTATE 22012  
division by zero. Currently, the function is returning NULL 
on a SELECT MOD(x,0). INSERTs and UPDATEs that use 
MOD are working correctly. 

How to repeat:
mysql> set sql_mode='traditional'; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> create table t1 (col1 tinyint) engine=innodb; 
Query OK, 0 rows affected (0.01 sec) 
 
mysql> insert into t1 values(50); 
Query OK, 1 row affected (0.00 sec) 
 
mysql> select mod(col1,0) from t1; 
+-------------+ 
| mod(col1,0) | 
+-------------+ 
|        NULL | 
+-------------+ 
1 row in set (0.00 sec) 
-- This is the incorrect response. The result should be 
SQLSTATE 22012 division by zero and no result set. 
 
mysql> insert into t1 values(mod(50,0)); 
ERROR 1365 (22012): Division by 0 
-- This is the correct response. 
 
mysql> select * from t1; 
+------+ 
| col1 | 
+------+ 
|   50 | 
+------+ 
1 row in set (0.00 sec) 
 
mysql> update t1 set col1=mod(col1,0); 
ERROR 1365 (22012): Division by 0 
-- This is the correct response. 
 
mysql> select * from t1; 
+------+ 
| col1 | 
+------+ 
|   50 | 
+------+ 
1 row in set (0.00 sec) 
-- This is the expected result.
[6 Oct 2004 17:32] MySQL Verification Team
Verified against latest BK source tree.
[17 Dec 2006 1:13] Vladimir Shebordaev
Yet another one. The warning is also generated only in traditional mode due to MODE_ERROR_ON)DIVISION_BY_ZERO is unset by default.

mysql> select * from t1 where mod(col1,0) = 1;
Empty set, 1 warning (47.40 sec)

mysql> show warnings;
+-------+------+---------------+
| Level | Code | Message       |
+-------+------+---------------+
| Error | 1365 | Division by 0 |
+-------+------+---------------+
1 row in set (3.04 sec)

These issues are due to Items' got evaluated with no abort_on_warning flag set, so all the faulty arithmetics in SELECT path just goes through.
[20 Dec 2006 9:45] Konstantin Osipov
Hello Timothy,
this bug is too big for the maintenance team. It's not a bug, but a documented limitation. I am claiming it anyway.
[11 Nov 2017 18:33] Federico Razzoli
Identical results in 8.0.3:

mysql> set sql_mode='traditional'; 
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (col1 tinyint) engine=innodb; 
Query OK, 0 rows affected (0.28 sec)

mysql> insert into t1 values(50); 
Query OK, 1 row affected (0.05 sec)

mysql> select mod(col1,0) from t1; 
+-------------+
| mod(col1,0) |
+-------------+
|        NULL |
+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> insert into t1 values(mod(50,0)); 
ERROR 1365 (22012): Division by 0
mysql> select * from t1; 
+------+
| col1 |
+------+
|   50 |
+------+
1 row in set (0.00 sec)

mysql> update t1 set col1=mod(col1,0); 
ERROR 1365 (22012): Division by 0
mysql> select * from t1; 
+------+
| col1 |
+------+
|   50 |
+------+
1 row in set (0.00 sec)

...and:

mysql> select * from t1 where mod(col1,0) = 1;
Empty set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------+
| Level   | Code | Message       |
+---------+------+---------------+
| Warning | 1365 | Division by 0 |
+---------+------+---------------+
1 row in set (0.00 sec)