You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by pa...@apache.org on 2021/10/06 11:08:51 UTC
[shardingsphere] branch master updated: Add optimization rules and
unit test for cross join (#12904)
This is an automated email from the ASF dual-hosted git repository.
panjuan pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new e969fc6 Add optimization rules and unit test for cross join (#12904)
e969fc6 is described below
commit e969fc625182c3e52e24af952df51e436cfc57ae
Author: coco <co...@gmail.com>
AuthorDate: Wed Oct 6 19:08:09 2021 +0800
Add optimization rules and unit test for cross join (#12904)
* refactor: modify the initialization of the volcano planneand
related tests.
* style: mdoify code style.
* feat: add rules to push down filter.
* feat: add rules to push down project.
* test: add unit test for cross join.
* : support optimization rule and test for SubQuery in from and project.
---
.../sql/federate/FederateJDBCExecutorTest.java | 85 ++++++++++++++++++++--
.../planner/QueryOptimizePlannerFactory.java | 12 ++-
2 files changed, 89 insertions(+), 8 deletions(-)
diff --git a/shardingsphere-infra/shardingsphere-infra-executor/src/test/java/org/apache/shardingsphere/infra/executor/sql/federate/FederateJDBCExecutorTest.java b/shardingsphere-infra/shardingsphere-infra-executor/src/test/java/org/apache/shardingsphere/infra/executor/sql/federate/FederateJDBCExecutorTest.java
index a44e98d..9b25f78 100644
--- a/shardingsphere-infra/shardingsphere-infra-executor/src/test/java/org/apache/shardingsphere/infra/executor/sql/federate/FederateJDBCExecutorTest.java
+++ b/shardingsphere-infra/shardingsphere-infra-executor/src/test/java/org/apache/shardingsphere/infra/executor/sql/federate/FederateJDBCExecutorTest.java
@@ -46,22 +46,40 @@ import static org.mockito.Mockito.when;
public final class FederateJDBCExecutorTest {
+ private static final String SELECT_CROSS_JOIN_CONDITION =
+ "SELECT t_order_federate.order_id, t_order_federate.user_id, t_user_info.user_id "
+ + "FROM t_order_federate JOIN t_user_info ON t_order_federate.user_id = t_user_info.user_id "
+ + "WHERE t_user_info.user_id = 13";
+
private static final String SELECT_WHERE_ALL_FIELDS =
"SELECT user_id, information FROM t_user_info WHERE user_id = 12";
private static final String SELECT_WHERE_SINGLE_FIELD =
"SELECT user_id FROM t_user_info WHERE user_id = 12";
- private static final String SELECT_JOIN =
+ private static final String SELECT_CROSS_WHERE =
"SELECT t_order_federate.order_id, t_order_federate.user_id, t_user_info.user_id "
+ "FROM t_order_federate , t_user_info "
+ "WHERE t_order_federate.user_id = t_user_info.user_id";
- private static final String SELECT_JOIN_WHERE =
+ private static final String SELECT_CROSS_JOIN =
+ "SELECT t_order_federate.order_id, t_order_federate.user_id, t_user_info.user_id "
+ + "FROM t_order_federate JOIN t_user_info "
+ + "ON t_order_federate.user_id = t_user_info.user_id";
+
+ private static final String SELECT_CROSS_WHERE_CONDITION =
"SELECT t_order_federate.order_id, t_order_federate.user_id, t_user_info.user_id "
+ "FROM t_order_federate ,t_user_info "
+ "WHERE t_order_federate.user_id = t_user_info.user_id AND t_user_info.user_id = 13";
+ private static final String SELECT_SUBQUERY_FROM =
+ "SELECT user.user_id, user.information "
+ + "FROM (SELECT * FROM t_user_info WHERE user_id > 1) as user ";
+
+ private static final String SELECT_SUBQUERY_WHERE_EXIST =
+ "SELECT t_order_federate.order_id, t_order_federate.user_id FROM t_order_federate "
+ + "WHERE EXISTS (SELECT * FROM t_user_info WHERE t_order_federate.user_id = t_user_info.user_id)";
+
private final String schemaName = "federate_jdbc";
private ShardingSphereOptimizer optimizer;
@@ -95,6 +113,21 @@ public final class FederateJDBCExecutorTest {
}
@Test
+ public void assertSelectCrossJoinCondition() {
+ ShardingSphereSQLParserEngine sqlParserEngine = new ShardingSphereSQLParserEngine(
+ DatabaseTypeRegistry.getTrunkDatabaseTypeName(new H2DatabaseType()), new ConfigurationProperties(new Properties()));
+ SQLStatement sqlStatement = sqlParserEngine.parse(SELECT_CROSS_JOIN_CONDITION, false);
+ String actual = optimizer.optimize(schemaName, sqlStatement).explain();
+ String expected = "EnumerableCalc(expr#0..4=[{inputs}],proj#0..1=[{exprs}],user_id0=[$t3])"
+ + " EnumerableInterpreterBindableJoin(condition=[=($2,$4)],joinType=[inner])"
+ + " BindableProject(order_id=[$0],user_id=[$1],user_id0=[CAST($1):VARCHAR])"
+ + " BindableTableScan(table=[[federate_jdbc,t_order_federate]],projects=[[0,1]])"
+ + " BindableProject(user_id=[$0],user_id0=[CAST($0):VARCHAR])"
+ + " BindableTableScan(table=[[federate_jdbc,t_user_info]],filters=[[=(CAST($0):INTEGER,13)]],projects=[[0]])";
+ assertThat(actual.replaceAll("\\s*", ""), is(expected.replaceAll("\\s*", "")));
+ }
+
+ @Test
public void assertSelectWhereAllFields() {
ShardingSphereSQLParserEngine sqlParserEngine = new ShardingSphereSQLParserEngine(
DatabaseTypeRegistry.getTrunkDatabaseTypeName(new H2DatabaseType()), new ConfigurationProperties(new Properties()));
@@ -117,10 +150,10 @@ public final class FederateJDBCExecutorTest {
}
@Test
- public void assertSelectJoin() {
+ public void assertSelectCrossWhere() {
ShardingSphereSQLParserEngine sqlParserEngine = new ShardingSphereSQLParserEngine(
DatabaseTypeRegistry.getTrunkDatabaseTypeName(new H2DatabaseType()), new ConfigurationProperties(new Properties()));
- SQLStatement sqlStatement = sqlParserEngine.parse(SELECT_JOIN, false);
+ SQLStatement sqlStatement = sqlParserEngine.parse(SELECT_CROSS_WHERE, false);
String actual = optimizer.optimize(schemaName, sqlStatement).explain();
String expected = "EnumerableInterpreter"
+ "BindableJoin(condition=[=(CAST($1):VARCHAR,CAST($2):VARCHAR)],joinType=[inner])"
@@ -130,10 +163,25 @@ public final class FederateJDBCExecutorTest {
}
@Test
+ public void assertSelectCrossJoin() {
+ ShardingSphereSQLParserEngine sqlParserEngine = new ShardingSphereSQLParserEngine(
+ DatabaseTypeRegistry.getTrunkDatabaseTypeName(new H2DatabaseType()), new ConfigurationProperties(new Properties()));
+ SQLStatement sqlStatement = sqlParserEngine.parse(SELECT_CROSS_JOIN, false);
+ String actual = optimizer.optimize(schemaName, sqlStatement).explain();
+ String expected = "EnumerableCalc(expr#0..4=[{inputs}],proj#0..1=[{exprs}],user_id0=[$t3])"
+ + "EnumerableInterpreterBindableJoin(condition=[=($2,$4)],joinType=[inner])"
+ + " BindableProject(order_id=[$0],user_id=[$1],user_id0=[CAST($1):VARCHAR])"
+ + " BindableTableScan(table=[[federate_jdbc,t_order_federate]],projects=[[0,1]])"
+ + " BindableProject(user_id=[$0],user_id0=[CAST($0):VARCHAR])"
+ + " BindableTableScan(table=[[federate_jdbc,t_user_info]],projects=[[0]])";
+ assertThat(actual.replaceAll("\\s*", ""), is(expected.replaceAll("\\s*", "")));
+ }
+
+ @Test
public void assertSelectJoinWhere() {
ShardingSphereSQLParserEngine sqlParserEngine = new ShardingSphereSQLParserEngine(
DatabaseTypeRegistry.getTrunkDatabaseTypeName(new H2DatabaseType()), new ConfigurationProperties(new Properties()));
- SQLStatement sqlStatement = sqlParserEngine.parse(SELECT_JOIN_WHERE, false);
+ SQLStatement sqlStatement = sqlParserEngine.parse(SELECT_CROSS_WHERE_CONDITION, false);
String actual = optimizer.optimize(schemaName, sqlStatement).explain();
String expected = "EnumerableInterpreter"
+ "BindableJoin(condition=[=(CAST($1):VARCHAR,CAST($2):VARCHAR)],joinType=[inner])"
@@ -141,4 +189,31 @@ public final class FederateJDBCExecutorTest {
+ " BindableTableScan(table=[[federate_jdbc,t_user_info]],filters=[[=(CAST($0):INTEGER,13)]],projects=[[0]])";
assertThat(actual.replaceAll("\\s*", ""), is(expected.replaceAll("\\s*", "")));
}
+
+ @Test
+ public void assertSelectSubQueryFrom() {
+ ShardingSphereSQLParserEngine sqlParserEngine = new ShardingSphereSQLParserEngine(
+ DatabaseTypeRegistry.getTrunkDatabaseTypeName(new H2DatabaseType()), new ConfigurationProperties(new Properties()));
+ SQLStatement sqlStatement = sqlParserEngine.parse(SELECT_SUBQUERY_FROM, false);
+ String actual = optimizer.optimize(schemaName, sqlStatement).explain();
+ String expected = "EnumerableInterpreter"
+ + "BindableTableScan(table=[[federate_jdbc,t_user_info]],filters=[[>(CAST($0):INTEGER,1)]])";
+ assertThat(actual.replaceAll("\\s*", ""), is(expected.replaceAll("\\s*", "")));
+ }
+
+ @Test
+ public void assertSelectSubQueryWhereExist() {
+ ShardingSphereSQLParserEngine sqlParserEngine = new ShardingSphereSQLParserEngine(
+ DatabaseTypeRegistry.getTrunkDatabaseTypeName(new H2DatabaseType()), new ConfigurationProperties(new Properties()));
+ SQLStatement sqlStatement = sqlParserEngine.parse(SELECT_SUBQUERY_WHERE_EXIST, false);
+ String actual = optimizer.optimize(schemaName, sqlStatement).explain();
+ String expected =
+ "EnumerableCalc(expr#0..3=[{inputs}],expr#4=[ISNOTNULL($t3)],proj#0..1=[{exprs}],$condition=[$t4])"
+ + " EnumerableCorrelate(correlation=[$cor0],joinType=[left],requiredColumns=[{1}]) "
+ + " EnumerableInterpreterBindableTableScan(table=[[federate_jdbc,t_order_federate]]) "
+ + " EnumerableInterpreterBindableAggregate(group=[{}],agg#0=[MIN($0)]) "
+ + " BindableProject($f0=[true]) "
+ + " BindableTableScan(table=[[federate_jdbc,t_user_info]],filters=[[=(CAST($cor0.user_id):VARCHAR,CAST($0):VARCHAR)]],projects=[[0]]) ";
+ assertThat(actual.replaceAll("\\s*", ""), is(expected.replaceAll("\\s*", "")));
+ }
}
diff --git a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/planner/QueryOptimizePlannerFactory.java b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/planner/QueryOptimizePlannerFactory.java
index b234474..ee68d02 100644
--- a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/planner/QueryOptimizePlannerFactory.java
+++ b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/planner/QueryOptimizePlannerFactory.java
@@ -60,10 +60,16 @@ public final class QueryOptimizePlannerFactory {
planner.addRule(Bindables.BINDABLE_VALUES_RULE);
planner.addRule(Bindables.BINDABLE_AGGREGATE_RULE);
planner.addRule(Bindables.BINDABLE_MATCH_RULE);
- planner.addRule(CoreRules.FILTER_SCAN);
planner.addRule(CoreRules.PROJECT_FILTER_TRANSPOSE);
- planner.addRule(CoreRules.FILTER_INTO_JOIN);
- planner.addRule(CoreRules.PROJECT_TABLE_SCAN);
planner.addRule(CoreRules.PROJECT_JOIN_TRANSPOSE);
+ planner.addRule(CoreRules.PROJECT_MERGE);
+ planner.addRule(CoreRules.PROJECT_TABLE_SCAN);
+ planner.addRule(CoreRules.FILTER_INTO_JOIN);
+ planner.addRule(CoreRules.FILTER_PROJECT_TRANSPOSE);
+ planner.addRule(CoreRules.FILTER_SCAN);
+ planner.addRule(EnumerableRules.ENUMERABLE_CORRELATE_RULE);
+ planner.addRule(EnumerableRules.ENUMERABLE_PROJECT_RULE);
+ planner.addRule(EnumerableRules.ENUMERABLE_FILTER_RULE);
+ planner.addRule(EnumerableRules.ENUMERABLE_MATCH_RULE);
}
}