By the way, I'd like one day to be able to optimize select * from t where y = (select max(y) from t)
to select * from t order by y desc limit 1 if y is unique and not null, or something a little more complex if it is not unique. I don't know whether this optimization would help all of the cases you are looking at. I logged https://issues.apache.org/jira/browse/CALCITE-1317. Julian On Fri, Jul 15, 2016 at 5:04 PM, Julian Hyde <[email protected]> wrote: > Can you turn on tracing and post the final planner state? (If you > like, log a JIRA case, and attach the output if it is large.) > > I would have expected there to be a Project immediately above the > BindableTableScan. (Just as in the first query, there was almost > certainly a Project above a TableScan, and then the projects were > correctly pushed into the TableScan.) > > Why is that Project not present? Maybe it is present, but the cost is > wrong. Or maybe the Project was not pushed through the Join (the fact > that the join condition is [true] is a worrying sign). > > The planner state should help answer these questions. > > Julian > > > On Fri, Jul 15, 2016 at 12:32 PM, Johannes Schulte > <[email protected]> wrote: >> Hi, >> >> I am trying to build something similar to drills way of querying directly >> on multiple files, without the columnar layer. I am using the >> ProjectableFilterableTable to filter out files not part of the query. No i >> got some problems and I hope i can express what I want to say: >> >> >> select max(path) from y >> allows for only checking the path column since it is projected >> >> [EnumerableAggregate(group=[{}], EXPR$0=[MAX($0)]) >> EnumerableInterpreter >> BindableTableScan(table=[[ourSchema, ourTable]], projects=[[0]]) >> ] >> >> but when trying to query the maximum path and using it as an filter, the >> projects are gone >> >> select * from y where path = (select max(path) from y). >> >> The explain plan then looks like this: >> >> [EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):VARCHAR(1) >> CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], >> expr#4=[CAST($t2):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE >> "ISO-8859-1$en_US$primary"], expr#5=[=($t3, $t4)], proj#0..1=[{exprs}], >> $condition=[$t5]) >> EnumerableJoin(condition=[true], joinType=[left]) >> EnumerableInterpreter >> BindableTableScan(table=[[ourSchema, ourTable]]) >> EnumerableAggregate(group=[{}], EXPR$0=[MAX($0)]) >> EnumerableInterpreter >> BindableTableScan(table=[[ourSchema, ourTable]]) >> ]] >> >> Is there a way to alter the query so ProjectableFilterableTable can still >> be used? Or is the only way for getting this a translatable table? >> >> Thanks so far, >> >> Johannes
