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: | |
Category: | MySQL Server: Parser | Severity: | S3 (Non-critical) |
Version: | any | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[23 Nov 2013 13:06]
Peter Laursen
[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).