implicit vs. explicit inner join

["Coiffe, Benjamin M" <be...@jpmorgan.com.INVALID>]

Hi,

I am building an adapter to a datasource that is not currently supported out of the box in calcite. I use it to integrate with Mondrian.
Mondrian is generating mostly implicit joins which don't have the most efficient plan in my implementation of calcite.

Take a look below:

EXPLICIT JOIN: explain plan for select "t1"."nodeIdL4" as "c0", count(distinct "t2"."requestId") as "m0" from "kdb_raw"."clRefHryClient" as "clRefHryClient" join "kdb_raw"."rfq" as "rfq" on "rfq"."spn" = "clRefHryClient"."spn" where "clRefHryClient"."nodeIdL4" = 908705 group by "clRefHryClient"."nodeIdL4";

[cid:image001.png@01CFF745.85BE5080]

IMPLICIT JOIN: explain plan for select "clRefHryClient"."nodeIdL4" as "c0", count(distinct "rfq"."requestId") as "m0" from "kdb_raw"."clRefHryClient" as "clRefHryClient", "kdb_raw"."rfq" as "rfq" where "rfq"."spn" = "clRefHryClient"."spn" and"clRefHryClient"."nodeIdL4" = 908705 group by "clRefHryClient"."nodeIdL4"

[cid:image002.png@01CFF745.85BE5080]

The filter on the IMPLICIT JOIN is applied on top of the JoinRel.
The filter on the EXPLICIT JOIN has been pushed down the JoinRel and is obviously more efficient as the join is applied to fewer rows.

The execution planner is ranking the implicit inner join like a cross join with 2 filters. I have enabled the FINE logging to see if this decision is cost related but this is pretty hard to tell for the big amount of logs that is generated.

I don't think Mondrian can be configured to use explicit joins rather than implicit joins and I have not managed to have the same plan generated for both queries...

Any pointers?

Thanks,

ben



This email is confidential and subject to important disclaimers and conditions including on offers for the purchase or sale of securities, accuracy and completeness of information, viruses, confidentiality, legal privilege, and legal entity disclaimers, available at http://www.jpmorgan.com/pages/disclosures/email.  

Re: implicit vs. explicit inner join

[Vladimir Sitnikov <si...@gmail.com>]

Hi,

I am sorry I do not see images.

I've just logged https://issues.apache.org/jira/browse/CALCITE-455
(Non-ansi join should not be processed as a filter on top of "on (true)"
join).
Can you please check if that looks like your case?

If it does not, can you please share the plans in text form and/or as a
ticket in https://issues.apache.org/jira/browse/CALCITE/?
​
-
Vladimir