date:20191108

Re: Question about Interpreter and Corelations

2019-11-08 Thread XING JIN

You may modify your sql and operator Collect should not reference correlate
variables.

XING JIN  于2019年11月9日周六 下午12:32写道：

> Currently RelDecorrelator doesn't support a decorrelateRel(Collect),
> that's why decorrelate failed
>
> Zoltan Farkas  于2019年11月9日周六 上午5:46写道：
>
>> Done: https://issues.apache.org/jira/browse/CALCITE-3488 <
>> https://issues.apache.org/jira/browse/CALCITE-3488>
>> let me know if you need any more info.
>>
>> thank you
>>
>> —Z
>>
>> > On Nov 8, 2019, at 2:17 PM, Julian Hyde  wrote:
>> >
>> > As I said, I think you should log a bug.
>> >
>> > On Fri, Nov 8, 2019 at 10:23 AM Haisheng Yuan 
>> wrote:
>> >>
>> >> I am afraid this query can't be easily decorrelated.
>> >>
>> >> - Haisheng
>> >>
>> >> --
>> >> 发件人：Zoltan Farkas
>> >> 日 期：2019年11月08日 22:46:53
>> >> 收件人：
>> >> 主 题：Re: Question about Interpreter and Corelations
>> >>
>> >> Thanks Julian,
>> >>
>> >> Any idea how could I de-corelate the query?
>> >> RelDecorrelator.decorrelateQuery does not seem to alter the plan.
>> >>
>> >> Will log a jira for the interpreter issue.
>> >>
>> >> thank you
>> >>
>> >> —Z
>> >>
>> >>
>> >>
>> >>> On Nov 7, 2019, at 5:08 PM, Julian Hyde 
>> wrote:
>> >>>
>> >>> I don’t recall what the interpreter is currently capable of, but you
>> should log a bug.
>> >>>
>> >>> I wonder whether if you could get the query to work in the
>> interpreter if you decorrelated the query first.
>> >>>
>> >>> Julian
>> >>>
>>  On Nov 7, 2019, at 11:16, Zoltan Farkas 
>> wrote:
>> 
>>  for a test query with the following plan:
>> 
>>  LogicalProject(name=[$1], friends=[$4])
>>  LogicalCorrelate(correlation=[$cor0], joinType=[inner],
>> requiredColumns=[{0}])
>>   LogicalTableScan(table=[[characters]])
>>   Collect(field=[EXPR$0])
>> LogicalProject(name=[$2])
>>   LogicalJoin(condition=[=($0, $1)], joinType=[inner])
>> LogicalProject(characterId2=[$1])
>>   LogicalFilter(condition=[=($0, $cor0.characterId)])
>>    LogicalTableScan(table=[[friendships]])
>> LogicalProject(characterId=[$0], name=[$1])
>>   LogicalTableScan(table=[[characters]])
>> 
>>  I get :
>> 
>>  java.lang.nsupportedOperationException
>>   at
>> o.a.c.i.JaninoRexCompiler.lambda$compile$0(JaninoRexCompiler.java:94)[calcite-core-1.21.0.jar:1.21.0]
>>   at
>> o.a.c.a.e.RexToLixTranslator.translate0(RexToLixTranslator.java:714)[^]
>>   at ^.translate(^:199)[^]
>>   at ^.translate0(^:684)[^]
>>   at ^.translate(^:199)[^]
>>   at ^.translate(^:194)[^]
>>   ...
>> 
>>  when trying to use the Interpreter.
>> 
>>  Is this a current limitation?
>> 
>>  the query is :
>> 
>>  select name,
>> ARRAY(select c2.name from friendships f, characters c2
>>    where f.characterId1 = c.characterId and
>> f.characterId2 = c2.characterId) as friends
>>  from characters c
>> 
>>  let me know
>> 
>>  thank you
>> 
>>  —Z
>>
>>

Re: Re: Re: Optimizer: All the inputs have relevant nodes, however the cost is still infinite.

2019-11-08 Thread Rui Wang

Xiening - You can reproduce it by running CoreQuidemTest in [1].


Haisheng - those enumerable rules all exist. My change in [1] shouldn't
require implementing new enumerable rules.



[1]: https://github.com/amaliujia/calcite/tree/rw-add_TVF_TUMBLE

On Fri, Nov 8, 2019 at 12:04 PM Haisheng Yuan 
wrote:

> Yes, looks like EnumerableTableFunctionScan doesn't override
> computeSelfCost.
>
> - Haisheng
>
> --
> 发件人：Haisheng Yuan
> 日 期：2019年11月09日 04:01:19
> 收件人：Apache Calcite dev list
> 主 题：Re: Re: Optimizer: All the inputs have relevant nodes, however the
> cost is still infinite.
>
> It is not surprising to get an infinitive cost, since the operators in the
> plan are logical operators, which need to be converted to physical
> operators to be costed. Did you try to add some implementation rules to the
> rule set, e.g. EnumerableProjectRule, EnumerableTableFunctionScanRule, etc..
>
> - Haisheng
>
> --
> 发件人：Xiening Dai
> 日 期：2019年11月09日 03:35:03
> 收件人：
> 主 题：Re: Optimizer: All the inputs have relevant nodes, however the cost is
> still infinite.
>
> Are you able to provide a test case to repro on current code base?
>
> From the error message, the given subset’s best RelNode is not available,
> aka cost is infinite. There could be multiple cases leading to it.
>
>
> > On Nov 8, 2019, at 11:28 AM, Rui Wang  wrote:
> >
> > My current guess is it could because of `computeSelfCost` of
> > `EnumerableTableFunctionScan`.
> >
> >
> > -Rui
> >
> > On Fri, Nov 8, 2019 at 10:04 AM Rui Wang  wrote:
> >
> >> Hello community:
> >>
> >> I am stuck an optimizer error while working on Calcite-3272. I am less
> >> familiar with Calcite's optimizer and struggled to debug the following
> >> error message. Is there some hints on what direction I should go? I can
> >> also provide more information if that is helpful.
> >>
> >>
> >>
> >>> java.sql.SQLException: Error while executing SQL "select * FROM
> >> TABLE(TUMBLE(TABLE ORDERS, 'ROWTIME', INTERVAL '1' MINUTE))": There are
> not
> >> enough rules to produce a node with desired properties:
> >> convention=ENUMERABLE, sort=[]. All the inputs have relevant nodes,
> however
> >> the cost is still infinite.
> >>> Root: rel#26:Subset#2.ENUMERABLE.[]
> >>> Original rel:
> >>> LogicalProject(ROWTIME=[$0], ID=[$1], PRODUCT=[$2], UNITS=[$3],
> >> wstart=[$4], wend=[$5]): rowcount = 100.0, cumulative cost =
> >> {1.7976931348623157E308 rows, 1.7976931348623157E308 cpu,
> >> 1.7976931348623157E308 io}, id = 12
> >>>  LogicalTableFunctionScan(invocation=[TUMBLE($3, 'ROWTIME',
> >> 6:INTERVAL MINUTE)], rowType=[RecordType(TIMESTAMP(0) ROWTIME,
> INTEGER
> >> ID, VARCHAR(10) PRODUCT, INTEGER UNITS, TIMESTAMP(0) wstart,
> TIMESTAMP(0)
> >> wend)]): rowcount = 100.0, cumulative cost = {1.7976931348623157E308
> rows,
> >> 1.7976931348623157E308 cpu, 1.7976931348623157E308 io}, id = 10
> >>>LogicalProject(ROWTIME=[$0], ID=[$1], PRODUCT=[$2], UNITS=[$3]):
> >> rowcount = 100.0, cumulative cost = {200.0 rows, 501.0 cpu, 0.0 io}, id
> = 8
> >>>  LogicalTableScan(table=[[ORINOCO, ORDERS]]): rowcount = 100.0,
> >> cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 2
> >>
> >>
> >>
> >> -Rui
> >>
>
>

