Re: NULL-Problem, Bug or Feature

Joerg Werner (joerg@mwtech.UUCP) 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. [ much deleted ] Joerg, What you've described sounds like another example of Informix's ternary logic that occurs in using NULL values. It plagues 4GL programmers. In 4GL you cannot compare two variables for inequality reliably if one of them is possibly NULL. For example: "if a <> B then" produces the following results, where n and m are two differing values: | a set | a set | a is | | to n | to m | NULL | --------------------------------- b set | FALSE | TRUE | FALSE | to n | | | * | --------------------------------- b set | TRUE | FALSE | FALSE | to m | | | * | --------------------------------- b is | FALSE | FALSE | FALSE | NULL | * | * | | --------------------------------- The results marked * are spurious. If you need to use NULLS it is necessary to code 4GL programs as follows: if (a <> B) or (a IS NOT NULL and b IS NULL) or (a IS NULL and b IS NOT NULL) then .... Therefore, I suspect part of the solution to your problem may lie in restructing your sql to account for NULL values in this manner. That is, I believe your second select: >select "" sel2, key, val from t1 where key not in ( select fkey from t2); will eliminate _every_ key value from t1 because there is a row in t2 where fkey is NULL. For those doubting Thomases, try the following 4GL program: 8<------cut here------ 8<------cut here------ 8<------cut here------ main define a,b integer let a = NULL let b = 1234 if a <> b then display "a does not equal b" else display "a equals b" end if end main 8<------cut here------ 8<------cut here------ 8<------cut here------ Hope this helps. +--------------------------------------------------------------------+ Ron Lees rlees@pdact.pd.necisa.oz.au NEC Information Systems Australia Pty Ltd Voice: +61-6-2516411 Software Development Centre (Canberra) Fax: +61-6-2516947 PO Box 244, Belconnen ACT 2616, AUSTRALIA +--------------------------------------------------------------------+