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

[Vladimir Sitnikov]

Xiening,

The bug is "obvious" when you open a Graphviz diagram.
Just wondering: have you tried?

The issue is EnumerableTableFunctionScan consumes Subset#0.NONE.[0] as an
input, which is never implementable.

The fix is trivial: EnumerableTableFunctionScanRule must convert inputs to
EnumerableConvention
(e.g. see how EnumerableProjectRule converts input to enumerable)

So getInputs() should be replaced with convertList(rel.getInputs(),
EnumerableConvention.INSTANCE)
Can you please try that?

Vladimir

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

[Rui Wang]

I found EnumerableTableFunctionScanRule and EnumerableTableFunctionScan are
not tested in unit tests in core (checked by code search in core/test). The
right approach seems to me now is add more tests and then gradually see if
I can narrow down the problem.

Are there recommended places that I should add more tests?


-Rui

On Fri, Nov 8, 2019 at 8:38 PM Rui Wang  wrote:

> 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: Re: Re: Optimizer: All the inputs have relevant nodes, however the cost is still infinite.

[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: Re: Re: Optimizer: All the inputs have relevant nodes, however the cost is still infinite.

[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
>>