+1 on that On 16 Jul 2016 5:57 a.m., "Julian Hyde" <[email protected]> wrote:
> 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 >
