Hi Michael, I am currently working on 873 which address this exact use case. I have the patch ready but have hit a bug in HepPlanner which Julian is looking into.
Post that, I should be able to push the JIRA and get this rule in. Will be glad for your review on it On Jun 27, 2017 8:22 PM, "Michael Alexeev" <[email protected]> wrote: > Is SortRemoveRule supposed to address this - Planner rule to remove Sort if > its input is already sorted. > > Looking at its onMatch implementation, I don't see that it actually > compares the Sort and its input node collations. What if they are > different? It only checks whether a planner has a RelCollationSet or not. > > A follow up question - how/when/where this set is getting set in the > planner? > > > On Mon, Jun 26, 2017 at 4:51 PM, Julian Hyde <[email protected]> wrote: > > > Is there a JIRA case for this? It sounds similar to what Atri is working > > on. > > > > Also, I think I ran into it while I was extending Atri’s partial fix for > > https://issues.apache.org/jira/browse/CALCITE-873 < > > https://issues.apache.org/jira/browse/CALCITE-873> in > > https://github.com/julianhyde/calcite/tree/873-weaken-sort < > > https://github.com/julianhyde/calcite/tree/873-weaken-sort> (which I > > never finished, possibly because I ran into deeper bugs). > > > > Julian > > > > > > > > > > > On Jun 20, 2017, at 8:42 PM, Michael Mior <[email protected]> wrote: > > > > > > The LogicalSort in this case can be pushed passed the LogicalJoin (e.g. > > via > > > SortJoinTransposeRule) so it's child would still be the TableScan and > the > > > rule would still match. > > > > > > -- > > > Michael Mior > > > [email protected] > > > > > > 2017-06-20 20:47 GMT-04:00 Michael Alexeev <[email protected] > >: > > > > > >> Michael, > > >> > > >> Yes, this would work in this case but what about a bit more > complicated > > >> example with join > > >> > > >> SELECT R1.I FROM R1 JOIN R2 on R1.I = R2.II ORDER BY R1.I; > > >> > > >> Here, the order will still be guaranteed by the R1 index and the sort > > can > > >> be eliminated but there will be a LogicalJoin in between the > LogicalSort > > >> and > > >> the TableScan that in general can not be pushed thought the > LogicalSort, > > >> right? > > >> > > >> Mike > > >> > > >> On Tue, Jun 20, 2017 at 1:14 PM, Michael Mior <[email protected]> > > wrote: > > >> > > >>> You may find it easier to just have your rule explicitly match a > > >>> LogicalSort on top of a TableScan. Then you can just simply check the > > >>> indices of the fields. Existing rules should help reorder other > > operators > > >>> to produce a tree of this form if needed. > > >>> > > >>> -- > > >>> Michael Mior > > >>> [email protected] > > >>> > > >>> 2017-06-19 16:39 GMT-04:00 Michael Alexeev < > [email protected] > > >: > > >>> > > >>>> Hi All, > > >>>> > > >>>> I am trying to write a rule to eliminate a potentially redundant > > >>>> LogicalSort node. > > >>>> > > >>>> Consider > > >>>> > > >>>> SELECT I FROM R1 ORDER BY I; > > >>>> > > >>>> The initial tree is > > >>>> LogicalSort(sort0=[$0], dir0=[DESC]) > > >>>> LogicalProject(I=[$0]) > > >>>> MyTableScan(table=[[R1]], expr#0..5=[{inputs}], > > >> proj#0..5=[{exprs}]) > > >>>> > > >>>> and after all the transformations are applied it becomes > > >>>> > > >>>> LogicalSort(sort0=[$0], dir0=[DESC]) > > >>>> MyTableScan(table=[[R1]], expr#0..5=[{inputs}], I=[$t0]) > > >>>> > > >>>> The LogicalProject is merged with the MyTableScan which retains all > > the > > >>>> original project expressions inside a local RexProgram instance > > >>>> > > >>>> If MyTableScan already produces rows in an order that matches the > > ORDER > > >>> BY > > >>>> (for example, an underlying table could have an index that > guarantees > > >> the > > >>>> ordering if the scan uses that index to retrieve the rows) then > > >>> LogicalSort > > >>>> is not required and could be eliminated. > > >>>> My approach is to write a transformation rule to match the > LogicalSort > > >>> node > > >>>> and its onMatch method would use a custom RelShuttleImpl class to > > >> visit a > > >>>> child TableScan. The shuttle can compare the collation fields list > > with > > >>> the > > >>>> list of the project expressions to decide whether the sort order > > >>>> is compatible with the index order and the sort can be eliminated. > > >>>> > > >>>> Does this approach sound reasonable? > > >>>> > > >>>> The problem is that I can't figure out how to establish a > > >> correspondence > > >>>> between the sort's collation fields and the scan's columns. Is > there a > > >>>> proper way? If not, what would be a recommended approach? > > >>>> > > >>>> > > >>>> Any help is greatly appreciated, > > >>>> Mike > > >>>> > > >>> > > >> > > > > >
