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/09 07:58:51 UTC
[GitHub] [shardingsphere] strongduanmu opened a new issue, #21420: Missing conversion is LogicalIntersect occurs when execute select intersect select with ADVANCED SQL federation
strongduanmu opened a new issue, #21420:
URL: https://github.com/apache/shardingsphere/issues/21420
## Bug Report
### Which version of ShardingSphere did you use?
https://github.com/apache/shardingsphere/commit/40b5076d63d21c0bb19cca16d4db211648b3c7f5
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy
### Expected behavior
Execute `SELECT * FROM t_order INTERSECT SELECT * FROM t_order;` successfully.
### Actual behavior
```
org.apache.calcite.plan.RelOptPlanner$CannotPlanException: There are not enough rules to produce a node with desired properties: convention=ENUMERABLE, sort=[].
Missing conversion is LogicalIntersect[convention: NONE -> ENUMERABLE]
There is 1 empty subset: rel#2594:RelSubset#1.ENUMERABLE.[], the relevant part of the original plan is as follows
2592:LogicalIntersect(all=[false])
2575:LogicalTableScan(subset=[rel#2591:RelSubset#0.NONE.[]], table=[[public, t_order]])
2575:LogicalTableScan(subset=[rel#2591:RelSubset#0.NONE.[]], table=[[public, t_order]])
Root: rel#2594:RelSubset#1.ENUMERABLE.[]
Original rel:
LogicalIntersect(subset=[rel#2594:RelSubset#1.ENUMERABLE.[]], all=[false]): rowcount = 100.0, cumulative cost = {100.0 rows, 100.0 cpu, 0.0 io}, id = 2592
LogicalTableScan(subset=[rel#2591:RelSubset#0.NONE.[]], table=[[public, t_order]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 2575
LogicalTableScan(subset=[rel#2591:RelSubset#0.NONE.[]], table=[[public, t_order]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 2575
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#2591:RelSubset#0.NONE.[], best=null
rel#2575:LogicalTableScan.NONE.[](table=[public, t_order]), rowcount=100.0, cumulative cost={inf}
rel#2597:RelSubset#0.ENUMERABLE.[], best=rel#2596
rel#2596: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, JavaType(class java.lang.Integer) user_id, VARCHAR status, JavaType(class java.lang.Integer) merchant_id, VARCHAR remark, JavaType(class java.sql.Date) creation_date)
rel#2593:RelSubset#1.NONE.[], best=null
rel#2592:LogicalIntersect.NONE.[](input#0=RelSubset#2591,input#1=RelSubset#2591,all=false), rowcount=100.0, cumulative cost={inf}
rel#2594:RelSubset#1.ENUMERABLE.[], best=null
rel#2595:AbstractConverter.ENUMERABLE.[](input=RelSubset#2593,convention=ENUMERABLE,sort=[]), rowcount=100.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)";
rel2575 [label="rel#2575:LogicalTableScan\ntable=[public, t_order]\nrows=100.0, cost={inf}",shape=box]
rel2596 [label="rel#2596:EnumerableTableScan\ntable=[public, t_order]\nrows=100.0, cost={100.0 rows, 101.0 cpu, 0.0 io}",color=blue,shape=box]
subset2591 [label="rel#2591:RelSubset#0.NONE.[]"]
subset2597 [label="rel#2597:RelSubset#0.ENUMERABLE.[]"]
}
subgraph cluster1{
label="Set 1 RecordType(JavaType(class java.lang.Integer) order_id, JavaType(class java.lang.Integer) user_id, VARCHAR status, JavaType(class java.lang.Integer) merchant_id, VARCHAR remark, JavaType(class java.sql.Date) creation_date)";
rel2592 [label="rel#2592:LogicalIntersect\ninput#0=RelSubset#2591,input#1=RelSubset#2591,all=false\nrows=100.0, cost={inf}",shape=box]
rel2595 [label="rel#2595:AbstractConverter\ninput=RelSubset#2593,convention=ENUMERABLE,sort=[]\nrows=100.0, cost={inf}",shape=box]
subset2593 [label="rel#2593:RelSubset#1.NONE.[]"]
subset2594 [label="rel#2594:RelSubset#1.ENUMERABLE.[]",color=red]
}
root -> subset2594;
subset2591 -> rel2575;
subset2597 -> rel2596[color=blue];
subset2593 -> rel2592; rel2592 -> subset2591[label="0"]; rel2592 -> subset2591[label="1"];
subset2594 -> rel2595; rel2595 -> subset2593;
}
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] strongduanmu commented on issue #21420: Missing conversion is LogicalIntersect occurs when execute select intersect select with ADVANCED SQL federation
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #21420:
URL: https://github.com/apache/shardingsphere/issues/21420#issuecomment-1272518186
When I execute `SELECT * FROM t_order MINUS SELECT * FROM t_order;`, following exception occurs.
```sql
sharding_db=> SELECT * FROM t_order MINUS SELECT * FROM t_order;
ERROR: Unsupported combine type: `MINUS`
```
--
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] tristaZero closed issue #21420: Missing conversion is LogicalIntersect occurs when execute select intersect select with ADVANCED SQL federation
Posted by GitBox <gi...@apache.org>.
tristaZero closed issue #21420: Missing conversion is LogicalIntersect occurs when execute select intersect select with ADVANCED SQL federation
URL: https://github.com/apache/shardingsphere/issues/21420
--
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 #21420: Missing conversion is LogicalIntersect occurs when execute select intersect select with ADVANCED SQL federation
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #21420:
URL: https://github.com/apache/shardingsphere/issues/21420#issuecomment-1272482476
When I execute `SELECT * FROM t_order EXCEPT SELECT * FROM t_order;`, a similar exception occurs.
```
ERROR: There are not enough rules to produce a node with desired properties: convention=ENUMERABLE, sort=[].
Missing conversion is LogicalMinus[convention: NONE -> ENUMERABLE]
There is 1 empty subset: rel#2618:RelSubset#1.ENUMERABLE.[], the relevant part of the original plan is as follows
2616:LogicalMinus(all=[false])
2599:LogicalTableScan(subset=[rel#2615:RelSubset#0.NONE.[]], table=[[public, t_order]])
2599:LogicalTableScan(subset=[rel#2615:RelSubset#0.NONE.[]], table=[[public, t_order]])
Root: rel#2618:RelSubset#1.ENUMERABLE.[]
Original rel:
LogicalMinus(subset=[rel#2618:RelSubset#1.ENUMERABLE.[]], all=[false]): rowcount = 100.0, cumulative cost = {100.0 rows, 100.0 cpu, 0.0 io}, id = 2616
LogicalTableScan(subset=[rel#2615:RelSubset#0.NONE.[]], table=[[public, t_order]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 2599
LogicalTableScan(subset=[rel#2615:RelSubset#0.NONE.[]], table=[[public, t_order]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 2599
```
--
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 #21420: Missing conversion is LogicalIntersect occurs when execute select intersect select with ADVANCED SQL federation
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #21420:
URL: https://github.com/apache/shardingsphere/issues/21420#issuecomment-1272482253
Ref #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