Re: Three valued logic (again)

An example may make this clear: sometable components product_code combination_code A A B <null> C B The left list is the product codes tested against in your where clause. The right list is the set returned from your subquery on the components table (in your database a multi set may be returned, but that doesn't make any difference here). 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. The above is the correct behaviour as far as I can see, and if your OnLine 4.11 behaves differently there is a defect in it. Although your explanation of the difference seems plausible there *may* be other explanations, and I would test it on a very simple select before I declared it a defect. Ian Goddard <igoddard@netcomuk.co.uk> wrote: :In the interests of stirring up a good discussion here (why should I :confine :it to the site where the situation originated?): : :We have a SELECT COUNT(*) which includes the following term in a complex :WHERE clause: : :product_code NOT IN (SELECT combination_code FROM components) : :(product_code and combination_code are both CHAR(7)s BTW) : :Running on 4.11 OL this gave a result of about 1700 and on 7.21 it gave :about 300. : :Further investigation revealed that there were a couple of rows with :null combination_code values in components. There are no NULLs amongst :the set of product_code. : :Adding WHERE combination_code IS NOT NULL to the SELECT FROM components :resulted in both versions giving the same result as 4.11 gave with the :original version. : :The explanation seems to be that when product_code has a value which :is in the non-null subset of combination_codes either both versions :treat : NOT IN as FALSE and the term fails. When product_code has a value :which is not in the non-null subset the two versions treat it :differently. :4.11 seems to be only comparing product_code against the non-null set :so that NOT IN is FALSE and the term succeeds. 7.21 is comparing it :against the full set of combination_code returned by the SELECT which :includes NULL so that NOT IN is UNKNOWN and the term fails. : :I have my view of which is correct and so does the author of the code :but :these views do not coincide; fortunately we do not have a copy of the :ANSI SQL spec (why ruin a good argument). : :Your views are invited..... : :Ian 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