Refactor query optimization and ShardingSphere integration architecture - Githubissues

apache / shardingsphere

Empowering Data Intelligence with Distributed SQL for Sharding, Scalability, and Security Across All Databases.

Apache License 2.0

19.94k stars 6.74k forks source link

Refactor query optimization and ShardingSphere integration architecture #13091

Closed strongduanmu closed 2 years ago

strongduanmu commented 3 years ago

Background

Hi community,

This issue is a subtask of issue #8284. Currently, ShardingSphere uses Calcite to implement cross database JOIN query, subquery and some complex aggregate query. But there are still some problems, such as:

ShardingSphereOptimizer module is not used
SQL parsing twice, including shardingsphere sql parsing and callite sql parsing
Two sets of metadata that make it difficult to maintain
Optimized logical SQL is difficult to route accurately

Therefore, we need to refactor the process of the FilterableExecutor implementation to solve these problems.

The overall process after optimization is as follows:

In order to accomplish this goal, we also need to complete the following tasks:

[x] support sql node convert to ShardingSphere sql statement
[x] move the logic of federation routing to the Binder module
[x] refactor federation metadata and unified it into logical metadata
[x] adjust the federation execution engine table scan implementation logic and integrate the standard kernel process
[x] convert Calcite ResultSet to ShardingSphere ResultSet
[x] add unit tests and integration test cases

menghaoranss commented 3 years ago

@strongduanmu Have all the tasks of this issue been completed?

strongduanmu commented 3 years ago

@menghaoranss This is a big task, and only a small part of the function has been completed so far, so I will remove 5.0.0 milestone.

TeslaCN commented 2 years ago

Hi @strongduanmu Any update here? Could we finish this in 5.1.1?

strongduanmu commented 2 years ago

Hi @strongduanmu Any update here? Could we finish this in 5.1.1?

This issue is too big, I will remove its milestone first.

strongduanmu commented 2 years ago

When I replace OriginalFederationExecutor with AdvancedFederationExecutor, and execute BaseDQLIT, the following exceptions occur——Fixed by https://github.com/apache/shardingsphere/pull/20052.

[ERROR] 2022-08-09 09:52:39.393 [main] o.a.s.t.i.f.watcher.ITWatcher - Error case: assertExecuteQuery[jdbc: db -> H2 -> Literal -> SELECT i.* FROM t_order o, t_order_item i WHERE o.order_id = i.order_id AND o.status = 'init' ORDER BY o.order_id DESC, 1], message: org.apache.shardingsphere.infra.exception.ShardingSphereException: java.lang.UnsupportedOperationException: unsupported IndexOrderByItemSegment
    at org.apache.shardingsphere.infra.federation.optimizer.ShardingSphereOptimizer.optimize(ShardingSphereOptimizer.java:53)
    at org.apache.shardingsphere.infra.federation.executor.advanced.AdvancedFederationExecutor.execute(AdvancedFederationExecutor.java:122)
    at org.apache.shardingsphere.infra.federation.executor.advanced.AdvancedFederationExecutor.executeQuery(AdvancedFederationExecutor.java:102)
    at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement.executeFederationQuery(ShardingSphereStatement.java:223)
    at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement.executeQuery(ShardingSphereStatement.java:167)
    at org.apache.shardingsphere.test.integration.engine.dql.GeneralDQLIT.assertExecuteQueryForStatement(GeneralDQLIT.java:68)
    at org.apache.shardingsphere.test.integration.engine.dql.GeneralDQLIT.assertExecuteQuery(GeneralDQLIT.java:58)
    at jdk.internal.reflect.GeneratedMethodAccessor62.invoke(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:55)
    at org.junit.rules.RunRules.evaluate(RunRules.java:20)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.junit.runners.Suite.runChild(Suite.java:128)
    at org.junit.runners.Suite.runChild(Suite.java:27)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.junit.runners.Suite.runChild(Suite.java:128)
    at org.junit.runners.Suite.runChild(Suite.java:27)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
    at com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
    at com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
    at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
    at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:235)
    at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:54)
