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
• Status: Verified
• Category: MySQL Server: Errors
• Severity: S3 (Non-critical)
• Version: 5.0.2-alpha-debug
• OS: Linux (SuSE 9.1)
• 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)