Bug #70986 Inconsistence with boolean operations on NULL
Submitted: 23 Nov 2013 13:06 Modified: 26 Nov 2013 10:28
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:any OS:Any
Assigned to: CPU Architecture:Any

[23 Nov 2013 13:06] Peter Laursen
Description:
I find the below results inconsistent. 

How to repeat:
SELECT 1 OR NULL; -- returns "1"
SELECT 1 OR NOT NULL; -- returns "1"
SELECT 0 OR NULL; -- returns NULL
SELECT 0 OR NOT NULL; -- returns NULL
SELECT 1 AND NULL; -- returns NULL
SELECT 1 AND NOT NULL; -- returns NULL
SELECT 0 AND NULL; -- returns "0"
SELECT 0 AND NOT NULL; -- returns "0"

I could not find clear docs about it - only http://dev.mysql.com/doc/refman/5.6/en/working-with-null.html that states 
1) "any arithmetic comparison with NULL is also NULL" 
2) "the default truth value from a boolean operation is 1" 
The last statement here is rather cryptical to me! I actually thought all comparisons with NULL should return NULL.  But not (always) the case with boolean comparisons?

If "The default truth value from a boolean operation is 1" then the statement "SELECT 0 OR NULL;" should be equal to "SELECT 0 OR 1" (and return "1" in that case - but it does not; it returns NULL) as I can understand this. And "SELECT 1 AND NULL" should also not return NULL, but "1".

Also note that 'boolean operations' using OR and AND yields different results in a way I don't find documented. As I understand 2) above, NULL should always be evaluated as "1" in boolean comparison (but it is not always). 

The statements above are the most simple cases I could reduce the problem to.  In real life it could affect statements with (more or less) complex WHERE and HAVING clauses where one condition in a 'boolean chain' is NULL or when testing on a NULL/NOT NULL condition. 

This report is a spin-off from http://bugs.mysql.com/bug.php?id=70984 (what I don't claim to understand as there are no data in this report). Maybe it is related and maybe not.

Suggested fix:
I can only understand documentation so that the statements above that return NULL should return a numerical value - or alternatively those that do return a numerical value should return NULL. 

At least some elaboration in docs is required, I think.  But not sure that this is only a documentation issue. Results seem inconsistent to me. And could affect 'real life queries'.
[23 Nov 2013 15:20] MySQL Verification Team
I guess in the case of "1 OR ..."  it may be short-circuited and return immediately.  I didn't see a mention of boolean short circuit in the docs (except comments).
[23 Nov 2013 15:50] Peter Laursen
I understand Shane's comment like this: when the server/parser (evaluating from left to right) sees "1 OR" it will decide that no matter what follws it should always be 1 and skip further evaluation (what is the meaning I get out of the term 'shortcut').

But
SELECT NULL OR 1; -- also returns 1
.. so it does not look that simple IMO.
[23 Nov 2013 15:59] Peter Laursen
sorry .. 'short circuit' and not 'shortcut'.
[23 Nov 2013 17:20] Davi Arnaut
http://dev.mysql.com/doc/refman/5.5/en/logical-operators.html#operator_or

"Logical OR. When both operands are non-NULL, the result is 1 if any operand is nonzero, and 0 otherwise. With a NULL operand, the result is 1 if the other operand is nonzero, and NULL otherwise. If both operands are NULL, the result is NULL."
[23 Nov 2013 18:40] Valeriy Kravchuk
Just substitute TRUE for 1 and FALSE for 0 and apply usual 3-valued logic, like explained here:

http://en.wikipedia.org/wiki/Three-valued_logic

(NULL is Unknown, check truth tables for Kleene logic specifically).

IMHO based on these all your results seem clear and "logical".
[23 Nov 2013 20:46] Peter Laursen
@Davi .. thanks for the links. I had tried for ½ hour to find an explanation.

If this is expected behavior then the page at http://dev.mysql.com/doc/refman/5.6/en/working-with-null.html should definitely be improved (with links to pages on boolean operators and/or explained examples) IMO. It is a 'stub' in its current form (using a Wikipedia term).  Examples should include use of NOT NULL.

And still the statement "the default truth value from a boolean operation is 1" makes no sense to me. Rather I find it confusing. I am missing a context for this statement.
 

(and besides SQL construction "NOT NULL" seems to be a challenge for Valeriy's ternary mathematical reference! Is NOT NULL == NOT UNKNOWN? Well it is UNKNOWN if it is TRUE or FALSE.  But is is KNOWN that it is NOT NULL ... :-). SQL does not always map that directly to mathematics.)
[24 Nov 2013 17:36] Valeriy Kravchuk
The logic I referred to (as well as SQL) is clear on what is the value of NOT NULL (NOT UNKNOWN), it is still NULL (UNKNOWN):

mysql> select NOT NULL;
+----------+
| NOT NULL |
+----------+
|     NULL |
+----------+
1 row in set (0.03 sec)

There are other three-valued logic implementations, but the one used by SQL is clear and natural, as soon as you take into account that there is no real Boolean type in MySQL and values are "mapped" to numbers.
[24 Nov 2013 20:42] Peter Laursen
Exactly!  In comparisons on boolean values NOT NULL is exactly the same a NULL (since it cannot be considered neither TRUE nor FALSE and SQL does not have a 'fourth state').

But when comparing a stored value (string or number) - or a user variable or whatever similar - with NULL or NOT NULL it is not the case. "..WHERE .. IS NULL" and ".. WHERE .. IS NOT NULL" on those does not yield identical results (obviously). 

All this needs elaboration in the page about "working with NULL" IMO. But thanks to both Davi and Valeriy for getting current behaviour explained.

(and one more paranthesis: I believe that stating that NULL in SQL should have been derived from UNKNOWN in ternary algebra is a post-rationalization. NULL in SQL is derived from same in various programming languages over the last 50 years, and is probably older than from when the mathematical discipline of ternary algebra became commonly known.  In my understanding "NOT NULL" is a computer language construction that really has no commonly accepted mathematical counterpart),
[25 Nov 2013 10:00] Hartmut Holzgraefe
1) "any arithmetic comparison with NULL is also NULL" 

this only applies to the actual comparison operators "=", "!=", ">" etc.

AND and OR are not really comparison operators though,
and maybe even more important: they are not arithmetic

2) "the default truth value from a boolean operation is 1" 

that just seems to be a more elaborate wording for

 "TRUE" is defined as "1"

so saying that e.g.

  SELECT 23 < 42;

will always return 1 and not some other arbitrary non-zero value
[25 Nov 2013 19:39] Sveta Smirnova
Thank you, Davi, Valeriy, Hartmut,

for the great explanations and examples. I am closing this report as not a bug, because order of execution of OR and AND has nothing to do with MySQL dialect of SQL.
[26 Nov 2013 10:28] Peter Laursen
@Sveata .. I am simply not able to undrstand your concluding comment whre you write "..because order of execution of OR and AND has nothing to do with..".

"order of execution" is what is normally referrred to as "operator hierachy" I think? I can't see how "operator hierachy" has relevance for this discussion.

Besides I am disappointed that you are not willing to consider the quality of the docs page on "working with null" and seem to be mostly interested just to get rid of a bug report.  This page is IMO sub-standard technical writing (and it has probably not been touched for 10 years).