Re: Question about Interpreter and Corelations

2019-11-08 Thread XING JIN

Currently RelDecorrelator doesn't support a decorrelateRel(Collect), that's
why decorrelate failed

Zoltan Farkas  于2019年11月9日周六 上午5:46写道：

> Done: https://issues.apache.org/jira/browse/CALCITE-3488 <
> https://issues.apache.org/jira/browse/CALCITE-3488>
> let me know if you need any more info.
>
> thank you
>
> —Z
>
> > On Nov 8, 2019, at 2:17 PM, Julian Hyde  wrote:
> >
> > As I said, I think you should log a bug.
> >
> > On Fri, Nov 8, 2019 at 10:23 AM Haisheng Yuan 
> wrote:
> >>
> >> I am afraid this query can't be easily decorrelated.
> >>
> >> - Haisheng
> >>
> >> --
> >> 发件人：Zoltan Farkas
> >> 日 期：2019年11月08日 22:46:53
> >> 收件人：
> >> 主 题：Re: Question about Interpreter and Corelations
> >>
> >> Thanks Julian,
> >>
> >> Any idea how could I de-corelate the query?
> >> RelDecorrelator.decorrelateQuery does not seem to alter the plan.
> >>
> >> Will log a jira for the interpreter issue.
> >>
> >> thank you
> >>
> >> —Z
> >>
> >>
> >>
> >>> On Nov 7, 2019, at 5:08 PM, Julian Hyde 
> wrote:
> >>>
> >>> I don’t recall what the interpreter is currently capable of, but you
> should log a bug.
> >>>
> >>> I wonder whether if you could get the query to work in the interpreter
> if you decorrelated the query first.
> >>>
> >>> Julian
> >>>
>  On Nov 7, 2019, at 11:16, Zoltan Farkas 
> wrote:
> 
>  for a test query with the following plan:
> 
>  LogicalProject(name=[$1], friends=[$4])
>  LogicalCorrelate(correlation=[$cor0], joinType=[inner],
> requiredColumns=[{0}])
>   LogicalTableScan(table=[[characters]])
>   Collect(field=[EXPR$0])
> LogicalProject(name=[$2])
>   LogicalJoin(condition=[=($0, $1)], joinType=[inner])
> LogicalProject(characterId2=[$1])
>   LogicalFilter(condition=[=($0, $cor0.characterId)])
>    LogicalTableScan(table=[[friendships]])
> LogicalProject(characterId=[$0], name=[$1])
>   LogicalTableScan(table=[[characters]])
> 
>  I get :
> 
>  java.lang.nsupportedOperationException
>   at
> o.a.c.i.JaninoRexCompiler.lambda$compile$0(JaninoRexCompiler.java:94)[calcite-core-1.21.0.jar:1.21.0]
>   at
> o.a.c.a.e.RexToLixTranslator.translate0(RexToLixTranslator.java:714)[^]
>   at ^.translate(^:199)[^]
>   at ^.translate0(^:684)[^]
>   at ^.translate(^:199)[^]
>   at ^.translate(^:194)[^]
>   ...
> 
>  when trying to use the Interpreter.
> 
>  Is this a current limitation?
> 
>  the query is :
> 
>  select name,
> ARRAY(select c2.name from friendships f, characters c2
>    where f.characterId1 = c.characterId and
> f.characterId2 = c2.characterId) as friends
>  from characters c
> 
>  let me know
> 
>  thank you
> 
>  —Z
>
>

Re: Question about Interpreter and Corelations

2019-11-08 Thread Zoltan Farkas

Done: https://issues.apache.org/jira/browse/CALCITE-3488 
 
let me know if you need any more info.

thank you

—Z

> On Nov 8, 2019, at 2:17 PM, Julian Hyde  wrote:
> 
> As I said, I think you should log a bug.
> 
> On Fri, Nov 8, 2019 at 10:23 AM Haisheng Yuan  wrote:
>> 
>> I am afraid this query can't be easily decorrelated.
>> 
>> - Haisheng
>> 
>> --
>> 发件人：Zoltan Farkas
>> 日　期：2019年11月08日 22:46:53
>> 收件人：
>> 主　题：Re: Question about Interpreter and Corelations
>> 
>> Thanks Julian,
>> 
>> Any idea how could I de-corelate the query?
>> RelDecorrelator.decorrelateQuery does not seem to alter the plan.
>> 
>> Will log a jira for the interpreter issue.
>> 
>> thank you
>> 
>> —Z
>> 
>> 
>> 
>>> On Nov 7, 2019, at 5:08 PM, Julian Hyde  wrote:
>>> 
>>> I don’t recall what the interpreter is currently capable of, but you should 
>>> log a bug.
>>> 
>>> I wonder whether if you could get the query to work in the interpreter if 
>>> you decorrelated the query first.
>>> 
>>> Julian
>>> 
 On Nov 7, 2019, at 11:16, Zoltan Farkas  
 wrote:
 
 for a test query with the following plan:
 
 LogicalProject(name=[$1], friends=[$4])
 LogicalCorrelate(correlation=[$cor0], joinType=[inner], 
 requiredColumns=[{0}])
  LogicalTableScan(table=[[characters]])
  Collect(field=[EXPR$0])
LogicalProject(name=[$2])
  LogicalJoin(condition=[=($0, $1)], joinType=[inner])
LogicalProject(characterId2=[$1])
  LogicalFilter(condition=[=($0, $cor0.characterId)])
   LogicalTableScan(table=[[friendships]])
LogicalProject(characterId=[$0], name=[$1])
  LogicalTableScan(table=[[characters]])
 
 I get :
 
 java.lang.nsupportedOperationException
  at 
 o.a.c.i.JaninoRexCompiler.lambda$compile$0(JaninoRexCompiler.java:94)[calcite-core-1.21.0.jar:1.21.0]
  at o.a.c.a.e.RexToLixTranslator.translate0(RexToLixTranslator.java:714)[^]
  at ^.translate(^:199)[^]
  at ^.translate0(^:684)[^]
  at ^.translate(^:199)[^]
  at ^.translate(^:194)[^]
  ...
 
 when trying to use the Interpreter.
 
 Is this a current limitation?
 
 the query is :
 
 select name,
ARRAY(select c2.name from friendships f, characters c2
   where f.characterId1 = c.characterId and 
 f.characterId2 = c2.characterId) as friends
 from characters c
 
 let me know
 
 thank you
 
 —Z

[jira] [Created] (CALCITE-3488) Interpreter does not implement LogicalCorelate.

2019-11-08 Thread Zoltan Farkas (Jira)

Zoltan Farkas created CALCITE-3488:
--

 Summary: Interpreter does not implement LogicalCorelate.
 Key: CALCITE-3488
 URL: https://issues.apache.org/jira/browse/CALCITE-3488
 Project: Calcite
  Issue Type: Bug
Reporter: Zoltan Farkas


Example query:

{code}
select name,
 ARRAY(select c2.name from friendships f, characters c2
where f.characterId1 = c.characterId and 
f.characterId2 = c2.characterId) as friends
{code}

where the 2 tables involved are:

{code}
record Character {
  string characterId;
  string name = "";
  string homeWorldPlanet;
  string speciesName;
}

record Friendship {
  string characterId1;
  string characterId2;
}
{code}


the plan:

{code}
LogicalProject(name=[$1], friends=[$4])
  LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{0}])
LogicalTableScan(table=[[characters]])
Collect(field=[EXPR$0])
  LogicalProject(name=[$2])
LogicalJoin(condition=[=($0, $1)], joinType=[inner])
  LogicalProject(characterId2=[$1])
