Bug #22942 sql_mode ERROR_FOR_DIVISION_BY_ZERO not working

• Submitted: 3 Oct 2006 14:56
• Modified: 3 Oct 2006 20:11
• Reporter: Marc ALFF
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.26
• CPU Architecture: Any

[3 Oct 2006 14:56] Marc ALFF

Description:
mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.0.26-debug-log |
+------------------+
1 row in set (0.00 sec)

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

mysql> set @x=1/0;
Query OK, 0 rows affected (0.00 sec)

Expecting a warning here, none reported.

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

mysql> select @@sql_mode;
--> note that sql_mode contains ERROR_FOR_DIVISION_BY_ZERO

mysql> set @x=10;
Query OK, 0 rows affected (0.00 sec)

mysql> set @x=1/0;
Query OK, 0 rows affected, 1 warning (0.00 sec)

As the name ERROR_FOR_DIVISION_BY_ZERO implies,
Expecting the query to fail with an error, but it succeded with a warning.

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

mysql> select @x;
+------+
| @x   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

Expecting x to be unchanged, not assigned to NULL

How to repeat:
See above

Suggested fix:
N/A

[3 Oct 2006 15:08] Paul DuBois

I see no difference from what is documented at
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html,
which says:

Produce an error in strict mode (otherwise a warning) when a division 
by zero (or MOD(X,0)) occurs during an INSERT or UPDATE. If this 
mode is not enabled, MySQL instead returns NULL for divisions by
zero. For INSERT IGNORE or UPDATE IGNORE, MySQL generates a warning
for divisions by zero, but the result of the operation is NULL.

You're not inserting any data, just selecting it. Div-0 errors occur only
when you attempt to modify table data.