NULL-Problem, Bug or Feature
Path: emory!samsung!uunet!mcsun!unido!omikros!mwtech!joerg
From: joerg@mwtech.UUCP (Joerg Werner)
Newsgroups: comp.databases.informix
Message-ID: <1254@mwtech.UUCP>
Date: 18 Nov 91 16:24:56 GMT
Reply-To: joerg@mwtech.UUCP (Joerg Werner)
Organization: MIKROS Systemware, Darmstadt/W-Germany
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?
Below is a short sql-script, which I used for tests.
Thanks for any help ...
Ciao, Joerg
PS: My informix version is: INFORMIX-SQL Version 2.10.03F
========================= cut here =========== test.sql ==========
create database test;
create table t1(
key char(2),
val integer
);
create table t2(
a integer,
fkey char(2)
);
insert into t1 values("a",1);
insert into t1 values("b",2);
insert into t1 values("c",3);
select "" t1, key, val from t1;
select "" sel1, key, val from t1 where key not in ( select fkey from t2 );
insert into t2 values(2,null);select "" sel2, key, val from t1 where key not in ( select fkey from t2 );
insert into t2 values(1,"a");
insert into t2 values(1,"c");select "" sel3, key, val from t1 where key not in ( select fkey from t2 );
close database;
drop database test;========================= cut here ===============================
--
Joerg Werner, email: joerg@mwtech.UUCP, voice: 49-(0)6151-37 60 96