LogicalFilter(condition=[=($0, $cor0.characterId)])
  LogicalTableScan(table=[[friendships]])
  LogicalProject(characterId=[$0], name=[$1])
LogicalTableScan(table=[[characters]])\n
{code}

when interpreted, results in:

{code}
java.lang.UnsupportedOperationException
at 
o.a.c.i.JaninoRexCompiler.lambda$compile$0(JaninoRexCompiler.java:94)[calcite-core-1.21.0.jar:1.21.0]
at 
o.a.c.a.e.RexToLixTranslator.translate0(RexToLixTranslator.java:714)[^]
at ^.translate(^:199)[^]
at ^.translate0(^:684)[^]
at ^.translate(^:199)[^]
at ^.translate(^:194)[^]
at ^.translateList(^:928)[^]
at ^.translateList(^:901)[^]
at o.a.c.a.e.RexImpTable.implementCall(RexImpTable.java:1163)[^]
at ^.implementNullSemantics(^:1153)[^]
at ^.implementNullSemantics0(^:1043)[^]
at ^.lambda$createImplementor$4(^:690)[^]
at 
o.a.c.a.e.RexToLixTranslator.translateCall(RexToLixTranslator.java:758)[^]
at ^.translate0(^:730)[^]
at ^.translate(^:199)[^]
at ^.translate0(^:684)[^]
at ^.translate(^:199)[^]
at ^.translate(^:194)[^]
at ^.translateList(^:928)[^]
at ^.translateProjects(^:169)[^]
at o.a.c.i.JaninoRexCompiler.compile(JaninoRexCompiler.java:101)[^]
at o.a.c.i.Interpreter$CompilerImpl.compile(Interpreter.java:487)[^]
at o.a.c.i.Nodes$CoreCompiler.compile(Nodes.java:43)[^]
at o.a.c.i.TableScanNode.createEnumerable(TableScanNode.java:266)[^]
at ^.createProjectableFilterable(^:233)[^]
at ^.create(^:81)[^]
at o.a.c.i.Nodes$CoreCompiler.visit(Nodes.java:70)[^]
at j.i.r.NativeMethodAccessorImpl.invoke0(Native Method)
at ^.invoke(^:62)[^]
at 
j.i.r.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)[^]
at j.l.r.Method.invoke(Method.java:566)[^]
at 
o.a.c.u.ReflectUtil.invokeVisitorInternal(ReflectUtil.java:257)[calcite-core-1.21.0.jar:1.21.0]
at ^.invokeVisitor(^:214)[^]
at o.a.c.u.ReflectUtil$1.invokeVisitor(^:464)[^]
at o.a.c.i.Interpreter$CompilerImpl.visit(Interpreter.java:451)[^]
at o.a.c.i.Nodes$CoreCompiler.visit(Nodes.java:43)[^]
at o.a.c.r.BiRel.childrenAccept(BiRel.java:45)[^]
at o.a.c.i.Interpreter$CompilerImpl.visit(Interpreter.java:447)[^]
at o.a.c.i.Nodes$CoreCompiler.visit(Nodes.java:43)[^]
at o.a.c.r.SingleRel.childrenAccept(SingleRel.java:72)[^]
at o.a.c.i.Interpreter$CompilerImpl.visit(Interpreter.java:447)[^]
at o.a.c.i.Nodes$CoreCompiler.visit(Nodes.java:43)[^]
at o.a.c.r.SingleRel.childrenAccept(SingleRel.java:72)[^]
at o.a.c.i.Interpreter$CompilerImpl.visit(Interpreter.java:447)[^]
at o.a.c.i.Nodes$CoreCompiler.visit(Nodes.java:43)[^]
at o.a.c.r.BiRel.childrenAccept(BiRel.java:46)[^]
at o.a.c.i.Interpreter$CompilerImpl.visit(Interpreter.java:447)[^]
at o.a.c.i.Nodes$CoreCompiler.visit(Nodes.java:43)[^]
at o.a.c.r.SingleRel.childrenAccept(SingleRel.java:72)[^]
at o.a.c.i.Interpreter$CompilerImpl.visit(Interpreter.java:447)[^]
at o.a.c.i.Nodes$CoreCompiler.visit(Nodes.java:43)[^]
at o.a.c.i.Interpreter$CompilerImpl.visitRoot(Interpreter.java:405)[^]
at o.a.c.i.Interpreter.(^:88)[^]
{code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

Re: Re: Re: Optimizer: All the inputs have relevant nodes, however the cost is still infinite.

2019-11-08 Thread Haisheng Yuan

Yes, looks like EnumerableTableFunctionScan doesn't override computeSelfCost.

- Haisheng

--
发件人：Haisheng Yuan
日　期：2019年11月09日 04:01:19
收件人：Apache Calcite dev list
主　题：Re: Re: Optimizer: All the inputs have relevant nodes, however the cost is 
still infinite.

It is not surprising to get an infinitive cost, since the operators in the plan 
are logical operators, which need to be converted to physical operators to be 
costed. Did you try to add some implementation rules to the rule set, e.g. 
EnumerableProjectRule, EnumerableTableFunctionScanRule, etc..

- Haisheng

--
发件人：Xiening Dai
日　期：2019年11月09日 03:35:03
收件人：
主　题：Re: Optimizer: All the inputs have relevant nodes, however the cost is 
still infinite.

Are you able to provide a test case to repro on current code base?

From the error message, the given subset’s best RelNode is not available, aka 
cost is infinite. There could be multiple cases leading to it.


> On Nov 8, 2019, at 11:28 AM, Rui Wang  wrote:
> 
> My current guess is it could because of `computeSelfCost` of
> `EnumerableTableFunctionScan`.
> 
> 
> -Rui
> 
> On Fri, Nov 8, 2019 at 10:04 AM Rui Wang  wrote:
> 
>> Hello community:
>> 
>> I am stuck an optimizer error while working on Calcite-3272. I am less
>> familiar with Calcite's optimizer and struggled to debug the following
>> error message. Is there some hints on what direction I should go? I can
>> also provide more information if that is helpful.
>> 
>> 
>> 
>>> java.sql.SQLException: Error while executing SQL "select * FROM
>> TABLE(TUMBLE(TABLE ORDERS, 'ROWTIME', INTERVAL '1' MINUTE))": There are not
>> enough rules to produce a node with desired properties:
>> convention=ENUMERABLE, sort=[]. All the inputs have relevant nodes, however
>> the cost is still infinite.
>>> Root: rel#26:Subset#2.ENUMERABLE.[]
>>> Original rel:
>>> LogicalProject(ROWTIME=[$0], ID=[$1], PRODUCT=[$2], UNITS=[$3],
>> wstart=[$4], wend=[$5]): rowcount = 100.0, cumulative cost =
>> {1.7976931348623157E308 rows, 1.7976931348623157E308 cpu,
>> 1.7976931348623157E308 io}, id = 12
>>>  LogicalTableFunctionScan(invocation=[TUMBLE($3, 'ROWTIME',
>> 6:INTERVAL MINUTE)], rowType=[RecordType(TIMESTAMP(0) ROWTIME, INTEGER
>> ID, VARCHAR(10) PRODUCT, INTEGER UNITS, TIMESTAMP(0) wstart, TIMESTAMP(0)
>> wend)]): rowcount = 100.0, cumulative cost = {1.7976931348623157E308 rows,
>> 1.7976931348623157E308 cpu, 1.7976931348623157E308 io}, id = 10
>>>LogicalProject(ROWTIME=[$0], ID=[$1], PRODUCT=[$2], UNITS=[$3]):
>> rowcount = 100.0, cumulative cost = {200.0 rows, 501.0 cpu, 0.0 io}, id = 8
>>>  LogicalTableScan(table=[[ORINOCO, ORDERS]]): rowcount = 100.0,
>> cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 2
>> 
>> 
>> 
>> -Rui
>>

Re: Re: Optimizer: All the inputs have relevant nodes, however the cost is still infinite.

2019-11-08 Thread Haisheng Yuan

It is not surprising to get an infinitive cost, since the operators in the plan 
are logical operators, which need to be converted to physical operators to be 
costed. Did you try to add some implementation rules to the rule set, e.g. 
EnumerableProjectRule, EnumerableTableFunctionScanRule, etc..

- Haisheng

--
发件人：Xiening Dai
日　期：2019年11月09日 03:35:03
收件人：
主　题：Re: Optimizer: All the inputs have relevant nodes, however the cost is 
still infinite.

Are you able to provide a test case to repro on current code base?

From the error message, the given subset’s best RelNode is not available, aka 
cost is infinite. There could be multiple cases leading to it.


> On Nov 8, 2019, at 11:28 AM, Rui Wang  wrote:
> 
> My current guess is it could because of `computeSelfCost` of
> `EnumerableTableFunctionScan`.
> 
> 
> -Rui
> 
> On Fri, Nov 8, 2019 at 10:04 AM Rui Wang  wrote:
> 
>> Hello community:
>> 
>> I am stuck an optimizer error while working on Calcite-3272. I am less
>> familiar with Calcite's optimizer and struggled to debug the following
>> error message. Is there some hints on what direction I should go? I can
>> also provide more information if that is helpful.
>> 
>> 
>> 
>>> java.sql.SQLException: Error while executing SQL "select * FROM
>> TABLE(TUMBLE(TABLE ORDERS, 'ROWTIME', INTERVAL '1' MINUTE))": There are not
>> enough rules to produce a node with desired properties:
>> convention=ENUMERABLE, sort=[]. All the inputs have relevant nodes, however
>> the cost is still infinite.
>>> Root: rel#26:Subset#2.ENUMERABLE.[]
>>> Original rel:
>>> LogicalProject(ROWTIME=[$0], ID=[$1], PRODUCT=[$2], UNITS=[$3],
>> wstart=[$4], wend=[$5]): rowcount = 100.0, cumulative cost =
>> {1.7976931348623157E308 rows, 1.7976931348623157E308 cpu,
>> 1.7976931348623157E308 io}, id = 12
>>>  LogicalTableFunctionScan(invocation=[TUMBLE($3, 'ROWTIME',
>> 6:INTERVAL MINUTE)], rowType=[RecordType(TIMESTAMP(0) ROWTIME, INTEGER
>> ID, VARCHAR(10) PRODUCT, INTEGER UNITS, TIMESTAMP(0) wstart, TIMESTAMP(0)
>> wend)]): rowcount = 100.0, cumulative cost = {1.7976931348623157E308 rows,
>> 1.7976931348623157E308 cpu, 1.7976931348623157E308 io}, id = 10
>>>LogicalProject(ROWTIME=[$0], ID=[$1], PRODUCT=[$2], UNITS=[$3]):
>> rowcount = 100.0, cumulative cost = {200.0 rows, 501.0 cpu, 0.0 io}, id = 8
>>>  LogicalTableScan(table=[[ORINOCO, ORDERS]]): rowcount = 100.0,
>> cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 2
>> 
>> 
>> 
>> -Rui
>>

Re: Optimizer: All the inputs have relevant nodes, however the cost is still infinite.

2019-11-08 Thread Xiening Dai

Are you able to provide a test case to repro on current code base?

From the error message, the given subset’s best RelNode is not available, aka 
cost is infinite. There could be multiple cases leading to it.


> On Nov 8, 2019, at 11:28 AM, Rui Wang  wrote:
> 
> My current guess is it could because of `computeSelfCost` of
> `EnumerableTableFunctionScan`.
> 
> 
> -Rui
> 
> On Fri, Nov 8, 2019 at 10:04 AM Rui Wang  wrote:
> 
>> Hello community:
>> 
>> I am stuck an optimizer error while working on Calcite-3272. I am less
>> familiar with Calcite's optimizer and struggled to debug the following
>> error message. Is there some hints on what direction I should go? I can
>> also provide more information if that is helpful.
>> 
>> 
>> 
>>> java.sql.SQLException: Error while executing SQL "select * FROM
>> TABLE(TUMBLE(TABLE ORDERS, 'ROWTIME', INTERVAL '1' MINUTE))": There are not
>> enough rules to produce a node with desired properties:
>> convention=ENUMERABLE, sort=[]. All the inputs have relevant nodes, however
>> the cost is still infinite.
>>> Root: rel#26:Subset#2.ENUMERABLE.[]
>>> Original rel:
>>> LogicalProject(ROWTIME=[$0], ID=[$1], PRODUCT=[$2], UNITS=[$3],
>> wstart=[$4], wend=[$5]): rowcount = 100.0, cumulative cost =
>> {1.7976931348623157E308 rows, 1.7976931348623157E308 cpu,
>> 1.7976931348623157E308 io}, id = 12
>>>  LogicalTableFunctionScan(invocation=[TUMBLE($3, 'ROWTIME',
>> 6:INTERVAL MINUTE)], rowType=[RecordType(TIMESTAMP(0) ROWTIME, INTEGER
>> ID, VARCHAR(10) PRODUCT, INTEGER UNITS, TIMESTAMP(0) wstart, TIMESTAMP(0)
>> wend)]): rowcount = 100.0, cumulative cost = {1.7976931348623157E308 rows,
>> 1.7976931348623157E308 cpu, 1.7976931348623157E308 io}, id = 10
>>>LogicalProject(ROWTIME=[$0], ID=[$1], PRODUCT=[$2], UNITS=[$3]):
>> rowcount = 100.0, cumulative cost = {200.0 rows, 501.0 cpu, 0.0 io}, id = 8
>>>  LogicalTableScan(table=[[ORINOCO, ORDERS]]): rowcount = 100.0,
>> cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 2
>> 
>> 
>> 
>> -Rui
>>

Re: Optimizer: All the inputs have relevant nodes, however the cost is still infinite.

2019-11-08 Thread Rui Wang

My current guess is it could because of `computeSelfCost` of
`EnumerableTableFunctionScan`.


-Rui

On Fri, Nov 8, 2019 at 10:04 AM Rui Wang  wrote:

> Hello community:
>
> I am stuck an optimizer error while working on Calcite-3272. I am less
> familiar with Calcite's optimizer and struggled to debug the following
> error message. Is there some hints on what direction I should go? I can
> also provide more information if that is helpful.
>
>
>
> > java.sql.SQLException: Error while executing SQL "select * FROM
> TABLE(TUMBLE(TABLE ORDERS, 'ROWTIME', INTERVAL '1' MINUTE))": There are not
> enough rules to produce a node with desired properties:
> convention=ENUMERABLE, sort=[]. All the inputs have relevant nodes, however
> the cost is still infinite.
> > Root: rel#26:Subset#2.ENUMERABLE.[]
> > Original rel:
> > LogicalProject(ROWTIME=[$0], ID=[$1], PRODUCT=[$2], UNITS=[$3],
> wstart=[$4], wend=[$5]): rowcount = 100.0, cumulative cost =
> {1.7976931348623157E308 rows, 1.7976931348623157E308 cpu,
> 1.7976931348623157E308 io}, id = 12
> >   LogicalTableFunctionScan(invocation=[TUMBLE($3, 'ROWTIME',
> 6:INTERVAL MINUTE)], rowType=[RecordType(TIMESTAMP(0) ROWTIME, INTEGER
> ID, VARCHAR(10) PRODUCT, INTEGER UNITS, TIMESTAMP(0) wstart, TIMESTAMP(0)
> wend)]): rowcount = 100.0, cumulative cost = {1.7976931348623157E308 rows,
> 1.7976931348623157E308 cpu, 1.7976931348623157E308 io}, id = 10
> > LogicalProject(ROWTIME=[$0], ID=[$1], PRODUCT=[$2], UNITS=[$3]):
> rowcount = 100.0, cumulative cost = {200.0 rows, 501.0 cpu, 0.0 io}, id = 8
> >   LogicalTableScan(table=[[ORINOCO, ORDERS]]): rowcount = 100.0,
> cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 2
>
>
>
> -Rui
>

Re: Re: Question about Interpreter and Corelations

2019-11-08 Thread Julian Hyde

As I said, I think you should log a bug.

On Fri, Nov 8, 2019 at 10:23 AM Haisheng Yuan  wrote:
>
> I am afraid this query can't be easily decorrelated.
>
> - Haisheng
>
> --
> 发件人：Zoltan Farkas
> 日　期：2019年11月08日 22:46:53
> 收件人：
> 主　题：Re: Question about Interpreter and Corelations
>
> Thanks Julian,
>
> Any idea how could I de-corelate the query?
> RelDecorrelator.decorrelateQuery does not seem to alter the plan.
>
> Will log a jira for the interpreter issue.
>
> thank you
>
> —Z
>
>
>
> > On Nov 7, 2019, at 5:08 PM, Julian Hyde  wrote:
> >
> > I don’t recall what the interpreter is currently capable of, but you should 
> > log a bug.
> >
> > I wonder whether if you could get the query to work in the interpreter if 
> > you decorrelated the query first.
> >
> > Julian
> >
> >> On Nov 7, 2019, at 11:16, Zoltan Farkas  
> >> wrote:
> >>
> >> for a test query with the following plan:
> >>
> >> LogicalProject(name=[$1], friends=[$4])
> >> LogicalCorrelate(correlation=[$cor0], joinType=[inner], 
> >> requiredColumns=[{0}])
> >>   LogicalTableScan(table=[[characters]])
> >>   Collect(field=[EXPR$0])
> >> LogicalProject(name=[$2])
> >>   LogicalJoin(condition=[=($0, $1)], joinType=[inner])
> >> LogicalProject(characterId2=[$1])
> >>   LogicalFilter(condition=[=($0, $cor0.characterId)])
> >>LogicalTableScan(table=[[friendships]])
> >> LogicalProject(characterId=[$0], name=[$1])
> >>   LogicalTableScan(table=[[characters]])
> >>
> >> I get :
> >>
> >> java.lang.nsupportedOperationException
> >>   at 
> >> o.a.c.i.JaninoRexCompiler.lambda$compile$0(JaninoRexCompiler.java:94)[calcite-core-1.21.0.jar:1.21.0]
> >>   at 
> >> o.a.c.a.e.RexToLixTranslator.translate0(RexToLixTranslator.java:714)[^]
> >>   at ^.translate(^:199)[^]
> >>   at ^.translate0(^:684)[^]
> >>   at ^.translate(^:199)[^]
> >>   at ^.translate(^:194)[^]
> >>   ...
> >>
> >> when trying to use the Interpreter.
> >>
> >> Is this a current limitation?
> >>
> >> the query is :
> >>
> >> select name,
> >> ARRAY(select c2.name from friendships f, characters c2
> >>where f.characterId1 = c.characterId and 
> >> f.characterId2 = c2.characterId) as friends
> >> from characters c
> >>
> >> let me know
> >>
> >> thank you
> >>
> >> —Z

Re: Re: Question about Interpreter and Corelations

2019-11-08 Thread Haisheng Yuan

I am afraid this query can't be easily decorrelated.

- Haisheng

--
发件人：Zoltan Farkas
日　期：2019年11月08日 22:46:53
收件人：
主　题：Re: Question about Interpreter and Corelations

Thanks Julian,

Any idea how could I de-corelate the query?
RelDecorrelator.decorrelateQuery does not seem to alter the plan.

Will log a jira for the interpreter issue.

thank you

—Z



> On Nov 7, 2019, at 5:08 PM, Julian Hyde  wrote:
> 
> I don’t recall what the interpreter is currently capable of, but you should 
> log a bug.
> 
> I wonder whether if you could get the query to work in the interpreter if you 
> decorrelated the query first. 
> 
> Julian
> 
>> On Nov 7, 2019, at 11:16, Zoltan Farkas  wrote:
>> 
>> for a test query with the following plan:
>> 
>> LogicalProject(name=[$1], friends=[$4])
>> LogicalCorrelate(correlation=[$cor0], joinType=[inner], 
>> requiredColumns=[{0}])
>>   LogicalTableScan(table=[[characters]])
>>   Collect(field=[EXPR$0])
>> LogicalProject(name=[$2])
>>   LogicalJoin(condition=[=($0, $1)], joinType=[inner])
>> LogicalProject(characterId2=[$1])
>>   LogicalFilter(condition=[=($0, $cor0.characterId)])
>>LogicalTableScan(table=[[friendships]])
>> LogicalProject(characterId=[$0], name=[$1])
>>   LogicalTableScan(table=[[characters]])
>> 
>> I get :
>> 
>> java.lang.nsupportedOperationException
>>   at 
>> o.a.c.i.JaninoRexCompiler.lambda$compile$0(JaninoRexCompiler.java:94)[calcite-core-1.21.0.jar:1.21.0]
>>   at o.a.c.a.e.RexToLixTranslator.translate0(RexToLixTranslator.java:714)[^]
>>   at ^.translate(^:199)[^]
>>   at ^.translate0(^:684)[^]
>>   at ^.translate(^:199)[^]
>>   at ^.translate(^:194)[^]
>>   ...
>> 
>> when trying to use the Interpreter.
>> 
>> Is this a current limitation?  
>> 
>> the query is :
>> 
>> select name,
>> ARRAY(select c2.name from friendships f, characters c2
>>where f.characterId1 = c.characterId and 
>> f.characterId2 = c2.characterId) as friends
>> from characters c
>> 
>> let me know
>> 
>> thank you
>> 
>> —Z

Optimizer: All the inputs have relevant nodes, however the cost is still infinite.

2019-11-08 Thread Rui Wang

Hello community:

I am stuck an optimizer error while working on Calcite-3272. I am less
familiar with Calcite's optimizer and struggled to debug the following
error message. Is there some hints on what direction I should go? I can
also provide more information if that is helpful.



> java.sql.SQLException: Error while executing SQL "select * FROM
TABLE(TUMBLE(TABLE ORDERS, 'ROWTIME', INTERVAL '1' MINUTE))": There are not
enough rules to produce a node with desired properties:
convention=ENUMERABLE, sort=[]. All the inputs have relevant nodes, however
the cost is still infinite.
> Root: rel#26:Subset#2.ENUMERABLE.[]
> Original rel:
> LogicalProject(ROWTIME=[$0], ID=[$1], PRODUCT=[$2], UNITS=[$3],
wstart=[$4], wend=[$5]): rowcount = 100.0, cumulative cost =
{1.7976931348623157E308 rows, 1.7976931348623157E308 cpu,
1.7976931348623157E308 io}, id = 12
>   LogicalTableFunctionScan(invocation=[TUMBLE($3, 'ROWTIME',
6:INTERVAL MINUTE)], rowType=[RecordType(TIMESTAMP(0) ROWTIME, INTEGER
ID, VARCHAR(10) PRODUCT, INTEGER UNITS, TIMESTAMP(0) wstart, TIMESTAMP(0)
wend)]): rowcount = 100.0, cumulative cost = {1.7976931348623157E308 rows,
1.7976931348623157E308 cpu, 1.7976931348623157E308 io}, id = 10
> LogicalProject(ROWTIME=[$0], ID=[$1], PRODUCT=[$2], UNITS=[$3]):
rowcount = 100.0, cumulative cost = {200.0 rows, 501.0 cpu, 0.0 io}, id = 8
>   LogicalTableScan(table=[[ORINOCO, ORDERS]]): rowcount = 100.0,
cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 2



-Rui

[jira] [Created] (CALCITE-3487) Should not hard code RelMetadataQuery class in VolcanoPlanner.isValid()

2019-11-08 Thread Xiening Dai (Jira)

Xiening Dai created CALCITE-3487:


 Summary: Should not hard code RelMetadataQuery class in 
VolcanoPlanner.isValid()
 Key: CALCITE-3487
 URL: https://issues.apache.org/jira/browse/CALCITE-3487
 Project: Calcite
  Issue Type: Bug
Reporter: Xiening Dai


With CALCITE-3446, a calcite user can choose to provide a customized provide a 
sub-class of RelMetadataQuery through RelOptCluster.setMetadataQuery(). We 
cannot assume the base class RelMetadataQuery is always used in planner.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Created] (CALCITE-3486) MysqlSqlDialect unparse ROW keyword does not exist

