MySQL Bugs: #106840: Inconsistent reporting division by zero

Bug #106840	Inconsistent reporting division by zero
Submitted:	25 Mar 2022 15:30	Modified:	28 Mar 2022 9:00
Reporter:	Michal Vrabel	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	8.0.27, 5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Query should consistently report Division by zero also in basic SELECT execute.

Current sql_mode: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

How to repeat:
SELECT 5/0;  
=> NULL

CREATE TEMPORARY TABLE tmpTbl SELECT 5/0; 
=> ERROR Division by 0

Suggested fix:
SELECT 5/0 
=> should get ERROR Division by 0, not NULL result

Hello Michal Vrabel,

Thank you for the report and feedback.

regards,
Umesh

Hi, Michal Vrabel,

You may have some misunderstandings about the sql_mode parameter 'ERROR_FOR_DIVISION_BY_ZERO', and I think that it is consistently to report Division by zero.
The reason why the second statement reports an error is because of sql_mode parameter 'STRICT_TRANS_TABLES'.

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

mysql> CREATE TEMPORARY TABLE tmpTbl SELECT 5/0;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 1

Posted by developer:
 
This is actually not a bug.
Quoting from the manual:
"If this mode (ERROR_FOR_DIVISION_BY_ZERO) and strict mode are enabled, division by zero produces an error"
and
"For SELECT, division by zero returns NULL. Enabling ERROR_FOR_DIVISION_BY_ZERO causes a warning to be produced as well, regardless of whether strict mode is enabled"

Thus, even if strict mode is enabled for a session, a SELECT statement does not operate in strict mode, and thus no error is given for division by zero. But the CREATE TABLE statement does operate in strict mode, and hence an error is reported.

Okey, but am I right that there is no way how to SELECT 5/0; returns Division by zero?

Posted by developer:
 
Unfortunately, there is currently no way to get this error in a SELECT statement.