MySQL Bugs: #23411: The "%" (MOD) operator is not documented; MOD-ing zero returns strange result

Bug #23411	The "%" (MOD) operator is not documented; MOD-ing zero returns strange result
Submitted:	18 Oct 2006 7:55	Modified:	23 Nov 2006 17:22
Reporter:	Siu Ching Pong (Asuka Kenji) (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.27-BK, All	OS:	Linux (Linux (Fedora Code 5))
Assigned to:	Chad MILLER	CPU Architecture:	Any
Tags:	Arithmetic Operators, Reference Manual

Description:
The "%" (MOD) operator is not documented in Reference Manual section 12.4.1.

Detail description of modulo operator could not be found in the following pages:
http://dev.mysql.com/doc/refman/4.1/en/arithmetic-functions.html
http://dev.mysql.com/doc/refman/5.0/en/arithmetic-functions.html
http://dev.mysql.com/doc/refman/5.1/en/arithmetic-functions.html

--------------------------------------------------------------------------------

In "5.0.24a-community-nt", when executing:

SELECT 12 MOD 0;

The displayed result is blank, instead of null.

"SELECT (12 MOD 0) <=> NULL;" returns 1.

"SELECT (12 MOD 0) IS NULL;" returns 0.

How to repeat:
Visit the specified links.

--------------------------------------------------------------------------------

Run the specified statements.

Suggested fix:
1. There should be a section in the documentation for the modulo opeartor.

2. The behavior of "MOD-ing zero" (What is returned? Is the result platform dependent?) should be documented.

Thank you for a bug report. Verified just as described with 5.0.27-BK on Linux:

openxs@suse:~/dbs/5.0> 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.27-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select 12%2 as c1, 12 mod 2 as c2;
+----+----+
| c1 | c2 |
+----+----+
|  0 |  0 |
+----+----+
1 row in set (0.00 sec)

mysql> select 12%0 as c1, 12 mod 0 as c2;
+----+----+
| c1 | c2 |
+----+----+
| NULL | NULL |
+----+----+
1 row in set (0.00 sec)

So, we have NULLs here, likely...

mysql> select (12 mod 0) is NULL;
+--------------------+
| (12 mod 0) is NULL |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.01 sec)

Or no? Looks like no? Either previous result (as I think) or this one is a BUG.

mysql> select (12 mod 2) is NULL;
+--------------------+
| (12 mod 2) is NULL |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

Below is the expected behaviour:

mysql> select (12 mod NULL) is NULL;
+-----------------------+
| (12 mod NULL) is NULL |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.00 sec)

So, looks like select (12 mod 0) gives something that is not NULL (it should give error or warning, but that is a different story...), but the results is shown as NULL in mysql command line client. It is a bug. 12/0 gives more consistent and understandable results.

In Server 5.1.12-beta and Client 5.1.12-beta:

- "SELECT 12 % 0" AND "SELECT 12 MOD 0" correctly display "NULL"
- "SELECT (12 % 0) IS NULL" returns 0;
- "SELECT (12 % 0) <=> NULL" returns 1;

I am looking at the source code "sql/item_func.cc", and comparing "Item_func_div::decimal_op" and "Item_func_mod::decimal_op" (and other pairs of data types), they look correct ...

I am wondering whether the problem is in "Item_func_numhybrid::val_real()".

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/14906

ChangeSet@1.2543, 2006-11-06 14:13:36-05:00, cmiller@zippy.cornsilk.net +3 -0
  Bug#23411: ... MOD-ing zero returns strange result
  
  The Item_func_mod objects never had maybe_null set, so objects never may 
  never expect that they can be NULL, and may therefore give wrong results.
  Now, set maybe_null.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/14911

ChangeSet@1.2542, 2006-11-06 17:13:19-05:00, cmiller@zippy.cornsilk.net +4 -0
  Bug#23411: ... MOD-ing zero returns strange result
    
  The Item_func_mod objects never had maybe_null set, so users had no reason 
  to expect that they can be NULL, and may therefore deduce wrong results.
  Now, set maybe_null.

Noted in 5.0.30, 5.1.13 changelogs.

M % 0 returns NULL, but (M % 0) IS NULL evaluated to false.

MOD is documented in the mathematical functions section.
I'll add a cross-reference to it in the arithmetic functions section.

Thank you for fixing the bug.

But what "Paul DuBois" suggested seems doesn't make sense to me.

Why should "(12 % 0) IS NULL" or "ISNULL(12 % 0)" evaluate to FALSE?

I am going to submit another bug report about CREATE-ing a VIEW that contains MOD columns. I would like to understand this before doing that. Perhaps that isn't a bug after I understand this.

By the way, this strange behavior of "ISNULL(expr)" and "expr IS NULL" is not documented, too.

"Why should "(12 % 0) IS NULL" or "ISNULL(12 % 0)" evaluate to FALSE?"

It shouldn't.  That was the bug that was fixed.

The comment you're looking at is the changelog entry
for the problem, not the behavior that should occur.