I have been working on supporting subqueries in ON clauses for some time. My current PR supports non-correlated subqueries in ON clauses of left and inner joins where subquery rewrite does not attempt to use the root to do the rewrite. For correlated subqueries and subqueries for joins other than inner or left an exception will be thrown.
https://github.com/apache/calcite/pull/2364 The subqueries cannot just be applied on top of the join as a filter because ON clauses for non-inner joins are not just filters. I am currently supporting left joins by rewriting the subqueries on the right side of the join, allowing multiplicity to be maintained. To support outer joins, I plan on rewriting the query to a left join + union of all values from the right that did not join to the left. In order to support correlated queries in On clauses, calcite's correlate type is insufficient because the join being created could be the correlate join but would also have a filter that needs to be applied. I could either add a new node or enhance correlate/join. Concerning Blackboard.register() vs mutating Blackboard.root to capture the subqueries after they have been rewritten, unless there is an easy way to do the necessary rewrite for reworking the query, then subquery rewrite needs to move away from directly mutating the root node. James