2019-11-08 Thread quxiucheng (Jira)

quxiucheng created CALCITE-3486:
---

 Summary: MysqlSqlDialect unparse ROW keyword does not exist
 Key: CALCITE-3486
 URL: https://issues.apache.org/jira/browse/CALCITE-3486
 Project: Calcite
  Issue Type: Bug
  Components: core
Affects Versions: 1.21.0
Reporter: quxiucheng
 Fix For: next


when we use mysql dialect unparse sql,the Mysql dialect does not have a ROW 
keyword,

but the ROW keyword does exist in SQL

SQL
{code:java}
insert into emps values (1,'Fredkin')
{code}
SqlNode.toString(MysqlSqlDialect.DEFAULT)
{code:java}
insert into emps values ROW(1,'Fredkin')
{code}
This syntax is problematic in mysql

 



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Created] (CALCITE-3485) Validation error in UDF with overloads and dynamic parameter

2019-11-08 Thread Stamatis Zampetakis (Jira)

Stamatis Zampetakis created CALCITE-3485:


 Summary: Validation error in UDF with overloads and dynamic 
parameter
 Key: CALCITE-3485
 URL: https://issues.apache.org/jira/browse/CALCITE-3485
 Project: Calcite
  Issue Type: Bug
  Components: core
Affects Versions: 1.21.0
Reporter: Stamatis Zampetakis
Assignee: Stamatis Zampetakis


