[
https://issues.apache.org/jira/browse/CALCITE-2015?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16216578#comment-16216578
]
Pavel Gubin commented on CALCITE-2015:
--------------------------------------
I am able to reproduce this bug using the following SQL:
{noformat}
select c."lname" as lastName, s."store_name" as storeName, s."coffee_bar" as
coffeeBar, sum(f."unit_sales") as unitSales
from "store" as s join "sales_fact_1998" as f using("store_id")
join "customer" as c using("customer_id")
where s."coffee_bar"=true
group by c."lname", s."store_name", s."coffee_bar"
{noformat}
{noformat}
./sqlline
sqlline version 1.3.0
sqlline> !connect
jdbc:calcite:schemaType=JDBC;schema.jdbcUser=FOODMART;schema.jdbcPassword=FOODMART;schema.jdbcUrl=jdbc:hsqldb:res:foodmart;schema.jdbcSchema=foodmart
0: jdbc:calcite:schemaType=JDBC> select c."lname" as lastName, s."store_name"
as storeName, s."coffee_bar" as coffeeBar, sum(f."unit_sales") as unitSales
. . . . . . . . . . . . . . . .> from "store" as s join "sales_fact_1998" as f
using("store_id")
. . . . . . . . . . . . . . . .> join "customer" as c using("customer_id")
. . . . . . . . . . . . . . . .> where s."coffee_bar"=true
. . . . . . . . . . . . . . . .> group by c."lname", s."store_name",
s."coffee_bar";
Error: Error while executing SQL "select c."lname" as lastName, s."store_name"
as storeName, s."coffee_bar" as coffeeBar, sum(f."unit_sales") as unitSales
from "store" as s join "sales_fact_1998" as f using("store_id")
join "customer" as c using("customer_id")
where s."coffee_bar"=true
group by c."lname", s."store_name", s."coffee_bar"":
org.apache.calcite.adapter.jdbc.JdbcRules$JdbcJoin cannot be cast to
org.apache.calcite.adapter.enumerable.EnumerableRel (state=,code=0)
{noformat}
> 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
> 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
(v6.4.14#64029)
