MySQL Bugs: #50507: Behavior of logarithmic functions depends on ERROR_FOR_DIVISION_BY

Bug #50507	Behavior of logarithmic functions depends on ERROR_FOR_DIVISION_BY_ZERO
Submitted:	21 Jan 2010 14:12	Modified:	27 Feb 2014 15:56
Reporter:	Alexey Kopytov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0+	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The manual says that if the argument of LN(), LOG2(), LOG10() or LOG() functions is less than or equal to 0, then NULL is returned.

However, if the ERROR_FOR_DIVISION_BY_ZERO server SQL mode is enabled, those functions return the "division by zero" error when in strict mode (or a warning otherwise) instead of NULL. This is counter-intuitive and not documented.

mysql> create table t1(a double);
Query OK, 0 rows affected (0.01 sec)

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

mysql> select * from t1;
+------+
| a    |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

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

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

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

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

mysql> insert into t1 values(log2(0));
ERROR 1365 (22012): Division by 0

How to repeat:
set sql_mode='';
create table t1(a double);
insert into t1 values(ln(0));
set sql_mode='ERROR_FOR_DIVISION_BY_ZERO';
insert into t1 values(log2(0));
set sql_mode='ERROR_FOR_DIVISION_BY_ZERO,STRICT_ALL_TABLES';
insert into t1 values(log2(0));

Suggested fix:
Returning NULL regardless of the SQL mode should be enough, I don't think we need separate error/warning messages or SQL modes for this.

Thank you for the bug report. Verified on Mac OS X:

77-52-24-143:5.0 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.90-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

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

mysql> create table t1(a double);
Query OK, 0 rows affected (0.01 sec)

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

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

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

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

mysql> insert into t1 values(log2(0));
ERROR 1365 (22012): Division by 0

Bug fix for 50507. Project/Directory name: sql, File name:item_func.cc , Function name:signal_divide_by_null(), Line no.: 636

Attachment: item_func.cc (text/plain), 166.72 KiB.

Bug fix. Directory: sql, File: item_func.cc, Functions: val_real() for Item_func_X where X=(ln, log, log2, log10).

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: item_func.cc (text/plain), 180.16 KiB.

Hi Sohair, Thank you for your contribution. We have decided to fix this differently, and will not use your contribution.

Noted in 5.7.4 changelog.

If the argument of LN(), LOG2(), LOG10(), or LOG() is less than or
equal to 0, the return value should be NULL. However, if the
ERROR_FOR_DIVISION_BY_ZERO SQL mode was enabled, a divide-by-zero
error occurred in strict mode, and a warning otherwise.

Revised changelog entry:

Logarithmic functions return NULL if the argument is less than or
equal to 0.0E0. They now also report a warning "Invalid argument for
logarithm".