[CALCITE-2015] Planner generates incompatible plan

• Type: Bug
• Status: Open
• Priority: Critical
• Resolution: Unresolved
• Affects Version/s: 1.14.0
• Component/s: core
• Assignee: Unassigned
• Reporter: Pavel Gubin
• Created: 18/Oct/17 20:15
• Updated: 28/Feb/24 06:03

Description

Rel tree was build using RelBuilder on foodmart dataset:

        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();

This tree is optimised to the following physical plan:

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

Which fails on execution:

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

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.

Issue Links

• duplicates: CALCITE-1382 ClassCastException in JDBC Adapter