[
https://issues.apache.org/jira/browse/CALCITE-2015?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16372857#comment-16372857
]
zhen wang commented on CALCITE-2015:
------------------------------------
interesting. with some "hint" planner knows not to strip the jdbc to enumerable
relNode, and thus actually works.
CalciteAssert.that()
.with(CalciteAssert.Config.JDBC_FOODMART)
.query("select lastName, storeName from("
+ "select c.\"lname\" as lastName, s.\"store_name\" as storeName,
s.\"coffee_bar\" as coffeeBar, sum(f"
+ ".\"unit_sales\") as unitSales\n"
+ "from "
+ "\"foodmart\".\"store\" as s "
+ "join \"foodmart\".\"sales_fact_1998\" as f "
+ "using(\"store_id\")\n"
+ "join \"foodmart\".\"customer\" as c "
+ "using(\"customer_id\")\n"
+ "where s.\"coffee_bar\"=true\n"
+ "group by c.\"lname\", s.\"store_name\", s.\"coffee_bar\")")
> 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)
