At 2:38 PM -0600 12/4/99, Mike Kephart wrote:
>Hello,
>
> Just to try to analyze this issue a bit more precisely...
>NULL compared to any other value evaluates to NULL (the undefined
>value), *not* FALSE. Additionally, the logic operators AND, OR,
>and NOT provide an undefined value when either operand is NULL.
>C. J. Date describes this as a "three-valued logic" (TRUE, FALSE,
>and UNDEFINED) in the book The SQL Standard, which I think is
>a quite useful description.
>
> Therefore, I belive the proper SELECT construct for
>Sasha's problem would be:
>
> SELECT * FROM t WHERE n IS NULL OR n <> 3
>
>or (with MySQL 3.23.x) use MySQL's NULL-safe equality comparator <=> :
>
> SELECT * FROM t WHERE NOT ( n <=> 3 )
>
> Curiously, although MySQL correctly implements comparison to
>NULL and logical NOT, it seems to me that it incorrectly implements
>(and documents this behavior of) AND and OR with NULL:
>
> mysql> select 1 AND NULL, 1 OR NULL;
> +----------+------------+-----------+
> | NOT NULL | 1 AND NULL | 1 OR NULL |
> +----------+------------+-----------+
> | NULL | 0 | 1 |
> +----------+------------+-----------+
> 1 row in set (0.00 sec)
Why do you consider that incorrect? 1 OR NULL is true if any of
its constituents is true, and 1 is true so the expression is true.
Similar considerations apply for 1 AND NULL.
--
Paul DuBois, paul@stripped
