>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 nulls. >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. Vladimir
