0x822a5b87
commented
2 months ago 我这边没办法复现你的异常，但是看这段报错：
Caused by: org.apache.calcite.plan.RelOptPlanner$CannotPlanException: There are not enough rules to produce a node with desired properties: convention=ENUMERABLE, sort=[].这个应该是发生在优化阶段，将一个RelNode转化为一个等价的RelNode以提升查询性能。
另外，在异常的最后一部分：
at org.apache.calcite.plan.volcano.RelSubset$CheapestPlanReplacer.visit(RelSubset.java:742)
at org.apache.calcite.plan.volcano.RelSubset.buildCheapestPlan(RelSubset.java:365)
at org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp(VolcanoPlanner.java:520)
at org.apache.calcite.tools.Programs.lambda$standard$3(Programs.java:271)这里的异常应该是在 findBestExp 这里，你可以试试单步调试一下这个位置，看看具体引起异常的原因是什么。
我这边用的最新版本的 1.37.0 下，代码应该是在这个 RelSubset#CheapestPlanReplacer#visit() 方法抛出的异常：
public RelNode visit(
RelNode p,
int ordinal,
@Nullable RelNode parent) {
// 优化阶段
planner.dump(pw);
pw.flush();
final String dump = sw.toString();
RuntimeException e =
new RelOptPlanner.CannotPlanException(dump);
LOGGER.trace("Caught exception in class={}, method=visit", getClass().getName(), e);
throw e;
}
AKang242411
你好，通过修改您连接mysql的例子，如下： { "version": "1.0", "defaultSchema": "MYSQL", "schemas": [ { "name": "MYSQL", "type": "custom", "factory": "com.xxx.calcite.mysql.MyMysqlSchemaFactory", "operand": { "url": "jdbc:mysql://localhost:3306/iam_service?serverTimezone=UTC", "user": "root", "pass": "***" }, "functions": [ { "name": "test", "className": "com.xxx.calcite.function.MyFunction", "methodName": "test" } ], "tables": [ { "name": "v_num", "type": "view", "sql": "select from \"iam_sys_user\"", "path": [ "MYSQL" ], "modifiable": false } ] } ] } @Test void validateView() throws SQLException, SqlParseException { String sql = "select \"user_name\" from MYSQL.\"v_num\""; executeQuery(sql); } 执行上面测试，不成功 报错信息： java.sql.SQLException: Error while executing SQL "select "user_name" from MYSQL."v_num"": There are not enough rules to produce a node with desired properties: convention=ENUMERABLE, sort=[]. Missing conversion is LogicalTableScan[convention: NONE -> ENUMERABLE] There is 1 empty subset: rel#24:RelSubset#0.ENUMERABLE.[], the relevant part of the original plan is as follows 8:LogicalTableScan(table=[[MYSQL, iam_sys_user]])
Root: rel#22:RelSubset#1.ENUMERABLE.[] Original rel: LogicalProject(user_name=[$2]): rowcount = 100.0, cumulative cost = {200.0 rows, 201.0 cpu, 0.0 io}, id = 17 LogicalTableScan(table=[[MYSQL, iam_sys_user]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 8
Sets: Set#0, type: RecordType(BIGINT user_id, BIGINT dept_id, VARCHAR user_name, VARCHAR nick_name, VARCHAR user_type, VARCHAR email, VARCHAR id_card, VARCHAR phonenumber, CHAR(1) sex, VARCHAR avatar, VARCHAR password, CHAR(1) status, CHAR(1) del_flag, VARCHAR login_ip, TIMESTAMP(0) login_date, VARCHAR create_by, TIMESTAMP(0) create_time, VARCHAR update_by, TIMESTAMP(0) update_time, VARCHAR remark, VARCHAR signature, TIMESTAMP(0) password_reset_time) rel#18:RelSubset#0.NONE.[], best=null rel#8:LogicalTableScan.NONE.[](table=[MYSQL, iam_sys_user]), rowcount=100.0, cumulative cost={inf} rel#24:RelSubset#0.ENUMERABLE.[], best=null Set#1, type: RecordType(VARCHAR user_name) rel#20:RelSubset#1.NONE.[], best=null rel#19:LogicalProject.NONE., rowcount=100.0, cumulative cost={inf} rel#22:RelSubset#1.ENUMERABLE.[], best=null rel#23:AbstractConverter.ENUMERABLE., rowcount=100.0, cumulative cost={inf} rel#25:EnumerableProject.ENUMERABLE., rowcount=100.0, cumulative cost={inf}
Graphviz: digraph G { root [style=filled,label="Root"]; subgraph cluster0{ label="Set 0 RecordType(BIGINT user_id, BIGINT dept_id, VARCHAR user_name, VARCHAR nick_name, VARCHAR user_type, VARCHAR email, VARCHAR id_card, VARCHAR phonenumber, CHAR(1) sex, VARCHAR avatar, VARCHAR password, CHAR(1) status, CHAR(1) del_flag, VARCHAR login_ip, TIMESTAMP(0) login_date, VARCHAR create_by, TIMESTAMP(0) create_time, VARCHAR update_by, TIMESTAMP(0) update_time, VARCHAR remark, VARCHAR signature, TIMESTAMP(0) password_reset_time)"; rel8 [label="rel#8:LogicalTableScan\ntable=[MYSQL, iam_sys_user]\nrows=100.0, cost={inf}",shape=box] subset18 [label="rel#18:RelSubset#0.NONE.[]"] subset24 [label="rel#24:RelSubset#0.ENUMERABLE.[]",color=red] } subgraph cluster1{ label="Set 1 RecordType(VARCHAR user_name)"; rel19 [label="rel#19:LogicalProject\ninput=RelSubset#18,exprs=[$2]\nrows=100.0, cost={inf}",shape=box] rel23 [label="rel#23:AbstractConverter\ninput=RelSubset#20,convention=ENUMERABLE,sort=[]\nrows=100.0, cost={inf}",shape=box] rel25 [label="rel#25:EnumerableProject\ninput=RelSubset#24,exprs=[$2]\nrows=100.0, cost={inf}",shape=box] subset20 [label="rel#20:RelSubset#1.NONE.[]"] subset22 [label="rel#22:RelSubset#1.ENUMERABLE.[]"] } root -> subset22; subset18 -> rel8; subset20 -> rel19; rel19 -> subset18; subset22 -> rel23; rel23 -> subset20; subset22 -> rel25; rel25 -> subset24; }
Caused by: org.apache.calcite.plan.RelOptPlanner$CannotPlanException: There are not enough rules to produce a node with desired properties: convention=ENUMERABLE, sort=[]. Missing conversion is LogicalTableScan[convention: NONE -> ENUMERABLE] There is 1 empty subset: rel#24:RelSubset#0.ENUMERABLE.[], the relevant part of the original plan is as follows 8:LogicalTableScan(table=[[MYSQL, iam_sys_user]])
Root: rel#22:RelSubset#1.ENUMERABLE.[] Original rel: LogicalProject(user_name=[$2]): rowcount = 100.0, cumulative cost = {200.0 rows, 201.0 cpu, 0.0 io}, id = 17 LogicalTableScan(table=[[MYSQL, iam_sys_user]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 8
Sets: Set#0, type: RecordType(BIGINT user_id, BIGINT dept_id, VARCHAR user_name, VARCHAR nick_name, VARCHAR user_type, VARCHAR email, VARCHAR id_card, VARCHAR phonenumber, CHAR(1) sex, VARCHAR avatar, VARCHAR password, CHAR(1) status, CHAR(1) del_flag, VARCHAR login_ip, TIMESTAMP(0) login_date, VARCHAR create_by, TIMESTAMP(0) create_time, VARCHAR update_by, TIMESTAMP(0) update_time, VARCHAR remark, VARCHAR signature, TIMESTAMP(0) password_reset_time) rel#18:RelSubset#0.NONE.[], best=null rel#8:LogicalTableScan.NONE.[](table=[MYSQL, iam_sys_user]), rowcount=100.0, cumulative cost={inf} rel#24:RelSubset#0.ENUMERABLE.[], best=null Set#1, type: RecordType(VARCHAR user_name) rel#20:RelSubset#1.NONE.[], best=null rel#19:LogicalProject.NONE., rowcount=100.0, cumulative cost={inf} rel#22:RelSubset#1.ENUMERABLE.[], best=null rel#23:AbstractConverter.ENUMERABLE., rowcount=100.0, cumulative cost={inf} rel#25:EnumerableProject.ENUMERABLE., rowcount=100.0, cumulative cost={inf}
Graphviz: digraph G { root [style=filled,label="Root"]; subgraph cluster0{ label="Set 0 RecordType(BIGINT user_id, BIGINT dept_id, VARCHAR user_name, VARCHAR nick_name, VARCHAR user_type, VARCHAR email, VARCHAR id_card, VARCHAR phonenumber, CHAR(1) sex, VARCHAR avatar, VARCHAR password, CHAR(1) status, CHAR(1) del_flag, VARCHAR login_ip, TIMESTAMP(0) login_date, VARCHAR create_by, TIMESTAMP(0) create_time, VARCHAR update_by, TIMESTAMP(0) update_time, VARCHAR remark, VARCHAR signature, TIMESTAMP(0) password_reset_time)"; rel8 [label="rel#8:LogicalTableScan\ntable=[MYSQL, iam_sys_user]\nrows=100.0, cost={inf}",shape=box] subset18 [label="rel#18:RelSubset#0.NONE.[]"] subset24 [label="rel#24:RelSubset#0.ENUMERABLE.[]",color=red] } subgraph cluster1{ label="Set 1 RecordType(VARCHAR username)"; rel19 [label="rel#19:LogicalProject\ninput=RelSubset#18,exprs=[$2]\nrows=100.0, cost={inf}",shape=box] rel23 [label="rel#23:AbstractConverter\ninput=RelSubset#20,convention=ENUMERABLE,sort=[]\nrows=100.0, cost={inf}",shape=box] rel25 [label="rel#25:EnumerableProject\ninput=RelSubset#24,exprs=[$2]\nrows=100.0, cost={inf}",shape=box] subset20 [label="rel#20:RelSubset#1.NONE.[]"] subset22 [label="rel#22:RelSubset#1.ENUMERABLE.[]"] } root -> subset22; subset18 -> rel8; subset20 -> rel19; rel19 -> subset18; subset22 -> rel23; rel23 -> subset20; subset22 -> rel25; rel25 -> subset24; } at org.apache.calcite.plan.volcano.RelSubset$CheapestPlanReplacer.visit(RelSubset.java:742) at org.apache.calcite.plan.volcano.RelSubset.buildCheapestPlan(RelSubset.java:365) at org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp(VolcanoPlanner.java:520) at org.apache.calcite.tools.Programs.lambda$standard$3(Programs.java:271) at org.apache.calcite.tools.Programs$SequenceProgram.run(Programs.java:331) at org.apache.calcite.prepare.Prepare.optimize(Prepare.java:166) at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:297) at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:208) at org.apache.calcite.prepare.CalcitePrepareImpl.prepare2(CalcitePrepareImpl.java:642) at org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:508) at org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:478) at org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:231) at org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:556) at org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:675) at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:156) ... 70 more