[jira] [Commented] (CALCITE-2015) Planner generates incompatible plan

2018-06-04 Thread Pavel Gubin (JIRA) 


[ 
https://issues.apache.org/jira/browse/CALCITE-2015?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16500244#comment-16500244
 ] 

Pavel Gubin commented on CALCITE-2015:
--

I investigated it a bit more. Seems the problem is caused by the fact that 
planner indefinitely generates more and more possible plans and then exits on 
give up condition. But at this stage JdbcToEnumerable conversion rules was not 
applied and the plan is essentially incompatible.

Current workaround for me is to exclude {{JoinCommuteRule}} and 
{{JoinPushThroughJoinRule}} to brake cyclicity. In this case planner applies 
all rules and exits successfully.

But in the end need to understand how to exit indefinite planning correctly.

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

[jira] [Commented] (CALCITE-2015) Planner generates incompatible plan

2018-02-23 Thread Julian Hyde (JIRA) 

[ 
https://issues.apache.org/jira/browse/CALCITE-2015?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=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 
>

[jira] [Commented] (CALCITE-2015) Planner generates incompatible plan

2018-02-23 Thread zhen wang (JIRA) 

[ 
https://issues.apache.org/jira/browse/CALCITE-2015?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16374080#comment-16374080
 ] 

zhen wang commented on CALCITE-2015:


there are just too many rules involved in the process, like 400 and takes a 
minute or more to execute (debug mode). need to come up with a simplified 
scenario. 



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

[jira] [Commented] (CALCITE-2015) Planner generates incompatible plan

2018-02-22 Thread zhen wang (JIRA) 

[ 
https://issues.apache.org/jira/browse/CALCITE-2015?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=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 
>

[jira] [Commented] (CALCITE-2015) Planner generates incompatible plan

2017-10-24 Thread Pavel Gubin (JIRA) 

[ 
https://issues.apache.org/jira/browse/CALCITE-2015?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16216579#comment-16216579
 ] 

Pavel Gubin commented on CALCITE-2015:
--

Increasing priority as it's quite generic.

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

[jira] [Commented] (CALCITE-2015) Planner generates incompatible plan

2017-10-24 Thread Pavel Gubin (JIRA) 

[ 
https://issues.apache.org/jira/browse/CALCITE-2015?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=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 
>