The problem can be reproduced by defining multiple user defined functions with 
the same name and trying to validate a query having at least one operand that 
is a dynamic parameter.

The interesting part of the stack trace is shown below:
{noformat}
com.onwbp.org.apache.calcite.runtime.CalciteContextException: At line 1, column 
84: Illegal use of dynamic parameter
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at 
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at 
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at 
com.onwbp.org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463)
at 
com.onwbp.org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:835)
at 
com.onwbp.org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:820)
at 
com.onwbp.org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:4881)
at 
com.onwbp.org.apache.calcite.sql.validate.SqlValidatorImpl.inferUnknownTypes(SqlValidatorImpl.java:1801)
at 
com.onwbp.org.apache.calcite.sql.validate.SqlValidatorImpl.inferUnknownTypes(SqlValidatorImpl.java:1880)
at 
com.onwbp.org.apache.calcite.sql.validate.SqlValidatorImpl.validateWhereOrOn(SqlValidatorImpl.java:4052)
at 
com.onwbp.org.apache.calcite.sql.validate.SqlValidatorImpl.validateWhereClause(SqlValidatorImpl.java:4044)
at 
com.onwbp.org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3386)
at 
com.onwbp.org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
at 
com.onwbp.org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
at 
com.onwbp.org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1009)
at 
com.onwbp.org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:969)
at 
com.onwbp.org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:216)
at 
com.onwbp.org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:944)
at 
com.onwbp.org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:651)
{noformat}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Created] (CALCITE-3484) SqlToRelConverter: ClassCastException when processing a query with ANY and UNNEST

