Yuck! NOT IN is toxic. I'd rather keep it out of the algebra.
On Sun, Jul 19, 2020 at 8:24 PM Haisheng Yuan <[email protected]> wrote: > > Hi all, > > Currently, JoinRelType.ANTI only represents NOT_EXISTS subquery (thanks to > Ruben for reminding). > For some simple boolean context NOT_IN subquery, we can't transform it to > ANTI join. e.g.: > > SELECT * FROM foo WHERE a NOT IN (SELECT b FROM bar); -- bar.b is nullable > > Because if there is a null value in the results of subquery, the NOT IN > predicate will return false, the whole query returns empty. And in Calcite, > the plan for this kind of query is inefficient. > > If we have ANTI_NOTIN to represent this kind of join, we can generate more > efficient plan, as long as the query executor support it. > > Thoughts? > > Haisheng Yuan > >
