[ https://issues.apache.org/jira/browse/CALCITE-2015?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16374996#comment-16374996 ]
Julian Hyde commented on CALCITE-2015: -------------------------------------- Looks very similar to CALCITE-1382. Marking duplicate unless someone says otherwise. > 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)