2019-11-08 Thread Ruben Q L (Jira)

Ruben Q L created CALCITE-3484:
--

 Summary: SqlToRelConverter: ClassCastException when processing a 
query with ANY and UNNEST
 Key: CALCITE-3484
 URL: https://issues.apache.org/jira/browse/CALCITE-3484
 Project: Calcite
  Issue Type: Bug
Affects Versions: 1.21.0
Reporter: Ruben Q L


The problem can be reproduced by adding the following test to 
SqlToRelConverterTest.java:
{code}
  @Test public void testEqAnyUnnest() {
final String sql = "select d.deptno from dept_nested d WHERE 'john' = ANY 
(SELECT e.ename from UNNEST(d.employees) e)";
sql(sql).ok();
  }
{code}
Which throws the following exception:
{code}
java.lang.ClassCastException: org.apache.calcite.rel.logical.LogicalCorrelate 
cannot be cast to org.apache.calcite.rel.core.Join

at 
org.apache.calcite.sql2rel.SqlToRelConverter.translateIn(SqlToRelConverter.java:1285)
at 
org.apache.calcite.sql2rel.SqlToRelConverter.substituteSubQuery(SqlToRelConverter.java:1157)
at 
org.apache.calcite.sql2rel.SqlToRelConverter.replaceSubQueries(SqlToRelConverter.java:1013)
at 
org.apache.calcite.sql2rel.SqlToRelConverter.convertWhere(SqlToRelConverter.java:979)
at 
org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:648)
at 
org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:626)
at 
org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3180)
at 
org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:562)
at 
org.apache.calcite.test.SqlToRelTestBase$TesterImpl.convertSqlToRel(SqlToRelTestBase.java:626)
at 
org.apache.calcite.test.SqlToRelTestBase$TesterImpl.assertConvertsTo(SqlToRelTestBase.java:745)
at 
org.apache.calcite.test.SqlToRelConverterTest$Sql.convertsTo(SqlToRelConverterTest.java:3664)
at 
org.apache.calcite.test.SqlToRelConverterTest$Sql.ok(SqlToRelConverterTest.java:3656)
at 
org.apache.calcite.test.SqlToRelConverterTest.testEqAnyUnnest(SqlToRelConverterTest.java:1266)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at 
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at 
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at 
org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at 
org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at 
org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at 
org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at 
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
at 
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at 
com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
at 
com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
at 
com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
at 
com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
{code}

