You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Pavel Gubin (JIRA)" <ji...@apache.org> on 2018/06/04 13:54:00 UTC
[jira] [Commented] (CALCITE-2015) Planner generates incompatible
plan
[ https://issues.apache.org/jira/browse/CALCITE-2015?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16500244#comment-16500244 ]
Pavel Gubin commented on CALCITE-2015:
--------------------------------------
I investigated it a bit more. Seems the problem is caused by the fact that planner indefinitely generates more and more possible plans and then exits on give up condition. But at this stage JdbcToEnumerable conversion rules was not applied and the plan is essentially incompatible.
Current workaround for me is to exclude {{JoinCommuteRule}} and {{JoinPushThroughJoinRule}} to brake cyclicity. In this case planner applies all rules and exits successfully.
But in the end need to understand how to exit indefinite planning correctly.
> Planner generates incompatible plan
> -----------------------------------
>
> Key: CALCITE-2015
> URL: https://issues.apache.org/jira/browse/CALCITE-2015
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.14.0
> Reporter: Pavel Gubin
> Assignee: Julian Hyde
> Priority: Critical
> Attachments: Main.java
>
>
> Rel tree was build using RelBuilder on foodmart dataset:
> {code:java}
> RelNode rel = b.scan("foodmart", "sales_fact_1998")
> .scan("foodmart", "customer")
> .join(JoinRelType.INNER, "customer_id")
> .scan("foodmart", "store")
> .join(JoinRelType.INNER, "store_id")
> .filter(b.equals(b.field("coffee_bar"), b.literal(true)))
> .project(
> b.alias(b.field("lname"), "lastName"),
> b.alias(b.field("store_name"), "storeName"),
> b.alias(b.field("coffee_bar"), "coffeeBar"),
> b.alias(b.field("unit_sales"), "unitSales")
> )
> .aggregate(b.groupKey(b.field("lastName"), b.field("storeName"),
> b.field("coffeeBar")), b.sum(false, "unitSales", b.field("unitSales")))
> .build();
> {code}
> This tree is optimised to the following physical plan:
> {noformat}
> 22:03:26.388 [main] DEBUG org.apache.calcite.prepare.Prepare - Plan after physical tweaks: EnumerableAggregate(group=[{1, 6, 7}], unitSales=[$SUM0($4)]): rowcount = 337.5, cumulative cost = {4466.6875 rows, 839.0 cpu, 0.0 io}, id = 11032
> JdbcJoin(condition=[=($2, $0)], joinType=[inner]): rowcount = 3375.0, cumulative cost = {4087.0 rows, 839.0 cpu, 0.0 io}, id = 11030
> JdbcProject(customer_id=[$0], lname=[$2]): rowcount = 100.0, cumulative cost = {180.0 rows, 261.0 cpu, 0.0 io}, id = 11018
> JdbcTableScan(table=[[foodmart, customer]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 1
> JdbcJoin(condition=[=($1, $3)], joinType=[inner]): rowcount = 225.0, cumulative cost = {532.0 rows, 578.0 cpu, 0.0 io}, id = 11028
> JdbcProject(customer_id=[$2], store_id=[$4], unit_sales=[$7]): rowcount = 100.0, cumulative cost = {180.0 rows, 341.0 cpu, 0.0 io}, id = 11021
> JdbcTableScan(table=[[foodmart, sales_fact_1998]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 0
> JdbcProject(store_id=[$0], store_name=[$3], coffee_bar=[$19]): rowcount = 15.0, cumulative cost = {127.0 rows, 237.0 cpu, 0.0 io}, id = 11026
> JdbcFilter(condition=[=($19, true)]): rowcount = 15.0, cumulative cost = {115.0 rows, 201.0 cpu, 0.0 io}, id = 11024
> JdbcTableScan(table=[[foodmart, store]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 3
> {noformat}
> Which fails on execution:
> {noformat}
> Exception in thread "main" java.lang.RuntimeException: java.sql.SQLException: Error while preparing statement [null]
> at org.apache.calcite.jdbc.CalciteConnectionImpl$1.prepare(CalciteConnectionImpl.java:172)
> at Main.main(Main.java:68)
> Caused by: java.sql.SQLException: Error while preparing statement [null]
> at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
> at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
> at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:210)
> at org.apache.calcite.jdbc.CalciteConnectionImpl.access$100(CalciteConnectionImpl.java:89)
> at org.apache.calcite.jdbc.CalciteConnectionImpl$1.prepare(CalciteConnectionImpl.java:168)
> ... 1 more
> Caused by: java.lang.ClassCastException: org.apache.calcite.adapter.jdbc.JdbcRules$JdbcJoin cannot be cast to org.apache.calcite.adapter.enumerable.EnumerableRel
> at org.apache.calcite.adapter.enumerable.EnumerableAggregate.implement(EnumerableAggregate.java:105)
> at org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:108)
> at org.apache.calcite.adapter.enumerable.EnumerableInterpretable.toBindable(EnumerableInterpretable.java:92)
> at org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.implement(CalcitePrepareImpl.java:1262)
> at org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepare_(CalcitePrepareImpl.java:1163)
> at org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepareRel(CalcitePrepareImpl.java:1113)
> at org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:807)
> at org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:640)
> at org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:610)
> at org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:221)
> at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:203)
> ... 3 more
> {noformat}
> It turns out that optimiser selects plan that cannot be executed because {{EnumerableAggregate}} requires child to be {{EnumerableRel}}. Looking into the optimiser output this plan actually the best one in terms of cost and the plan where aggregation is done in JDBC is not as good. That's probably the second problem because I'd like aggregate to be run rather in the DB.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)