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: | |
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
[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