This is an automated email from the ASF dual-hosted git repository.
duanzhengqiang 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 1beb261 feat: change rules and add sub-query test. (#12972)
1beb261 is described below
commit 1beb26125aff7ea315ec9e23f8aee5d9f2878052
Author: coco <[email protected]>
AuthorDate: Mon Oct 11 10:49:11 2021 +0800
feat: change rules and add sub-query test. (#12972)
---
.../sql/federate/FederateJDBCExecutorTest.java | 110 ++++++++++++++++-----
.../planner/QueryOptimizePlannerFactory.java | 29 +-----
2 files changed, 87 insertions(+), 52 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 9b25f78..217ed13 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
@@ -80,6 +80,15 @@ public final class FederateJDBCExecutorTest {
"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 static final String SELECT_SUBQUERY_WHERE_IN =
+ "SELECT t_order_federate.order_id, t_order_federate.user_id FROM
t_order_federate "
+ + "WHERE t_order_federate.user_id IN (SELECT t_user_info.user_id
FROM t_user_info)";
+
+ private static final String SELECT_SUBQUERY_WHERE_BETWEEN =
+ "SELECT t_order_federate.order_id, t_order_federate.user_id FROM
t_order_federate "
+ + "WHERE user_id BETWEEN (SELECT user_id FROM t_user_info WHERE
information = 'before') "
+ + "AND (SELECT user_id FROM t_user_info WHERE information =
'after')";
+
private final String schemaName = "federate_jdbc";
private ShardingSphereOptimizer optimizer;
@@ -118,12 +127,13 @@ public final class FederateJDBCExecutorTest {
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]])";
+ String expected =
+
"EnumerableCalc(expr#0..6=[{inputs}],order_id=[$t3],user_id=[$t4],user_id0=[$t0])"
+ + " EnumerableHashJoin(condition=[=($2,$6)],joinType=[inner])"
+ + "
EnumerableCalc(expr#0..1=[{inputs}],expr#2=[CAST($t0):VARCHAR],proj#0..2=[{exprs}])"
+ + "
EnumerableInterpreterBindableTableScan(table=[[federate_jdbc,t_user_info]],filters=[[=(CAST($0):INTEGER,13)]])"
+ + "
EnumerableCalc(expr#0..2=[{inputs}],expr#3=[CAST($t1):VARCHAR],proj#0..3=[{exprs}])"
+ + "
EnumerableTableScan(table=[[federate_jdbc,t_order_federate]])";
assertThat(actual.replaceAll("\\s*", ""),
is(expected.replaceAll("\\s*", "")));
}
@@ -133,8 +143,9 @@ public final class FederateJDBCExecutorTest {
DatabaseTypeRegistry.getTrunkDatabaseTypeName(new
H2DatabaseType()), new ConfigurationProperties(new Properties()));
SQLStatement sqlStatement =
sqlParserEngine.parse(SELECT_WHERE_ALL_FIELDS, false);
String actual = optimizer.optimize(schemaName, sqlStatement).explain();
- String expected = "EnumerableInterpreter"
- +
"BindableTableScan(table=[[federate_jdbc,t_user_info]],filters=[[=(CAST($0):INTEGER,12)]])";
+ String expected =
+ "EnumerableInterpreter"
+ + "
BindableTableScan(table=[[federate_jdbc,t_user_info]],filters=[[=(CAST($0):INTEGER,12)]])";
assertThat(actual.replaceAll("\\s*", ""),
is(expected.replaceAll("\\s*", "")));
}
@@ -144,8 +155,9 @@ public final class FederateJDBCExecutorTest {
DatabaseTypeRegistry.getTrunkDatabaseTypeName(new
H2DatabaseType()), new ConfigurationProperties(new Properties()));
SQLStatement sqlStatement =
sqlParserEngine.parse(SELECT_WHERE_SINGLE_FIELD, false);
String actual = optimizer.optimize(schemaName, sqlStatement).explain();
- String expected = "EnumerableInterpreter"
- +
"BindableTableScan(table=[[federate_jdbc,t_user_info]],filters=[[=(CAST($0):INTEGER,12)]],projects=[[0]])";
+ String expected =
+ "EnumerableInterpreter"
+ + "
BindableTableScan(table=[[federate_jdbc,t_user_info]],filters=[[=(CAST($0):INTEGER,12)]],projects=[[0]])";
assertThat(actual.replaceAll("\\s*", ""),
is(expected.replaceAll("\\s*", "")));
}
@@ -155,10 +167,15 @@ public final class FederateJDBCExecutorTest {
DatabaseTypeRegistry.getTrunkDatabaseTypeName(new
H2DatabaseType()), new ConfigurationProperties(new Properties()));
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])"
- + "
BindableTableScan(table=[[federate_jdbc,t_order_federate]],projects=[[0,1]])"
- + "
BindableTableScan(table=[[federate_jdbc,t_user_info]],projects=[[0]])";
+ String expected =
+
"EnumerableCalc(expr#0..6=[{inputs}],order_id=[$t3],user_id=[$t4],user_id0=[$t0])"
+ + " EnumerableMergeJoin(condition=[=($2,$6)],joinType=[inner])"
+ + " EnumerableSort(sort0=[$2],dir0=[ASC])"
+ + "
EnumerableCalc(expr#0..1=[{inputs}],expr#2=[CAST($t0):VARCHAR],proj#0..2=[{exprs}])"
+ + "
EnumerableTableScan(table=[[federate_jdbc,t_user_info]])"
+ + " EnumerableSort(sort0=[$3],dir0=[ASC])"
+ + "
EnumerableCalc(expr#0..2=[{inputs}],expr#3=[CAST($t1):VARCHAR],proj#0..3=[{exprs}])"
+ + "
EnumerableTableScan(table=[[federate_jdbc,t_order_federate]])";
assertThat(actual.replaceAll("\\s*", ""),
is(expected.replaceAll("\\s*", "")));
}
@@ -168,12 +185,15 @@ public final class FederateJDBCExecutorTest {
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]])";
+ String expected =
+
"EnumerableCalc(expr#0..6=[{inputs}],proj#0..1=[{exprs}],user_id0=[$t4])"
+ + " EnumerableMergeJoin(condition=[=($3,$6)],joinType=[inner])"
+ + " EnumerableSort(sort0=[$3],dir0=[ASC])"
+ + "
EnumerableCalc(expr#0..2=[{inputs}],expr#3=[CAST($t1):VARCHAR],proj#0..3=[{exprs}])"
+ + "
EnumerableTableScan(table=[[federate_jdbc,t_order_federate]])"
+ + " EnumerableSort(sort0=[$2],dir0=[ASC])"
+ + "
EnumerableCalc(expr#0..1=[{inputs}],expr#2=[CAST($t0):VARCHAR],proj#0..2=[{exprs}])"
+ + "
EnumerableTableScan(table=[[federate_jdbc,t_user_info]])";
assertThat(actual.replaceAll("\\s*", ""),
is(expected.replaceAll("\\s*", "")));
}
@@ -183,10 +203,11 @@ public final class FederateJDBCExecutorTest {
DatabaseTypeRegistry.getTrunkDatabaseTypeName(new
H2DatabaseType()), new ConfigurationProperties(new Properties()));
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])"
- + "
BindableTableScan(table=[[federate_jdbc,t_order_federate]],projects=[[0,1]])"
- + "
BindableTableScan(table=[[federate_jdbc,t_user_info]],filters=[[=(CAST($0):INTEGER,13)]],projects=[[0]])";
+ String expected =
+
"EnumerableCalc(expr#0..4=[{inputs}],proj#0..1=[{exprs}],user_id0=[$t3])"
+ + "
EnumerableInterpreterBindableJoin(condition=[=(CAST($1):VARCHAR,CAST($3):VARCHAR)],joinType=[inner])"
+ + " BindableTableScan(table=[[federate_jdbc,t_order_federate]])"
+ + "
BindableTableScan(table=[[federate_jdbc,t_user_info]],filters=[[=(CAST($0):INTEGER,13)]])";
assertThat(actual.replaceAll("\\s*", ""),
is(expected.replaceAll("\\s*", "")));
}
@@ -196,8 +217,9 @@ public final class FederateJDBCExecutorTest {
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)]])";
+ String expected =
+ "EnumerableInterpreter"
+ + "
BindableTableScan(table=[[federate_jdbc,t_user_info]],filters=[[>(CAST($0):INTEGER,1)]])";
assertThat(actual.replaceAll("\\s*", ""),
is(expected.replaceAll("\\s*", "")));
}
@@ -210,10 +232,44 @@ public final class FederateJDBCExecutorTest {
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]])
"
+ + "
EnumerableTableScan(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*", "")));
}
+
+ @Test
+ public void assertSelectSubQueryWhereIn() {
+ ShardingSphereSQLParserEngine sqlParserEngine = new
ShardingSphereSQLParserEngine(
+ DatabaseTypeRegistry.getTrunkDatabaseTypeName(new
H2DatabaseType()), new ConfigurationProperties(new Properties()));
+ SQLStatement sqlStatement =
sqlParserEngine.parse(SELECT_SUBQUERY_WHERE_IN, false);
+ String actual = optimizer.optimize(schemaName, sqlStatement).explain();
+ String expected =
+ "EnumerableInterpreter"
+ + " BindableProject(order_id=[$0],user_id=[$1])"
+ + " BindableJoin(condition=[=($1,$3)],joinType=[semi])"
+ + "
BindableTableScan(table=[[federate_jdbc,t_order_federate]])"
+ + "
BindableTableScan(table=[[federate_jdbc,t_user_info]],projects=[[0]])";
+ assertThat(actual.replaceAll("\\s*", ""),
is(expected.replaceAll("\\s*", "")));
+ }
+
+ @Test
+ public void assertSelectSubQueryWhereBetween() {
+ ShardingSphereSQLParserEngine sqlParserEngine = new
ShardingSphereSQLParserEngine(
+ DatabaseTypeRegistry.getTrunkDatabaseTypeName(new
H2DatabaseType()), new ConfigurationProperties(new Properties()));
+ SQLStatement sqlStatement =
sqlParserEngine.parse(SELECT_SUBQUERY_WHERE_BETWEEN, false);
+ String actual = optimizer.optimize(schemaName, sqlStatement).explain();
+ String expected =
+ "EnumerableCalc(expr#0..4=[{inputs}],proj#0..1=[{exprs}])"
+ + "
EnumerableInterpreterBindableFilter(condition=[AND(>=($1,$3),<=($1,$4))])"
+ + " BindableJoin(condition=[true],joinType=[left])"
+ + " BindableJoin(condition=[true],joinType=[left])"
+ + "
BindableTableScan(table=[[federate_jdbc,t_order_federate]])"
+ + " BindableAggregate(group=[{}],agg#0=[SINGLE_VALUE($0)])"
+ + "
BindableTableScan(table=[[federate_jdbc,t_user_info]],filters=[[=(CAST($1):VARCHAR,'before')]],projects=[[0]])"
+ + " BindableAggregate(group=[{}],agg#0=[SINGLE_VALUE($0)])"
+ + "
BindableTableScan(table=[[federate_jdbc,t_user_info]],filters=[[=(CAST($1):VARCHAR,'after')]],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 ee68d02..d7285e0 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
@@ -19,12 +19,11 @@ package org.apache.shardingsphere.infra.optimize.planner;
import lombok.AccessLevel;
import lombok.NoArgsConstructor;
-import org.apache.calcite.adapter.enumerable.EnumerableRules;
-import org.apache.calcite.interpreter.Bindables;
import org.apache.calcite.plan.ConventionTraitDef;
import org.apache.calcite.plan.RelOptPlanner;
+import org.apache.calcite.plan.RelOptUtil;
import org.apache.calcite.plan.volcano.VolcanoPlanner;
-import org.apache.calcite.rel.rules.CoreRules;
+import org.apache.calcite.rel.RelCollationTraitDef;
/**
* Query optimize planner factory.
@@ -49,27 +48,7 @@ public final class QueryOptimizePlannerFactory {
private static void setUpRules(final RelOptPlanner planner) {
planner.addRelTraitDef(ConventionTraitDef.INSTANCE);
- planner.addRule(EnumerableRules.TO_INTERPRETER);
- planner.addRule(Bindables.FROM_NONE_RULE);
- planner.addRule(Bindables.BINDABLE_TABLE_SCAN_RULE);
- planner.addRule(Bindables.BINDABLE_FILTER_RULE);
- planner.addRule(Bindables.BINDABLE_PROJECT_RULE);
- planner.addRule(Bindables.BINDABLE_SORT_RULE);
- planner.addRule(Bindables.BINDABLE_JOIN_RULE);
- planner.addRule(Bindables.BINDABLE_SET_OP_RULE);
- planner.addRule(Bindables.BINDABLE_VALUES_RULE);
- planner.addRule(Bindables.BINDABLE_AGGREGATE_RULE);
- planner.addRule(Bindables.BINDABLE_MATCH_RULE);
- planner.addRule(CoreRules.PROJECT_FILTER_TRANSPOSE);
- 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);
+ planner.addRelTraitDef(RelCollationTraitDef.INSTANCE);
+ RelOptUtil.registerDefaultRules(planner, false, true);
}
}
