Bug #45661 Add SQL MODE that will treat a NULL as an Empty String for Comparison Operations
Submitted: 22 Jun 2009 19:24
Reporter: Chris Calender Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S4 (Feature request)
Version:All OS:Any
Assigned to: CPU Architecture:Any
Tags: comparison, empty string, null, sql mode, SQL_MODE

[22 Jun 2009 19:24] Chris Calender
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).