calcite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Vladimir Sitnikov <>
Subject Re: Rewriting IN
Date Tue, 28 Jul 2015 23:06:10 GMT
>But if you solve the general problem,

That is important. However, as we solve that "general" problem, we
should take into account that "downstream engines" might support
3-valued logic.
For instance, OracleDB has null-aware anti-join. In other words, "not
in" just works yet still supports 3-valued logic behind the scenes.

My point #1 is:
1.1) When I approach IN/NOT IN kind of queries, I typically add NOT
NULL everywhere. This solves 3-valued problem. As you add "not null"
you identify the required handling of nulls in a particular query.
1.2) As Calcite focuses on query optimization, it should probably
treat "the most typical cases" first. For instance, it shouldn't
convert every join to cross join+count "just in case" there will be

>So, Oracle is compliant with my understanding of the standard.

Thanks for taking time and explaining this.
I just tried to provoke some conversation over a well-known
hard-to-reason-about problem.
I was inspired with your topic and tried a couple of
never-ask-in-an-interview queries.


View raw message