calcite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Julian Hyde <>
Subject Re: Rewriting IN
Date Tue, 28 Jul 2015 21:01:22 GMT

> On Jul 28, 2015, at 1:07 PM, Vladimir Sitnikov <> 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.


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.


View raw message