I created a pull request <https://github.com/apache/calcite/pull/2138> several days ago to fix sub queries in ON clauses <https://issues.apache.org/jira/browse/CALCITE-4210>. Before my changes, sub-queries rewrites were dropped from an ON clause.
A good way to observe the subquery node being dropped is place to a breakpoint on SqlToRelConverter.java:2201 <https://github.com/apache/calcite/blob/1ae20f3e47d9ffab52e934c077d1b7629f798a2f/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L2201> and run a query with sub-query in the ON clause. The rewrites are fromBlackboard.root, but are not accounted for when the join is created. My changes replay the registered subqueries on the right rel node then joins on the modified rel right node to left rel node with the rewritten condition. There is no particular reason I decided on the right over the left, but they cannot be played on the top of the node to maintain left and outer join semantics. I would greatly appreciate comments, suggestions on my changes. James Starr
