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
