Re: NULL-Problem, Bug or Feature
Path: emory!swrinde!zaphod.mps.ohio-state.edu!uakari.primate.wisc.edu!usenet.coe.montana.edu!ogicse!sequent!muncher.sequent.com!news
From: krader@sequent.com (Kurtis D. Rader)
Newsgroups: comp.databases.informix
Message-ID: <1991Nov20.012037.16593@sequent.com>
Date: 20 Nov 91 01:20:37 GMT
References: <1254@mwtech.UUCP>
Sender: news@sequent.com (News on Muncher)
Organization: Sequent Computer Systems, Inc.
joerg@mwtech.UUCP (Joerg Werner) writes:
>Can anyone explain the following results?
>Below are listed two tables t1 and t2. I execute the
>select-statement three times and before the 2nd and 3rd
>execution I inserted new rows into table t2.
> Table t1 Table t2
> ======== ========
> a. b. c.
> key I val a I fkey a I fkey a I fkey
> -----+------ -----+----- -----+----- -----+-----
> a I 1 I 2 I NULL 2 I NULL
> b I 2 I I 1 I a
> c I 3 I I 1 I c
>-----------time--------------<1>-----------<2>------------<3>---->
>select * from t1 where key not in ( select fkey from t2 );
> Result: at <1> at <2> at <3>
> key I val key I val key I val
> -----+----- -----+----- -----+-----
> a I 1 I b I 2
> b I 2 I I
> c I 3 I I
> ok #@?%@ ok
>I think the result at <2> is wrong (should be the same table as
>after <1>). Am I right?
Actually test #3 is wrong. See chapter 8 of "Relational Database:
Writings 1985-1989" by C. J. Date. Since the set represented by
fkey in table 2 contains a NULL value a query like
Does fkey contain value x?
can only be answered MAYBE, not TRUE or FALSE. I've worked with
several RDBM's (e.g., Oracle, Ingres) and they all exhibit strange
behavior when it comes to NULLs.