Description:
Add SQL MODE that will treat a NULL as an Empty String for Comparison Operations.
A mode such as this would allow a user to control how they would like for values with NULL to be treated.
For example, if you have the following rows of data in a table:
mysql> select * from t;
+------+------+
| id | val |
+------+------+
| 1 | aaa |
| 2 | bbb |
| 3 | ccc |
| 4 | NULL |
+------+------+
Now, you want to select all rows where val does not equal 'aaa':
mysql> select * from t where val != 'aaa';
+------+------+
| id | val |
+------+------+
| 2 | bbb |
| 3 | ccc |
+------+------+
This returns rows 2 and 3, but not row 4. This is the correct, and documented behavior for NULLs:
http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html
However, it is not always intuitive, and in some cases inconvenient (even with the work-arounds), so it would be useful if one could control how NULLs are treated in comparisons such as the above via a new SQL MODE.
Note that this should also include the option to treat NULL as 0 for numeric comparisons.
(Note that a similar feature request exists here, but it is for a request for opposite behavior - that is, to treat Empty Strings as NULLs:
http://bugs.mysql.com/bug.php?id=31131)
How to repeat:
N/A
Suggested fix:
Add a new SQL MODE so users can control how NULLs should be treated.
For instance, there could be STRICT_NULL, which is what it is currently, and a new RELAXED_NULL mode (or NULL_EQUALS_EMPTY_STRING).
RELAXED_NULL could be a mode where NULLs evaluate to TRUE when compared to the Empty String (for string comparisons) or to 0 (zero) (for numeric comparisons).
