You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by "Pavel Gubin (JIRA)" <ji...@apache.org> on 2017/10/18 20:16:01 UTC
[jira] [Created] (CALCITE-2015) Planner generates incompatible plan
Pavel Gubin created CALCITE-2015:
------------------------------------
Summary: 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
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
(v6.4.14#64029)