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 6a29dba optimize binding table route logic without sharding column in
join condition (#13882)
6a29dba is described below
commit 6a29dba09a3bebeb145260b7c4312e769ced2d45
Author: Zhengqiang Duan <[email protected]>
AuthorDate: Wed Dec 1 21:01:01 2021 +0800
optimize binding table route logic without sharding column in join
condition (#13882)
---
.../engine/type/ShardingRouteEngineFactory.java | 115 ++++++++++++++++++---
.../type/complex/ShardingComplexRoutingEngine.java | 4 +-
.../shardingsphere/sharding/rule/ShardingRule.java | 9 +-
.../complex/ShardingComplexRoutingEngineTest.java | 12 +--
.../dql/dataset/encrypt/select_join_encrypt.xml | 3 -
.../cases/dql/dql-integration-test-cases.xml | 10 +-
.../resources/scenario/sharding/case/select.xml | 1 +
7 files changed, 124 insertions(+), 30 deletions(-)
diff --git
a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/route/engine/type/ShardingRouteEngineFactory.java
b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/route/engine/type/ShardingRouteEngineFactory.java
index 917df12..cc9e2a9 100644
---
a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/route/engine/type/ShardingRouteEngineFactory.java
+++
b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/route/engine/type/ShardingRouteEngineFactory.java
@@ -19,12 +19,16 @@ package
org.apache.shardingsphere.sharding.route.engine.type;
import lombok.AccessLevel;
import lombok.NoArgsConstructor;
+import
org.apache.shardingsphere.infra.binder.segment.select.projection.impl.ColumnProjection;
import org.apache.shardingsphere.infra.binder.statement.SQLStatementContext;
import
org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementContext;
import org.apache.shardingsphere.infra.binder.type.TableAvailable;
import
org.apache.shardingsphere.infra.config.properties.ConfigurationProperties;
import
org.apache.shardingsphere.infra.config.properties.ConfigurationPropertyKey;
import org.apache.shardingsphere.infra.metadata.ShardingSphereMetaData;
+import org.apache.shardingsphere.infra.metadata.schema.ShardingSphereSchema;
+import
org.apache.shardingsphere.sharding.api.config.strategy.sharding.ShardingStrategyConfiguration;
+import
org.apache.shardingsphere.sharding.api.config.strategy.sharding.StandardShardingStrategyConfiguration;
import
org.apache.shardingsphere.sharding.route.engine.condition.ShardingCondition;
import
org.apache.shardingsphere.sharding.route.engine.condition.ShardingConditions;
import
org.apache.shardingsphere.sharding.route.engine.condition.value.ShardingConditionValue;
@@ -38,6 +42,12 @@ import
org.apache.shardingsphere.sharding.route.engine.type.ignore.ShardingIgnor
import
org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine;
import
org.apache.shardingsphere.sharding.route.engine.type.unicast.ShardingUnicastRoutingEngine;
import org.apache.shardingsphere.sharding.rule.ShardingRule;
+import org.apache.shardingsphere.sharding.rule.TableRule;
+import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.column.ColumnSegment;
+import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.BinaryOperationExpression;
+import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.ExpressionSegment;
+import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.predicate.AndPredicate;
+import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.predicate.WhereSegment;
import org.apache.shardingsphere.sql.parser.sql.common.statement.SQLStatement;
import
org.apache.shardingsphere.sql.parser.sql.common.statement.dal.AnalyzeTableStatement;
import
org.apache.shardingsphere.sql.parser.sql.common.statement.dal.DALStatement;
@@ -58,13 +68,20 @@ import
org.apache.shardingsphere.sql.parser.sql.common.statement.ddl.DropTablesp
import
org.apache.shardingsphere.sql.parser.sql.common.statement.dml.DMLStatement;
import
org.apache.shardingsphere.sql.parser.sql.common.statement.dml.SelectStatement;
import
org.apache.shardingsphere.sql.parser.sql.common.statement.tcl.TCLStatement;
+import
org.apache.shardingsphere.sql.parser.sql.common.util.ExpressionExtractUtil;
+import org.apache.shardingsphere.sql.parser.sql.common.util.WhereExtractUtil;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.mysql.dal.MySQLCreateResourceGroupStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.mysql.dal.MySQLOptimizeTableStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.mysql.dal.MySQLSetResourceGroupStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.mysql.dal.MySQLShowDatabasesStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.mysql.dal.MySQLUseStatement;
+import java.util.Arrays;
import java.util.Collection;
+import java.util.HashSet;
+import java.util.LinkedList;
+import java.util.Map;
+import java.util.Optional;
import java.util.stream.Collectors;
/**
@@ -73,6 +90,8 @@ import java.util.stream.Collectors;
@NoArgsConstructor(access = AccessLevel.PRIVATE)
public final class ShardingRouteEngineFactory {
+ private static final String EQUAL = "=";
+
/**
* Create new instance of routing engine.
*
@@ -98,7 +117,7 @@ public final class ShardingRouteEngineFactory {
if (sqlStatement instanceof DCLStatement) {
return getDCLRoutingEngine(shardingRule, metaData,
sqlStatementContext);
}
- return getDQLRoutingEngine(shardingRule, sqlStatementContext,
shardingConditions, props);
+ return getDQLRoutingEngine(shardingRule, metaData.getSchema(),
sqlStatementContext, shardingConditions, props);
}
private static ShardingRouteEngine getDDLRoutingEngine(final ShardingRule
shardingRule, final ShardingSphereMetaData metaData, final
SQLStatementContext<?> sqlStatementContext) {
@@ -175,7 +194,7 @@ public final class ShardingRouteEngineFactory {
return false;
}
- private static ShardingRouteEngine getDQLRoutingEngine(final ShardingRule
shardingRule, final SQLStatementContext<?> sqlStatementContext,
+ private static ShardingRouteEngine getDQLRoutingEngine(final ShardingRule
shardingRule, final ShardingSphereSchema schema, final SQLStatementContext<?>
sqlStatementContext,
final
ShardingConditions shardingConditions, final ConfigurationProperties props) {
Collection<String> tableNames =
sqlStatementContext.getTablesContext().getTableNames();
if (shardingRule.isAllBroadcastTables(tableNames)) {
@@ -188,19 +207,20 @@ public final class ShardingRouteEngineFactory {
if (shardingLogicTableNames.isEmpty()) {
return new ShardingIgnoreRoutingEngine();
}
- return getDQLRouteEngineForShardingTable(shardingRule,
sqlStatementContext, shardingConditions, shardingLogicTableNames, props);
+ return getDQLRouteEngineForShardingTable(shardingRule, schema,
sqlStatementContext, shardingConditions, props, shardingLogicTableNames);
}
- private static ShardingRouteEngine getDQLRouteEngineForShardingTable(final
ShardingRule shardingRule, final SQLStatementContext<?> sqlStatementContext,
- final
ShardingConditions shardingConditions, final Collection<String> tableNames,
final ConfigurationProperties props) {
- if (isShardingFederatedQuery(sqlStatementContext, tableNames,
shardingRule, shardingConditions, props)) {
+ private static ShardingRouteEngine getDQLRouteEngineForShardingTable(final
ShardingRule shardingRule, final ShardingSphereSchema schema, final
SQLStatementContext<?> sqlStatementContext,
+ final
ShardingConditions shardingConditions, final ConfigurationProperties props,
final Collection<String> tableNames) {
+ boolean allBindingTables = tableNames.size() > 1 &&
isAllBindingTables(shardingRule, schema, sqlStatementContext, tableNames);
+ if (isShardingFederatedQuery(shardingRule, sqlStatementContext,
shardingConditions, props, tableNames, allBindingTables)) {
return new ShardingFederatedRoutingEngine(tableNames);
}
- if (isShardingStandardQuery(tableNames, shardingRule)) {
+ if (isShardingStandardQuery(shardingRule, tableNames,
allBindingTables)) {
return new
ShardingStandardRoutingEngine(getLogicTableName(shardingConditions,
tableNames), shardingConditions, props);
}
// TODO config for cartesian set
- return new ShardingComplexRoutingEngine(tableNames,
shardingConditions, props);
+ return new ShardingComplexRoutingEngine(shardingConditions, props,
tableNames);
}
private static String getLogicTableName(final ShardingConditions
shardingConditions, final Collection<String> tableNames) {
@@ -212,12 +232,12 @@ public final class ShardingRouteEngineFactory {
return tableNames.iterator().next();
}
- private static boolean isShardingStandardQuery(final Collection<String>
tableNames, final ShardingRule shardingRule) {
- return 1 == tableNames.size() &&
shardingRule.isAllShardingTables(tableNames) ||
shardingRule.isAllBindingTables(tableNames);
+ private static boolean isShardingStandardQuery(final ShardingRule
shardingRule, final Collection<String> tableNames, final boolean
allBindingTables) {
+ return 1 == tableNames.size() &&
shardingRule.isAllShardingTables(tableNames) || allBindingTables;
}
- private static boolean isShardingFederatedQuery(final
SQLStatementContext<?> sqlStatementContext, final Collection<String>
tableNames,
- final ShardingRule
shardingRule, final ShardingConditions shardingConditions, final
ConfigurationProperties props) {
+ private static boolean isShardingFederatedQuery(final ShardingRule
shardingRule, final SQLStatementContext<?> sqlStatementContext, final
ShardingConditions shardingConditions,
+ final
ConfigurationProperties props, final Collection<String> tableNames, final
boolean allBindingTables) {
boolean sqlFederationEnabled =
props.getValue(ConfigurationPropertyKey.SQL_FEDERATION_ENABLED);
if (!sqlFederationEnabled || !(sqlStatementContext instanceof
SelectStatementContext)) {
return false;
@@ -232,6 +252,75 @@ public final class ShardingRouteEngineFactory {
if (!select.isContainsJoinQuery() ||
shardingRule.isAllTablesInSameDataSource(tableNames)) {
return false;
}
- return tableNames.size() > 1 &&
!shardingRule.isAllBindingTables(tableNames);
+ return tableNames.size() > 1 && !allBindingTables;
+ }
+
+ private static boolean isAllBindingTables(final ShardingRule shardingRule,
final ShardingSphereSchema schema,
+ final SQLStatementContext<?>
sqlStatementContext, final Collection<String> tableNames) {
+ if (!(sqlStatementContext instanceof SelectStatementContext)) {
+ return shardingRule.isAllBindingTables(tableNames);
+ }
+ return shardingRule.isAllBindingTables(tableNames) &&
isJoinConditionContainsShardingColumns(shardingRule, schema,
(SelectStatementContext) sqlStatementContext, tableNames);
+ }
+
+ private static boolean isJoinConditionContainsShardingColumns(final
ShardingRule shardingRule, final ShardingSphereSchema schema,
+ final
SelectStatementContext select, final Collection<String> tableNames) {
+ Collection<String> databaseJoinConditionTables = new
HashSet<>(tableNames.size());
+ Collection<String> tableJoinConditionTables = new
HashSet<>(tableNames.size());
+ for (WhereSegment each :
WhereExtractUtil.getJoinWhereSegments(select.getSqlStatement())) {
+ Collection<AndPredicate> andPredicates =
ExpressionExtractUtil.getAndPredicates(each.getExpr());
+ if (andPredicates.size() > 1) {
+ return false;
+ }
+ for (AndPredicate andPredicate : andPredicates) {
+
databaseJoinConditionTables.addAll(getJoinConditionTables(shardingRule, schema,
select, andPredicate.getPredicates(), true));
+
tableJoinConditionTables.addAll(getJoinConditionTables(shardingRule, schema,
select, andPredicate.getPredicates(), false));
+ }
+ }
+ TableRule tableRule =
shardingRule.getTableRule(tableNames.iterator().next());
+ boolean containsDatabaseShardingColumns =
!(tableRule.getDatabaseShardingStrategyConfig() instanceof
StandardShardingStrategyConfiguration)
+ || databaseJoinConditionTables.containsAll(tableNames);
+ boolean containsTableShardingColumns =
!(tableRule.getTableShardingStrategyConfig() instanceof
StandardShardingStrategyConfiguration) ||
tableJoinConditionTables.containsAll(tableNames);
+ return containsDatabaseShardingColumns && containsTableShardingColumns;
+ }
+
+ private static Collection<String> getJoinConditionTables(final
ShardingRule shardingRule, final ShardingSphereSchema schema, final
SelectStatementContext select,
+ final
Collection<ExpressionSegment> predicates, final boolean
isDatabaseJoinCondition) {
+ Collection<String> result = new LinkedList<>();
+ for (ExpressionSegment expression : predicates) {
+ if (!isJoinTableConditionExpression(expression)) {
+ continue;
+ }
+ ColumnProjection leftColumn =
buildColumnProjection((ColumnSegment) ((BinaryOperationExpression)
expression).getLeft());
+ ColumnProjection rightColumn =
buildColumnProjection((ColumnSegment) ((BinaryOperationExpression)
expression).getRight());
+ Map<String, String> columnTableNames =
select.getTablesContext().findTableName(Arrays.asList(leftColumn, rightColumn),
schema);
+ Optional<TableRule> leftTableRule =
shardingRule.findTableRule(columnTableNames.get(leftColumn.getExpression()));
+ Optional<TableRule> rightTableRule =
shardingRule.findTableRule(columnTableNames.get(rightColumn.getExpression()));
+ if (!leftTableRule.isPresent() || !rightTableRule.isPresent()) {
+ continue;
+ }
+ ShardingStrategyConfiguration leftConfiguration =
isDatabaseJoinCondition
+ ?
shardingRule.getDatabaseShardingStrategyConfiguration(leftTableRule.get()) :
shardingRule.getTableShardingStrategyConfiguration(leftTableRule.get());
+ ShardingStrategyConfiguration rightConfiguration =
isDatabaseJoinCondition
+ ?
shardingRule.getDatabaseShardingStrategyConfiguration(rightTableRule.get()) :
shardingRule.getTableShardingStrategyConfiguration(rightTableRule.get());
+ if (shardingRule.isShardingColumn(leftConfiguration,
leftColumn.getName()) && shardingRule.isShardingColumn(rightConfiguration,
rightColumn.getName())) {
+ result.add(columnTableNames.get(leftColumn.getExpression()));
+ result.add(columnTableNames.get(rightColumn.getExpression()));
+ }
+ }
+ return result;
+ }
+
+ private static ColumnProjection buildColumnProjection(final ColumnSegment
segment) {
+ String owner = segment.getOwner().map(optional ->
optional.getIdentifier().getValue()).orElse(null);
+ return new ColumnProjection(owner, segment.getIdentifier().getValue(),
null);
+ }
+
+ private static boolean isJoinTableConditionExpression(final
ExpressionSegment expression) {
+ if (!(expression instanceof BinaryOperationExpression)) {
+ return false;
+ }
+ BinaryOperationExpression binaryExpression =
(BinaryOperationExpression) expression;
+ return binaryExpression.getLeft() instanceof ColumnSegment &&
binaryExpression.getRight() instanceof ColumnSegment &&
EQUAL.equals(binaryExpression.getOperator());
}
}
diff --git
a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/route/engine/type/complex/ShardingComplexRoutingEngine.java
b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/route/engine/type/complex/ShardingComplexRoutingEngine.java
index f04c15c..f096952 100644
---
a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/route/engine/type/complex/ShardingComplexRoutingEngine.java
+++
b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/route/engine/type/complex/ShardingComplexRoutingEngine.java
@@ -38,11 +38,11 @@ import java.util.TreeSet;
@RequiredArgsConstructor
public final class ShardingComplexRoutingEngine implements ShardingRouteEngine
{
- private final Collection<String> logicTables;
-
private final ShardingConditions shardingConditions;
private final ConfigurationProperties props;
+
+ private final Collection<String> logicTables;
@Override
public RouteContext route(final ShardingRule shardingRule) {
diff --git
a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/rule/ShardingRule.java
b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/rule/ShardingRule.java
index 7f8ec33..ff4b9b9 100644
---
a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/rule/ShardingRule.java
+++
b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/rule/ShardingRule.java
@@ -366,7 +366,14 @@ public final class ShardingRule implements SchemaRule,
DataNodeContainedRule, Ta
return
isShardingColumn(getDatabaseShardingStrategyConfiguration(tableRule),
columnName) ||
isShardingColumn(getTableShardingStrategyConfiguration(tableRule), columnName);
}
- private boolean isShardingColumn(final ShardingStrategyConfiguration
shardingStrategyConfig, final String columnName) {
+ /**
+ * Judge whether given logic table column is sharding column or not.
+ *
+ * @param shardingStrategyConfig sharding strategy config
+ * @param columnName column name
+ * @return whether given logic table column is sharding column or not
+ */
+ public boolean isShardingColumn(final ShardingStrategyConfiguration
shardingStrategyConfig, final String columnName) {
if (shardingStrategyConfig instanceof
StandardShardingStrategyConfiguration) {
String shardingColumn = null ==
((StandardShardingStrategyConfiguration)
shardingStrategyConfig).getShardingColumn()
? defaultShardingColumn :
((StandardShardingStrategyConfiguration)
shardingStrategyConfig).getShardingColumn();
diff --git
a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/test/java/org/apache/shardingsphere/sharding/route/engine/type/complex/ShardingComplexRoutingEngineTest.java
b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/test/java/org/apache/shardingsphere/sharding/route/engine/type/complex/ShardingComplexRoutingEngineTest.java
index ee88a9b..2e325ee 100644
---
a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/test/java/org/apache/shardingsphere/sharding/route/engine/type/complex/ShardingComplexRoutingEngineTest.java
+++
b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/test/java/org/apache/shardingsphere/sharding/route/engine/type/complex/ShardingComplexRoutingEngineTest.java
@@ -39,8 +39,8 @@ public final class ShardingComplexRoutingEngineTest extends
AbstractRoutingEngin
@Test
public void assertRoutingForBindingTables() {
- ShardingComplexRoutingEngine complexRoutingEngine = new
ShardingComplexRoutingEngine(Arrays.asList("t_order", "t_order_item"),
- createShardingConditions("t_order"), new
ConfigurationProperties(new Properties()));
+ ShardingComplexRoutingEngine complexRoutingEngine = new
ShardingComplexRoutingEngine(createShardingConditions("t_order"),
+ new ConfigurationProperties(new Properties()),
Arrays.asList("t_order", "t_order_item"));
RouteContext routeContext =
complexRoutingEngine.route(createBindingShardingRule());
List<RouteUnit> routeUnits = new
ArrayList<>(routeContext.getRouteUnits());
assertThat(routeContext.getRouteUnits().size(), is(1));
@@ -52,8 +52,8 @@ public final class ShardingComplexRoutingEngineTest extends
AbstractRoutingEngin
@Test
public void assertRoutingForShardingTableJoinBroadcastTable() {
- ShardingComplexRoutingEngine complexRoutingEngine = new
ShardingComplexRoutingEngine(Arrays.asList("t_order", "t_config"),
- createShardingConditions("t_order"), new
ConfigurationProperties(new Properties()));
+ ShardingComplexRoutingEngine complexRoutingEngine = new
ShardingComplexRoutingEngine(createShardingConditions("t_order"),
+ new ConfigurationProperties(new Properties()),
Arrays.asList("t_order", "t_config"));
RouteContext routeContext =
complexRoutingEngine.route(createBroadcastShardingRule());
List<RouteUnit> routeUnits = new
ArrayList<>(routeContext.getRouteUnits());
assertThat(routeContext.getRouteUnits().size(), is(1));
@@ -65,8 +65,8 @@ public final class ShardingComplexRoutingEngineTest extends
AbstractRoutingEngin
@Test(expected = ShardingSphereException.class)
public void assertRoutingForNonLogicTable() {
- ShardingComplexRoutingEngine complexRoutingEngine = new
ShardingComplexRoutingEngine(Collections.emptyList(),
- createShardingConditions("t_order"), new
ConfigurationProperties(new Properties()));
+ ShardingComplexRoutingEngine complexRoutingEngine = new
ShardingComplexRoutingEngine(createShardingConditions("t_order"),
+ new ConfigurationProperties(new Properties()),
Collections.emptyList());
RouteContext routeContext =
complexRoutingEngine.route(mock(ShardingRule.class));
}
}
diff --git
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/dql/dataset/encrypt/select_join_encrypt.xml
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/dql/dataset/encrypt/select_join_encrypt.xml
index a105cb0..3556aa4 100644
---
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/dql/dataset/encrypt/select_join_encrypt.xml
+++
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/dql/dataset/encrypt/select_join_encrypt.xml
@@ -26,8 +26,5 @@
<column name="status" />
<column name="creation_date" />
</metadata>
- <row values="10, 10000, a10, init, 100000, 10, init, 2017-08-08" />
<row values="11, 11000, b11, init, 110000, 11, init, 2017-08-08" />
- <row values="12, 12000, c12, init, 120000, 12, init, 2017-08-08" />
- <row values="13, 13000, d13, init, 130000, 13, init, 2017-08-08" />
</dataset>
diff --git
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
index caf3b16..0eff84c 100644
---
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
+++
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
@@ -99,15 +99,15 @@
<assertion parameters="100001:int"
expected-data-file="select_alias_as_keyword.xml" />
</test-case>
- <test-case sql="SELECT i.* FROM t_order o FORCE INDEX(order_index) JOIN
t_order_item i ON o.order_id=i.order_id WHERE o.order_id = ?" db-types="MySQL"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <test-case sql="SELECT i.* FROM t_order o FORCE INDEX(order_index) JOIN
t_order_item i ON o.order_id=i.order_id AND o.user_id = i.user_id WHERE
o.order_id = ?" db-types="MySQL"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion parameters="1000:int"
expected-data-file="select_with_force_index_join.xml" />
</test-case>
- <test-case sql="SELECT i.* FROM t_order o FORCE INDEX(order_index) JOIN
t_order_item i ON o.order_id=i.order_id AND o.order_id = ?" db-types="MySQL"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <test-case sql="SELECT i.* FROM t_order o FORCE INDEX(order_index) JOIN
t_order_item i ON o.order_id=i.order_id AND o.user_id = i.user_id AND
o.order_id = ?" db-types="MySQL"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion parameters="1000:int"
expected-data-file="select_with_force_index_join.xml" />
</test-case>
- <test-case sql="SELECT i.* FROM t_order o FORCE INDEX(order_index) JOIN
t_order_item i ON o.order_id=i.order_id AND o.order_id in (?,?)"
db-types="MySQL"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <test-case sql="SELECT i.* FROM t_order o FORCE INDEX(order_index) JOIN
t_order_item i ON o.order_id=i.order_id AND o.user_id = i.user_id AND
o.order_id in (?,?)" db-types="MySQL"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion parameters="1000:int,1001:int"
expected-data-file="select_in_with_force_index_join.xml" />
</test-case>
@@ -487,7 +487,7 @@
<assertion expected-data-file="select_with_case_expression.xml" />
</test-case>
- <test-case sql="SELECT * FROM t_order o LEFT JOIN t_order_item m ON
o.order_id = m.order_id" db-types="MySQL,H2"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <test-case sql="SELECT * FROM t_order o LEFT JOIN t_order_item m ON
o.order_id = m.order_id AND o.user_id = m.user_id" db-types="MySQL,H2"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion expected-data-file="select_for_left_join.xml"/>
</test-case>
@@ -513,7 +513,7 @@
<assertion expected-data-file="select_in_or_encrypt.xml"/>
</test-case>
- <test-case sql="SELECT * FROM t_user u INNER JOIN t_user_item m ON
u.user_id=m.user_id"
scenario-types="encrypt,dbtbl_with_readwrite_splitting_and_encrypt">
+ <test-case sql="SELECT * FROM t_user u INNER JOIN t_user_item m ON
u.user_id=m.user_id WHERE u.user_id IN (0, 11, 22, 33, 44, 55, 66, 77, 88, 99)"
scenario-types="encrypt,dbtbl_with_readwrite_splitting_and_encrypt">
<assertion expected-data-file="select_join_encrypt.xml"/>
</test-case>
diff --git
a/shardingsphere-test/shardingsphere-rewrite-test/src/test/resources/scenario/sharding/case/select.xml
b/shardingsphere-test/shardingsphere-rewrite-test/src/test/resources/scenario/sharding/case/select.xml
index 7995f25..f0b0d22 100644
---
a/shardingsphere-test/shardingsphere-rewrite-test/src/test/resources/scenario/sharding/case/select.xml
+++
b/shardingsphere-test/shardingsphere-rewrite-test/src/test/resources/scenario/sharding/case/select.xml
@@ -504,6 +504,7 @@
<rewrite-assertion id="select_multi_nested_subquery_with_binding_tables"
db-types="MySQL,PostgreSQL,openGauss,SQLServer,SQL92">
<input sql="SELECT * FROM (SELECT id, content FROM t_user WHERE id = ?
AND content IN (SELECT content FROM t_user_extend WHERE user_id = ?)) AS temp"
parameters="1, 1"/>
+ <output sql="SELECT * FROM (SELECT id, content FROM t_user_0 WHERE id
= ? AND content IN (SELECT content FROM t_user_extend_0 WHERE user_id = ?)) AS
temp" parameters="1, 1"/>
<output sql="SELECT * FROM (SELECT id, content FROM t_user_1 WHERE id
= ? AND content IN (SELECT content FROM t_user_extend_1 WHERE user_id = ?)) AS
temp" parameters="1, 1"/>
</rewrite-assertion>