Note that the following "similar" queries are correctly converted:
{code}
  @Test public void testUnnestEq() {
final String sql = "select d.deptno from dept_nested d, UNNEST(d.employees) 
e WHERE e.ename = 'john'";
sql(sql).ok();
  }

  @Test public void testInUnnest() {
final String sql = "select d.deptno from dept_nested d WHERE 'john' IN 
(SELECT e.ename from UNNEST(d.employees) e)";
sql(sql).ok();
  }

  @Test public void testExistsUnnest() {
final String sql = "select d.deptno from dept_nested d WHERE EXISTS (SELECT 
1 from UNNEST(d.employees) e WHERE e.ename = 'john')";
sql(sql).ok();
  }
{code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

Re: Question about Interpreter and Corelations

2019-11-08 Thread Zoltan Farkas

Thanks Julian,

Any idea how could I de-corelate the query?
RelDecorrelator.decorrelateQuery does not seem to alter the plan.

Will log a jira for the interpreter issue.

thank you

—Z

 

> On Nov 7, 2019, at 5:08 PM, Julian Hyde  wrote:
> 
> I don’t recall what the interpreter is currently capable of, but you should 
> log a bug.
> 
> I wonder whether if you could get the query to work in the interpreter if you 
> decorrelated the query first. 
> 
> Julian
> 
>> On Nov 7, 2019, at 11:16, Zoltan Farkas  wrote:
>> 
>> for a test query with the following plan:
>> 
>> LogicalProject(name=[$1], friends=[$4])
>> LogicalCorrelate(correlation=[$cor0], joinType=[inner], 
>> requiredColumns=[{0}])
>>   LogicalTableScan(table=[[characters]])
>>   Collect(field=[EXPR$0])
>> LogicalProject(name=[$2])
>>   LogicalJoin(condition=[=($0, $1)], joinType=[inner])
>> LogicalProject(characterId2=[$1])
>>   LogicalFilter(condition=[=($0, $cor0.characterId)])
>>LogicalTableScan(table=[[friendships]])
>> LogicalProject(characterId=[$0], name=[$1])
>>   LogicalTableScan(table=[[characters]])
>> 
>> I get :
>> 
>> java.lang.nsupportedOperationException
>>   at 
>> o.a.c.i.JaninoRexCompiler.lambda$compile$0(JaninoRexCompiler.java:94)[calcite-core-1.21.0.jar:1.21.0]
>>   at o.a.c.a.e.RexToLixTranslator.translate0(RexToLixTranslator.java:714)[^]
>>   at ^.translate(^:199)[^]
>>   at ^.translate0(^:684)[^]
>>   at ^.translate(^:199)[^]
>>   at ^.translate(^:194)[^]
>>   ...
>> 
>> when trying to use the Interpreter.
>> 
>> Is this a current limitation?  
>> 
>> the query is :
>> 
>> select name,
>> ARRAY(select c2.name from friendships f, characters c2
>>where f.characterId1 = c.characterId and 
>> f.characterId2 = c2.characterId) as friends
>> from characters c
>> 
>> let me know
>> 
>> thank you
>> 
>> —Z

Re: [DISCUSS] Proposal to add API to force rules matching specific rels

2019-11-08 Thread XING JIN

Hi Vladimir,

I think the way PlannerTests#GoodSingleRule and EnumerableXXXRule work may
help you ~
They work by a top-down fashion, but when matching parent, they convert the
children explicitly.
You may try below steps:
1. Construct a rule LogicalParentRule to match the LogicalParent without
distribution/physical requirement for the LogicalChild;
2. In this rule, you call 'planner.changeTraits' on the LogicalChild to
build a new child with physical convention. Note that at this moment only
an empty RelSubset is created and no PhysicalChild exists.
3. Then set the RelNode to be the new input of LogicalParent;

By above steps, you can build a parent-child relationship between
LogicalParent and PhysicalChild, and at last the PhysicalParentRule will be
fired based on this relationship.

I have a commit to illustrate my idea, check VolcanoPlannerTest#testDEV in
below link, hope it may help you ~
https://github.com/jinxing64/calcite/tree/demo

Also I'm +1 with Seliverstov that to get all parents of a set, which
against the current check in RelSubset#getParentRels

Best,
Jin

Vladimir Ozerov  于2019年11月5日周二 下午6:41写道：

> Hi Xiening,
>
> I read the thread about on-demand trait requests. It seems pretty similar
> to what I am trying to achieve, as it facilitates the bottom-up propagation
> of physical traits. In fact, both your and my strategy propagate traits
> bottom-up, but I do this through rules, which also fire bottom-up, while in
> your case only the traits are propagated bottom-up, while rules continue
> working in a top-down fashion.
>
> However, I am thinking of how I would potentially implement my optimizer
> with your approach, and it feels like with on-demand traits resulting
> implementation of metadata queries may become very complex to that point
> that it will look like another set of rules, parallel to the already
> existing ruleset. For example, consider that I have a couple of distributed
> tables in an OLTP application. These tables have a number of indexes, and I
> would like to join them. First, I have a number of choices on how to join
> tables with respect to distribution. Then, I have a number of choices on
> which access method to use. Because sometimes it is beneficial to pick
> index scans instead of table scans even without index conditions, for
> example, to preserve a comfortable collation. So when my logical scan
> receives such metadata request, it typically cannot return all possible
> combinations, because there are too many of them. Instead, some heuristical
> or cost-based logic will be used to calculate a couple of most prospective
> ones. But it seems that we will have to duplicate the same logic in the
> corresponding rule, aren't we?
>
> I would love to read your design because this is a really interesting
> topic, and it is of great importance for the distributed engines developed
> on top of Calcite since proper use of distribution and collation is the key
> success factor for efficient query optimization.
>
> Regards,
> Vladimir.
>
> пт, 1 нояб. 2019 г. в 00:40, Xiening Dai :
>
> > Actually we solved this problem in our setup using a mechanism called
> > “Pull-Up Traits”, which explores the possible trait set of children’s
> input
> > to decide parent’s physical properties. In order to determine child input
> > trait, you would have to look at child’s children, and all the way to the
> > leaves nodes or a barrier. A barrier is a rel node which cannot derive
> any
> > traits regardless the input. A good example would be a user define
> function
> > which would throw off any distribution or collation. Then we realize just
> > pulling up is not enough, sometimes we would need to look at parent’s
> > requirement as well. So we try to solve this in a unified framework,
> which
> > we call “On Demand Trait” and implement it as part of the framework so
> > anyone can be benefited. I hope Haisheng can share a design doc once we
> > have more concrete ideas.
> >
> >
> > > On Oct 31, 2019, at 11:37 AM, Jinfeng Ni  wrote:
> > >
> > > Hi Vladimir,
> > >
> > > The SubsetTransformer interface and the iterating over the RelNodes
> > > within a RelSubset in Drill  is exactly implemented to do the trait
> > > propagation. We also had to rely on AbstractConverter to fire
> > > necessary rule to avoid the CanNotPlan issue. At some point, Calcite
> > > community chooses to remove AbstractConverter and Drill had to add it
> > > back, which is probably one of the main reasons for us to continue
> > > using a Calcite fork.  I still remember we constantly had to deal with
> > > the dilemma between "CanNotPlan" and long planing time due to explored
> > > search space.
> > >
> > > Glad to see more people are joining the effort to solve this long
> > > overdue issue, something missing in Calcite's core optimizer framework
> > > "since before Calcite was Calcite" (Jacques's words).
> > >
> > > Jinfeng
> > >
> > >
> > > On Thu, Oct 31, 2019 at 3:38 AM Vladimir Ozerov 
> > wrote:
> > >>
> > >> Hi

Re: Re: [DISCUSS] On-demand traitset request

2019-11-08 Thread XING JIN

Hi Haisheng,

Thanks a lot for sharing this great proposal ~
For short I understand your idea as below:
1. Derive the distributions/collations that children COULD/MIGHT offer
2. Decide the best distributions/collations by first point and computing
logic of operator, say gropuings in Aggregate;

It comes to me that another important part is that children operator should
also provide the corresponding COST for the POSSIBLE distribution/collation.
The COST is not for the final plan, but a hypothesis.

Take below example
SELECT DISTINCT c, b FROM
  ( SELECT R.c c, S.b b FROM R, S
WHERE R.a=S.a and R.b=S.b and R.c=S.c) t;
Suppose R is ordered by (c, b, a), and S is ordered by (b, c, a).
Aggregate
+--- InnerJoin
 |--- TableScan on R
 +--- TableScan on S

InnerJoin should deliver that its possible collations and corresponding
costs at the same time.
- If ordered by (c, b, a) my cost is ...
- If ordered by (b, c, a) my cost is ...
- If ordered by (a, b, c) my cost is ...
By which Aggregate decide the 'best' required collation.
By this way we can better limit the searching space and also target the
relatively optimized (if not best) plan.

Also when you say "I didn't say adding to RelNode, but a new API/interface
for physical operator only.", I'm not so clear;
Currently the physical operators in Calcite like EnumerableHashJoin,
EnumerableMergeJoin, when created, their physical behavior(like real
collations) are determined.
So I belive you intend to add new API at upper layer, but there's no
physical optimizing phase in Calcite at this moment. Where do you want to
add the new API, can you specify ?

Thanks,
Jin

Jinfeng Ni  于2019年11月6日周三 上午1:56写道：

> @Haisheng, @Xiening,
>
> Thanks for pointing that previous email out.  Overall, I agree that
> the physical trait enforcement should be done in the engine, not in
> the rule. For the rule, it should only specify the request, and the
> corresponding transformation, and let the engine to explore the search
> space. It will be great if we can revamp the Volcano optimizer
> framework, to do that way.
>
> In terms of search space, it's always a tradeoff between the space
> searched and the optimality of the plan found. I think it's fine for
> the engine to explore a potential big search space, as long as it has
> effective "bound-and-prune" strategy. In the original Volcano paper,
> there is a way to prune the search space based on the best plan found
> so far, using the parameter "limit".  When an implementable plan is
> found, a "real" cost is obtained, which could be used to prune
> un-necessary search space.  That's actually the advantage of Volcano's
> "top-down" approach. However,  seems to me that Calcite's Volcano did
> not apply that approach effectively, because of the existence of
> AbstractConverter.
>
>
> On Sun, Nov 3, 2019 at 10:12 PM Haisheng Yuan 
> wrote:
> >
> > Hi Jinfeng,
> >
> > I think you might have missed the email about proposed API for physical
> operators I sent out previously in [1].
> >
> > We don't need request all the permutation, which is also impossible in
> practice, the search space is going to explode.
> >
> > In the example in email [1], I already talked about your concen on
> passing down parent request into children may lead to less optimal plan.
> Besically join operator can send 2 collation optimization requests, one is
> to pass request down, the other one is ignore the parent's request.
> >
> > Using AbstractConverter to enforce properties is inapporpriate, which
> handles all the optimization work to physical operator providers, meaning
> there is almost no physical level optimization mechanism in Calcite. SQL
> Server and Greenplum's optimizer, which are Cascades framework based,
> implemented the property enforcement in the core optimizer engine, not
> through AbstractConverter and rules, physical operators just need to
> implement those methods (or similar) I mentioned in email [1]. My goal is
> completely abolishing AbstractConverter.
> >
> > [1]
> http://mail-archives.apache.org/mod_mbox/calcite-dev/201910.mbox/%3cd75b20f4-542a-4a73-897e-66ab426494c1.h.y...@alibaba-inc.com%3e
> >
> > - Haisheng
> >
> > --
> > 发件人：Jinfeng Ni
> > 日 期：2019年11月01日 14:10:30
> > 收件人：
> > 主 题：Re: [DISCUSS] On-demand traitset request
> >
> > Hi Xiening,
> >
> > "Let say if R and S doesn’t have sorting properties at all. In your
> > case, we would end up adding enforcers for LHS and RHS to get
> > collation (a, b, c). Then we would need another enforcer to get
> > collation (b, c). This is a sub optimal plan as we could have use (b,
> > c, a) for join."
> >
> > In such case, for step 2 when MergeJoin request a permutation match of
> > (a, b,c) on both it's input, it is not necessary to end up with
> > collation (a, b, c) only. Since it request "permutation", MJ could ask
> > all possible satisfying collations, which include (b, c, a). In other
> > words, the steps I

Re: [avatica] STRUCT type information missing in type name

2019-11-08 Thread Stamatis Zampetakis

Hi Alessandro,

>From what I understand ColumnMetaData mainly exists to provide the
necessary information required to satisfy the API of ResultSetMetaData.
As far as I can see the ResultSetMetaData does not provide the possibility
to obtain information about nested struct fields (only top-level columns)
so I guess the current implementation of ColumnMetaData is sufficient to
satisfy these requirements.
I can imagine that having additional information about nested fields would
be useful for some clients other than ResultSetMetaData so I would say that
it would be nice to have it.

Best,
Stamatis



On Tue, Nov 5, 2019 at 2:12 PM Alessandro Solimando <
alessandro.solima...@gmail.com> wrote:

> Hello,
> I noticed that type information inside the "name" field into
> "ColumnMetaData" are dropped for the inner components of STRUCT, while they
> are preserved for MULTISET, ARRAY, LIST, MAP.
>
> For example the following "RelRecordType":
>
> > RecordType(
> >   INTEGER f_int,
> >   INTEGER NOT NULL ARRAY f_list_1,
> >   (VARCHAR NOT NULL, VARCHAR NOT NULL) MAP f_map_1,
> >   DOUBLE NOT NULL MULTISET f_set,
> >   RecordType(
> > BIGINT f_tuple_1_0,
> > VARBINARY f_tuple_1_1,
> > TIMESTAMP(0) f_tuple_1_2
> >   ) f_tuple_1
> > ) NOT NULL
>
>
> is "translated" into the following when printing the elements of
> "ResultSetMetaData":
>
> > [f_int INTEGER,
>
> f_list_1 INTEGER ARRAY,
>
> f_map_1 (VARCHAR, VARCHAR) MAP
> > f_set DOUBLE MULTISET,
> > f_tuple_1 STRUCT]
>
>
> while I was expecting something like:
>
> > [f_int INTEGER,
>
> f_list_1 INTEGER ARRAY,
>
> f_map_1 (VARCHAR, VARCHAR) MAP
> > f_set DOUBLE MULTISET,
> > f_tuple_1 (BIGINT, VARBINARY, TIMESTAMP) STRUCT]
>
>
> The difference comes from
> "*org.apache.calcite.avatica.ColumnMetaData", *where you
> have*:*
>
> > public static StructType struct(List columns);
> > public static ArrayType array(AvaticaType componentType, String typeName,
> > Rep rep);
> > public static ScalarType scalar(int type, String typeName, Rep rep);
>
>
> For struct you don't pass "typeName", and the type name is set to "STRUCT"
> inside the constructor.
>
> For uniformity I'd build the type name (inside the aforementioned "struct"
> method) similarly to the other "collections", that is: "(columnTypeName_1,
> ..., columnTypeName_n) STRUCT".
> For this aim, the information inside the parameter "List
> columns" suffices.
>
> What do you think? Is there any reason I am missing for this different
> treatment of collections?
>
> Best regards,
> Alessandro
>

