Bug #43998 using null in math or string operation should not produce null result
Submitted: 31 Mar 2009 20:52 Modified: 1 Apr 2009 1:44
Reporter: avi weiss Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:5.1 OS:Any
Assigned to: CPU Architecture:Any
Tags: operation using null

[31 Mar 2009 20:52] avi weiss
Description:
I believe that "select 5 + null" should return "5", not null. likewise, "select concat("my string", null)" should return "my string" and not null. When adding "nothing" to "5", you should still have "5", not nothing (unless you are Bernie Madoff). Similar argument applies to catting strings together: if I add "nothing" to a non-null string, I shouldn't lose the whole value of the string. "null" should not act "destructively" on data; it should simply have "no effect".

While I appreciate the special characteristic of null from a computer science perspective, as a developer, it makes more sense for "null" to map to "0" or "" for numeric and string operations respectively, as that is how most people view them. Most scripting languages with "run-time" automatic type-casting agree with this perspective and provide that exact mapping.

To work around this, I have to add "ifnull(var, 0)" or "ifnull(var, "")" to many queries in many places, needless adding complexity and computation to protect against having "null" values returned. I'm finding it hard to envision a situation where it even makes sense to have 5 + null return null instead of 5. I'm hopeful MySQL will agree with this, and change the engine accordingly.

Thanks

-avi

How to repeat:
select 5 +  null
[31 Mar 2009 21:13] Daniel Fischer
You're right in that 5 + NULL = NULL doesn't make sense, but this is caused by 5 + NULL not making any sense in the first place.
[31 Mar 2009 21:58] MySQL Verification Team
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php
[31 Mar 2009 22:07] avi weiss
I realize that at first blush, it seems incongruous for the situation to arise, but it is an unfortunate artifact of left join operations, and using the DB engine to perform math calculations against columns.

It DOES make sense if in an "outer select", you are adding one column "a1" which has "5", to another column "a2" that has "null", where the "a2" column null entry was created from a left join to a subquery where there was no record found for "a2" for a particular row value in the outer select.

I can provide more details of my application, but they are superfluous and will not shed any additional light on the basic issue. Suffice to say that the are quite a few times where having "null" map to "0" would help immensely, so that I can get 5 + 0 = 5, instead of 5 + null = null.

I'm hopeful that MySQL will reconsider this as a "bug", or at the very least a feature request.

thanks
[31 Mar 2009 22:37] MySQL Verification Team
Thank you for the feedback. MySQL handles NULL values according the SQL Standard if you test your query against an RDBMS which is SQL Standard compliant of NULL like the Microsoft SQL Server you will get the same result as MySQL as well. Please read the below Manual's item for further information:

http://dev.mysql.com/doc/refman/5.1/en/problems-with-null.html

Thanks in advance.
[1 Apr 2009 1:33] avi weiss
Miguel;

Well, its unfortunate that the standard is set so low. Having been on a standards committee for 5 years (WAP), I'm often surprised at how sub-standard "standards" can be. That said, having been developing with MySQL for 5+ years, it is clear that MySQL has done an excellent job supplementing the standard and in many cases improving on it without breaking compliance to it. Perhaps they can do so here as well. 

Perhaps a flag or option can be implemented, say "MAP_NULL_TO_ZERO" for any query components that entail math functions of "null" columns (especially if they are integer).

As I said, I have added the necessary "ifnull" support to cover the null cases, but it would be helpful to not have to do that. 

Thanks

-avi
[1 Apr 2009 1:44] avi weiss
Miguel;

Actually, there already IS a work around:

from http://dev.mysql.com/doc/refman/5.1/en/working-with-null.html user comments:

"The function 'COALESCE' can simplify working with null
values.

for example, to treat null as zero, you can use:
select COALESCE(colname,0) from table where
COALESCE(colname,0) > 1;"

So, I guess my wish has already been granted...Might be good to move this out of the user comments and into main documentation area.

thanks

-avi