Sheer logical inconsistencies (of optimizer??)

Hi, I ve got a question, it all started with a nested subquery, basically as SELECT * FROM tab WHERE NOT ( id IN ( SELECT UNIQUE id FROM tab2 WHERE vip=1 ))-> 0 results what was the problem, NULLs in tab2, to simplify things, the result of the nested query was (NULL,8,9) in tab we had rows with ids 4,6,8,9 and expected row 4 and 6 to be selected... so tried to figure out what happens, I guess the following WHERE NOT ( id IN (NULL,8,9) ) got transformed to WHERE id NOT IN (NULL,8,9) got transformed to WHERE id!=NULL AND id!=8 AND id!=9 first expression id!=NULL is always false, (as we suppose is every operation with a NULL value, = != >= < IN ...) so no rows, ok, sounds logical but this isn't, negation should satisfy: A v ~A = 1 how could one know for more difficult logical expressions what's the interpretation of the query, if it doesn't conform to basic logic. so basically, if the query optimizer is transforming WHERE NOT (age>=21) shouldn't be the result WHERE (age<21) OR (age IS NULL) instead of WHERE (age<21) ????? that way it would still conform to mathematical relation properties with NULL values, is this a conceptual problem of informix (or even SQL) one has to live with or "hopefully" just a bug? thanks for your comments, umu

Ulrich Mueller said: > > that way it would still conform to > mathematical relation properties with NULL values, Please explain? -- Bye now, Obnoxio Information within this post contains forward looking statements within the meaning of Section 27A of the Securities Act of 1933 and Section 21B of the S E C Act of 1934. Statements that involve discussions with respect to projections of future events are not statements of historical fact and may be forward looking statements. Don't rely on them to make a decision. The poster is not a reporting company registered under the Exchange Act of 1934. I have received a life peerage from Her Majesty, who is not an officer, minister or affiliate Labour party member. I intend to recover my loan now, which could cause the parliamentary majority to go down, resulting in losses for you. Today's Labour party has: an accumulated deficit and a reliance on loans from officers and affiliates to pay expenses. It is not an operating political party. The party is going to need financing to continue as a going concern. A failure to finance could cause the party to go out of business. This report shall not be construed as any kind of investment advice or solicitation. You can lose all your money by investing in this party.