Re: Question about Interpreter and Corelations

Re: Re: Re: Optimizer: All the inputs have relevant nodes, however the cost is still infinite.

Re: Question about Interpreter and Corelations

Re: Question about Interpreter and Corelations

[jira] [Created] (CALCITE-3488) Interpreter does not implement LogicalCorelate.

Re: Re: Re: Optimizer: All the inputs have relevant nodes, however the cost is still infinite.

Re: Re: Optimizer: All the inputs have relevant nodes, however the cost is still infinite.

Re: Optimizer: All the inputs have relevant nodes, however the cost is still infinite.

Re: Optimizer: All the inputs have relevant nodes, however the cost is still infinite.

Re: Re: Question about Interpreter and Corelations

Re: Re: Question about Interpreter and Corelations

Optimizer: All the inputs have relevant nodes, however the cost is still infinite.

[jira] [Created] (CALCITE-3487) Should not hard code RelMetadataQuery class in VolcanoPlanner.isValid()

[jira] [Created] (CALCITE-3486) MysqlSqlDialect unparse ROW keyword does not exist

[jira] [Created] (CALCITE-3485) Validation error in UDF with overloads and dynamic parameter

[jira] [Created] (CALCITE-3484) SqlToRelConverter: ClassCastException when processing a query with ANY and UNNEST

Re: Question about Interpreter and Corelations

Re: [DISCUSS] Proposal to add API to force rules matching specific rels

Re: Re: [DISCUSS] On-demand traitset request

Re: [avatica] STRUCT type information missing in type name

20 matches

Site Navigation

Mail list logo

Footer information