PS Here’s the tweet: https://x.com/julianhyde/status/2088821579530784928
> On Aug 16, 2026, at 12:21 PM, Julian Hyde <[email protected]> wrote: > > 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 > > >
