@Julian, this exactly what i would expect as logical behavior. In fact it (consistently) behaves like this (e.g. Sort is after Project) when the Sort is on a single column! But for some reasons if the Sort is performed on two columns (same used in the Project) then the Sort is performed before the Project!
This would be an expected if the Sort columns are not contained in the Project such. So i wonder could it be that somewhere during the Sort optimization the this column check gets confused? On 11 November 2017 at 03:05, Julian Hyde <jh...@apache.org> wrote: > While the cost of Project does not depend heavily on the number of input > columns, the cost of Sort (or at least a typical Sort algorithm such as > external merge sort) does depend on the number of columns (or more > precisely on the average row size in bytes). So, if the Project reduces the > number of columns (as most Projects do) then the Sort will have lower cost > if performed after the Project, because it is handling fewer bytes. > > > > On Nov 10, 2017, at 10:32 AM, Luis Fernando Kauer > <lfka...@yahoo.com.br.INVALID> wrote: > > > > I'm trying to fix https://issues.apache.org/jira/browse/CALCITE-1906 > and I'm facing a similar problem. > > After managing to make JdbcSort to work, sometimes the JdbcProject is > above JdbcSort and the generated SQL is wrong because RelToSqlConverter > uses SqlImplementator.Clause enum to decide when to create subqueries, but > since ORDER_BY is after SELECT, once it gets to JdbcProject it can't use > the same query because it already used ORDER_BY. > > The rule responsable for this is SortProjectTransposeRule. The opposite > rule is ProjectSortTransposeRule, but this one only matches if the sort > node is exactly Sort.class, so it ends up not matching. > > Is pushing the Project above Sort usually a good final plan or is it > done to allow other rules to match? If it is not, maybe we should solve > this in the core project. > > > > > > > > Em sexta-feira, 10 de novembro de 2017 15:57:34 BRST, Michael Mior < > mm...@uwaterloo.ca> escreveu: > > > > Since the cost of the project doesn't depend on the number of columns > being > > projected or the size of the input, putting the project before or after > the > > sort will result in the same estimated cost. One approach would be to > scale > > the cost of the projection based on the fraction of columns projected. > > > > -- > > Michael Mior > > mm...@apache.org > > > > 2017-11-10 12:42 GMT-05:00 Christian Tzolov <ctzo...@pivotal.io>: > > > >> I've observed in my > >> no-sql adapter > >> implementation that for q > >> ueries with > >> P > >> roject > >> + > >> S > >> ort by > >> ONE > >> column > >> t > >> he > >> Project > >> > >> is pushed (as expected) > >> before the Sort but for Sort > >> on MULTIPLE > >> columns > >> the Sort is before the Project. > >> For example > >> for a query with one > >> sort column: > >> > >> SELECT yearPublished FROM BookMaster ORDER BY yearPublished ASC > >> > >> The plan looks like expected (project before the sort) > >> > >> > >> PLAN=GeodeToEnumerableConverterRel > >> *GeodeSortRel*(sort0=[$0], dir0=[ASC]) > >> GeodeProjectRel(yearPublished=[$2]) > >> GeodeTableScanRel(table=[[TEST, BookMaster]]) > >> > >> But > >> for sort > >> with > >> two > >> > >> columns: > >> > >> SELECT yearPublished, itemNumber from BookMaster ORDER BY yearPublished > >> ASC, itemNumber ASC > >> > >> The > >> the plan is: > >> > >> > >> PLAN=GeodeToEnumerableConverterRel > >> GeodeProjectRel(yearPublished=[$2], itemNumber=[$0]) > >> *GeodeSortRel*(sort0=[$2], sort1=[$0], dir0=[ASC], dir1=[ASC]) > >> GeodeTableScanRel(table=[[TEST, BookMaster]]) > >> > >> I'm not sure i can explain > >> why in the second case the Sort appears before the Project? > >> Here > >> are my cost functions: > >> > >> * > >> GeodeSortRel > >> : > >> https://github.com/tzolov/calcite/blob/geode-1.3/geode/ > >> src/main/java/org/apache/calcite/adapter/geode/rel/ > GeodeSortRel.java#L51 > >> > >> * GoedeProjectRel: > >> https://github.com/tzolov/calcite/blob/4a631d9055340f64f5e644454551f9 > >> 64ea08f9de/geode/src/main/java/org/apache/calcite/adapter/geode/rel/ > >> GeodeProjectRel.java#L52 > >> > >> > >> > >> Cheers, > >> Christian > > -- Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle Software Engineer | Pivotal <http://pivotal.io/> | ctzo...@pivotal.io |+31610285517
