> On Jul 28, 2015, at 1:07 PM, Vladimir Sitnikov <[email protected]> > wrote: > > 1) Am I right this transformation is not performed for sane queries? > By "sane" I mean the ones that have not null constraints and/or > "where" conditions.
Correct. Very often (e.g. if the condition is in a WHERE clause) the caller doesn’t care whether IN evaluates to FALSE or UNKNOWN. So then you can optimize. But if you solve the general problem, then you have also solved NOT IN. NOT IN’s behavior is downright mysterious unless you think in 3-valued logic. > 2) I just did a quick check and "() not in ()" is positive while > "(null) not in (null)" is negative. > select * from dual where (select * from dual where 1=2) not in (select > * from dual where 1=2); <-- returns a row > select * from dual where (null) not in (null); <-- returns no rows Here’s how I think about it. Suppose you have "x IN (query)” and “query” returns values "y, z” then it is the same as “x IN (y, z)”, which is the same as “x = y OR x = z”. If x is 1, y is 2 and z is null, then as you know “1 = 2 OR 1 = null” evaluates to UNKNOWN. You’re making one false assumption in your experiments. The LHS is an empty scalar sub-query, so evaluates to “null”, not the empty list. Using the reasoning above, * “null not in ()” evaluates to TRUE. * “null not in (null)” evaluates to UNKNOWN. So, Oracle is compliant with my understanding of the standard. Julian
