MySQL Bugs: #6105: Traditional: Division with small float equalt to zero should return error

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

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)

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.

Check of division by zero in SELECT part or WHERE clause is considered for 5.1

Moving to 'verified' as we are discontinuing the use of 'to be fixed later'.

I still see the same behaviour in mysql-trunk.

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

so we need sql strict mode to apply to selects too.

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'