Caused by: java.lang.UnsupportedOperationException: unsupported IndexOrderByItemSegment
    at org.apache.shardingsphere.infra.federation.optimizer.converter.segment.orderby.item.OrderByItemConverterUtil.convertToSQLNode(OrderByItemConverterUtil.java:54)
    at org.apache.shardingsphere.infra.federation.optimizer.converter.segment.orderby.OrderByConverter.convertToSQLNode(OrderByConverter.java:37)
    at org.apache.shardingsphere.infra.federation.optimizer.converter.statement.select.SelectStatementConverter.lambda$convertToSQLNode$3(SelectStatementConverter.java:64)
    at java.base/java.util.Optional.flatMap(Optional.java:289)
    at org.apache.shardingsphere.infra.federation.optimizer.converter.statement.select.SelectStatementConverter.convertToSQLNode(SelectStatementConverter.java:64)
    at org.apache.shardingsphere.infra.federation.optimizer.converter.SQLNodeConverterEngine.convertToSQLNode(SQLNodeConverterEngine.java:51)
    at org.apache.shardingsphere.infra.federation.optimizer.ShardingSphereOptimizer.optimize(ShardingSphereOptimizer.java:48)
    ... 49 more

strongduanmu commented 2 years ago

SQLFeatureNotSupportedException——Fixed by #20017:

[ERROR] 2022-08-09 10:38:28.237 [main] o.a.s.t.i.f.watcher.ITWatcher - Error case: assertExecuteQuery[jdbc: db -> H2 -> Literal -> SELECT i.* FROM t_order o, t_order_item i WHERE o.order_id = i.order_id AND o.status = 'init' ORDER BY i.creation_date DESC, o.order_id DESC, i.item_id], message: java.sql.SQLFeatureNotSupportedException: can't get index from columnLabel[item_id].
    at org.apache.shardingsphere.infra.federation.executor.advanced.resultset.FederationResultSet.getIndexFromColumnLabelAndIndexMap(FederationResultSet.java:450)
    at org.apache.shardingsphere.infra.federation.executor.advanced.resultset.FederationResultSet.getObject(FederationResultSet.java:331)
    at org.apache.shardingsphere.test.integration.engine.dql.BaseDQLIT.assertRow(BaseDQLIT.java:114)
    at org.apache.shardingsphere.test.integration.engine.dql.BaseDQLIT.assertRows(BaseDQLIT.java:104)
    at org.apache.shardingsphere.test.integration.engine.dql.BaseDQLIT.assertResultSet(BaseDQLIT.java:79)
    at org.apache.shardingsphere.test.integration.engine.dql.GeneralDQLIT.assertExecuteQueryForStatement(GeneralDQLIT.java:71)
    at org.apache.shardingsphere.test.integration.engine.dql.GeneralDQLIT.assertExecuteQuery(GeneralDQLIT.java:58)
    at jdk.internal.reflect.GeneratedMethodAccessor62.invoke(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:55)
    at org.junit.rules.RunRules.evaluate(RunRules.java:20)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.junit.runners.Suite.runChild(Suite.java:128)
    at org.junit.runners.Suite.runChild(Suite.java:27)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.junit.runners.Suite.runChild(Suite.java:128)
    at org.junit.runners.Suite.runChild(Suite.java:27)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
    at com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
    at com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
    at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
    at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:235)
    at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:54)

strongduanmu commented 2 years ago

Size of actual result set is different with size of expected result set ——supported by #https://github.com/apache/shardingsphere/pull/20055.

[ERROR] 2022-08-09 10:53:37.727 [main] o.a.s.t.i.f.watcher.ITWatcher - Error case: assertExecuteQuery[jdbc: db -> H2 -> Placeholder -> SELECT i.* FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id WHERE o.order_id = ?], message: java.lang.AssertionError: Size of actual result set is different with size of expected result set.
    at org.junit.Assert.fail(Assert.java:88)
    at org.junit.Assert.assertTrue(Assert.java:41)
    at org.junit.Assert.assertFalse(Assert.java:64)
    at org.apache.shardingsphere.test.integration.engine.dql.BaseDQLIT.assertRows(BaseDQLIT.java:106)
    at org.apache.shardingsphere.test.integration.engine.dql.BaseDQLIT.assertResultSet(BaseDQLIT.java:79)
    at org.apache.shardingsphere.test.integration.engine.dql.GeneralDQLIT.assertExecuteQueryForPreparedStatement(GeneralDQLIT.java:86)
    at org.apache.shardingsphere.test.integration.engine.dql.GeneralDQLIT.assertExecuteQuery(GeneralDQLIT.java:60)
    at jdk.internal.reflect.GeneratedMethodAccessor62.invoke(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:55)
    at org.junit.rules.RunRules.evaluate(RunRules.java:20)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.junit.runners.Suite.runChild(Suite.java:128)
    at org.junit.runners.Suite.runChild(Suite.java:27)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.junit.runners.Suite.runChild(Suite.java:128)
    at org.junit.runners.Suite.runChild(Suite.java:27)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
    at com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
    at com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
    at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
    at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:235)
    at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:54)

strongduanmu commented 2 years ago

