This is an automated email from the ASF dual-hosted git repository.
tuichenchuxin 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 d6f6c547e6f Add CoreRules.AGGREGATE_REDUCE_FUNCTIONS to rewrite AVG(x)
to SUM(x) / COUNT(x) (#21408)
d6f6c547e6f is described below
commit d6f6c547e6f791f7fb4527a9904eb6d9df99eee2
Author: Zhengqiang Duan <[email protected]>
AuthorDate: Sun Oct 9 11:51:41 2022 +0800
Add CoreRules.AGGREGATE_REDUCE_FUNCTIONS to rewrite AVG(x) to SUM(x) /
COUNT(x) (#21408)
---
.../infra/metadata/database/ShardingSphereDatabase.java | 2 +-
.../optimizer/util/SQLFederationPlannerUtil.java | 8 ++++++++
.../sqlfederation/optimizer/SQLOptimizeEngineTest.java | 17 +++++++++++++++++
.../ProcessListClusterPersistRepositoryFixture.java | 8 ++++----
.../cluster/zookeeper/CuratorZookeeperRepository.java | 8 ++++----
.../fixture/StandalonePersistRepositoryFixture.java | 2 +-
.../proxy/fixture/ClusterPersistRepositoryFixture.java | 4 ++--
.../resources/cases/dql/dql-integration-test-cases.xml | 3 +++
8 files changed, 40 insertions(+), 12 deletions(-)
diff --git
a/infra/common/src/main/java/org/apache/shardingsphere/infra/metadata/database/ShardingSphereDatabase.java
b/infra/common/src/main/java/org/apache/shardingsphere/infra/metadata/database/ShardingSphereDatabase.java
index bcd6bd6df74..0e10d89ccaa 100644
---
a/infra/common/src/main/java/org/apache/shardingsphere/infra/metadata/database/ShardingSphereDatabase.java
+++
b/infra/common/src/main/java/org/apache/shardingsphere/infra/metadata/database/ShardingSphereDatabase.java
@@ -86,7 +86,7 @@ public final class ShardingSphereDatabase {
final DatabaseConfiguration
databaseConfig, final ConfigurationProperties props, final InstanceContext
instanceContext) throws SQLException {
Collection<ShardingSphereRule> databaseRules =
DatabaseRulesBuilder.build(name, databaseConfig, instanceContext);
Map<String, ShardingSphereSchema> schemas = new ConcurrentHashMap<>();
- schemas.putAll(GenericSchemaBuilder.build(new
GenericSchemaBuilderMaterials(protocolType, storageType,
+ schemas.putAll(GenericSchemaBuilder.build(new
GenericSchemaBuilderMaterials(protocolType, storageType,
DataSourceStateManager.getInstance().getEnabledDataSourceMap(name,
databaseConfig.getDataSources()), databaseRules, props,
DatabaseTypeEngine.getDefaultSchemaName(storageType, name))));
schemas.putAll(SystemSchemaBuilder.build(name, protocolType));
diff --git
a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/util/SQLFederationPlannerUtil.java
b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/util/SQLFederationPlannerUtil.java
index bdb95155070..110ede11d61 100644
---
a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/util/SQLFederationPlannerUtil.java
+++
b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/util/SQLFederationPlannerUtil.java
@@ -99,6 +99,7 @@ public final class SQLFederationPlannerUtil {
HepProgramBuilder builder = new HepProgramBuilder();
builder.addGroupBegin().addRuleCollection(getFilterRules()).addGroupEnd().addMatchOrder(HepMatchOrder.BOTTOM_UP);
builder.addGroupBegin().addRuleCollection(getProjectRules()).addGroupEnd().addMatchOrder(HepMatchOrder.BOTTOM_UP);
+
builder.addGroupBegin().addRuleCollection(getAggregationRules()).addGroupEnd().addMatchOrder(HepMatchOrder.BOTTOM_UP);
builder.addGroupBegin().addRuleCollection(getCalcRules()).addGroupEnd().addMatchOrder(HepMatchOrder.BOTTOM_UP);
builder.addGroupBegin().addRuleCollection(getSubQueryRules()).addGroupEnd().addMatchOrder(HepMatchOrder.BOTTOM_UP);
builder.addMatchLimit(DEFAULT_MATCH_LIMIT);
@@ -172,6 +173,13 @@ public final class SQLFederationPlannerUtil {
return result;
}
+ private static Collection<RelOptRule> getAggregationRules() {
+ Collection<RelOptRule> result = new LinkedList<>();
+ result.add(CoreRules.AGGREGATE_MERGE);
+ result.add(CoreRules.AGGREGATE_REDUCE_FUNCTIONS);
+ return result;
+ }
+
/**
* Create catalog reader.
*
diff --git
a/kernel/sql-federation/optimizer/src/test/java/org/apache/shardingsphere/sqlfederation/optimizer/SQLOptimizeEngineTest.java
b/kernel/sql-federation/optimizer/src/test/java/org/apache/shardingsphere/sqlfederation/optimizer/SQLOptimizeEngineTest.java
index c0bfe837e59..56a73299af1 100644
---
a/kernel/sql-federation/optimizer/src/test/java/org/apache/shardingsphere/sqlfederation/optimizer/SQLOptimizeEngineTest.java
+++
b/kernel/sql-federation/optimizer/src/test/java/org/apache/shardingsphere/sqlfederation/optimizer/SQLOptimizeEngineTest.java
@@ -92,6 +92,8 @@ public final class SQLOptimizeEngineTest {
private static final String SELECT_LIMIT = "SELECT order_id, user_id FROM
t_order_federate LIMIT 1";
+ private static final String SELECT_AGGREGATION = "SELECT MAX(order_id),
MIN(order_id), SUM(order_id), AVG(order_id), COUNT(1) FROM t_order_federate
GROUP BY user_id";
+
private static final String SCHEMA_NAME = "federate_jdbc";
private final SQLParserRule sqlParserRule = new SQLParserRule(new
DefaultSQLParserRuleConfigurationBuilder().build());
@@ -280,4 +282,19 @@ public final class SQLOptimizeEngineTest {
+ " TranslatableTableScan(table=[[federate_jdbc,
t_order_federate]], fields=[[0, 1]])" + LINE_SEPARATOR;
assertThat(actual, is(expected));
}
+
+ @Test
+ public void assertSelectAggregationFunction() {
+ ShardingSphereSQLParserEngine sqlParserEngine =
sqlParserRule.getSQLParserEngine(DatabaseTypeEngine.getTrunkDatabaseTypeName(new
H2DatabaseType()));
+ SQLStatement sqlStatement = sqlParserEngine.parse(SELECT_AGGREGATION,
false);
+ String actual =
optimizeEngine.optimize(sqlStatement).getBestPlan().explain();
+ String expected =
+ "EnumerableCalc(expr#0..5=[{inputs}], expr#6=[0],
expr#7=[=($t4, $t6)], expr#8=[null:DECIMAL(19, 9)], "
+ + "expr#9=[CASE($t7, $t8, $t3)], expr#10=[/($t9,
$t4)], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t9], EXPR$3=[$t10], EXPR$4=[$t5])"
+ + LINE_SEPARATOR
+ + " EnumerableAggregate(group=[{0}],
EXPR$0=[MAX($1)], EXPR$1=[MIN($1)], EXPR$2=[$SUM0($2)], agg#3=[COUNT($2)],
EXPR$4=[COUNT()])" + LINE_SEPARATOR
+ + " EnumerableCalc(expr#0..1=[{inputs}],
expr#2=[CAST($t0):DECIMAL(19, 9)], user_id=[$t1], order_id=[$t0], $f2=[$t2])" +
LINE_SEPARATOR
+ + " TranslatableTableScan(table=[[federate_jdbc,
t_order_federate]], fields=[[1, 0]])" + LINE_SEPARATOR;
+ assertThat(actual, is(expected));
+ }
}
diff --git
a/mode/type/cluster/core/src/test/java/org/apache/shardingsphere/mode/manager/cluster/coordinator/registry/process/ProcessListClusterPersistRepositoryFixture.java
b/mode/type/cluster/core/src/test/java/org/apache/shardingsphere/mode/manager/cluster/coordinator/registry/process/ProcessListClusterPersistRepositoryFixture.java
index e2312d09d09..1e6bd0d565d 100644
---
a/mode/type/cluster/core/src/test/java/org/apache/shardingsphere/mode/manager/cluster/coordinator/registry/process/ProcessListClusterPersistRepositoryFixture.java
+++
b/mode/type/cluster/core/src/test/java/org/apache/shardingsphere/mode/manager/cluster/coordinator/registry/process/ProcessListClusterPersistRepositoryFixture.java
@@ -43,12 +43,12 @@ public final class
ProcessListClusterPersistRepositoryFixture implements Cluster
@Override
public void addCacheData(final String cachePath) {
-
+
}
@Override
public void evictCacheData(final String cachePath) {
-
+
}
@Override
@@ -58,7 +58,7 @@ public final class ProcessListClusterPersistRepositoryFixture
implements Cluster
@Override
public void executeInLeader(final String key, final
LeaderExecutionCallback callback) {
-
+
}
@Override
@@ -87,7 +87,7 @@ public final class ProcessListClusterPersistRepositoryFixture
implements Cluster
@Override
public void update(final String key, final String value) {
-
+
}
@Override
diff --git
a/mode/type/cluster/repository/provider/zookeeper-curator/src/main/java/org/apache/shardingsphere/mode/repository/cluster/zookeeper/CuratorZookeeperRepository.java
b/mode/type/cluster/repository/provider/zookeeper-curator/src/main/java/org/apache/shardingsphere/mode/repository/cluster/zookeeper/CuratorZookeeperRepository.java
index 60759a0974e..d17016881f0 100644
---
a/mode/type/cluster/repository/provider/zookeeper-curator/src/main/java/org/apache/shardingsphere/mode/repository/cluster/zookeeper/CuratorZookeeperRepository.java
+++
b/mode/type/cluster/repository/provider/zookeeper-curator/src/main/java/org/apache/shardingsphere/mode/repository/cluster/zookeeper/CuratorZookeeperRepository.java
@@ -134,9 +134,9 @@ public final class CuratorZookeeperRepository implements
ClusterPersistRepositor
if (null != stat) {
return stat.getNumChildren();
}
- //CHECKSTYLE:OFF
+ // CHECKSTYLE:OFF
} catch (final Exception ex) {
- //CHECKSTYLE:ON
+ // CHECKSTYLE:ON
CuratorZookeeperExceptionHandler.handleException(ex);
}
return 0;
@@ -283,9 +283,9 @@ public final class CuratorZookeeperRepository implements
ClusterPersistRepositor
try {
persist(key, "");
result = client.checkExists().forPath(key).getMtime();
- //CHECKSTYLE:OFF
+ // CHECKSTYLE:OFF
} catch (final Exception ex) {
- //CHECKSTYLE:ON
+ // CHECKSTYLE:ON
RegExceptionHandler.handleException(ex);
}
Preconditions.checkState(0L != result, "Cannot get registry center
time.");
diff --git
a/mode/type/standalone/core/src/test/java/org/apache/shardingsphere/mode/manager/standalone/fixture/StandalonePersistRepositoryFixture.java
b/mode/type/standalone/core/src/test/java/org/apache/shardingsphere/mode/manager/standalone/fixture/StandalonePersistRepositoryFixture.java
index d66dac72a1e..e4714de6fba 100644
---
a/mode/type/standalone/core/src/test/java/org/apache/shardingsphere/mode/manager/standalone/fixture/StandalonePersistRepositoryFixture.java
+++
b/mode/type/standalone/core/src/test/java/org/apache/shardingsphere/mode/manager/standalone/fixture/StandalonePersistRepositoryFixture.java
@@ -64,7 +64,7 @@ public final class StandalonePersistRepositoryFixture
implements StandalonePersi
@Override
public void update(final String key, final String value) {
-
+
}
@Override
diff --git
a/proxy/bootstrap/src/test/java/org/apache/shardingsphere/proxy/fixture/ClusterPersistRepositoryFixture.java
b/proxy/bootstrap/src/test/java/org/apache/shardingsphere/proxy/fixture/ClusterPersistRepositoryFixture.java
index ef1dc6c5ef1..ef174b063b9 100644
---
a/proxy/bootstrap/src/test/java/org/apache/shardingsphere/proxy/fixture/ClusterPersistRepositoryFixture.java
+++
b/proxy/bootstrap/src/test/java/org/apache/shardingsphere/proxy/fixture/ClusterPersistRepositoryFixture.java
@@ -43,12 +43,12 @@ public final class ClusterPersistRepositoryFixture
implements ClusterPersistRepo
@Override
public void addCacheData(final String cachePath) {
-
+
}
@Override
public void evictCacheData(final String cachePath) {
-
+
}
@Override
diff --git
a/test/integration-test/test-suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
b/test/integration-test/test-suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
index f217f69ae16..217855c4f74 100644
---
a/test/integration-test/test-suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
+++
b/test/integration-test/test-suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
@@ -648,6 +648,9 @@
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
+ <test-case sql="SELECT MAX(p.price) AS max_price, MIN(p.price) AS
min_price, SUM(p.price) AS sum_price, AVG(p.price) AS avg_price, COUNT(1) AS
count FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id INNER
JOIN t_product p ON i.product_id = p.product_id GROUP BY o.order_id HAVING
SUM(p.price) > ? ORDER BY max_price" db-types="PostgreSQL,openGauss"
scenario-types="db">
+ <assertion parameters="10000:int"
expected-data-source-name="read_dataset" />
+ </test-case>
<!-- TODO FIX ME Expected: is "true" but was "1"-->
<!-- <test-case sql="SELECT order_id, user_id, order_name, type_char,
type_boolean, type_smallint, type_enum, type_decimal, type_date, type_time,
type_timestamp FROM t_shadow WHERE user_id = ?" db-types="MySQL"
scenario-types="sharding_and_shadow">-->
<!-- <assertion parameters="1:int"
expected-data-source-name="prod_dataset" />-->
