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/06/18 08:33:24 UTC
[shardingsphere] branch master updated: support subquery
aggregation and partial distinct aggregation (#10856)
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 7e81aae support subquery aggregation and partial distinct aggregation (#10856)
7e81aae is described below
commit 7e81aaebb0baefe8a486ed5534b866d9146ca8b7
Author: Zhengqiang Duan <st...@gmail.com>
AuthorDate: Fri Jun 18 16:32:52 2021 +0800
support subquery aggregation and partial distinct aggregation (#10856)
* support subquery aggregation and partial distinct aggregation
* fix ut error
* fix subquery test error when engine logic change
* fix rewrite test error
---
.../engine/type/ShardingRouteEngineFactory.java | 18 +++--
.../dml/impl/ShardingSelectStatementValidator.java | 2 +-
.../engine/type/standard/AbstractSQLRouteTest.java | 8 ++-
.../engine/type/standard/SubqueryRouteTest.java | 28 ++++----
.../statement/dml/SelectStatementContext.java | 36 ++++++++--
.../statement/impl/SelectStatementContextTest.java | 2 +
.../statement/FederatePrepareStatementTest.java | 78 ++++++++++++++++++++++
.../jdbc/core/statement/FederateStatementTest.java | 75 +++++++++++++++++++++
.../resources/scenario/sharding/case/select.xml | 2 +-
9 files changed, 219 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 d06b785..a5addf3 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
@@ -169,7 +169,7 @@ public final class ShardingRouteEngineFactory {
if (!shardingRule.tableRuleExists(tableNames)) {
return new SingleTablesRoutingEngine(tableNames, sqlStatement);
}
- if (isShardingStandardQuery(sqlStatementContext, shardingRule, tableNames)) {
+ if (isShardingStandardQuery(sqlStatementContext, tableNames, shardingRule)) {
return new ShardingStandardRoutingEngine(tableNames.iterator().next(), shardingConditions, props);
}
if (isShardingFederatedQuery(sqlStatementContext, tableNames, shardingRule)) {
@@ -179,9 +179,9 @@ public final class ShardingRouteEngineFactory {
return new ShardingComplexRoutingEngine(tableNames, shardingConditions, props);
}
- private static boolean isShardingStandardQuery(final SQLStatementContext<?> sqlStatementContext, final ShardingRule shardingRule, final Collection<String> tableNames) {
- boolean containsHaving = sqlStatementContext instanceof SelectStatementContext && ((SelectStatementContext) sqlStatementContext).isContainsHaving();
- return (shardingRule.isAllBindingTables(tableNames) || shardingRule.isAllShardingTables(tableNames) && 1 == tableNames.size()) && !containsHaving;
+ private static boolean isShardingStandardQuery(final SQLStatementContext<?> sqlStatementContext, final Collection<String> tableNames, final ShardingRule shardingRule) {
+ boolean needExecuteByCalcite = sqlStatementContext instanceof SelectStatementContext && isNeedExecuteByCalcite((SelectStatementContext) sqlStatementContext);
+ return (shardingRule.isAllBindingTables(tableNames) || shardingRule.isAllShardingTables(tableNames) && 1 == tableNames.size()) && !needExecuteByCalcite;
}
private static boolean isShardingFederatedQuery(final SQLStatementContext<?> sqlStatementContext, final Collection<String> tableNames, final ShardingRule shardingRule) {
@@ -189,12 +189,16 @@ public final class ShardingRouteEngineFactory {
return false;
}
SelectStatementContext select = (SelectStatementContext) sqlStatementContext;
- if (!select.isContainsJoinQuery() && !select.isContainsSubquery() && !select.isContainsHaving()) {
- return false;
+ if (isNeedExecuteByCalcite(select)) {
+ return true;
}
- if (shardingRule.isAllTablesInSameDataSource(tableNames) && !select.isContainsHaving()) {
+ if ((!select.isContainsJoinQuery() && !select.isContainsSubquery()) || shardingRule.isAllTablesInSameDataSource(tableNames)) {
return false;
}
return shardingRule.isAllShardingTables(tableNames) || (shardingRule.tableRuleExists(tableNames) && shardingRule.singleTableRuleExists(tableNames));
}
+
+ private static boolean isNeedExecuteByCalcite(final SelectStatementContext select) {
+ return select.isContainsHaving() || select.isContainsSubqueyAggregation() || select.isContainsPartialDistinctAggregation();
+ }
}
diff --git a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/route/engine/validator/dml/impl/ShardingSelectStatementValidator.java b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/route/engine/validator/dml/impl/ShardingSelectStatementValidator.java
index 98fdf13..44e0ec4 100644
--- a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/route/engine/validator/dml/impl/ShardingSelectStatementValidator.java
+++ b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/route/engine/validator/dml/impl/ShardingSelectStatementValidator.java
@@ -45,7 +45,7 @@ public final class ShardingSelectStatementValidator extends ShardingDMLStatement
@Override
public void postValidate(final ShardingRule shardingRule, final SQLStatementContext<SelectStatement> sqlStatementContext,
final RouteContext routeContext, final ShardingSphereSchema schema) {
- if (needCheckDatabaseInstance) {
+ if (!routeContext.isFederated() && needCheckDatabaseInstance) {
Preconditions.checkState(routeContext.isSingleRouting(), "Sharding value must same with subquery.");
}
}
diff --git a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/test/java/org/apache/shardingsphere/sharding/route/engine/type/standard/AbstractSQLRouteTest.java b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/test/java/org/apache/shardingsphere/sharding/route/engine/type/standard/AbstractSQLRouteTest.java
index d3f4334..b57e0de 100644
--- a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/test/java/org/apache/shardingsphere/sharding/route/engine/type/standard/AbstractSQLRouteTest.java
+++ b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/test/java/org/apache/shardingsphere/sharding/route/engine/type/standard/AbstractSQLRouteTest.java
@@ -24,8 +24,8 @@ import org.apache.shardingsphere.infra.config.properties.ConfigurationProperties
import org.apache.shardingsphere.infra.metadata.ShardingSphereMetaData;
import org.apache.shardingsphere.infra.metadata.resource.ShardingSphereResource;
import org.apache.shardingsphere.infra.metadata.rule.ShardingSphereRuleMetaData;
-import org.apache.shardingsphere.infra.metadata.schema.model.ColumnMetaData;
import org.apache.shardingsphere.infra.metadata.schema.ShardingSphereSchema;
+import org.apache.shardingsphere.infra.metadata.schema.model.ColumnMetaData;
import org.apache.shardingsphere.infra.metadata.schema.model.TableMetaData;
import org.apache.shardingsphere.infra.parser.sql.SQLStatementParserEngine;
import org.apache.shardingsphere.infra.route.context.RouteContext;
@@ -49,6 +49,10 @@ import static org.mockito.Mockito.mock;
public abstract class AbstractSQLRouteTest extends AbstractRoutingEngineTest {
protected final RouteContext assertRoute(final String sql, final List<Object> parameters) {
+ return assertRoute(sql, parameters, 1);
+ }
+
+ protected final RouteContext assertRoute(final String sql, final List<Object> parameters, final int routeUnitSize) {
ShardingRule shardingRule = createAllShardingRule();
ShardingSphereSchema schema = buildSchema();
ConfigurationProperties props = new ConfigurationProperties(new Properties());
@@ -58,7 +62,7 @@ public abstract class AbstractSQLRouteTest extends AbstractRoutingEngineTest {
ShardingSphereRuleMetaData ruleMetaData = new ShardingSphereRuleMetaData(Collections.emptyList(), Collections.singleton(shardingRule));
ShardingSphereMetaData metaData = new ShardingSphereMetaData("sharding_db", mock(ShardingSphereResource.class, RETURNS_DEEP_STUBS), ruleMetaData, schema);
RouteContext result = new SQLRouteEngine(Collections.singletonList(shardingRule), props).route(logicSQL, metaData);
- assertThat(result.getRouteUnits().size(), is(1));
+ assertThat(result.getRouteUnits().size(), is(routeUnitSize));
return result;
}
diff --git a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/test/java/org/apache/shardingsphere/sharding/route/engine/type/standard/SubqueryRouteTest.java b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/test/java/org/apache/shardingsphere/sharding/route/engine/type/standard/SubqueryRouteTest.java
index 14714a9..c566b83 100644
--- a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/test/java/org/apache/shardingsphere/sharding/route/engine/type/standard/SubqueryRouteTest.java
+++ b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/test/java/org/apache/shardingsphere/sharding/route/engine/type/standard/SubqueryRouteTest.java
@@ -26,30 +26,30 @@ import java.util.List;
public final class SubqueryRouteTest extends AbstractSQLRouteTest {
@Test
- public void assertOneTableError() {
+ public void assertOneTableDifferentConditionWithFederate() {
String sql = "select (select max(id) from t_order b where b.user_id =? ) from t_order a where user_id = ? ";
List<Object> parameters = new LinkedList<>();
parameters.add(3);
parameters.add(2);
- assertRoute(sql, parameters);
+ assertRoute(sql, parameters, 2);
}
@Test
- public void assertOneTable() {
+ public void assertOneTableSameConditionWithFederate() {
String sql = "select (select max(id) from t_order b where b.user_id = ? and b.user_id = a.user_id) from t_order a where user_id = ? ";
List<Object> parameters = new LinkedList<>();
parameters.add(1);
parameters.add(1);
- assertRoute(sql, parameters);
+ assertRoute(sql, parameters, 2);
}
@Test
- public void assertBindingTable() {
+ public void assertBindingTableWithFederate() {
String sql = "select (select max(id) from t_order_item b where b.user_id = ?) from t_order a where user_id = ? ";
List<Object> parameters = new LinkedList<>();
parameters.add(1);
parameters.add(1);
- assertRoute(sql, parameters);
+ assertRoute(sql, parameters, 2);
}
@Test
@@ -62,33 +62,33 @@ public final class SubqueryRouteTest extends AbstractSQLRouteTest {
}
@Test
- public void assertBindingTableWithDifferentValue() {
+ public void assertBindingTableWithDifferentValueWithFederate() {
String sql = "select (select max(id) from t_order_item b where b.user_id = ? ) from t_order a where user_id = ? ";
List<Object> parameters = new LinkedList<>();
parameters.add(2);
parameters.add(3);
- assertRoute(sql, parameters);
+ assertRoute(sql, parameters, 2);
}
- @Test(expected = IllegalStateException.class)
- public void assertTwoTableWithDifferentOperator() {
+ @Test
+ public void assertTwoTableWithDifferentOperatorWithFederate() {
List<Object> parameters = new LinkedList<>();
parameters.add(1);
parameters.add(2);
parameters.add(1);
String sql = "select (select max(id) from t_order_item b where b.user_id in(?,?)) from t_order a where user_id = ? ";
- assertRoute(sql, parameters);
+ assertRoute(sql, parameters, 2);
}
- @Test(expected = IllegalStateException.class)
- public void assertTwoTableWithIn() {
+ @Test
+ public void assertTwoTableWithInWithFederate() {
List<Object> parameters = new LinkedList<>();
parameters.add(1);
parameters.add(2);
parameters.add(1);
parameters.add(3);
String sql = "select (select max(id) from t_order_item b where b.user_id in(?,?)) from t_order a where user_id in(?,?) ";
- assertRoute(sql, parameters);
+ assertRoute(sql, parameters, 2);
}
@Test
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 c4ba413..0e11ffa 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
@@ -36,6 +36,7 @@ import org.apache.shardingsphere.infra.binder.type.TableAvailable;
import org.apache.shardingsphere.infra.binder.type.WhereAvailable;
import org.apache.shardingsphere.infra.metadata.schema.ShardingSphereSchema;
import org.apache.shardingsphere.sql.parser.sql.common.extractor.TableExtractor;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.item.AggregationProjectionSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.order.item.ColumnOrderByItemSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.order.item.ExpressionOrderByItemSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.order.item.IndexOrderByItemSegment;
@@ -78,7 +79,7 @@ public final class SelectStatementContext extends CommonSQLStatementContext<Sele
private final int generateOrderByStartIndex;
- private final boolean containsHaving;
+ private final boolean containsSubqueyAggregation;
// TODO to be remove, for test case only
public SelectStatementContext(final SelectStatement sqlStatement, final GroupByContext groupByContext,
@@ -91,7 +92,7 @@ public final class SelectStatementContext extends CommonSQLStatementContext<Sele
this.paginationContext = paginationContext;
containsSubquery = containsSubquery();
generateOrderByStartIndex = generateOrderByStartIndex();
- containsHaving = sqlStatement.getHaving().isPresent();
+ containsSubqueyAggregation = containsSubqueyAggregation();
}
public SelectStatementContext(final ShardingSphereSchema schema, final List<Object> parameters, final SelectStatement sqlStatement) {
@@ -103,13 +104,18 @@ public final class SelectStatementContext extends CommonSQLStatementContext<Sele
paginationContext = new PaginationContextEngine().createPaginationContext(sqlStatement, projectionsContext, parameters);
containsSubquery = containsSubquery();
generateOrderByStartIndex = generateOrderByStartIndex();
- containsHaving = sqlStatement.getHaving().isPresent();
+ containsSubqueyAggregation = containsSubqueyAggregation();
}
private boolean containsSubquery() {
return !SubqueryExtractUtil.getSubquerySegments(getSqlStatement()).isEmpty();
}
+ private boolean containsSubqueyAggregation() {
+ return SubqueryExtractUtil.getSubquerySegments(getSqlStatement()).stream().flatMap(each
+ -> each.getSelect().getProjections().getProjections().stream()).anyMatch(each -> each instanceof AggregationProjectionSegment);
+ }
+
private int generateOrderByStartIndex() {
SelectStatement sqlStatement = getSqlStatement();
int stopIndex;
@@ -128,15 +134,35 @@ public final class SelectStatementContext extends CommonSQLStatementContext<Sele
}
/**
- * Whether it contain join query.
+ * Judge whether contains join query or not.
*
- * @return contain join query or not
+ * @return whether contains join query or not
*/
public boolean isContainsJoinQuery() {
return getSqlStatement().getFrom() instanceof JoinTableSegment;
}
/**
+ * Judge whether contains having or not.
+ *
+ * @return whether contains having or not
+ */
+ public boolean isContainsHaving() {
+ return getSqlStatement().getHaving().isPresent();
+ }
+
+ /**
+ * Judge whether contains partial distinct aggregation.
+ *
+ * @return whether contains partial distinct aggregation
+ */
+ public boolean isContainsPartialDistinctAggregation() {
+ Collection<Projection> aggregationProjections = projectionsContext.getProjections().stream().filter(each -> each instanceof AggregationProjection).collect(Collectors.toList());
+ return aggregationProjections.size() > 1 && projectionsContext.getAggregationDistinctProjections().size() > 0
+ && aggregationProjections.size() != projectionsContext.getAggregationDistinctProjections().size();
+ }
+
+ /**
* Set indexes.
*
* @param columnLabelIndexMap map for column label and index
diff --git a/shardingsphere-infra/shardingsphere-infra-binder/src/test/java/org/apache/shardingsphere/infra/binder/statement/impl/SelectStatementContextTest.java b/shardingsphere-infra/shardingsphere-infra-binder/src/test/java/org/apache/shardingsphere/infra/binder/statement/impl/SelectStatementContextTest.java
index 47d328b..135e7f0 100644
--- a/shardingsphere-infra/shardingsphere-infra-binder/src/test/java/org/apache/shardingsphere/infra/binder/statement/impl/SelectStatementContextTest.java
+++ b/shardingsphere-infra/shardingsphere-infra-binder/src/test/java/org/apache/shardingsphere/infra/binder/statement/impl/SelectStatementContextTest.java
@@ -416,6 +416,7 @@ public final class SelectStatementContextTest {
when(projectionSegment.getSubquery().getSelect().getWhere()).thenReturn(Optional.of(mock(WhereSegment.class)));
WhereSegment whereSegment = new WhereSegment(0, 0, null);
subSelectStatement.setWhere(whereSegment);
+ subSelectStatement.setProjections(new ProjectionsSegment(0, 0));
SubquerySegment subquerySegment = new SubquerySegment(0, 0, subSelectStatement);
when(projectionSegment.getSubquery()).thenReturn(subquerySegment);
ProjectionsSegment projectionsSegment = new ProjectionsSegment(0, 0);
@@ -457,6 +458,7 @@ public final class SelectStatementContextTest {
BinaryOperationExpression expression = new BinaryOperationExpression(0, 0, left, right, "=", null);
WhereSegment subWhereSegment = new WhereSegment(0, 0, expression);
subSelectStatement.setWhere(subWhereSegment);
+ subSelectStatement.setProjections(new ProjectionsSegment(0, 0));
SubqueryExpressionSegment subqueryExpressionSegment = new SubqueryExpressionSegment(new SubquerySegment(0, 0, subSelectStatement));
SubqueryProjectionSegment projectionSegment = mock(SubqueryProjectionSegment.class);
WhereSegment whereSegment = new WhereSegment(0, 0, subqueryExpressionSegment);
diff --git a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/java/org/apache/shardingsphere/driver/jdbc/core/statement/FederatePrepareStatementTest.java b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/java/org/apache/shardingsphere/driver/jdbc/core/statement/FederatePrepareStatementTest.java
index 426ce3a..6949d53 100644
--- a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/java/org/apache/shardingsphere/driver/jdbc/core/statement/FederatePrepareStatementTest.java
+++ b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/java/org/apache/shardingsphere/driver/jdbc/core/statement/FederatePrepareStatementTest.java
@@ -20,6 +20,7 @@ package org.apache.shardingsphere.driver.jdbc.core.statement;
import org.apache.shardingsphere.driver.jdbc.base.AbstractShardingSphereDataSourceForFederateTest;
import org.junit.Test;
+import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
@@ -62,6 +63,15 @@ public final class FederatePrepareStatementTest extends AbstractShardingSphereDa
private static final String SELECT_SQL_BY_ID_ACROSS_TWO_SHARDING_TABLES =
"select o.order_id_sharding, i.order_id from t_order_federate_sharding o, t_order_item_federate_sharding i "
+ "where o.order_id_sharding = i.item_id and i.order_id > ?";
+
+ private static final String SELECT_HAVING_SQL_FOR_SHARDING_TABLE =
+ "SELECT user_id, SUM(order_id_sharding) FROM t_order_federate_sharding GROUP BY user_id HAVING SUM(order_id_sharding) > ?";
+
+ private static final String SELECT_SUBQUEY_AGGREGATION_SQL_FOR_SHARDING_TABLE =
+ "SELECT (SELECT MAX(user_id) FROM t_order_federate_sharding) max_user_id, order_id_sharding, status FROM t_order_federate_sharding WHERE order_id_sharding > ?";
+
+ private static final String SELECT_PARTIAL_DISTINCT_AGGREGATION_SQL_FOR_SHARDING_TABLE =
+ "SELECT SUM(DISTINCT user_id), SUM(order_id_sharding) FROM t_order_federate_sharding WHERE order_id_sharding > ?";
@Test
public void assertQueryWithFederateInSingleTablesByExecuteQuery() throws SQLException {
@@ -253,4 +263,72 @@ public final class FederatePrepareStatementTest extends AbstractShardingSphereDa
assertThat(resultSet.getInt(2), is(10001));
assertFalse(resultSet.next());
}
+
+ @Test
+ public void assertHavingForShardingTableWithFederateByExecuteQuery() throws SQLException {
+ assertHavingForShardingTableWithFederate(true);
+ }
+
+ @Test
+ public void assertHavingForShardingTableWithFederateByExecute() throws SQLException {
+ assertHavingForShardingTableWithFederate(false);
+ }
+
+ private void assertHavingForShardingTableWithFederate(final boolean executeQuery) throws SQLException {
+ PreparedStatement preparedStatement = getShardingSphereDataSource().getConnection().prepareStatement(SELECT_HAVING_SQL_FOR_SHARDING_TABLE);
+ preparedStatement.setInt(1, 1000);
+ ResultSet resultSet = getResultSet(preparedStatement, executeQuery);
+ assertNotNull(resultSet);
+ assertTrue(resultSet.next());
+ assertThat(resultSet.getInt(1), is(10));
+ assertThat(resultSet.getInt(2), is(2110));
+ assertNotNull(resultSet);
+ assertTrue(resultSet.next());
+ assertThat(resultSet.getInt(1), is(11));
+ assertThat(resultSet.getInt(2), is(2112));
+ assertFalse(resultSet.next());
+ }
+
+ @Test
+ public void assertSubqueyAggregationForShardingTableWithFederateByExecuteQuery() throws SQLException {
+ assertSubqueyAggregationForShardingTableWithFederate(true);
+ }
+
+ @Test
+ public void assertSubqueyAggregationForShardingTableWithFederateByExecute() throws SQLException {
+ assertSubqueyAggregationForShardingTableWithFederate(false);
+ }
+
+ private void assertSubqueyAggregationForShardingTableWithFederate(final boolean executeQuery) throws SQLException {
+ PreparedStatement preparedStatement = getShardingSphereDataSource().getConnection().prepareStatement(SELECT_SUBQUEY_AGGREGATION_SQL_FOR_SHARDING_TABLE);
+ preparedStatement.setInt(1, 1100);
+ ResultSet resultSet = getResultSet(preparedStatement, executeQuery);
+ assertNotNull(resultSet);
+ assertTrue(resultSet.next());
+ assertThat(resultSet.getInt(1), is(11));
+ assertThat(resultSet.getInt(2), is(1101));
+ assertThat(resultSet.getString(3), is("init"));
+ assertNotNull(resultSet);
+ }
+
+ @Test
+ public void assertPartialDistinctAggregationForShardingTableWithFederateByExecuteQuery() throws SQLException {
+ assertPartialDistinctAggregationForShardingTableWithFederate(true);
+ }
+
+ @Test
+ public void assertPartialDistinctAggregationForShardingTableWithFederateByExecute() throws SQLException {
+ assertPartialDistinctAggregationForShardingTableWithFederate(false);
+ }
+
+ private void assertPartialDistinctAggregationForShardingTableWithFederate(final boolean executeQuery) throws SQLException {
+ PreparedStatement preparedStatement = getShardingSphereDataSource().getConnection().prepareStatement(SELECT_PARTIAL_DISTINCT_AGGREGATION_SQL_FOR_SHARDING_TABLE);
+ preparedStatement.setInt(1, 1000);
+ ResultSet resultSet = getResultSet(preparedStatement, executeQuery);
+ assertNotNull(resultSet);
+ assertTrue(resultSet.next());
+ assertThat(resultSet.getInt(1), is(21));
+ assertThat(resultSet.getInt(2), is(4222));
+ assertNotNull(resultSet);
+ }
}
diff --git a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/java/org/apache/shardingsphere/driver/jdbc/core/statement/FederateStatementTest.java b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/java/org/apache/shardingsphere/driver/jdbc/core/statement/FederateStatementTest.java
index 55689b5..9688e10 100644
--- a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/java/org/apache/shardingsphere/driver/jdbc/core/statement/FederateStatementTest.java
+++ b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/java/org/apache/shardingsphere/driver/jdbc/core/statement/FederateStatementTest.java
@@ -22,6 +22,7 @@ import org.junit.Test;
import java.sql.ResultSet;
import java.sql.SQLException;
+import java.sql.Statement;
import static org.hamcrest.CoreMatchers.is;
import static org.junit.Assert.assertFalse;
@@ -66,6 +67,15 @@ public final class FederateStatementTest extends AbstractShardingSphereDataSourc
"select o.order_id_sharding, i.order_id from t_order_federate_sharding o, t_order_item_federate_sharding i "
+ "where o.order_id_sharding = i.item_id";
+ private static final String SELECT_HAVING_SQL_FOR_SHARDING_TABLE =
+ "SELECT user_id, SUM(order_id_sharding) FROM t_order_federate_sharding GROUP BY user_id HAVING SUM(order_id_sharding) > 1000";
+
+ private static final String SELECT_SUBQUEY_AGGREGATION_SQL_FOR_SHARDING_TABLE =
+ "SELECT (SELECT MAX(user_id) FROM t_order_federate_sharding) max_user_id, order_id_sharding, status FROM t_order_federate_sharding WHERE order_id_sharding > 1100";
+
+ private static final String SELECT_PARTIAL_DISTINCT_AGGREGATION_SQL_FOR_SHARDING_TABLE =
+ "SELECT SUM(DISTINCT user_id), SUM(order_id_sharding) FROM t_order_federate_sharding WHERE order_id_sharding > 1000";
+
@Test
public void assertQueryWithFederateInSingleTablesByExecuteQuery() throws SQLException {
assertQueryWithFederateInSingleTables(true);
@@ -291,4 +301,69 @@ public final class FederateStatementTest extends AbstractShardingSphereDataSourc
assertThat(resultSet.getInt(2), is(10001));
assertFalse(resultSet.next());
}
+
+ @Test
+ public void assertHavingForShardingTableWithFederateByExecuteQuery() throws SQLException {
+ assertHavingForShardingTableWithFederate(true);
+ }
+
+ @Test
+ public void assertHavingForShardingTableWithFederateByExecute() throws SQLException {
+ assertHavingForShardingTableWithFederate(false);
+ }
+
+ private void assertHavingForShardingTableWithFederate(final boolean executeQuery) throws SQLException {
+ Statement statement = getShardingSphereDataSource().getConnection().createStatement();
+ ResultSet resultSet = getResultSet(statement, SELECT_HAVING_SQL_FOR_SHARDING_TABLE, executeQuery);
+ assertNotNull(resultSet);
+ assertTrue(resultSet.next());
+ assertThat(resultSet.getInt(1), is(10));
+ assertThat(resultSet.getInt(2), is(2110));
+ assertNotNull(resultSet);
+ assertTrue(resultSet.next());
+ assertThat(resultSet.getInt(1), is(11));
+ assertThat(resultSet.getInt(2), is(2112));
+ assertFalse(resultSet.next());
+ }
+
+ @Test
+ public void assertSubqueyAggregationForShardingTableWithFederateByExecuteQuery() throws SQLException {
+ assertSubqueyAggregationForShardingTableWithFederate(true);
+ }
+
+ @Test
+ public void assertSubqueyAggregationForShardingTableWithFederateByExecute() throws SQLException {
+ assertSubqueyAggregationForShardingTableWithFederate(false);
+ }
+
+ private void assertSubqueyAggregationForShardingTableWithFederate(final boolean executeQuery) throws SQLException {
+ Statement statement = getShardingSphereDataSource().getConnection().createStatement();
+ ResultSet resultSet = getResultSet(statement, SELECT_SUBQUEY_AGGREGATION_SQL_FOR_SHARDING_TABLE, executeQuery);
+ assertNotNull(resultSet);
+ assertTrue(resultSet.next());
+ assertThat(resultSet.getInt(1), is(11));
+ assertThat(resultSet.getInt(2), is(1101));
+ assertThat(resultSet.getString(3), is("init"));
+ assertNotNull(resultSet);
+ }
+
+ @Test
+ public void assertPartialDistinctAggregationForShardingTableWithFederateByExecuteQuery() throws SQLException {
+ assertPartialDistinctAggregationForShardingTableWithFederate(true);
+ }
+
+ @Test
+ public void assertPartialDistinctAggregationForShardingTableWithFederateByExecute() throws SQLException {
+ assertPartialDistinctAggregationForShardingTableWithFederate(false);
+ }
+
+ private void assertPartialDistinctAggregationForShardingTableWithFederate(final boolean executeQuery) throws SQLException {
+ Statement statement = getShardingSphereDataSource().getConnection().createStatement();
+ ResultSet resultSet = getResultSet(statement, SELECT_PARTIAL_DISTINCT_AGGREGATION_SQL_FOR_SHARDING_TABLE, executeQuery);
+ assertNotNull(resultSet);
+ assertTrue(resultSet.next());
+ assertThat(resultSet.getInt(1), is(21));
+ assertThat(resultSet.getInt(2), is(4222));
+ assertNotNull(resultSet);
+ }
}
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 324e5a7..6be5eee 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
@@ -35,7 +35,7 @@
<rewrite-assertion id="select_with_sum_fun">
<input sql="SELECT SUM(DISTINCT account_id), SUM(account_id) FROM t_account WHERE account_id = 100" />
- <output sql="SELECT SUM(DISTINCT account_id), SUM(account_id) FROM t_account_0 WHERE account_id = 100" />
+ <output sql="SELECT SUM(DISTINCT account_id), SUM(account_id) FROM t_account WHERE account_id = 100" />
</rewrite-assertion>
<rewrite-assertion id="select_with_avg_fun">