Missing conversion is LogicalUnion[convention: NONE -> ENUMERABLE].——Fixed by #20022

[ERROR] 2022-08-09 10:53:43.303 [main] o.a.s.t.i.f.watcher.ITWatcher - Error case: assertExecuteQuery[jdbc: db -> H2 -> Literal -> SELECT order_id_sharding AS order_id, user_id, status FROM t_order_federate_sharding WHERE order_id_sharding = ? UNION ALL SELECT order_id, user_id, status FROM t_order_item_federate_sharding WHERE user_id = ?], message: org.apache.calcite.plan.RelOptPlanner$CannotPlanException: There are not enough rules to produce a node with desired properties: convention=ENUMERABLE, sort=[].
Missing conversion is LogicalUnion[convention: NONE -> ENUMERABLE]
There is 1 empty subset: rel#6136:RelSubset#4.ENUMERABLE.[], the relevant part of the original plan is as follows
6134:LogicalUnion(all=[true])
  6129:LogicalCalc(subset=[rel#6130:RelSubset#1.NONE.[]], expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER], expr#4=[1010], expr#5=[=($t3, $t4)], proj#0..2=[{exprs}], $condition=[$t5])
    6101:LogicalTableScan(subset=[rel#6128:RelSubset#0.NONE.[]], table=[[logic_db, t_order_federate_sharding]])
  6132:LogicalCalc(subset=[rel#6133:RelSubset#3.NONE.[]], expr#0..4=[{inputs}], expr#5=[CAST($t2):INTEGER], expr#6=[10], expr#7=[=($t5, $t6)], order_id=[$t1], user_id=[$t2], status=[$t3], $condition=[$t7])
    6105:LogicalTableScan(subset=[rel#6131:RelSubset#2.NONE.[]], table=[[logic_db, t_order_item_federate_sharding]])

Root: rel#6136:RelSubset#4.ENUMERABLE.[]
Original rel:
LogicalUnion(subset=[rel#6136:RelSubset#4.ENUMERABLE.[]], all=[true]): rowcount = 30.0, cumulative cost = {30.0 rows, 30.0 cpu, 0.0 io}, id = 6134
  LogicalCalc(subset=[rel#6130:RelSubset#1.NONE.[]], expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER], expr#4=[1010], expr#5=[=($t3, $t4)], proj#0..2=[{exprs}], $condition=[$t5]): rowcount = 15.0, cumulative cost = {15.0 rows, 1000.0 cpu, 0.0 io}, id = 6129
    LogicalTableScan(subset=[rel#6128:RelSubset#0.NONE.[]], table=[[logic_db, t_order_federate_sharding]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 6101
  LogicalCalc(subset=[rel#6133:RelSubset#3.NONE.[]], expr#0..4=[{inputs}], expr#5=[CAST($t2):INTEGER], expr#6=[10], expr#7=[=($t5, $t6)], order_id=[$t1], user_id=[$t2], status=[$t3], $condition=[$t7]): rowcount = 15.0, cumulative cost = {15.0 rows, 1200.0 cpu, 0.0 io}, id = 6132
    LogicalTableScan(subset=[rel#6131:RelSubset#2.NONE.[]], table=[[logic_db, t_order_item_federate_sharding]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 6105

Sets:
Set#0, type: RecordType(JavaType(class java.lang.Integer) order_id_sharding, JavaType(class java.lang.Integer) user_id, JavaType(class java.lang.String) status)
    rel#6128:RelSubset#0.NONE.[], best=null
        rel#6101:LogicalTableScan.NONE.[](table=[logic_db, t_order_federate_sharding]), rowcount=100.0, cumulative cost={inf}
    rel#6139:RelSubset#0.ENUMERABLE.[], best=rel#6138
        rel#6138:EnumerableTableScan.ENUMERABLE.[](table=[logic_db, t_order_federate_sharding]), 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_sharding, JavaType(class java.lang.Integer) user_id, JavaType(class java.lang.String) status)
    rel#6130:RelSubset#1.NONE.[], best=null
        rel#6129:LogicalCalc.NONE.[](input=RelSubset#6128,expr#0..2={inputs},expr#3=CAST($t0):INTEGER,expr#4=1010,expr#5==($t3, $t4),proj#0..2={exprs},$condition=$t5), rowcount=15.0, cumulative cost={inf}
    rel#6141:RelSubset#1.ENUMERABLE.[], best=rel#6140
        rel#6140:EnumerableCalc.ENUMERABLE.[](input=RelSubset#6139,expr#0..2={inputs},expr#3=CAST($t0):INTEGER,expr#4=1010,expr#5==($t3, $t4),proj#0..2={exprs},$condition=$t5), rowcount=15.0, cumulative cost={115.0 rows, 1101.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.String) status, JavaType(class java.lang.String) remarks)
    rel#6131:RelSubset#2.NONE.[], best=null
        rel#6105:LogicalTableScan.NONE.[](table=[logic_db, t_order_item_federate_sharding]), rowcount=100.0, cumulative cost={inf}
    rel#6143:RelSubset#2.ENUMERABLE.[], best=rel#6142
        rel#6142:EnumerableTableScan.ENUMERABLE.[](table=[logic_db, t_order_item_federate_sharding]), 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) user_id, JavaType(class java.lang.String) status)
    rel#6133:RelSubset#3.NONE.[], best=null
        rel#6132:LogicalCalc.NONE.[](input=RelSubset#6131,expr#0..4={inputs},expr#5=CAST($t2):INTEGER,expr#6=10,expr#7==($t5, $t6),0=$t1,1=$t2,2=$t3,$condition=$t7), rowcount=15.0, cumulative cost={inf}
    rel#6145:RelSubset#3.ENUMERABLE.[], best=rel#6144
        rel#6144:EnumerableCalc.ENUMERABLE.[](input=RelSubset#6143,expr#0..4={inputs},expr#5=CAST($t2):INTEGER,expr#6=10,expr#7==($t5, $t6),0=$t1,1=$t2,2=$t3,$condition=$t7), rowcount=15.0, cumulative cost={115.0 rows, 1301.0 cpu, 0.0 io}
Set#4, type: RecordType(JavaType(class java.lang.Integer) order_id_sharding, JavaType(class java.lang.Integer) user_id, VARCHAR status)
    rel#6135:RelSubset#4.NONE.[], best=null
        rel#6134:LogicalUnion.NONE.[](input#0=RelSubset#6130,input#1=RelSubset#6133,all=true), rowcount=30.0, cumulative cost={inf}
    rel#6136:RelSubset#4.ENUMERABLE.[], best=null
        rel#6137:AbstractConverter.ENUMERABLE.[](input=RelSubset#6135,convention=ENUMERABLE,sort=[]), rowcount=30.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_sharding, JavaType(class java.lang.Integer) user_id, JavaType(class java.lang.String) status)";
        rel6101 [label="rel#6101:LogicalTableScan\ntable=[logic_db, t_order_federate_sharding]\nrows=100.0, cost={inf}",shape=box]
        rel6138 [label="rel#6138:EnumerableTableScan\ntable=[logic_db, t_order_federate_sharding]\nrows=100.0, cost={100.0 rows, 101.0 cpu, 0.0 io}",color=blue,shape=box]
        subset6128 [label="rel#6128:RelSubset#0.NONE.[]"]
        subset6139 [label="rel#6139:RelSubset#0.ENUMERABLE.[]"]
    }
    subgraph cluster1{
        label="Set 1 RecordType(JavaType(class java.lang.Integer) order_id_sharding, JavaType(class java.lang.Integer) user_id, JavaType(class java.lang.String) status)";
        rel6129 [label="rel#6129:LogicalCalc\ninput=RelSubset#6128,expr#0..2={inputs},expr#3=CAST($t0):INTEGER,expr#4=1010,expr#5==($t3, $t4),proj#0..2={exprs},$condition=$t5\nrows=15.0, cost={inf}",shape=box]
        rel6140 [label="rel#6140:EnumerableCalc\ninput=RelSubset#6139,expr#0..2={inputs},expr#3=CAST($t0):INTEGER,expr#4=1010,expr#5==($t3, $t4),proj#0..2={exprs},$condition=$t5\nrows=15.0, cost={115.0 rows, 1101.0 cpu, 0.0 io}",color=blue,shape=box]
        subset6130 [label="rel#6130:RelSubset#1.NONE.[]"]
        subset6141 [label="rel#6141: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.String) status, JavaType(class java.lang.String) remarks)";
        rel6105 [label="rel#6105:LogicalTableScan\ntable=[logic_db, t_order_item_federate_sharding]\nrows=100.0, cost={inf}",shape=box]
        rel6142 [label="rel#6142:EnumerableTableScan\ntable=[logic_db, t_order_item_federate_sharding]\nrows=100.0, cost={100.0 rows, 101.0 cpu, 0.0 io}",color=blue,shape=box]
        subset6131 [label="rel#6131:RelSubset#2.NONE.[]"]
        subset6143 [label="rel#6143:RelSubset#2.ENUMERABLE.[]"]
    }
    subgraph cluster3{
        label="Set 3 RecordType(JavaType(class java.lang.Integer) order_id, JavaType(class java.lang.Integer) user_id, JavaType(class java.lang.String) status)";
        rel6132 [label="rel#6132:LogicalCalc\ninput=RelSubset#6131,expr#0..4={inputs},expr#5=CAST($t2):INTEGER,expr#6=10,expr#7==($t5, $t6),0=$t1,1=$t2,2=$t3,$condition=$t7\nrows=15.0, cost={inf}",shape=box]
        rel6144 [label="rel#6144:EnumerableCalc\ninput=RelSubset#6143,expr#0..4={inputs},expr#5=CAST($t2):INTEGER,expr#6=10,expr#7==($t5, $t6),0=$t1,1=$t2,2=$t3,$condition=$t7\nrows=15.0, cost={115.0 rows, 1301.0 cpu, 0.0 io}",color=blue,shape=box]
        subset6133 [label="rel#6133:RelSubset#3.NONE.[]"]
        subset6145 [label="rel#6145:RelSubset#3.ENUMERABLE.[]"]
    }
    subgraph cluster4{
        label="Set 4 RecordType(JavaType(class java.lang.Integer) order_id_sharding, JavaType(class java.lang.Integer) user_id, VARCHAR status)";
        rel6134 [label="rel#6134:LogicalUnion\ninput#0=RelSubset#6130,input#1=RelSubset#6133,all=true\nrows=30.0, cost={inf}",shape=box]
        rel6137 [label="rel#6137:AbstractConverter\ninput=RelSubset#6135,convention=ENUMERABLE,sort=[]\nrows=30.0, cost={inf}",shape=box]
        subset6135 [label="rel#6135:RelSubset#4.NONE.[]"]
        subset6136 [label="rel#6136:RelSubset#4.ENUMERABLE.[]",color=red]
    }
    root -> subset6136;
    subset6128 -> rel6101;
    subset6139 -> rel6138[color=blue];
    subset6130 -> rel6129; rel6129 -> subset6128;
    subset6141 -> rel6140[color=blue]; rel6140 -> subset6139[color=blue];
    subset6131 -> rel6105;
    subset6143 -> rel6142[color=blue];
    subset6133 -> rel6132; rel6132 -> subset6131;
    subset6145 -> rel6144[color=blue]; rel6144 -> subset6143[color=blue];
    subset6135 -> rel6134; rel6134 -> subset6130[label="0"]; rel6134 -> subset6133[label="1"];
    subset6136 -> rel6137; rel6137 -> subset6135;
}
    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.infra.federation.optimizer.ShardingSphereOptimizer.optimizeWithCBO(ShardingSphereOptimizer.java:65)
    at org.apache.shardingsphere.infra.federation.optimizer.ShardingSphereOptimizer.optimize(ShardingSphereOptimizer.java:51)
    at org.apache.shardingsphere.infra.federation.executor.advanced.AdvancedFederationExecutor.execute(AdvancedFederationExecutor.java:122)
    at org.apache.shardingsphere.infra.federation.executor.advanced.AdvancedFederationExecutor.executeQuery(AdvancedFederationExecutor.java:102)
    at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement.executeFederationQuery(ShardingSphereStatement.java:223)
    at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement.executeQuery(ShardingSphereStatement.java:167)
    at org.apache.shardingsphere.test.integration.engine.dql.GeneralDQLIT.assertExecuteQueryForStatement(GeneralDQLIT.java:68)
    at org.apache.shardingsphere.test.integration.engine.dql.GeneralDQLIT.assertExecuteQuery(GeneralDQLIT.java:58)
    at jdk.internal.reflect.GeneratedMethodAccessor62.invoke(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:55)
    at org.junit.rules.RunRules.evaluate(RunRules.java:20)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.junit.runners.Suite.runChild(Suite.java:128)
    at org.junit.runners.Suite.runChild(Suite.java:27)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.junit.runners.Suite.runChild(Suite.java:128)
    at org.junit.runners.Suite.runChild(Suite.java:27)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
    at com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
    at com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
    at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
    at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:235)
    at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:54)

strongduanmu commented 2 years ago

All task has been finished, so I will close this issue.

kanha-gupta commented 1 year ago

@strongduanmu @TeslaCN Hey ! I would love to work on this.

strongduanmu commented 1 year ago

Hi @kanha-gupta, this work has been finished yet. Are you interested in this issue - https://github.com/apache/shardingsphere/issues/13456?

kanha-gupta commented 1 year ago

@strongduanmu Yeah,I'll comment on that too