Conditions that cause EnumerableCalc to be inserted into plan

[Ted Wilmes <te...@wellaware.us>]

Hello,

I am currently developing a Calcite adapter for the Titan graph database.  Things have been going pretty smoothly and I'm very impressed with what you guys have created.  I've ran into some planner behavior that I have a question about.  Given the following two queries (that are identical other than the query join order) I'm confused as to why the EnumerableCalc node is being introduced into the first query's plan.  The join orders are identical plan-wise, as I would expect with the costing information that I am providing.  This is currently causing some issues for me because I've implemented a custom enumerable join and my guess is it is not being triggered on the top level join because EnumerableCalc is relying on the EnumerableDefaults join implementation.  Any thoughts would be much appreciated.  Thank you.


Query 1:

select * from asset

    inner join company on company.company_id = asset.company_id

    inner join measurement on measurement.asset_id = asset.asset_id


Query 1 Plan:

EnumerableJoin(condition=[=($0, $8)], joinType=[inner])

  EnumerableCalc(expr#0..6=[{inputs}], ASSET_ID=[$t2], COMPANY_ID=[$t3], SITE_ID=[$t4], WASABI_NAME=[$t5], ASSET_TYPE=[$t6], COMPANY_ID0=[$t0], NAME=[$t1])

    EnumerableJoin(condition=[=($0, $3)], joinType=[inner])

      VertexToEnumerableConverter

        VertexTableScan(table=[[nori, COMPANY]])

      VertexToEnumerableConverter

        VertexTableScan(table=[[nori, ASSET]])

  VertexToEnumerableConverter

    VertexTableScan(table=[[nori, MEASUREMENT]])


Query 2:

select * from company

    inner join asset on asset.company_id = company.company_id

    inner join measurement on measurement.asset_id = asset.asset_id


Query 2 Plan:

EnumerableJoin(condition=[=($2, $8)], joinType=[inner])

  EnumerableJoin(condition=[=($0, $3)], joinType=[inner])

    VertexToEnumerableConverter

      VertexTableScan(table=[[nori, COMPANY]])

    VertexToEnumerableConverter

      VertexTableScan(table=[[nori, ASSET]])

  VertexToEnumerableConverter

    VertexTableScan(table=[[nori, MEASUREMENT]])



Ted Wilmes

Re: Conditions that cause EnumerableCalc to be inserted into plan

[Julian Hyde <ju...@hydromatic.net>]

The EnumerableCalc is just re-ordering its input fields. I believe it
is created from a Project, and that Project was created by
JoinCommuteRule (formerly called SwapJoinRule), to make sure that the
columns appear in the same order before and after the rule is applied.

JoinCommuteRule does serve an important purpose -- because joins treat
their inputs differently, and may be much more efficient if the
smaller input is on a particular side. The project doesn't seem to be
doing much, but is necessary.

Possible remedies. You could try running a rule that pulls projects
through joins (thus they'll all end up above the top-most join). Or
you could write a variant of your rule that can deal with an
intervening project.

Your problem is related to
https://issues.apache.org/jira/browse/CALCITE-92 (which dealt with a
simpler case, and is fixed) and
https://issues.apache.org/jira/browse/CALCITE-62 (which would solve
the problem, but would also be a major API change, so we keep on
deferring it).

Can you please log a jira case to track your issue?

Julian


On Wed, Mar 25, 2015 at 8:00 AM, Ted Wilmes <te...@wellaware.us> wrote:
> Hello,
>
> I am currently developing a Calcite adapter for the Titan graph database.  Things have been going pretty smoothly and I'm very impressed with what you guys have created.  I've ran into some planner behavior that I have a question about.  Given the following two queries (that are identical other than the query join order) I'm confused as to why the EnumerableCalc node is being introduced into the first query's plan.  The join orders are identical plan-wise, as I would expect with the costing information that I am providing.  This is currently causing some issues for me because I've implemented a custom enumerable join and my guess is it is not being triggered on the top level join because EnumerableCalc is relying on the EnumerableDefaults join implementation.  Any thoughts would be much appreciated.  Thank you.
>
>
> Query 1:
>
> select * from asset
>
>     inner join company on company.company_id = asset.company_id
>
>     inner join measurement on measurement.asset_id = asset.asset_id
>
>
> Query 1 Plan:
>
> EnumerableJoin(condition=[=($0, $8)], joinType=[inner])
>
>   EnumerableCalc(expr#0..6=[{inputs}], ASSET_ID=[$t2], COMPANY_ID=[$t3], SITE_ID=[$t4], WASABI_NAME=[$t5], ASSET_TYPE=[$t6], COMPANY_ID0=[$t0], NAME=[$t1])
>
>     EnumerableJoin(condition=[=($0, $3)], joinType=[inner])
>
>       VertexToEnumerableConverter
>
>         VertexTableScan(table=[[nori, COMPANY]])
>
>       VertexToEnumerableConverter
>
>         VertexTableScan(table=[[nori, ASSET]])
>
>   VertexToEnumerableConverter
>
>     VertexTableScan(table=[[nori, MEASUREMENT]])
>
>
> Query 2:
>
> select * from company
>
>     inner join asset on asset.company_id = company.company_id
>
>     inner join measurement on measurement.asset_id = asset.asset_id
>
>
> Query 2 Plan:
>
> EnumerableJoin(condition=[=($2, $8)], joinType=[inner])
>
>   EnumerableJoin(condition=[=($0, $3)], joinType=[inner])
>
>     VertexToEnumerableConverter
>
>       VertexTableScan(table=[[nori, COMPANY]])
>
>     VertexToEnumerableConverter
>
>       VertexTableScan(table=[[nori, ASSET]])
>
>   VertexToEnumerableConverter
>
>     VertexTableScan(table=[[nori, MEASUREMENT]])
>
>
>
> Ted Wilmes
>