[ 
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)

Reply via email to