Bug #6105 Traditional: Division with small float equalt to zero should return error
Submitted: 14 Oct 2004 18:06 Modified: 8 Jan 2014 13:34
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: Assigned Account CPU Architecture:Any

[14 Oct 2004 18:06] Trudy Pelzer
Description:
When sql_mode='traditional', all division operations where the
divisor is zero (0) must return an error: 
SQLSTATE 22012 division by zero.

But when the divisor is a very small floating-point
number, MySQL does not reject the operation.

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

mysql> select 1E-500 = 0;
+------------+
| 1E-500 = 0 |
+------------+
|          1     |
+------------+
1 row in set (0.00 sec)
-- This result shows that the server considers 1E-500 to be equal to zero.

mysql> select 1 / 1E-500;
+------------+
| 1 / 1E-500 |
+------------+
|       NULL |
+------------+
1 row in set, 1 warning (0.00 sec)
-- This is the incorrect result. Since 1E-500 is the same as zero,
the select should be rejected with SQLSTATE 22012 division by zero

mysql> show warnings;
+-------+------+---------------+
| Level | Code | Message       |
+-------+------+---------------+
| Error | 1365 | Division by 0 |
+-------+------+---------------+
1 row in set (0.00 sec)
[2 Dec 2004 18:28] Trudy Pelzer
Here's another example, with the division operation 
in the WHERE clause, run on 5.0.3-alpha-debug: 
 
mysql> set sql_mode='traditional'; 
mysql> create table t1 (col1 tinyint); 
mysql> insert into t1 values(0); 
 
mysql> delete from t1 where 5=1/col1; 
Query OK, 0 rows affected, 1 warning (0.00 sec) 
-- Instead of a warning, the server should reject the DELETE 
with SQLSTATE 22012 division by zero 
 
mysql> delete from t1 where 5=1/0; 
Query OK, 0 rows affected, 1 warning (0.00 sec) 
-- Again, the DELETE should fail 
 
mysql> update t1 set col1=col1 where 5=1/col1; 
ERROR 1365 (22012): Division by 0 
-- This is the correct response.
[1 Apr 2005 11:56] Michael Widenius
Check of division by zero in SELECT part or WHERE clause is considered for 5.1
[8 Jan 2014 13:34] Erlend Dahl
Moving to 'verified' as we are discontinuing the use of 'to be fixed later'.

I still see the same behaviour in mysql-trunk.
[11 Nov 2017 18:45] Federico Razzoli
First test still fails in 8.0.3, seconds seems fixed.

First:

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

mysql> select 1E-500 = 0;
+------------+
| 1E-500 = 0 |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

mysql> select 1 / 1E-500;
+------------+
| 1 / 1E-500 |
+------------+
|       NULL |
+------------+
1 row in set, 1 warning (0.00 sec)

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

Second:

mysql> create table t1 (col1 tinyint); 
Query OK, 0 rows affected (0.27 sec)

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

mysql> delete from t1 where 5=1/col1;
ERROR 1365 (22012): Division by 0
mysql> delete from t1 where 5=1/0; 
ERROR 1365 (22012): Division by 0
mysql> update t1 set col1=col1 where 5=1/col1; 
ERROR 1365 (22012): Division by 0
[12 Nov 2017 4:22] Shane Bester
so we need sql strict mode to apply to selects too.
[12 Nov 2017 10:59] Federico Razzoli
Isn't this already the case?

mysql> SELECT @@SQL_MODE;
+---------------------+
| @@SQL_MODE          |
+---------------------+
| ALLOW_INVALID_DATES |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE '2017-12-32' AS d;
ERROR 1525 (HY000): Incorrect DATE value: '2017-12-32'