Logical operations with NULL
While NULL in C is a macro with different implementations for a NULL-Pointer such as "0" or a void* cast for "0" (void *)0, NULL in PHP and MySQL has a special meaning. If you test in PHP NULL against an empty string or an empty array you will get true, assumed you don't use typesafe relational operators (=== or !==).
The title of this article is "logical operations with NULL" - so let's see how the results will look like, when we have a real NULL as an operand.
var_dump(1 || 1); var_dump(0 || 1); var_dump(1 || 0); var_dump(0 || 0); var_dump(1 || NULL); var_dump(0 || NULL); var_dump(NULL || 1); var_dump(NULL || 0); var_dump(NULL || NULL);
The output is the following:
bool(true) bool(true) bool(true) bool(false) bool(true) bool(false) bool(true) bool(false) bool(false)
A simple definition could be: PHP cast the given operands to the accordant logical value and runs the operation between the operands.
Quite easy I think - so let's see how this is implemented in MySQL:
SELECT (1 || 1); SELECT (0 || 1); SELECT (1 || 0); SELECT (0 || 0); SELECT (1 || NULL); SELECT (0 || NULL); SELECT (NULL || 1); SELECT (NULL || 0); SELECT (NULL || NULL);
(1 || 1): 1 (0 || 1): 1 (1 || 0): 1 (0 || 0): 0 (1 || NULL): 1 (0 || NULL): NULL (NULL || 1): 1 (NULL || 0): NULL (NULL || NULL): NULL
The first results are the same - but what happens with the results where we have a NULL operand? When you go the list from top to bottom you could think about the following defintion: "MySQL implements logical operations by look at the first operand and decide if you use the first or second operand". But this definition is depricated when you arrived (NULL || 0): NULL. Now you could think MySQL looks at a casted version of NULL - 0 - so skip this value and look what is in the second. This is 0 too so use the first operand. But no; The only logical conclusion is that MySQL gives NULL a special meaning. My definition for this manner looks as follows: "A expression is ever NULL if one operator is NULL and the interpreter arrives this without a logical exclusion."By the way - this behavior for SQL was described by Edgar Codd.
You might also be interested in the following
- LightsOut Solution using Linear Algebra
- PHP Hacking
- MySQL Wishlist
- Derivation of Pagination Calculation
Sorry, comments are closed for this article. Contact me if you want to leave a note.