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)
