The idea for that rule was to be able to exploit the limit/fetch spec of the Sort operator to reduce the number of rows that needed to be joined, that is why it was only applied to LEFT/RIGHT outer join.
I think option 2 below sounds better than creating a new rule variant. Thanks, Jesús On 9/6/18, 10:28 AM, "Julian Hyde" <[email protected]> wrote: Ah, that makes sense. Reading the code, I couldn’t figure out why it applies to LEFT and RIGHT but not to INNER. (For some kinds of join, for example inner merge join, it could push the sort to both sides, as long as the sort was compatible with what is needed to ensure that the keys arrive at the right time.) If needed, we could have a variant of the rule that omits the Sort after the Join. Or perhaps we leave the Sort and have a rule that notices the output order of the Join and, based on that, weakens[1] or removes the Sort. Julian [1] https://issues.apache.org/jira/browse/CALCITE-2540 <https://issues.apache.org/jira/browse/CALCITE-2540> > On Sep 6, 2018, at 10:08 AM, Jesus Camacho Rodriguez <[email protected]> wrote: > > If I remember correctly, the rule pushes the Sort through the Join (if possible), but it also preserves the Sort on top of the Join to ensure correctness. > > -Jesús > > > On 9/6/18, 9:57 AM, "Julian Hyde" <[email protected]> wrote: > > Yes, it depends very much on the operator. Some examples: > Merge join typically requires inputs to be sorted, and preserves that order. (But some outer joins may throw in null values out of order.) > Map join typically preserves the order of the probing side, not the build side. > Hash join typically destroys the order of both sides. > Use the rule with caution. > > Julian > > >> On Sep 6, 2018, at 9:33 AM, Stamatis Zampetakis <[email protected]> wrote: >> >> Hello, >> >> I noticed that there is a Calcite rule (i.e., SortJoinTransposeRule) that >> pushes a LogicalSort past a LogicalJoin if the join is either left outer or >> right outer. >> >> Who guarantees that the left and right outer joins are preserving the order >> of the inputs? >> Does the SQL standard requires that these types of joins are order >> preserving? >> >> Since we are working with logical operators, I would tend to think that we >> cannot assume anything about the physical equivalent. >> >> Best, >> Stamatis > > >
