You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2022/10/08 09:42:25 UTC
[GitHub] [shardingsphere] strongduanmu opened a new issue, #21388: RelOptPlanner$CannotPlanException occurs when execute select aggregation statement with ADVANCED sql federation
strongduanmu opened a new issue, #21388:
URL: https://github.com/apache/shardingsphere/issues/21388
## Bug Report
### Which version of ShardingSphere did you use?
https://github.com/apache/shardingsphere/commit/e2f2e4491006d7e57615b1926913921e9414b9df
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy
### Expected behavior
Execute `SELECT MAX(p.price) AS max_price, MIN(p.price) AS min_price, SUM(p.price) AS sum_price, AVG(p.price) AS avg_price, COUNT(1) AS count FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id INNER JOIN t_product p ON i.product_id = p.product_id GROUP BY o.order_id HAVING SUM(p.price) > 10000;` successfully.
### Actual behavior
```sql
[ERROR] 2022-10-08 17:23:24.832 [Connection-1-ThreadExecutor] o.a.s.p.f.c.CommandExecutorTask - Exception occur:
org.apache.calcite.plan.RelOptPlanner$CannotPlanException: There are not enough rules to produce a node with desired properties: convention=ENUMERABLE, sort=[].
Missing conversion is LogicalAggregate[convention: NONE -> ENUMERABLE]
There is 1 empty subset: rel#999:RelSubset#10.ENUMERABLE.[], the relevant part of the original plan is as follows
972:LogicalAggregate(group=[{0}], sum_price=[SUM($1)], avg_price=[AVG($1)], count=[COUNT()])
970:LogicalCalc(subset=[rel#971:RelSubset#9.NONE.[]], expr#0..3=[{inputs}], order_id=[$t0], price=[$t3])
968:LogicalJoin(subset=[rel#969:RelSubset#8.NONE.[]], condition=[=($1, $2)], joinType=[inner])
963:LogicalCalc(subset=[rel#964:RelSubset#5.NONE.[]], expr#0..2=[{inputs}], order_id=[$t0], product_id=[$t2])
961:LogicalJoin(subset=[rel#962:RelSubset#4.NONE.[]], condition=[=($0, $1)], joinType=[inner])
956:LogicalCalc(subset=[rel#957:RelSubset#1.NONE.[]], expr#0..5=[{inputs}], order_id=[$t0])
892:LogicalTableScan(subset=[rel#955:RelSubset#0.NONE.[]], table=[[public, t_order]])
959:LogicalCalc(subset=[rel#960:RelSubset#3.NONE.[]], expr#0..5=[{inputs}], order_id=[$t1], product_id=[$t3])
894:LogicalTableScan(subset=[rel#958:RelSubset#2.NONE.[]], table=[[public, t_order_item]])
966:LogicalCalc(subset=[rel#967:RelSubset#7.NONE.[]], expr#0..5=[{inputs}], product_id=[$t0], price=[$t3])
898:LogicalTableScan(subset=[rel#965:RelSubset#6.NONE.[]], table=[[public, t_product]])
Root: rel#976:RelSubset#11.ENUMERABLE.[]
Original rel:
LogicalCalc(subset=[rel#976:RelSubset#11.ENUMERABLE.[]], expr#0..3=[{inputs}], sum_price=[$t1], avg_price=[$t2], count=[$t3]): rowcount = 2250.0, cumulative cost = {2250.0 rows, 15750.0 cpu, 0.0 io}, id = 974
LogicalAggregate(subset=[rel#973:RelSubset#10.NONE.[]], group=[{0}], sum_price=[SUM($1)], avg_price=[AVG($1)], count=[COUNT()]): rowcount = 2250.0, cumulative cost = {3121.8751072883606 rows, 0.0 cpu, 0.0 io}, id = 972
LogicalCalc(subset=[rel#971:RelSubset#9.NONE.[]], expr#0..3=[{inputs}], order_id=[$t0], price=[$t3]): rowcount = 22500.0, cumulative cost = {22500.0 rows, 135000.0 cpu, 0.0 io}, id = 970
LogicalJoin(subset=[rel#969:RelSubset#8.NONE.[]], condition=[=($1, $2)], joinType=[inner]): rowcount = 22500.0, cumulative cost = {22500.0 rows, 0.0 cpu, 0.0 io}, id = 968
LogicalCalc(subset=[rel#964:RelSubset#5.NONE.[]], expr#0..2=[{inputs}], order_id=[$t0], product_id=[$t2]): rowcount = 1500.0, cumulative cost = {1500.0 rows, 7500.0 cpu, 0.0 io}, id = 963
LogicalJoin(subset=[rel#962:RelSubset#4.NONE.[]], condition=[=($0, $1)], joinType=[inner]): rowcount = 1500.0, cumulative cost = {1500.0 rows, 0.0 cpu, 0.0 io}, id = 961
LogicalCalc(subset=[rel#957:RelSubset#1.NONE.[]], expr#0..5=[{inputs}], order_id=[$t0]): rowcount = 100.0, cumulative cost = {100.0 rows, 700.0 cpu, 0.0 io}, id = 956
LogicalTableScan(subset=[rel#955:RelSubset#0.NONE.[]], table=[[public, t_order]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 892
LogicalCalc(subset=[rel#960:RelSubset#3.NONE.[]], expr#0..5=[{inputs}], order_id=[$t1], product_id=[$t3]): rowcount = 100.0, cumulative cost = {100.0 rows, 800.0 cpu, 0.0 io}, id = 959
LogicalTableScan(subset=[rel#958:RelSubset#2.NONE.[]], table=[[public, t_order_item]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 894
LogicalCalc(subset=[rel#967:RelSubset#7.NONE.[]], expr#0..5=[{inputs}], product_id=[$t0], price=[$t3]): rowcount = 100.0, cumulative cost = {100.0 rows, 800.0 cpu, 0.0 io}, id = 966
LogicalTableScan(subset=[rel#965:RelSubset#6.NONE.[]], table=[[public, t_product]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 898
Sets:
Set#0, type: RecordType(JavaType(class java.lang.Integer) order_id, JavaType(class java.lang.Integer) user_id, JavaType(class java.lang.String) status, JavaType(class java.lang.Integer) merchant_id, JavaType(class java.lang.String) remark, JavaType(class java.sql.Date) creation_date)
rel#955:RelSubset#0.NONE.[], best=null
rel#892:LogicalTableScan.NONE.[](table=[public, t_order]), rowcount=100.0, cumulative cost={inf}
rel#979:RelSubset#0.ENUMERABLE.[], best=rel#978
rel#978:EnumerableTableScan.ENUMERABLE.[](table=[public, t_order]), rowcount=100.0, cumulative cost={100.0 rows, 101.0 cpu, 0.0 io}
Set#1, type: RecordType(JavaType(class java.lang.Integer) order_id)
rel#957:RelSubset#1.NONE.[], best=null
rel#956:LogicalCalc.NONE.[](input=RelSubset#955,expr#0..5={inputs},0=$t0), rowcount=100.0, cumulative cost={inf}
rel#981:RelSubset#1.ENUMERABLE.[], best=rel#980
rel#980:EnumerableCalc.ENUMERABLE.[](input=RelSubset#979,expr#0..5={inputs},0=$t0), rowcount=100.0, cumulative cost={200.0 rows, 801.0 cpu, 0.0 io}
Set#2, type: RecordType(JavaType(class java.lang.Integer) item_id, JavaType(class java.lang.Integer) order_id, JavaType(class java.lang.Integer) user_id, JavaType(class java.lang.Integer) product_id, JavaType(class java.lang.Integer) quantity, JavaType(class java.sql.Date) creation_date)
rel#958:RelSubset#2.NONE.[], best=null
rel#894:LogicalTableScan.NONE.[](table=[public, t_order_item]), rowcount=100.0, cumulative cost={inf}
rel#983:RelSubset#2.ENUMERABLE.[], best=rel#982
rel#982:EnumerableTableScan.ENUMERABLE.[](table=[public, t_order_item]), rowcount=100.0, cumulative cost={100.0 rows, 101.0 cpu, 0.0 io}
Set#3, type: RecordType(JavaType(class java.lang.Integer) order_id, JavaType(class java.lang.Integer) product_id)
rel#960:RelSubset#3.NONE.[], best=null
rel#959:LogicalCalc.NONE.[](input=RelSubset#958,expr#0..5={inputs},0=$t1,1=$t3), rowcount=100.0, cumulative cost={inf}
rel#985:RelSubset#3.ENUMERABLE.[], best=rel#984
rel#984:EnumerableCalc.ENUMERABLE.[](input=RelSubset#983,expr#0..5={inputs},0=$t1,1=$t3), rowcount=100.0, cumulative cost={200.0 rows, 901.0 cpu, 0.0 io}
Set#4, type: RecordType(JavaType(class java.lang.Integer) order_id, JavaType(class java.lang.Integer) order_id0, JavaType(class java.lang.Integer) product_id)
rel#962:RelSubset#4.NONE.[], best=null
rel#961:LogicalJoin.NONE.[](left=RelSubset#957,right=RelSubset#960,condition==($0, $1),joinType=inner), rowcount=1500.0, cumulative cost={inf}
rel#987:RelSubset#4.ENUMERABLE.[], best=rel#986
rel#986:EnumerableHashJoin.ENUMERABLE.[](left=RelSubset#981,right=RelSubset#985,condition==($0, $1),joinType=inner), rowcount=1500.0, cumulative cost={2461.517018598809 rows, 1702.0 cpu, 0.0 io}
Set#5, type: RecordType(JavaType(class java.lang.Integer) order_id, JavaType(class java.lang.Integer) product_id)
rel#964:RelSubset#5.NONE.[], best=null
rel#963:LogicalCalc.NONE.[](input=RelSubset#962,expr#0..2={inputs},0=$t0,1=$t2), rowcount=1500.0, cumulative cost={inf}
rel#989:RelSubset#5.ENUMERABLE.[], best=rel#988
rel#988:EnumerableCalc.ENUMERABLE.[](input=RelSubset#987,expr#0..2={inputs},0=$t0,1=$t2), rowcount=1500.0, cumulative cost={3961.517018598809 rows, 9202.0 cpu, 0.0 io}
Set#6, type: RecordType(JavaType(class java.lang.Integer) product_id, JavaType(class java.lang.String) product_name, JavaType(class java.lang.Integer) category_id, JavaType(class java.math.BigDecimal) price, JavaType(class java.lang.String) status, JavaType(class java.sql.Date) creation_date)
rel#965:RelSubset#6.NONE.[], best=null
rel#898:LogicalTableScan.NONE.[](table=[public, t_product]), rowcount=100.0, cumulative cost={inf}
rel#991:RelSubset#6.ENUMERABLE.[], best=rel#990
rel#990:EnumerableTableScan.ENUMERABLE.[](table=[public, t_product]), rowcount=100.0, cumulative cost={100.0 rows, 101.0 cpu, 0.0 io}
Set#7, type: RecordType(JavaType(class java.lang.Integer) product_id, JavaType(class java.math.BigDecimal) price)
rel#967:RelSubset#7.NONE.[], best=null
rel#966:LogicalCalc.NONE.[](input=RelSubset#965,expr#0..5={inputs},0=$t0,1=$t3), rowcount=100.0, cumulative cost={inf}
rel#993:RelSubset#7.ENUMERABLE.[], best=rel#992
rel#992:EnumerableCalc.ENUMERABLE.[](input=RelSubset#991,expr#0..5={inputs},0=$t0,1=$t3), rowcount=100.0, cumulative cost={200.0 rows, 901.0 cpu, 0.0 io}
Set#8, type: RecordType(JavaType(class java.lang.Integer) order_id, JavaType(class java.lang.Integer) product_id, JavaType(class java.lang.Integer) product_id0, JavaType(class java.math.BigDecimal) price)
rel#969:RelSubset#8.NONE.[], best=null
rel#968:LogicalJoin.NONE.[](left=RelSubset#964,right=RelSubset#967,condition==($1, $2),joinType=inner), rowcount=22500.0, cumulative cost={inf}
rel#995:RelSubset#8.ENUMERABLE.[], best=rel#994
rel#994:EnumerableHashJoin.ENUMERABLE.[](left=RelSubset#989,right=RelSubset#993,condition==($1, $2),joinType=inner), rowcount=22500.0, cumulative cost={37731.347599234265 rows, 10103.0 cpu, 0.0 io}
Set#9, type: RecordType(JavaType(class java.lang.Integer) order_id, JavaType(class java.math.BigDecimal) price)
rel#971:RelSubset#9.NONE.[], best=null
rel#970:LogicalCalc.NONE.[](input=RelSubset#969,expr#0..3={inputs},0=$t0,1=$t3), rowcount=22500.0, cumulative cost={inf}
rel#997:RelSubset#9.ENUMERABLE.[], best=rel#996
rel#996:EnumerableCalc.ENUMERABLE.[](input=RelSubset#995,expr#0..3={inputs},0=$t0,1=$t3), rowcount=22500.0, cumulative cost={60231.347599234265 rows, 145103.0 cpu, 0.0 io}
Set#10, type: RecordType(JavaType(class java.lang.Integer) order_id, JavaType(class java.math.BigDecimal) sum_price, JavaType(class java.math.BigDecimal) avg_price, BIGINT count)
rel#973:RelSubset#10.NONE.[], best=null
rel#972:LogicalAggregate.NONE.[](input=RelSubset#971,group={0},sum_price=SUM($1),avg_price=AVG($1),count=COUNT()), rowcount=2250.0, cumulative cost={inf}
rel#999:RelSubset#10.ENUMERABLE.[], best=null
Set#11, type: RecordType(JavaType(class java.math.BigDecimal) sum_price, JavaType(class java.math.BigDecimal) avg_price, BIGINT count)
rel#975:RelSubset#11.NONE.[], best=null
rel#974:LogicalCalc.NONE.[](input=RelSubset#973,expr#0..3={inputs},0=$t1,1=$t2,2=$t3), rowcount=2250.0, cumulative cost={inf}
rel#976:RelSubset#11.ENUMERABLE.[], best=null
rel#977:AbstractConverter.ENUMERABLE.[](input=RelSubset#975,convention=ENUMERABLE,sort=[]), rowcount=2250.0, cumulative cost={inf}
rel#1000:EnumerableCalc.ENUMERABLE.[](input=RelSubset#999,expr#0..3={inputs},0=$t1,1=$t2,2=$t3), rowcount=2250.0, cumulative cost={inf}
Graphviz:
digraph G {
root [style=filled,label="Root"];
subgraph cluster0{
label="Set 0 RecordType(JavaType(class java.lang.Integer) order_id, JavaType(class java.lang.Integer) user_id, JavaType(class java.lang.String) status, JavaType(class java.lang.Integer) merchant_id, JavaType(class java.lang.String) remark, JavaType(class java.sql.Date) creation_date)";
rel892 [label="rel#892:LogicalTableScan\ntable=[public, t_order]\nrows=100.0, cost={inf}",shape=box]
rel978 [label="rel#978:EnumerableTableScan\ntable=[public, t_order]\nrows=100.0, cost={100.0 rows, 101.0 cpu, 0.0 io}",color=blue,shape=box]
subset955 [label="rel#955:RelSubset#0.NONE.[]"]
subset979 [label="rel#979:RelSubset#0.ENUMERABLE.[]"]
}
subgraph cluster1{
label="Set 1 RecordType(JavaType(class java.lang.Integer) order_id)";
rel956 [label="rel#956:LogicalCalc\ninput=RelSubset#955,expr#0..5={inputs},0=$t0\nrows=100.0, cost={inf}",shape=box]
rel980 [label="rel#980:EnumerableCalc\ninput=RelSubset#979,expr#0..5={inputs},0=$t0\nrows=100.0, cost={200.0 rows, 801.0 cpu, 0.0 io}",color=blue,shape=box]
subset957 [label="rel#957:RelSubset#1.NONE.[]"]
subset981 [label="rel#981:RelSubset#1.ENUMERABLE.[]"]
}
subgraph cluster2{
label="Set 2 RecordType(JavaType(class java.lang.Integer) item_id, JavaType(class java.lang.Integer) order_id, JavaType(class java.lang.Integer) user_id, JavaType(class java.lang.Integer) product_id, JavaType(class java.lang.Integer) quantity, JavaType(class java.sql.Date) creation_date)";
rel894 [label="rel#894:LogicalTableScan\ntable=[public, t_order_item]\nrows=100.0, cost={inf}",shape=box]
rel982 [label="rel#982:EnumerableTableScan\ntable=[public, t_order_item]\nrows=100.0, cost={100.0 rows, 101.0 cpu, 0.0 io}",color=blue,shape=box]
subset958 [label="rel#958:RelSubset#2.NONE.[]"]
subset983 [label="rel#983:RelSubset#2.ENUMERABLE.[]"]
}
subgraph cluster3{
label="Set 3 RecordType(JavaType(class java.lang.Integer) order_id, JavaType(class java.lang.Integer) product_id)";
rel959 [label="rel#959:LogicalCalc\ninput=RelSubset#958,expr#0..5={inputs},0=$t1,1=$t3\nrows=100.0, cost={inf}",shape=box]
rel984 [label="rel#984:EnumerableCalc\ninput=RelSubset#983,expr#0..5={inputs},0=$t1,1=$t3\nrows=100.0, cost={200.0 rows, 901.0 cpu, 0.0 io}",color=blue,shape=box]
subset960 [label="rel#960:RelSubset#3.NONE.[]"]
subset985 [label="rel#985:RelSubset#3.ENUMERABLE.[]"]
}
subgraph cluster4{
label="Set 4 RecordType(JavaType(class java.lang.Integer) order_id, JavaType(class java.lang.Integer) order_id0, JavaType(class java.lang.Integer) product_id)";
rel961 [label="rel#961:LogicalJoin\nleft=RelSubset#957,right=RelSubset#960,condition==($0, $1),joinType=inner\nrows=1500.0, cost={inf}",shape=box]
rel986 [label="rel#986:EnumerableHashJoin\nleft=RelSubset#981,right=RelSubset#985,condition==($0, $1),joinType=inner\nrows=1500.0, cost={2461.517018598809 rows, 1702.0 cpu, 0.0 io}",color=blue,shape=box]
subset962 [label="rel#962:RelSubset#4.NONE.[]"]
subset987 [label="rel#987:RelSubset#4.ENUMERABLE.[]"]
}
subgraph cluster5{
label="Set 5 RecordType(JavaType(class java.lang.Integer) order_id, JavaType(class java.lang.Integer) product_id)";
rel963 [label="rel#963:LogicalCalc\ninput=RelSubset#962,expr#0..2={inputs},0=$t0,1=$t2\nrows=1500.0, cost={inf}",shape=box]
rel988 [label="rel#988:EnumerableCalc\ninput=RelSubset#987,expr#0..2={inputs},0=$t0,1=$t2\nrows=1500.0, cost={3961.517018598809 rows, 9202.0 cpu, 0.0 io}",color=blue,shape=box]
subset964 [label="rel#964:RelSubset#5.NONE.[]"]
subset989 [label="rel#989:RelSubset#5.ENUMERABLE.[]"]
}
subgraph cluster6{
label="Set 6 RecordType(JavaType(class java.lang.Integer) product_id, JavaType(class java.lang.String) product_name, JavaType(class java.lang.Integer) category_id, JavaType(class java.math.BigDecimal) price, JavaType(class java.lang.String) status, JavaType(class java.sql.Date) creation_date)";
rel898 [label="rel#898:LogicalTableScan\ntable=[public, t_product]\nrows=100.0, cost={inf}",shape=box]
rel990 [label="rel#990:EnumerableTableScan\ntable=[public, t_product]\nrows=100.0, cost={100.0 rows, 101.0 cpu, 0.0 io}",color=blue,shape=box]
subset965 [label="rel#965:RelSubset#6.NONE.[]"]
subset991 [label="rel#991:RelSubset#6.ENUMERABLE.[]"]
}
subgraph cluster7{
label="Set 7 RecordType(JavaType(class java.lang.Integer) product_id, JavaType(class java.math.BigDecimal) price)";
rel966 [label="rel#966:LogicalCalc\ninput=RelSubset#965,expr#0..5={inputs},0=$t0,1=$t3\nrows=100.0, cost={inf}",shape=box]
rel992 [label="rel#992:EnumerableCalc\ninput=RelSubset#991,expr#0..5={inputs},0=$t0,1=$t3\nrows=100.0, cost={200.0 rows, 901.0 cpu, 0.0 io}",color=blue,shape=box]
subset967 [label="rel#967:RelSubset#7.NONE.[]"]
subset993 [label="rel#993:RelSubset#7.ENUMERABLE.[]"]
}
subgraph cluster8{
label="Set 8 RecordType(JavaType(class java.lang.Integer) order_id, JavaType(class java.lang.Integer) product_id, JavaType(class java.lang.Integer) product_id0, JavaType(class java.math.BigDecimal) price)";
rel968 [label="rel#968:LogicalJoin\nleft=RelSubset#964,right=RelSubset#967,condition==($1, $2),joinType=inner\nrows=22500.0, cost={inf}",shape=box]
rel994 [label="rel#994:EnumerableHashJoin\nleft=RelSubset#989,right=RelSubset#993,condition==($1, $2),joinType=inner\nrows=22500.0, cost={37731.347599234265 rows, 10103.0 cpu, 0.0 io}",color=blue,shape=box]
subset969 [label="rel#969:RelSubset#8.NONE.[]"]
subset995 [label="rel#995:RelSubset#8.ENUMERABLE.[]"]
}
subgraph cluster9{
label="Set 9 RecordType(JavaType(class java.lang.Integer) order_id, JavaType(class java.math.BigDecimal) price)";
rel970 [label="rel#970:LogicalCalc\ninput=RelSubset#969,expr#0..3={inputs},0=$t0,1=$t3\nrows=22500.0, cost={inf}",shape=box]
rel996 [label="rel#996:EnumerableCalc\ninput=RelSubset#995,expr#0..3={inputs},0=$t0,1=$t3\nrows=22500.0, cost={60231.347599234265 rows, 145103.0 cpu, 0.0 io}",color=blue,shape=box]
subset971 [label="rel#971:RelSubset#9.NONE.[]"]
subset997 [label="rel#997:RelSubset#9.ENUMERABLE.[]"]
}
subgraph cluster10{
label="Set 10 RecordType(JavaType(class java.lang.Integer) order_id, JavaType(class java.math.BigDecimal) sum_price, JavaType(class java.math.BigDecimal) avg_price, BIGINT count)";
rel972 [label="rel#972:LogicalAggregate\ninput=RelSubset#971,group={0},sum_price=SUM($1),avg_price=AVG($1),count=COUNT()\nrows=2250.0, cost={inf}",shape=box]
subset973 [label="rel#973:RelSubset#10.NONE.[]"]
subset999 [label="rel#999:RelSubset#10.ENUMERABLE.[]",color=red]
}
subgraph cluster11{
label="Set 11 RecordType(JavaType(class java.math.BigDecimal) sum_price, JavaType(class java.math.BigDecimal) avg_price, BIGINT count)";
rel974 [label="rel#974:LogicalCalc\ninput=RelSubset#973,expr#0..3={inputs},0=$t1,1=$t2,2=$t3\nrows=2250.0, cost={inf}",shape=box]
rel977 [label="rel#977:AbstractConverter\ninput=RelSubset#975,convention=ENUMERABLE,sort=[]\nrows=2250.0, cost={inf}",shape=box]
rel1000 [label="rel#1000:EnumerableCalc\ninput=RelSubset#999,expr#0..3={inputs},0=$t1,1=$t2,2=$t3\nrows=2250.0, cost={inf}",shape=box]
subset975 [label="rel#975:RelSubset#11.NONE.[]"]
subset976 [label="rel#976:RelSubset#11.ENUMERABLE.[]"]
}
root -> subset976;
subset955 -> rel892;
subset979 -> rel978[color=blue];
subset957 -> rel956; rel956 -> subset955;
subset981 -> rel980[color=blue]; rel980 -> subset979[color=blue];
subset958 -> rel894;
subset983 -> rel982[color=blue];
subset960 -> rel959; rel959 -> subset958;
subset985 -> rel984[color=blue]; rel984 -> subset983[color=blue];
subset962 -> rel961; rel961 -> subset957[label="0"]; rel961 -> subset960[label="1"];
subset987 -> rel986[color=blue]; rel986 -> subset981[color=blue,label="0"]; rel986 -> subset985[color=blue,label="1"];
subset964 -> rel963; rel963 -> subset962;
subset989 -> rel988[color=blue]; rel988 -> subset987[color=blue];
subset965 -> rel898;
subset991 -> rel990[color=blue];
subset967 -> rel966; rel966 -> subset965;
subset993 -> rel992[color=blue]; rel992 -> subset991[color=blue];
subset969 -> rel968; rel968 -> subset964[label="0"]; rel968 -> subset967[label="1"];
subset995 -> rel994[color=blue]; rel994 -> subset989[color=blue,label="0"]; rel994 -> subset993[color=blue,label="1"];
subset971 -> rel970; rel970 -> subset969;
subset997 -> rel996[color=blue]; rel996 -> subset995[color=blue];
subset973 -> rel972; rel972 -> subset971;
subset975 -> rel974; rel974 -> subset973;
subset976 -> rel977; rel977 -> subset975;
subset976 -> rel1000; rel1000 -> subset999;
}
at org.apache.calcite.plan.volcano.RelSubset$CheapestPlanReplacer.visit(RelSubset.java:709)
at org.apache.calcite.plan.volcano.RelSubset.buildCheapestPlan(RelSubset.java:390)
at org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp(VolcanoPlanner.java:533)
at org.apache.shardingsphere.sqlfederation.optimizer.SQLOptimizeEngine.optimizeWithCBO(SQLOptimizeEngine.java:69)
at org.apache.shardingsphere.sqlfederation.optimizer.SQLOptimizeEngine.optimize(SQLOptimizeEngine.java:53)
at org.apache.shardingsphere.sqlfederation.advanced.AdvancedSQLFederationExecutor.execute(AdvancedSQLFederationExecutor.java:141)
at org.apache.shardingsphere.sqlfederation.advanced.AdvancedSQLFederationExecutor.executeQuery(AdvancedSQLFederationExecutor.java:110)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.doExecuteFederation(JDBCDatabaseCommunicationEngine.java:163)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.execute(JDBCDatabaseCommunicationEngine.java:121)
at org.apache.shardingsphere.proxy.frontend.opengauss.command.query.simple.OpenGaussComQueryExecutor.execute(OpenGaussComQueryExecutor.java:76)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:111)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:78)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
```
### Reason analyze (If you can)
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
### Example codes for reproduce this issue (such as a github link).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuichenchuxin closed issue #21388: RelOptPlanner$CannotPlanException occurs when execute select aggregation statement with ADVANCED sql federation
Posted by GitBox <gi...@apache.org>.
tuichenchuxin closed issue #21388: RelOptPlanner$CannotPlanException occurs when execute select aggregation statement with ADVANCED sql federation
URL: https://github.com/apache/shardingsphere/issues/21388
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #21388: RelOptPlanner$CannotPlanException occurs when execute select aggregation statement with ADVANCED sql federation
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #21388:
URL: https://github.com/apache/shardingsphere/issues/21388#issuecomment-1272284574
Ref https://github.com/apache/shardingsphere/issues/19580.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #21388: RelOptPlanner$CannotPlanException occurs when execute select aggregation statement with ADVANCED sql federation
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #21388:
URL: https://github.com/apache/shardingsphere/issues/21388#issuecomment-1272431639
Following is the latest result:
```
sharding_db=> SELECT MAX(p.price) AS max_price, MIN(p.price) AS min_price, SUM(p.price) AS sum_price, AVG(p.price) AS avg_price, COUNT(1) AS count FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id INNER JOIN t_product p ON i.product_id = p.product_id GROUP BY o.order_id HAVING SUM(p.price) > 10000;
max_price | min_price | sum_price | avg_price | count
-----------+-----------+-----------+-----------+-------
6666 | 6666 | 13332 | 6666 | 2
8200 | 8200 | 16400 | 8200 | 2
6666 | 6666 | 13332 | 6666 | 2
8200 | 8200 | 16400 | 8200 | 2
324600 | 324600 | 649200 | 324600 | 2
324600 | 324600 | 649200 | 324600 | 2
9000 | 9000 | 18000 | 9000 | 2
9000 | 9000 | 18000 | 9000 | 2
(8 rows)
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #21388: RelOptPlanner$CannotPlanException occurs when execute select aggregation statement with ADVANCED sql federation
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #21388:
URL: https://github.com/apache/shardingsphere/issues/21388#issuecomment-1272425497
I have debug into calcite source code, and I found that calcite doesn't support avg function now.
<img width="1026" alt="image" src="https://user-images.githubusercontent.com/10829171/194732858-79c8b638-1b73-4ac0-83f0-82f8ccbf75d1.png">
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #21388: RelOptPlanner$CannotPlanException occurs when execute select aggregation statement with ADVANCED sql federation
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #21388:
URL: https://github.com/apache/shardingsphere/issues/21388#issuecomment-1272427366
In calcite, a more recommended way is to use the `CoreRules.AGGREGATE_REDUCE_FUNCTIONS` rule rewrites `AVG(x)` to `SUM(x) / COUNT(x)`, so we can implement AVG aggregation by adding this rule.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org