Prev: reorgchk_ix_stats and reorgchk_tb_stats question
Next: Help! weird Common Table Expressions behavior with SYSTEM TEMP TABLESPACE
From: Lennart on 7 Nov 2009 02:12
On 7 Nov, 06:59, Tonkuma <tonk...(a)fiberbit.net> wrote:
[...]
>
> My way to remember three valued logic in SQL is .....
>
> 1) Assign nubbers(0,1,2) to Logical value
>  Logical Value(P) : Number(p)
>  True             : 2
>  Unknown          : 1
>  False            : 0
>
> 2) Take MIN(p, q) for P AND Q, MAX(p, q) for P OR Q, and 2 - p for NOT
> (P).
>

Ah, that's interesting. I never thought of that. However I usually
just use

TRUE and UNKOWN : UNKOWN
TRUE or UNKNOWN : TRUE (doesn't matter what unknown is)
FALSE and UNKNOWN : FALSE (doesn't matter what unknown is)
FALSE or UNKNOWN : UNKNOWN

together with the normal rules (and op(null, null)) that apply for 2
values logic.

Even though three valued logic in it self adds much complexity, there
is something worse lurking around (IMO). In many data models nulls
sometime mean unknown, sometime non existing, sometime .... Depending
on the meaning of null one must use different approaches when querying
for data.

It is easy enough to construct strange things with the help of null. A
few examples that comes to mind:

CREATE TABLE T (
A INT,
B INT
);

INSERT INTO T (A,B)
VALUES (null,null), (1,null), (null,1), (1,1);


Example 1
select sum(A)+sum(B) as X, sum(A+B) as Y from T;

4 2


Example 2
select T1.A, T1.B from T T1, T T2
where T1.A = T2.A and T1.B = T2.B;

1 1

select T1.A, T1.B from T T1
intersect
select T2.A, T2.B from T T2;

1 1
- 1
1 -
- -

Exempel 3
select T1.A, T1.B from T T1
where T1.A not in (select T2.A from T T2 where T2.A = 1);

Empty set

select T1.A, T1.B from T T1
where not exists (
select T2.A from T T2
where T2.A = 1 and T1.A = T2.A
);

- -
- 1

Etc.

IMO, it is difficult to avoid nulls in the data model at all times,
but I get a feeling that they are often introduced as a result of old
habits rather than necessity.

/Lennart
From: Damir on 9 Nov 2009 05:30
Thanks for clearing out this one!

Damir


--
> My way to remember three valued logic in SQL is .....
>



First  |  Prev  | 
Pages: 1 2
Prev: reorgchk_ix_stats and reorgchk_tb_stats question
Next: Help! weird Common Table Expressions behavior with SYSTEM TEMP TABLESPACE