List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:December 4 1999 11:16pm
Subject:Re: is 3 <> NULL true or false?
View as plain text  
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
Thread
is 3 <> NULL true or false?Sasha Pachev4 Dec
  • Re: is 3 <> NULL true or false?James Rogers4 Dec
    • Re: is 3 <> NULL true or false?Benjamin Pflugmann6 Dec
      • Re: is 3 <> NULL true or false?Benjamin Pflugmann6 Dec
  • Re: is 3 <> NULL true or false?sinisa4 Dec
  • Re: is 3 <> NULL true or false?Sasha Pachev4 Dec
  • Re: is 3 <> NULL true or false?Mike Kephart4 Dec
    • Re: is 3 <> NULL true or false?Paul DuBois5 Dec
      • Re: is 3 <> NULL true or false?Peter Strömberg5 Dec
    • Re: is 3 <> NULL true or false?Mike Kephart5 Dec
      • Re: is 3 <> NULL true or false?Michael Widenius30 Jan
        • Re: is 3 <> NULL true or false?Tim Bunce30 Jan
          • Re: is 3 <> NULL true or false?Michael Widenius5 Feb
            • Re: is 3 <> NULL true or false?Tim Bunce7 Feb
        • Re: is 3 <> NULL true or false?Benjamin Pflugmann30 Jan
          • Re: is 3 <> NULL true or false?Michael Widenius5 Feb
            • Re: is 3 <> NULL true or false?Benjamin Pflugmann5 Feb
              • Re: is 3 <> NULL true or false?Michael Widenius7 Feb
                • Re: is 3 <> NULL true or false?Benjamin Pflugmann8 Feb
  • Re: is 3 <> NULL true or false?Sasha Pachev6 Dec