Re: A Question regarding null columns.

Martin Doherty wrote:

> (drifting off thread...)
> It's not right to say that NULL = NULL is a false statement, or that NULL <> NULL is a true statement. The
> picture is more complicated than that, and involves a strange twist on boolean logic caused by the presence of
> nulls. You end up with 'trinary' truth tables instead of the classic binary TRUE/FALSE truth tables.
>
> Assume your code example is written in PL/SQL. It is better to think of NULL as 'unknown value'. If a PL/SQL
> comparison is unable to evaluate as definitely TRUE or FALSE, then it will evaluate as NULL, and the 'else'
> clause of the if statement will be executed.
>
> Here are some illustrative examples:
>
> if (null = null) then a := 1; else a:=2; endif;
>
> Does one unknown equal another unknown? Well, the answer is of course, unknown (i.e. null) and so the 'else'
> clause is triggered and a = 2.
>
> if (2 + 2 = 4 and b = null) then a := 1; else a:=2; endif;
>
> 2+2=4 is certainly TRUE, but b = null yields NULL (an unknown answer). So we have TRUE AND NULL, which because
> of the unknown will also be unknown (both operands must be TRUE for the AND operator to return TRUE). Result is
> null, and a = 2.
>
> if (2 + 2 = 4 or b = null) then a := 1; else a:=2; endif;
>
> Now we have TRUE OR NULL? The OR operator only requires one of its operands to be TRUE in order to return TRUE.
> Result is TRUE, and a = 1.
>
> true and true => true
> true and false => false
> true and null => null
> false and false => false
> false and null => false (AND only needs one false to return a false)
> null and null => null
>
> true or true => true
> true or false => true
> true or null => true
> false or false => false
> false or null => null (OR will only return false if both operands are false)
> null or null => null
>
> Same rules apply to the predicate in a WHERE clause, if the overall predicate evaluates to TRUE then the row
> will be selected, if it evaluates to FALSE or NULL then it will not.
>
> If you want to test for two variables' equality, (treating NULL as a data value), you'd have to write it like
> this:
>
> if (a = b or (a is null and b is null)) then ... else ... endif;
>
> or maybe
>
> if (nvl(a,'X') = nvl(b, 'X')) then ... else ... endif;
>
> but this is less reliable because a might be null and b might be 'X' but they would evaluate as equal.
>
> (Please note, above examples based on knowledge of PL/SQL 2.0 only)
>
> Regards
> Martin Doherty
>
> P.S. if rusty memory serves me, in Oracle 7.3 it was possible to create indexes on NULL columns, it could even
> be a UNIQUE index. You could have any number of rows in the table with a key value of null (or all nulls), and
> the index would contain no entries for those rows. If it's a multi-column unique index, NULL begins to act like
> a data value, e.g. with two key columns you would not be allowed to have two rows both with the same key
> "ABC"/null but you could still have unlimited rows with the key of null/null.
>
> P.P.S. You'll also see null behaving something like a data value when you ORDER BY (all nulls go to the end) or
> GROUP BY (all nulls in same group), which is a practical if not theoretically pure way of handling the
> situation. Think of it as putting all the unknown values together, without implying that they equal each
> other.
>
> Chris Forbis wrote:
>
> > One possible reason is NULL can not be indexed with a normal index.
> > It would need a Bitmap index. (Unless someting has changed with newer
> > Oracle versions)
> >
> > 0 <> NULL
> > heck.. NULL <> NULL keep that in mind
> >
> > to test you can make a procedure..
> > if (NULL=NULL) then ... this will be be done because NULL <> NULL.
> >
> > "Telemachus" <telemachus_at_ulysseswillreturn.net> wrote in message news:<ZVZT8.588$i5.5345_at_news.indigo.ie>...
> > > Consider :
> > >
> > > (Oracle 8173)
> > > Create table eraseme as select object_id from all_objects;
> > >
> > > Connected to:
> > > Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
> > > JServer Release 8.1.7.3.0 - Production
> > >
> > > SQL> desc eraseme;
> > > Name Null? Type
> > > ----------------------------------------- -------- ----------
> > >
> > > OBJECT_ID NOT NULL NUMBER
> > >
> > >
> > > SQL> explain plan for select count (*) from eraseme where object_id is null;
> > >
> > > Explained.
> > >
> > > SQL> @ ?/rdbms/admin/utlxplp.sql
> > >
> > > Plan Table
> > > ----------------------------------------------------------------------------
> > > ---
> > > ----------------------------
> > > | Operation | Name | Rows | Bytes| Cost | TQ
> > > |IN-OUT|
> > > PQ Distrib | Pstart| Pstop |
> > > ----------------------------------------------------------------------------
> > > ---
> > > ----------------------------
> > > | SELECT STATEMENT | | | | | |
> > > |
> > > | | |
> > > | SORT AGGREGATE | | | | | |
> > > |
> > > | | |
> > > | TABLE ACCESS FULL |ERASEME | | | | |
> > > |
> > > | | |
> > > ----------------------------------------------------------------------------
> > > ---
> > > ----------------------------
> > >
> > > 6 rows selected.
> > >
> > >
> > > Without wishing to cause major fuss my question would be -
> > >
> > > since object_id is known to be not null from the data dictionary why does
> > > the optimizer ask for an FTS rather than immediate 0 ?
> > >
> > > or is it a 'fiddled' FTS ? Checking the waits (there aren't many but there
> > > are a few consistent reads )
> > >
> > > Just part of an issue I am trying to solve with distributed tables (the
> > > above is self-contained on one instance though)
> > >
> > > TIA
> > >
> > > T

Correct.

Which will, no doubt, some day show up in Oracle's marketing literature as part of a claim that they were the first to introduce a database product with quantum computing:

PLS_INTEGER variables already have the values of 1, 0, and indeterminate. ;-)

Daniel Morgan Received on Mon Jul 01 2002 - 18:11:04 CDT