Re: Sheer logical inconsistencies (of optimizer??)

Ulrich Mueller wrote: > 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, Always NULL. Null is 'unknown', the result of an expression using it is still unknown. The only useful thing you can say about nulls is "<column> IS [NOT] NULL" > (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 In a 2VL, then yes. Introduce nulls and you need (at least) a 3VL. > > 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) > ????? No. If 'age' could be null, then you must cater for that possibility yourself. Say: 'WHERE age IS NULL OR age < 21', if that's what you want. > > > 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? Not a bug, a feature. You'll have to learn to live with it. Ask in comp.databases.theory, they just love nulls over there. -- rh

On Wed, 17 May 2006, Richard Harnden wrote: > > 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, > > Always NULL. > Null is 'unknown', the result of an expression using it is still unknown. > The only useful thing you can say about nulls is "<column> IS [NOT] NULL" > > > negation should satisfy: A v ~A = 1 > > In a 2VL, then yes. Introduce nulls and you need (at least) a 3VL. > Thank you for your quick intro to 3 value logic, because I always thought of WHERE expressions being in 2VL, now it seems they are handled in 3VL, and I cant see any contradictions anymore, (but at least pitfalls a la NOT IN ( SELECT ... ) ) world is fine again, thanks. > > Not a bug, a feature. You'll have to learn to live with it. > Ask in comp.databases.theory, they just love nulls over there. > I'll probably do so some day, cause I start loving them too :) umu