My work in Morel gave me an insight for how correlated joins could have been done differently in Calcite.
Correlations are tricky because the correlation variable that they introduce a free variables. Calcite has not just correlated joins but also correlated projects and filters, there’s a case to be made for correlated aggregate (aggregate functions contain a query that references group-keys), and then there’s unnest and cross-apply. It is well known that flatMap can simulate project (map) using a singleton expression, and can simulate filter by emitting an empty collection if the condition is false and a singleton collection if the condition is true. So, my insight was that if we had a ProjectMany operator, which sets a variable for each incoming row, evaluates a set valued expression, and when unions the sets together to produce a stream of output rows, then it’s the only correlated operator we need. (ProjectMany is similar to dependent join except that the join emits rows that combine the left and right side, and ProjectMany’s expression may include fields from the left if it wishes, but doesn’t have to.) It’s not an obvious win. ProjectMany forces us to deal with nested collections, which are more difficult to reason about (and write transformation rules for) than flat records. But it does allow us to reduce the number of core operators, and that means that we can get by with fewer rewrite rules. I doubt that it’s worth the disruption to Calcite making the change now. (It’s not as if we could remove the correlated versions of Project etc.) Just something to think about next time you are writing a query-planning framework. Julian
