Hugo Kornelis wrote:

> On Thu, 29 Mar 2007 18:08:00 -0700, Ed Murphy wrote:
>
>> --CELKO-- wrote:
>>
>>> All data types have to be NULL-able in SQL. Having a BOOLEAN type
>>> would lead to 4 valued logic with inconsistent rules about how NULLs
>>> propagate. And the various vendor extension do not work or port
>>> either.
>> It seems like dropping UNKNOWN would leave a sensible set of rules:
>>
>> and | T N F or | T N F not |
>> ----+------ ---+------ ----+--
>> T | T N F T | T T T T | F
>> N | N N F N | T N N N | N
>> F | F F F F | T N F F | T
>>
>> Am I overlooking anything?
>
> Hi Ed,
>
> You've overlooked the basic rule of NULL propagation: any expression
> involving NULL results in NULL. In the tables above, there are
> exceptions to this rule, such as NULL AND FALSE resulting in FALSE, and
> TRUE OR NULL resulting in TRUE.

That rule is oversimplified. Really, it should be "any expression
whose value _depends_ on a NULL input results in NULL", i.e. could
replacing the NULL with different non-NULL values lead to different
values of the expression? NULL + 2 qualifies; NULL = 'ABC' qualifies;
but NULL AND FALSE does not, and neither does TRUE OR NULL.

(As usual, IS NULL and IS NOT NULL remain special cases.)

>> It seems like dropping UNKNOWN would leave a sensible set of rules:

and | T N F or | T N F not |
----+------ ---+------ ----+--
T | T N F T | T T T T | F
N | N N F N | T N N N | N
F | F F F F | T N F F | T

Am I overlooking anything? <<

The NULL propagation rule.

and | T N F or | T N F not |
----+------ ---+------ ----+--
T | T N F T | T N T T | F
N | N N N N | N N N N | N
F | F N F F | T N F F | T

This means that TRUE OR NULL = NULL, etc. and you can now prove that
TRUE = FALSE. The UNKNOWN logical value does not have this behavior
and that is why we have it.

--CELKO-- wrote:

>>> It seems like dropping UNKNOWN would leave a sensible set of rules:
>
> and | T N F or | T N F not |
> ----+------ ---+------ ----+--
> T | T N F T | T T T T | F
> N | N N F N | T N N N | N
> F | F F F F | T N F F | T
>
> Am I overlooking anything? <<
>
> The NULL propagation rule.
>
> and | T N F or | T N F not |
> ----+------ ---+------ ----+--
> T | T N F T | T N T T | F
> N | N N N N | N N N N | N
> F | F N F F | T N F F | T
>
> This means that TRUE OR NULL = NULL, etc. and you can now prove that
> TRUE = FALSE. The UNKNOWN logical value does not have this behavior
> and that is why we have it.

NULL represents the concept "unknown" in all other contexts; it should
represent it in the context of the Boolean data type as well.

IINM, while SQL doesn't have a mandatory Boolean *type*, it already
follows TRUE OR NULL = TRUE and FALSE AND NULL = FALSE in Boolean
*expressions*. Example:

CREATE TABLE Table1 (Column1 varchar(10), Column2 varchar(10))

INSERT INTO Table1 (Column1, Column2) values ('A' , null)
INSERT INTO Table1 (Column1, Column2) values (null, 'B' )

SELECT * FROM Table1 WHERE Column1 = 'A' OR Column2 = 'B'
-- returns 2 rows

SELECT * FROM Table1 WHERE Column1 = 'A' AND Column2 = 'B'
-- returns 0 rows