You are overthinking the issue.
mysql> SELECT VERSION(); +-----------+ | VERSION() | +-----------+ | 4.1.7 | +-----------+ 1 row in set (0.00 sec)
mysql> SELECT 1 OR NULL; +-----------+ | 1 OR NULL | +-----------+ | 1 | +-----------+ 1 row in set (0.00 sec)
We do not need to know x to determine that 1 OR x is TRUE (1). That is the nature of OR - it only takes one TRUE value to result in TRUE (1). Hence, 1 or NULL must evaluate to TRUE (1). This is a case where we should correct the definition in the manual, rather than redefining how OR should behave based on the the manual's poor choice of wording. I have no doubt that the wording is a translation of the code. Something like: if either argument is 1 return 1, else if any argument is null return null, else return 0. Unfortunately, the "else"s are missing, leaving the progression implied rather than explicit.
Therefore, Vlad has found a bug:
mysql> CREATE TABLE nullbug (a INT NOT NULL); Query OK, 0 rows affected (0.01 sec)
mysql> SELECT MIN(a), MIN(a) IS NULL, MIN(a) IS NULL OR NULL FROM nullbug; +--------+----------------+------------------------+ | MIN(a) | MIN(a) IS NULL | MIN(a) IS NULL OR NULL | +--------+----------------+------------------------+ | NULL | 1 | NULL | +--------+----------------+------------------------+ 1 row in set (0.00 sec)
I expect an overly aggressive optimizer.
Michael
Roger Baklund wrote:
Vlad Shalnev wrote:
* from the manual: >> Logical OR. Evaluates to 1 if any operand is non-zero, to NULL if any >> operand is NULL, otherwise 0 is returned.
* Roger Baklund:
This definition (from the manual) is self-contradicting: 1 OR NULL should evaluate to 1 because "any operand is non-zero", but it should also evaluate to NULL because "any operand is NULL".
Why self-contradicting ?
It is self-contradicting because one part of the definition contradicts another part of the same definition.
> If you apply this rule as described ( from left
to right ) you will get correct result.
Definitions are usually not implemented left to right. Consider this definition: "The day can be devided into two parts: at night it is dark, in the day you can breathe." Implemented from left to right, it gives the "correct" answer, but that does not make it a good definition, mostly because the second part does not only fit the "day", it also fits the "night". A good definition is clear and unambiguous.
While we're at it: the term "non-zero"... what does it mean? As we all know, NULL != 0, and 0 == zero, consequently NULL must be non-zero.
I guess "non-false" or simply "true" would be more correct, as NULL evaluates to false in a boolean expression.
> And it isn't work in 4.1.7 :((
I don't know why the behaviour has changed. I suggest it is because the definition is unclear, but I don't know.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
