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
