Re: Three valued logic (again)

In article <3317D0C3.27EA@netcomuk.co.uk>, Ian Goddard <igoddard@netcomuk.co.uk> writes >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. > Correct. >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 Correct. >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 Correct to check if it is not in the subset you must test against every member of the subset i.e. For all product codes if <the sepecfici product code) is not in (the set of all combination codes in compents) - set A. >includes NULL so that NOT IN is UNKNOWN and the term fails. > Correct. Null = unkown value. E.g. product code = "AAA" set of all combination codes in components = {"B","C",NULL} is not possible to determine in "AAA" in set B since it contains a null (unknown) value. The truth value of the statement "AAA" is in {"B","C",NULL} is unknown ie NULL. since The set could be {"B","C","AAA"} when the answer is TRUE or {"B","C","BIGONES"} when the answer is FALSE If something can be either TRUE or FALSE and you cannot determine which one it is then it is unknown i.e. NULL. PS Yes, I did 2 years of this at university and I still enjoy logic theorms/puzzles. >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 -- David Williams