Re: Three valued logic (again)
Nils Myklebust wrote:
>
> An example may make this clear:
>
> sometable components
> product_code combination_code
> A A
> B <null>
> C B
>
... CLIPPED some ...
> A is in components so not in is false
> B as A - false
> C is unknown whether it's in components or not. There is one row with
> <null> which in sql means the value is unknown (it may or may not be
> C). Wheter you test for in or not in the result is unknown. As unknown
> is treated as false (sql sadly have no notion of unknown outside "is
> null") this should also be false.
>
> That part of your where clause should be false in all cases.
>
> This is also the result I get using Informix OnLine 7.10.UC1.
>
> If you do as you suggest to include WHERE combination_code IS NOT NULL
> in the subquery from components the result is of course different.
> Product code C is than not in the set of values the subquery returns,
> and not in is true.
... CLIPPED some ...
>
> Nils.Myklebust@idg.no
> NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway
> My opinions are those of my company
> The Informix FAQ is at http://www.iiug.org
Thank you Nils!
Your explanation was just what I needed. (now my query WORKS!)
However, I am still am left with a puzzle related to NULLs.
Here is a query to demonstrate:
set explain on;
CREATE TABLE tbl ( col int);
INSERT INTO tbl VALUES (1);
INSERT INTO tbl VALUES ('');
SELECT COUNT(*) FROM tbl WHERE col IS NULL;
SELECT COUNT(col) FROM tbl WHERE col IS NULL;
DROP TABLE tbl;
This returns:
(count(*))
1
(count)
0
Why do I not get a "1" for BOTH?
I ran this same query with a
"SET EXPLAIN ON;"
at the beginning. Both SELECTs seem to be handled the same
yet if I specify a Column name in the COUNT() it seems at
though NULLs DON'T COUNT.