This is an automated email from the ASF dual-hosted git repository.
zhangliang 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 b073e62 Use union all to merge sql route units for simple select
(#14172)
b073e62 is described below
commit b073e622d58c6c3d4b79295fff261184c50d8968
Author: Zhengqiang Duan <[email protected]>
AuthorDate: Mon Dec 20 22:52:29 2021 +0800
Use union all to merge sql route units for simple select (#14172)
* Use union all to merge sql route units for simple select
* add unit test
* fix unit test exception
* fix integration test
---
.../merge/dql/ShardingDQLResultMerger.java | 6 ++-
.../statement/dml/SelectStatementContext.java | 3 ++
.../table/FilterableTableScanExecutor.java | 6 +--
.../rewrite/engine/RouteSQLRewriteEngine.java | 57 +++++++++++++++++++++-
.../infra/rewrite/SQLRewriteEntryTest.java | 12 +++--
.../rewrite/engine/RouteSQLRewriteEngineTest.java | 26 ++++++++--
.../cases/ral/dataset/empty_rules/preview_sql.xml | 50 ++++---------------
.../mix/case/select_for_query_with_cipher.xml | 12 ++---
.../mix/case/select_for_query_with_plain.xml | 12 ++---
.../resources/scenario/sharding/case/select.xml | 21 +++-----
10 files changed, 123 insertions(+), 82 deletions(-)
diff --git
a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/merge/dql/ShardingDQLResultMerger.java
b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/merge/dql/ShardingDQLResultMerger.java
index 3c5544f..3441817 100644
---
a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/merge/dql/ShardingDQLResultMerger.java
+++
b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/merge/dql/ShardingDQLResultMerger.java
@@ -54,7 +54,7 @@ public final class ShardingDQLResultMerger implements
ResultMerger {
@Override
public MergedResult merge(final List<QueryResult> queryResults, final
SQLStatementContext<?> sqlStatementContext, final ShardingSphereSchema schema)
throws SQLException {
- if (1 == queryResults.size()) {
+ if (1 == queryResults.size() &&
!isNeedAggregateRewrite(sqlStatementContext)) {
return new IteratorStreamMergedResult(queryResults);
}
Map<String, Integer> columnLabelIndexMap =
getColumnLabelIndexMap(queryResults.get(0));
@@ -64,6 +64,10 @@ public final class ShardingDQLResultMerger implements
ResultMerger {
return decorate(queryResults, selectStatementContext, mergedResult);
}
+ private boolean isNeedAggregateRewrite(final SQLStatementContext<?>
sqlStatementContext) {
+ return sqlStatementContext instanceof SelectStatementContext &&
((SelectStatementContext) sqlStatementContext).isNeedAggregateRewrite();
+ }
+
private Map<String, Integer> getColumnLabelIndexMap(final QueryResult
queryResult) throws SQLException {
Map<String, Integer> result = new
TreeMap<>(String.CASE_INSENSITIVE_ORDER);
for (int i = queryResult.getMetaData().getColumnCount(); i > 0; i--) {
diff --git
a/shardingsphere-infra/shardingsphere-infra-binder/src/main/java/org/apache/shardingsphere/infra/binder/statement/dml/SelectStatementContext.java
b/shardingsphere-infra/shardingsphere-infra-binder/src/main/java/org/apache/shardingsphere/infra/binder/statement/dml/SelectStatementContext.java
index 98f51d1..5cc6e20 100644
---
a/shardingsphere-infra/shardingsphere-infra-binder/src/main/java/org/apache/shardingsphere/infra/binder/statement/dml/SelectStatementContext.java
+++
b/shardingsphere-infra/shardingsphere-infra-binder/src/main/java/org/apache/shardingsphere/infra/binder/statement/dml/SelectStatementContext.java
@@ -89,6 +89,9 @@ public final class SelectStatementContext extends
CommonSQLStatementContext<Sele
@Setter
private SubqueryType subqueryType;
+ @Setter
+ private boolean needAggregateRewrite;
+
public SelectStatementContext(final Map<String, ShardingSphereMetaData>
metaDataMap, final List<Object> parameters, final SelectStatement sqlStatement,
final String defaultSchemaName) {
super(sqlStatement);
subqueryContexts = createSubqueryContexts(metaDataMap, parameters,
defaultSchemaName);
diff --git
a/shardingsphere-infra/shardingsphere-infra-federation/shardingsphere-infra-federation-executor/src/main/java/org/apache/shardingsphere/infra/federation/executor/original/table/FilterableTableScanExecutor.java
b/shardingsphere-infra/shardingsphere-infra-federation/shardingsphere-infra-federation-executor/src/main/java/org/apache/shardingsphere/infra/federation/executor/original/table/FilterableTableScanExecutor.java
index 8c5ffc6..622b2c5 100644
---
a/shardingsphere-infra/shardingsphere-infra-federation/shardingsphere-infra-federation-executor/src/main/java/org/apache/shardingsphere/infra/federation/executor/original/table/FilterableTableScanExecutor.java
+++
b/shardingsphere-infra/shardingsphere-infra-federation/shardingsphere-infra-federation-executor/src/main/java/org/apache/shardingsphere/infra/federation/executor/original/table/FilterableTableScanExecutor.java
@@ -142,7 +142,7 @@ public final class FilterableTableScanExecutor {
ExecutionContext context = new
KernelProcessor().generateExecutionContext(logicSQL, metaData, props);
try {
ExecutionGroupContext<JDBCExecutionUnit> executionGroupContext =
prepareEngine.prepare(context.getRouteContext(), context.getExecutionUnits());
- setParameters(executionGroupContext.getInputGroups(),
logicSQL.getParameters());
+ setParameters(executionGroupContext.getInputGroups());
ExecuteProcessEngine.initialize(context.getLogicSQL(),
executionGroupContext, props);
List<QueryResult> result =
jdbcExecutor.execute(executionGroupContext, callback).stream().map(each ->
(QueryResult) each).collect(Collectors.toList());
ExecuteProcessEngine.finish(executionGroupContext.getExecutionID());
@@ -162,13 +162,13 @@ public final class FilterableTableScanExecutor {
}
@SneakyThrows
- private void setParameters(final
Collection<ExecutionGroup<JDBCExecutionUnit>> inputGroups, final List<Object>
parameters) {
+ private void setParameters(final
Collection<ExecutionGroup<JDBCExecutionUnit>> inputGroups) {
for (ExecutionGroup<JDBCExecutionUnit> each : inputGroups) {
for (JDBCExecutionUnit executionUnit : each.getInputs()) {
if (!(executionUnit.getStorageResource() instanceof
PreparedStatement)) {
continue;
}
- setParameters((PreparedStatement)
executionUnit.getStorageResource(), parameters);
+ setParameters((PreparedStatement)
executionUnit.getStorageResource(),
executionUnit.getExecutionUnit().getSqlUnit().getParameters());
}
}
}
diff --git
a/shardingsphere-infra/shardingsphere-infra-rewrite/src/main/java/org/apache/shardingsphere/infra/rewrite/engine/RouteSQLRewriteEngine.java
b/shardingsphere-infra/shardingsphere-infra-rewrite/src/main/java/org/apache/shardingsphere/infra/rewrite/engine/RouteSQLRewriteEngine.java
index e02c297..5789ce1 100644
---
a/shardingsphere-infra/shardingsphere-infra-rewrite/src/main/java/org/apache/shardingsphere/infra/rewrite/engine/RouteSQLRewriteEngine.java
+++
b/shardingsphere-infra/shardingsphere-infra-rewrite/src/main/java/org/apache/shardingsphere/infra/rewrite/engine/RouteSQLRewriteEngine.java
@@ -17,6 +17,8 @@
package org.apache.shardingsphere.infra.rewrite.engine;
+import org.apache.shardingsphere.infra.binder.statement.SQLStatementContext;
+import
org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementContext;
import org.apache.shardingsphere.infra.datanode.DataNode;
import org.apache.shardingsphere.infra.rewrite.context.SQLRewriteContext;
import
org.apache.shardingsphere.infra.rewrite.engine.result.RouteSQLRewriteResult;
@@ -27,12 +29,15 @@ import
org.apache.shardingsphere.infra.rewrite.parameter.builder.impl.StandardPa
import org.apache.shardingsphere.infra.rewrite.sql.impl.RouteSQLBuilder;
import org.apache.shardingsphere.infra.route.context.RouteContext;
import org.apache.shardingsphere.infra.route.context.RouteUnit;
+import org.apache.shardingsphere.sql.parser.sql.common.util.SQLUtil;
+import
org.apache.shardingsphere.sql.parser.sql.dialect.handler.dml.SelectStatementHandler;
import java.util.Collection;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
+import java.util.Map.Entry;
/**
* Route SQL rewrite engine.
@@ -48,12 +53,60 @@ public final class RouteSQLRewriteEngine {
*/
public RouteSQLRewriteResult rewrite(final SQLRewriteContext
sqlRewriteContext, final RouteContext routeContext) {
Map<RouteUnit, SQLRewriteUnit> result = new
LinkedHashMap<>(routeContext.getRouteUnits().size(), 1);
- for (RouteUnit each : routeContext.getRouteUnits()) {
- result.put(each, new SQLRewriteUnit(new
RouteSQLBuilder(sqlRewriteContext, each).toSQL(),
getParameters(sqlRewriteContext.getParameterBuilder(), routeContext, each)));
+ for (Entry<String, Collection<RouteUnit>> entry :
aggregateRouteUnitGroups(routeContext.getRouteUnits()).entrySet()) {
+ Collection<RouteUnit> routeUnits = entry.getValue();
+ if
(isNeedAggregateRewrite(sqlRewriteContext.getSqlStatementContext(),
routeUnits)) {
+ result.put(routeUnits.iterator().next(),
createSQLRewriteUnit(sqlRewriteContext, routeContext, routeUnits));
+ } else {
+ result.putAll(createSQLRewriteUnits(sqlRewriteContext,
routeContext, routeUnits));
+ }
}
return new RouteSQLRewriteResult(result);
}
+ private SQLRewriteUnit createSQLRewriteUnit(final SQLRewriteContext
sqlRewriteContext, final RouteContext routeContext, final Collection<RouteUnit>
routeUnits) {
+ Collection<String> sql = new LinkedList<>();
+ List<Object> parameters = new LinkedList<>();
+ for (RouteUnit each : routeUnits) {
+ sql.add(SQLUtil.trimSemicolon(new
RouteSQLBuilder(sqlRewriteContext, each).toSQL()));
+
parameters.addAll(getParameters(sqlRewriteContext.getParameterBuilder(),
routeContext, each));
+ }
+ return new SQLRewriteUnit(String.join(" UNION ALL ", sql), parameters);
+ }
+
+ private Map<RouteUnit, SQLRewriteUnit> createSQLRewriteUnits(final
SQLRewriteContext sqlRewriteContext, final RouteContext routeContext, final
Collection<RouteUnit> routeUnits) {
+ Map<RouteUnit, SQLRewriteUnit> result = new
LinkedHashMap<>(routeContext.getRouteUnits().size(), 1);
+ for (RouteUnit each : routeUnits) {
+ result.put(each, new SQLRewriteUnit(new
RouteSQLBuilder(sqlRewriteContext, each).toSQL(),
getParameters(sqlRewriteContext.getParameterBuilder(), routeContext, each)));
+ }
+ return result;
+ }
+
+ private boolean isNeedAggregateRewrite(final SQLStatementContext<?>
sqlStatementContext, final Collection<RouteUnit> routeUnits) {
+ if (!(sqlStatementContext instanceof SelectStatementContext) ||
routeUnits.size() == 1) {
+ return false;
+ }
+ SelectStatementContext statementContext = (SelectStatementContext)
sqlStatementContext;
+ boolean containsSubqueryJoinQuery =
statementContext.isContainsSubquery() || statementContext.isContainsJoinQuery();
+ boolean containsOrderByLimitClause =
!statementContext.getOrderByContext().getItems().isEmpty() ||
statementContext.getPaginationContext().isHasPagination();
+ boolean containsLockClause =
SelectStatementHandler.getLockSegment(statementContext.getSqlStatement()).isPresent();
+ boolean needAggregateRewrite = !containsSubqueryJoinQuery &&
!containsOrderByLimitClause && !containsLockClause;
+ statementContext.setNeedAggregateRewrite(needAggregateRewrite);
+ return needAggregateRewrite;
+ }
+
+ private Map<String, Collection<RouteUnit>> aggregateRouteUnitGroups(final
Collection<RouteUnit> routeUnits) {
+ Map<String, Collection<RouteUnit>> result = new
LinkedHashMap<>(routeUnits.size(), 1);
+ for (RouteUnit each : routeUnits) {
+ String dataSourceName = each.getDataSourceMapper().getActualName();
+ if (!result.containsKey(dataSourceName)) {
+ result.put(dataSourceName, new LinkedList<>());
+ }
+ result.get(dataSourceName).add(each);
+ }
+ return result;
+ }
+
private List<Object> getParameters(final ParameterBuilder
parameterBuilder, final RouteContext routeContext, final RouteUnit routeUnit) {
if (parameterBuilder instanceof StandardParameterBuilder) {
return parameterBuilder.getParameters();
diff --git
a/shardingsphere-infra/shardingsphere-infra-rewrite/src/test/java/org/apache/shardingsphere/infra/rewrite/SQLRewriteEntryTest.java
b/shardingsphere-infra/shardingsphere-infra-rewrite/src/test/java/org/apache/shardingsphere/infra/rewrite/SQLRewriteEntryTest.java
index eaeebec..4974a8e 100644
---
a/shardingsphere-infra/shardingsphere-infra-rewrite/src/test/java/org/apache/shardingsphere/infra/rewrite/SQLRewriteEntryTest.java
+++
b/shardingsphere-infra/shardingsphere-infra-rewrite/src/test/java/org/apache/shardingsphere/infra/rewrite/SQLRewriteEntryTest.java
@@ -17,13 +17,14 @@
package org.apache.shardingsphere.infra.rewrite;
+import org.apache.shardingsphere.infra.binder.statement.SQLStatementContext;
import
org.apache.shardingsphere.infra.config.properties.ConfigurationProperties;
+import org.apache.shardingsphere.infra.metadata.schema.ShardingSphereSchema;
import
org.apache.shardingsphere.infra.rewrite.engine.result.GenericSQLRewriteResult;
import
org.apache.shardingsphere.infra.rewrite.engine.result.RouteSQLRewriteResult;
import org.apache.shardingsphere.infra.route.context.RouteContext;
+import org.apache.shardingsphere.infra.route.context.RouteMapper;
import org.apache.shardingsphere.infra.route.context.RouteUnit;
-import org.apache.shardingsphere.infra.metadata.schema.ShardingSphereSchema;
-import org.apache.shardingsphere.infra.binder.statement.SQLStatementContext;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.mockito.Mock;
@@ -35,6 +36,7 @@ import java.util.Collections;
import static org.hamcrest.CoreMatchers.is;
import static org.junit.Assert.assertThat;
import static org.mockito.Mockito.mock;
+import static org.mockito.Mockito.when;
@RunWith(MockitoJUnitRunner.class)
public final class SQLRewriteEntryTest {
@@ -58,7 +60,11 @@ public final class SQLRewriteEntryTest {
public void assertRewriteForRouteSQLRewriteResult() {
SQLRewriteEntry sqlRewriteEntry = new SQLRewriteEntry(schema, props,
Collections.emptyList());
RouteContext routeContext = new RouteContext();
-
routeContext.getRouteUnits().addAll(Arrays.asList(mock(RouteUnit.class),
mock(RouteUnit.class)));
+ RouteUnit firstRouteUnit = mock(RouteUnit.class);
+ when(firstRouteUnit.getDataSourceMapper()).thenReturn(new
RouteMapper("ds", "ds_0"));
+ RouteUnit secondRouteUnit = mock(RouteUnit.class);
+ when(secondRouteUnit.getDataSourceMapper()).thenReturn(new
RouteMapper("ds", "ds_1"));
+ routeContext.getRouteUnits().addAll(Arrays.asList(firstRouteUnit,
secondRouteUnit));
RouteSQLRewriteResult sqlRewriteResult = (RouteSQLRewriteResult)
sqlRewriteEntry.rewrite("SELECT ?", Collections.singletonList(1),
mock(SQLStatementContext.class), routeContext);
assertThat(sqlRewriteResult.getSqlRewriteUnits().size(), is(2));
}
diff --git
a/shardingsphere-infra/shardingsphere-infra-rewrite/src/test/java/org/apache/shardingsphere/infra/rewrite/engine/RouteSQLRewriteEngineTest.java
b/shardingsphere-infra/shardingsphere-infra-rewrite/src/test/java/org/apache/shardingsphere/infra/rewrite/engine/RouteSQLRewriteEngineTest.java
index 84f84f8..fbb5380 100644
---
a/shardingsphere-infra/shardingsphere-infra-rewrite/src/test/java/org/apache/shardingsphere/infra/rewrite/engine/RouteSQLRewriteEngineTest.java
+++
b/shardingsphere-infra/shardingsphere-infra-rewrite/src/test/java/org/apache/shardingsphere/infra/rewrite/engine/RouteSQLRewriteEngineTest.java
@@ -17,22 +17,25 @@
package org.apache.shardingsphere.infra.rewrite.engine;
+import org.apache.shardingsphere.infra.binder.statement.SQLStatementContext;
+import
org.apache.shardingsphere.infra.binder.statement.dml.InsertStatementContext;
+import
org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementContext;
import org.apache.shardingsphere.infra.datanode.DataNode;
+import org.apache.shardingsphere.infra.metadata.schema.ShardingSphereSchema;
import org.apache.shardingsphere.infra.rewrite.context.SQLRewriteContext;
import
org.apache.shardingsphere.infra.rewrite.engine.result.RouteSQLRewriteResult;
import org.apache.shardingsphere.infra.route.context.RouteContext;
import org.apache.shardingsphere.infra.route.context.RouteMapper;
import org.apache.shardingsphere.infra.route.context.RouteUnit;
-import org.apache.shardingsphere.infra.metadata.schema.ShardingSphereSchema;
-import org.apache.shardingsphere.infra.binder.statement.SQLStatementContext;
-import
org.apache.shardingsphere.infra.binder.statement.dml.InsertStatementContext;
import org.junit.Test;
+import java.util.Arrays;
import java.util.Collections;
import static org.hamcrest.CoreMatchers.is;
import static org.junit.Assert.assertThat;
import static org.junit.Assert.assertTrue;
+import static org.mockito.Mockito.RETURNS_DEEP_STUBS;
import static org.mockito.Mockito.mock;
import static org.mockito.Mockito.when;
@@ -51,6 +54,23 @@ public final class RouteSQLRewriteEngineTest {
}
@Test
+ public void
assertRewriteWithStandardParameterBuilderWhenNeedAggregateRewrite() {
+ SelectStatementContext statementContext =
mock(SelectStatementContext.class, RETURNS_DEEP_STUBS);
+
when(statementContext.getOrderByContext().getItems()).thenReturn(Collections.emptyList());
+
when(statementContext.getPaginationContext().isHasPagination()).thenReturn(false);
+ SQLRewriteContext sqlRewriteContext = new
SQLRewriteContext(mock(ShardingSphereSchema.class), statementContext, "SELECT
?", Collections.singletonList(1));
+ RouteContext routeContext = new RouteContext();
+ RouteUnit firstRouteUnit = new RouteUnit(new RouteMapper("ds",
"ds_0"), Collections.singletonList(new RouteMapper("tbl", "tbl_0")));
+ RouteUnit secondRouteUnit = new RouteUnit(new RouteMapper("ds",
"ds_0"), Collections.singletonList(new RouteMapper("tbl", "tbl_1")));
+ routeContext.getRouteUnits().add(firstRouteUnit);
+ routeContext.getRouteUnits().add(secondRouteUnit);
+ RouteSQLRewriteResult actual = new
RouteSQLRewriteEngine().rewrite(sqlRewriteContext, routeContext);
+ assertThat(actual.getSqlRewriteUnits().size(), is(1));
+ assertThat(actual.getSqlRewriteUnits().get(firstRouteUnit).getSql(),
is("SELECT ? UNION ALL SELECT ?"));
+
assertThat(actual.getSqlRewriteUnits().get(firstRouteUnit).getParameters(),
is(Arrays.asList(1, 1)));
+ }
+
+ @Test
public void assertRewriteWithGroupedParameterBuilderForBroadcast() {
InsertStatementContext statementContext =
mock(InsertStatementContext.class);
when(statementContext.getGroupedParameters()).thenReturn(Collections.singletonList(Collections.singletonList(1)));
diff --git
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/ral/dataset/empty_rules/preview_sql.xml
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/ral/dataset/empty_rules/preview_sql.xml
index 7426d5e..f5af2bf 100644
---
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/ral/dataset/empty_rules/preview_sql.xml
+++
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/ral/dataset/empty_rules/preview_sql.xml
@@ -20,44 +20,14 @@
<column name="data_source_name"/>
<column name="sql"/>
</metadata>
- <row values="encrypt_write_ds_0| select * from t_user_item_0"/>
- <row values="encrypt_write_ds_0| select * from t_user_item_10"/>
- <row values="encrypt_write_ds_0| select * from t_user_item_20"/>
- <row values="encrypt_write_ds_0| select * from t_user_item_30"/>
- <row values="encrypt_write_ds_1| select * from t_user_item_1"/>
- <row values="encrypt_write_ds_1| select * from t_user_item_11"/>
- <row values="encrypt_write_ds_1| select * from t_user_item_21"/>
- <row values="encrypt_write_ds_1| select * from t_user_item_31"/>
- <row values="encrypt_write_ds_2| select * from t_user_item_2"/>
- <row values="encrypt_write_ds_2| select * from t_user_item_12"/>
- <row values="encrypt_write_ds_2| select * from t_user_item_22"/>
- <row values="encrypt_write_ds_2| select * from t_user_item_32"/>
- <row values="encrypt_write_ds_3| select * from t_user_item_3"/>
- <row values="encrypt_write_ds_3| select * from t_user_item_13"/>
- <row values="encrypt_write_ds_3| select * from t_user_item_23"/>
- <row values="encrypt_write_ds_3| select * from t_user_item_33"/>
- <row values="encrypt_write_ds_4| select * from t_user_item_4"/>
- <row values="encrypt_write_ds_4| select * from t_user_item_14"/>
- <row values="encrypt_write_ds_4| select * from t_user_item_24"/>
- <row values="encrypt_write_ds_4| select * from t_user_item_34"/>
- <row values="encrypt_write_ds_5| select * from t_user_item_5"/>
- <row values="encrypt_write_ds_5| select * from t_user_item_15"/>
- <row values="encrypt_write_ds_5| select * from t_user_item_25"/>
- <row values="encrypt_write_ds_5| select * from t_user_item_35"/>
- <row values="encrypt_write_ds_6| select * from t_user_item_6"/>
- <row values="encrypt_write_ds_6| select * from t_user_item_16"/>
- <row values="encrypt_write_ds_6| select * from t_user_item_26"/>
- <row values="encrypt_write_ds_6| select * from t_user_item_36"/>
- <row values="encrypt_write_ds_7| select * from t_user_item_7"/>
- <row values="encrypt_write_ds_7| select * from t_user_item_17"/>
- <row values="encrypt_write_ds_7| select * from t_user_item_27"/>
- <row values="encrypt_write_ds_7| select * from t_user_item_37"/>
- <row values="encrypt_write_ds_8| select * from t_user_item_8"/>
- <row values="encrypt_write_ds_8| select * from t_user_item_18"/>
- <row values="encrypt_write_ds_8| select * from t_user_item_28"/>
- <row values="encrypt_write_ds_8| select * from t_user_item_38"/>
- <row values="encrypt_write_ds_9| select * from t_user_item_9"/>
- <row values="encrypt_write_ds_9| select * from t_user_item_19"/>
- <row values="encrypt_write_ds_9| select * from t_user_item_29"/>
- <row values="encrypt_write_ds_9| select * from t_user_item_39"/>
+ <row values="encrypt_write_ds_0| select * from t_user_item_0 UNION ALL
select * from t_user_item_10 UNION ALL select * from t_user_item_20 UNION ALL
select * from t_user_item_30"/>
+ <row values="encrypt_write_ds_1| select * from t_user_item_1 UNION ALL
select * from t_user_item_11 UNION ALL select * from t_user_item_21 UNION ALL
select * from t_user_item_31"/>
+ <row values="encrypt_write_ds_2| select * from t_user_item_2 UNION ALL
select * from t_user_item_12 UNION ALL select * from t_user_item_22 UNION ALL
select * from t_user_item_32"/>
+ <row values="encrypt_write_ds_3| select * from t_user_item_3 UNION ALL
select * from t_user_item_13 UNION ALL select * from t_user_item_23 UNION ALL
select * from t_user_item_33"/>
+ <row values="encrypt_write_ds_4| select * from t_user_item_4 UNION ALL
select * from t_user_item_14 UNION ALL select * from t_user_item_24 UNION ALL
select * from t_user_item_34"/>
+ <row values="encrypt_write_ds_5| select * from t_user_item_5 UNION ALL
select * from t_user_item_15 UNION ALL select * from t_user_item_25 UNION ALL
select * from t_user_item_35"/>
+ <row values="encrypt_write_ds_6| select * from t_user_item_6 UNION ALL
select * from t_user_item_16 UNION ALL select * from t_user_item_26 UNION ALL
select * from t_user_item_36"/>
+ <row values="encrypt_write_ds_7| select * from t_user_item_7 UNION ALL
select * from t_user_item_17 UNION ALL select * from t_user_item_27 UNION ALL
select * from t_user_item_37"/>
+ <row values="encrypt_write_ds_8| select * from t_user_item_8 UNION ALL
select * from t_user_item_18 UNION ALL select * from t_user_item_28 UNION ALL
select * from t_user_item_38"/>
+ <row values="encrypt_write_ds_9| select * from t_user_item_9 UNION ALL
select * from t_user_item_19 UNION ALL select * from t_user_item_29 UNION ALL
select * from t_user_item_39"/>
</dataset>
diff --git
a/shardingsphere-test/shardingsphere-rewrite-test/src/test/resources/scenario/mix/case/select_for_query_with_cipher.xml
b/shardingsphere-test/shardingsphere-rewrite-test/src/test/resources/scenario/mix/case/select_for_query_with_cipher.xml
index b2e8f52..a179ad2 100644
---
a/shardingsphere-test/shardingsphere-rewrite-test/src/test/resources/scenario/mix/case/select_for_query_with_cipher.xml
+++
b/shardingsphere-test/shardingsphere-rewrite-test/src/test/resources/scenario/mix/case/select_for_query_with_cipher.xml
@@ -44,14 +44,12 @@
<rewrite-assertion id="select_with_unqualified_shorthand" db-types="MySQL">
<input sql="SELECT * FROM t_account" />
- <output sql="SELECT `t_account_0`.`account_id`,
`t_account_0`.`cipher_password` AS `password`, `t_account_0`.`cipher_amount` AS
`amount`, `t_account_0`.`status` FROM t_account_0" />
- <output sql="SELECT `t_account_1`.`account_id`,
`t_account_1`.`cipher_password` AS `password`, `t_account_1`.`cipher_amount` AS
`amount`, `t_account_1`.`status` FROM t_account_1" />
+ <output sql="SELECT `t_account_0`.`account_id`,
`t_account_0`.`cipher_password` AS `password`, `t_account_0`.`cipher_amount` AS
`amount`, `t_account_0`.`status` FROM t_account_0 UNION ALL SELECT
`t_account_1`.`account_id`, `t_account_1`.`cipher_password` AS `password`,
`t_account_1`.`cipher_amount` AS `amount`, `t_account_1`.`status` FROM
t_account_1" />
</rewrite-assertion>
<rewrite-assertion id="select_with_qualified_shorthand" db-types="MySQL">
<input sql="SELECT a.* FROM t_account a" />
- <output sql="SELECT `a`.`account_id`, `a`.`cipher_password` AS
`password`, `a`.`cipher_amount` AS `amount`, `a`.`status` FROM t_account_0 a" />
- <output sql="SELECT `a`.`account_id`, `a`.`cipher_password` AS
`password`, `a`.`cipher_amount` AS `amount`, `a`.`status` FROM t_account_1 a" />
+ <output sql="SELECT `a`.`account_id`, `a`.`cipher_password` AS
`password`, `a`.`cipher_amount` AS `amount`, `a`.`status` FROM t_account_0 a
UNION ALL SELECT `a`.`account_id`, `a`.`cipher_password` AS `password`,
`a`.`cipher_amount` AS `amount`, `a`.`status` FROM t_account_1 a" />
</rewrite-assertion>
<rewrite-assertion
id="select_with_sharding_qualified_shorthand_join_table" db-types="MySQL">
@@ -88,13 +86,11 @@
<rewrite-assertion
id="select_with_mix_qualified_shorthand_and_other_projection" db-types="MySQL">
<input sql="SELECT a.*, account_id, 1+1 FROM t_account a" />
- <output sql="SELECT `a`.`account_id`, `a`.`cipher_password` AS
`password`, `a`.`cipher_amount` AS `amount`, `a`.`status`, account_id, 1+1 FROM
t_account_0 a" />
- <output sql="SELECT `a`.`account_id`, `a`.`cipher_password` AS
`password`, `a`.`cipher_amount` AS `amount`, `a`.`status`, account_id, 1+1 FROM
t_account_1 a" />
+ <output sql="SELECT `a`.`account_id`, `a`.`cipher_password` AS
`password`, `a`.`cipher_amount` AS `amount`, `a`.`status`, account_id, 1+1 FROM
t_account_0 a UNION ALL SELECT `a`.`account_id`, `a`.`cipher_password` AS
`password`, `a`.`cipher_amount` AS `amount`, `a`.`status`, account_id, 1+1 FROM
t_account_1 a" />
</rewrite-assertion>
<rewrite-assertion id="select_with_table_qualified_shorthand"
db-types="MySQL">
<input sql="SELECT t_account.* FROM t_account" />
- <output sql="SELECT `t_account_0`.`account_id`,
`t_account_0`.`cipher_password` AS `password`, `t_account_0`.`cipher_amount` AS
`amount`, `t_account_0`.`status` FROM t_account_0" />
- <output sql="SELECT `t_account_1`.`account_id`,
`t_account_1`.`cipher_password` AS `password`, `t_account_1`.`cipher_amount` AS
`amount`, `t_account_1`.`status` FROM t_account_1" />
+ <output sql="SELECT `t_account_0`.`account_id`,
`t_account_0`.`cipher_password` AS `password`, `t_account_0`.`cipher_amount` AS
`amount`, `t_account_0`.`status` FROM t_account_0 UNION ALL SELECT
`t_account_1`.`account_id`, `t_account_1`.`cipher_password` AS `password`,
`t_account_1`.`cipher_amount` AS `amount`, `t_account_1`.`status` FROM
t_account_1" />
</rewrite-assertion>
</rewrite-assertions>
diff --git
a/shardingsphere-test/shardingsphere-rewrite-test/src/test/resources/scenario/mix/case/select_for_query_with_plain.xml
b/shardingsphere-test/shardingsphere-rewrite-test/src/test/resources/scenario/mix/case/select_for_query_with_plain.xml
index 57c7cbc..671f790 100644
---
a/shardingsphere-test/shardingsphere-rewrite-test/src/test/resources/scenario/mix/case/select_for_query_with_plain.xml
+++
b/shardingsphere-test/shardingsphere-rewrite-test/src/test/resources/scenario/mix/case/select_for_query_with_plain.xml
@@ -29,25 +29,21 @@
<rewrite-assertion id="select_with_unqualified_shorthand" db-types="MySQL">
<input sql="SELECT * FROM t_account_bak" />
- <output sql="SELECT `t_account_bak_0`.`account_id`,
`t_account_bak_0`.`plain_password` AS `password`,
`t_account_bak_0`.`plain_amount` AS `amount`, `t_account_bak_0`.`status` FROM
t_account_bak_0" />
- <output sql="SELECT `t_account_bak_1`.`account_id`,
`t_account_bak_1`.`plain_password` AS `password`,
`t_account_bak_1`.`plain_amount` AS `amount`, `t_account_bak_1`.`status` FROM
t_account_bak_1" />
+ <output sql="SELECT `t_account_bak_0`.`account_id`,
`t_account_bak_0`.`plain_password` AS `password`,
`t_account_bak_0`.`plain_amount` AS `amount`, `t_account_bak_0`.`status` FROM
t_account_bak_0 UNION ALL SELECT `t_account_bak_1`.`account_id`,
`t_account_bak_1`.`plain_password` AS `password`,
`t_account_bak_1`.`plain_amount` AS `amount`, `t_account_bak_1`.`status` FROM
t_account_bak_1" />
</rewrite-assertion>
<rewrite-assertion id="select_with_qualified_shorthand" db-types="MySQL">
<input sql="SELECT a.* FROM t_account_bak a" />
- <output sql="SELECT `a`.`account_id`, `a`.`plain_password` AS
`password`, `a`.`plain_amount` AS `amount`, `a`.`status` FROM t_account_bak_0
a" />
- <output sql="SELECT `a`.`account_id`, `a`.`plain_password` AS
`password`, `a`.`plain_amount` AS `amount`, `a`.`status` FROM t_account_bak_1
a" />
+ <output sql="SELECT `a`.`account_id`, `a`.`plain_password` AS
`password`, `a`.`plain_amount` AS `amount`, `a`.`status` FROM t_account_bak_0 a
UNION ALL SELECT `a`.`account_id`, `a`.`plain_password` AS `password`,
`a`.`plain_amount` AS `amount`, `a`.`status` FROM t_account_bak_1 a" />
</rewrite-assertion>
<rewrite-assertion
id="select_with_mix_qualified_shorthand_and_other_projection" db-types="MySQL">
<input sql="SELECT a.*, account_id, 1+1 FROM t_account_bak a" />
- <output sql="SELECT `a`.`account_id`, `a`.`plain_password` AS
`password`, `a`.`plain_amount` AS `amount`, `a`.`status`, account_id, 1+1 FROM
t_account_bak_0 a" />
- <output sql="SELECT `a`.`account_id`, `a`.`plain_password` AS
`password`, `a`.`plain_amount` AS `amount`, `a`.`status`, account_id, 1+1 FROM
t_account_bak_1 a" />
+ <output sql="SELECT `a`.`account_id`, `a`.`plain_password` AS
`password`, `a`.`plain_amount` AS `amount`, `a`.`status`, account_id, 1+1 FROM
t_account_bak_0 a UNION ALL SELECT `a`.`account_id`, `a`.`plain_password` AS
`password`, `a`.`plain_amount` AS `amount`, `a`.`status`, account_id, 1+1 FROM
t_account_bak_1 a" />
</rewrite-assertion>
<rewrite-assertion id="select_with_table_qualified_shorthand"
db-types="MySQL">
<input sql="SELECT t_account_bak.* FROM t_account_bak" />
- <output sql="SELECT `t_account_bak_0`.`account_id`,
`t_account_bak_0`.`plain_password` AS `password`,
`t_account_bak_0`.`plain_amount` AS `amount`, `t_account_bak_0`.`status` FROM
t_account_bak_0" />
- <output sql="SELECT `t_account_bak_1`.`account_id`,
`t_account_bak_1`.`plain_password` AS `password`,
`t_account_bak_1`.`plain_amount` AS `amount`, `t_account_bak_1`.`status` FROM
t_account_bak_1" />
+ <output sql="SELECT `t_account_bak_0`.`account_id`,
`t_account_bak_0`.`plain_password` AS `password`,
`t_account_bak_0`.`plain_amount` AS `amount`, `t_account_bak_0`.`status` FROM
t_account_bak_0 UNION ALL SELECT `t_account_bak_1`.`account_id`,
`t_account_bak_1`.`plain_password` AS `password`,
`t_account_bak_1`.`plain_amount` AS `amount`, `t_account_bak_1`.`status` FROM
t_account_bak_1" />
</rewrite-assertion>
</rewrite-assertions>
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..6695477 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
@@ -64,8 +64,7 @@
<rewrite-assertion id="select_with_schema">
<input sql="SELECT * FROM sharding_db.t_account" />
- <output sql="SELECT * FROM t_account_0"/>
- <output sql="SELECT * FROM t_account_1"/>
+ <output sql="SELECT * FROM t_account_0 UNION ALL SELECT * FROM
t_account_1"/>
</rewrite-assertion>
<rewrite-assertion id="select_with_subquery" db-types="MySQL">
@@ -105,14 +104,12 @@
<rewrite-assertion id="select_without_sharding_value_for_parameters">
<input sql="SELECT * FROM sharding_db.t_account WHERE amount = ?"
parameters="1000" />
- <output sql="SELECT * FROM t_account_0 WHERE amount = ?"
parameters="1000" />
- <output sql="SELECT * FROM t_account_1 WHERE amount = ?"
parameters="1000" />
+ <output sql="SELECT * FROM t_account_0 WHERE amount = ? UNION ALL
SELECT * FROM t_account_1 WHERE amount = ?" parameters="1000, 1000" />
</rewrite-assertion>
<rewrite-assertion id="select_without_sharding_value_for_literals">
<input sql="SELECT * FROM sharding_db.t_account WHERE amount = 1000" />
- <output sql="SELECT * FROM t_account_0 WHERE amount = 1000" />
- <output sql="SELECT * FROM t_account_1 WHERE amount = 1000" />
+ <output sql="SELECT * FROM t_account_0 WHERE amount = 1000 UNION ALL
SELECT * FROM t_account_1 WHERE amount = 1000" />
</rewrite-assertion>
<rewrite-assertion id="select_for_literals_with_order_by" db-types="MySQL">
@@ -162,8 +159,7 @@
<rewrite-assertion id="select_avg_with_multiple_route">
<input sql="SELECT AVG(amount) FROM t_account WHERE amount = ?"
parameters="1000" />
- <output sql="SELECT AVG(amount) , COUNT(amount) AS AVG_DERIVED_COUNT_0
, SUM(amount) AS AVG_DERIVED_SUM_0 FROM t_account_0 WHERE amount = ?"
parameters="1000" />
- <output sql="SELECT AVG(amount) , COUNT(amount) AS AVG_DERIVED_COUNT_0
, SUM(amount) AS AVG_DERIVED_SUM_0 FROM t_account_1 WHERE amount = ?"
parameters="1000" />
+ <output sql="SELECT AVG(amount) , COUNT(amount) AS AVG_DERIVED_COUNT_0
, SUM(amount) AS AVG_DERIVED_SUM_0 FROM t_account_0 WHERE amount = ? UNION ALL
SELECT AVG(amount) , COUNT(amount) AS AVG_DERIVED_COUNT_0 , SUM(amount) AS
AVG_DERIVED_SUM_0 FROM t_account_1 WHERE amount = ?" parameters="1000, 1000" />
</rewrite-assertion>
<rewrite-assertion id="select_distinct_with_single_route">
@@ -173,14 +169,12 @@
<rewrite-assertion id="select_distinct_with_alias_with_multiple_route">
<input sql="SELECT COUNT(DISTINCT account_id) a, SUM(DISTINCT
account_id) b FROM t_account" />
- <output sql="SELECT DISTINCT account_id a, account_id b FROM
t_account_0" />
- <output sql="SELECT DISTINCT account_id a, account_id b FROM
t_account_1" />
+ <output sql="SELECT DISTINCT account_id a, account_id b FROM
t_account_0 UNION ALL SELECT DISTINCT account_id a, account_id b FROM
t_account_1" />
</rewrite-assertion>
<rewrite-assertion id="select_distinct_without_alias_with_multiple_route">
<input sql="SELECT COUNT(DISTINCT account_id), SUM(DISTINCT
account_id) FROM t_account" />
- <output sql="SELECT DISTINCT account_id AS
AGGREGATION_DISTINCT_DERIVED_0, account_id AS AGGREGATION_DISTINCT_DERIVED_1
FROM t_account_0" />
- <output sql="SELECT DISTINCT account_id AS
AGGREGATION_DISTINCT_DERIVED_0, account_id AS AGGREGATION_DISTINCT_DERIVED_1
FROM t_account_1" />
+ <output sql="SELECT DISTINCT account_id AS
AGGREGATION_DISTINCT_DERIVED_0, account_id AS AGGREGATION_DISTINCT_DERIVED_1
FROM t_account_0 UNION ALL SELECT DISTINCT account_id AS
AGGREGATION_DISTINCT_DERIVED_0, account_id AS AGGREGATION_DISTINCT_DERIVED_1
FROM t_account_1" />
</rewrite-assertion>
<rewrite-assertion id="select_account_by_with_single_route">
@@ -498,8 +492,7 @@
<rewrite-assertion id="select_with_or_and_condition">
<input sql="SELECT * FROM t_account WHERE amount=? OR amount=? AND
account_id=?" parameters="1, 2, 3"/>
- <output sql="SELECT * FROM t_account_0 WHERE amount=? OR amount=? AND
account_id=?" parameters="1, 2, 3"/>
- <output sql="SELECT * FROM t_account_1 WHERE amount=? OR amount=? AND
account_id=?" parameters="1, 2, 3"/>
+ <output sql="SELECT * FROM t_account_0 WHERE amount=? OR amount=? AND
account_id=? UNION ALL SELECT * FROM t_account_1 WHERE amount=? OR amount=? AND
account_id=?" parameters="1, 2, 3, 1, 2, 3"/>
</rewrite-assertion>
<rewrite-assertion id="select_multi_nested_subquery_with_binding_tables"
db-types="MySQL,PostgreSQL,openGauss,SQLServer,SQL92">
