This is an automated email from the ASF dual-hosted git repository.
gavinchou pushed a commit to branch branch-3.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-3.0 by this push:
new 4d810394f9c [improvement](mtmv) Support to partition prune when query
rewrite by sync materialized view (#38527)
4d810394f9c is described below
commit 4d810394f9cceb399c81050f25eedc9fde1386c6
Author: seawinde <149132972+seawi...@users.noreply.github.com>
AuthorDate: Tue Aug 6 10:25:43 2024 +0800
[improvement](mtmv) Support to partition prune when query rewrite by sync
materialized view (#38527)
## Proposed changes
Support to partition prune when query rewrite by sync materialized view
such as table def is as following:
```sql
CREATE TABLE IF NOT EXISTS test_unique (
`time` DATETIME NULL COMMENT '查询时间',
`app_name` VARCHAR(64) NULL COMMENT '标识',
`event_id` VARCHAR(128) NULL COMMENT '标识',
`decision` VARCHAR(32) NULL COMMENT '枚举值',
`id` VARCHAR(35) NOT NULL COMMENT 'od',
`code` VARCHAR(64) NULL COMMENT '标识',
`event_type` VARCHAR(32) NULL COMMENT '事件类型'
)
UNIQUE KEY(time)
PARTITION BY RANGE(time)
(
FROM ("2024-07-01 00:00:00") TO ("2024-07-15 00:00:00") INTERVAL 1
HOUR
)
DISTRIBUTED BY HASH(time)
BUCKETS 3 PROPERTIES ("replication_num" = "1");
```
sync materialized view def is
```sql
create materialized view as
select
app_name,
event_id,
time,
count(*)
from
test_duplicate
group by
app_name,
event_id,
time;
```
if your query is following, if rewritten by sync materialized view
successfully, should partition prune
```sql
select
app_name,
event_id,
time,
count(*)
from
test_duplicate
where time < '2024-07-05 01:00:00'
group by
app_name,
time,
event_id;
```
---
.../mv/AbstractMaterializedViewRule.java | 2 +-
.../mv/AsyncMaterializationContext.java | 13 +-
.../exploration/mv/MaterializationContext.java | 2 +-
.../exploration/mv/MaterializedViewUtils.java | 34 +--
.../exploration/mv/SyncMaterializationContext.java | 25 +-
.../doris/nereids/mv/IdStatisticsMapTest.java | 3 +-
.../data/mv_p0/partition_prune/partition_prune.out | 43 ++++
.../suites/auth_p0/test_select_column_auth.groovy | 4 +-
.../mv_p0/partition_prune/partition_prune.groovy | 257 +++++++++++++++++++++
9 files changed, 346 insertions(+), 37 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/AbstractMaterializedViewRule.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/AbstractMaterializedViewRule.java
index f51f1098949..b34faaf1250 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/AbstractMaterializedViewRule.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/AbstractMaterializedViewRule.java
@@ -233,7 +233,7 @@ public abstract class AbstractMaterializedViewRule
implements ExplorationRuleFac
continue;
}
Plan rewrittenPlan;
- Plan mvScan = materializationContext.getScanPlan();
+ Plan mvScan = materializationContext.getScanPlan(queryStructInfo);
Plan queryPlan = queryStructInfo.getTopPlan();
if (compensatePredicates.isAlwaysTrue()) {
rewrittenPlan = mvScan;
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/AsyncMaterializationContext.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/AsyncMaterializationContext.java
index b555f71e04d..0d88672fed6 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/AsyncMaterializationContext.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/AsyncMaterializationContext.java
@@ -27,6 +27,7 @@ import org.apache.doris.nereids.CascadesContext;
import org.apache.doris.nereids.rules.exploration.mv.mapping.ExpressionMapping;
import org.apache.doris.nereids.trees.plans.ObjectId;
import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.PreAggStatus;
import org.apache.doris.nereids.trees.plans.RelationId;
import org.apache.doris.nereids.trees.plans.algebra.Relation;
import org.apache.doris.nereids.trees.plans.logical.LogicalOlapScan;
@@ -56,7 +57,8 @@ public class AsyncMaterializationContext extends
MaterializationContext {
*/
public AsyncMaterializationContext(MTMV mtmv, Plan mvPlan, Plan
mvOriginalPlan, List<Table> baseTables,
List<Table> baseViews, CascadesContext cascadesContext, StructInfo
structInfo) {
- super(mvPlan, mvOriginalPlan,
MaterializedViewUtils.generateMvScanPlan(mtmv, cascadesContext),
+ super(mvPlan, mvOriginalPlan,
MaterializedViewUtils.generateMvScanPlan(mtmv, mtmv.getBaseIndexId(),
+ mtmv.getPartitionIds(), PreAggStatus.on(),
cascadesContext),
cascadesContext, structInfo);
this.mtmv = mtmv;
}
@@ -67,7 +69,8 @@ public class AsyncMaterializationContext extends
MaterializationContext {
@Override
Plan doGenerateScanPlan(CascadesContext cascadesContext) {
- return MaterializedViewUtils.generateMvScanPlan(this.mtmv,
cascadesContext);
+ return MaterializedViewUtils.generateMvScanPlan(this.mtmv,
this.mtmv.getBaseIndexId(),
+ this.mtmv.getPartitionIds(), PreAggStatus.on(),
cascadesContext);
}
@Override
@@ -107,7 +110,8 @@ public class AsyncMaterializationContext extends
MaterializationContext {
return Optional.empty();
}
RelationId relationId = null;
- Optional<LogicalOlapScan> logicalOlapScan =
this.getScanPlan().collectFirst(LogicalOlapScan.class::isInstance);
+ Optional<LogicalOlapScan> logicalOlapScan = this.getScanPlan(null)
+ .collectFirst(LogicalOlapScan.class::isInstance);
if (logicalOlapScan.isPresent()) {
relationId = logicalOlapScan.get().getRelationId();
}
@@ -127,7 +131,8 @@ public class AsyncMaterializationContext extends
MaterializationContext {
);
}
- public Plan getScanPlan() {
+ @Override
+ public Plan getScanPlan(StructInfo queryInfo) {
return scanPlan;
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/MaterializationContext.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/MaterializationContext.java
index 0f1768e29ce..bd1dcee1bbb 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/MaterializationContext.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/MaterializationContext.java
@@ -276,7 +276,7 @@ public abstract class MaterializationContext {
return originalPlan;
}
- public Plan getScanPlan() {
+ public Plan getScanPlan(StructInfo queryStructInfo) {
return scanPlan;
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/MaterializedViewUtils.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/MaterializedViewUtils.java
index 835b01c00b2..b76a27e37bc 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/MaterializedViewUtils.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/MaterializedViewUtils.java
@@ -18,7 +18,6 @@
package org.apache.doris.nereids.rules.exploration.mv;
import org.apache.doris.catalog.Column;
-import org.apache.doris.catalog.MTMV;
import org.apache.doris.catalog.OlapTable;
import org.apache.doris.catalog.PartitionType;
import org.apache.doris.catalog.TableIf;
@@ -222,35 +221,20 @@ public class MaterializedViewUtils {
* when query rewrite, because one plan may hit the materialized view
repeatedly and the mv scan output
* should be different
*/
- public static Plan generateMvScanPlan(MTMV materializedView,
CascadesContext cascadesContext) {
+ public static Plan generateMvScanPlan(OlapTable table, long indexId,
+ List<Long> partitionIds,
+ PreAggStatus preAggStatus,
+ CascadesContext cascadesContext) {
return new LogicalOlapScan(
cascadesContext.getStatementContext().getNextRelationId(),
- materializedView,
- materializedView.getFullQualifiers(),
+ table,
+ ImmutableList.of(table.getQualifiedDbName()),
ImmutableList.of(),
- materializedView.getPartitionIds(),
- materializedView.getBaseIndexId(),
- PreAggStatus.on(),
- ImmutableList.of(),
- // this must be empty, or it will be used to sample
- ImmutableList.of(),
- Optional.empty());
- }
-
- /**
- * LIke above but generate scan plan for sync materialized view
- */
- public static Plan generateMvScanPlan(OlapTable olapTable, long indexId,
CascadesContext cascadesContext) {
- return new LogicalOlapScan(
- cascadesContext.getStatementContext().getNextRelationId(),
- olapTable,
- ImmutableList.of(olapTable.getQualifiedDbName()),
- // this must be empty, or it will be used to sample
- ImmutableList.of(),
- olapTable.getPartitionIds(),
+ partitionIds,
indexId,
- PreAggStatus.unset(),
+ preAggStatus,
ImmutableList.of(),
+ // this must be empty, or it will be used to sample
ImmutableList.of(),
Optional.empty());
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/SyncMaterializationContext.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/SyncMaterializationContext.java
index 1f50e069891..ea9e80cf7e6 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/SyncMaterializationContext.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/SyncMaterializationContext.java
@@ -23,6 +23,7 @@ import org.apache.doris.common.Pair;
import org.apache.doris.nereids.CascadesContext;
import org.apache.doris.nereids.trees.plans.ObjectId;
import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.PreAggStatus;
import org.apache.doris.nereids.trees.plans.RelationId;
import org.apache.doris.nereids.trees.plans.algebra.Relation;
import org.apache.doris.nereids.trees.plans.logical.LogicalOlapScan;
@@ -54,7 +55,8 @@ public class SyncMaterializationContext extends
MaterializationContext {
public SyncMaterializationContext(Plan mvPlan, Plan mvOriginalPlan,
OlapTable olapTable,
long indexId, String indexName, CascadesContext cascadesContext,
Statistics statistics) {
super(mvPlan, mvOriginalPlan,
- MaterializedViewUtils.generateMvScanPlan(olapTable, indexId,
cascadesContext), cascadesContext, null);
+ MaterializedViewUtils.generateMvScanPlan(olapTable, indexId,
olapTable.getPartitionIds(),
+ PreAggStatus.unset(), cascadesContext),
cascadesContext, null);
this.olapTable = olapTable;
this.indexId = indexId;
this.indexName = indexName;
@@ -63,7 +65,8 @@ public class SyncMaterializationContext extends
MaterializationContext {
@Override
Plan doGenerateScanPlan(CascadesContext cascadesContext) {
- return MaterializedViewUtils.generateMvScanPlan(olapTable, indexId,
cascadesContext);
+ return MaterializedViewUtils.generateMvScanPlan(olapTable, indexId,
olapTable.getPartitionIds(),
+ PreAggStatus.unset(), cascadesContext);
}
@Override
@@ -96,13 +99,29 @@ public class SyncMaterializationContext extends
MaterializationContext {
@Override
Optional<Pair<Id, Statistics>> getPlanStatistics(CascadesContext
cascadesContext) {
RelationId relationId = null;
- Optional<LogicalOlapScan> scanObj =
this.getScanPlan().collectFirst(LogicalOlapScan.class::isInstance);
+ Optional<LogicalOlapScan> scanObj = this.getScanPlan(null)
+ .collectFirst(LogicalOlapScan.class::isInstance);
if (scanObj.isPresent()) {
relationId = scanObj.get().getRelationId();
}
return Optional.of(Pair.of(relationId,
normalizeStatisticsColumnExpression(statistics)));
}
+ @Override
+ public Plan getScanPlan(StructInfo queryStructInfo) {
+ if (queryStructInfo == null) {
+ return scanPlan;
+ }
+ if (queryStructInfo.getRelations().size() == 1
+ && queryStructInfo.getRelations().get(0) instanceof
LogicalOlapScan
+ && !((LogicalOlapScan)
queryStructInfo.getRelations().get(0)).getSelectedPartitionIds().isEmpty()
+ && scanPlan instanceof LogicalOlapScan) {
+ return ((LogicalOlapScan) scanPlan).withSelectedPartitionIds(
+ ((LogicalOlapScan)
queryStructInfo.getRelations().get(0)).getSelectedPartitionIds());
+ }
+ return scanPlan;
+ }
+
/**
* Calc the relation is chosen finally or not
*/
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/mv/IdStatisticsMapTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/mv/IdStatisticsMapTest.java
index 4b0ca184955..a4c05fa81e6 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/mv/IdStatisticsMapTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/mv/IdStatisticsMapTest.java
@@ -75,7 +75,8 @@ public class IdStatisticsMapTest extends SqlTestBase {
.analyze()
.rewrite();
// scan plan output will be refreshed after mv rewrite successfully,
so need tmp store
- Set<Slot> materializationScanOutput =
c1.getMaterializationContexts().get(0).getScanPlan().getOutputSet();
+ Set<Slot> materializationScanOutput =
c1.getMaterializationContexts().get(0)
+ .getScanPlan(null).getOutputSet();
tmpPlanChecker
.optimize()
.printlnBestPlanTree();
diff --git a/regression-test/data/mv_p0/partition_prune/partition_prune.out
b/regression-test/data/mv_p0/partition_prune/partition_prune.out
new file mode 100644
index 00000000000..9f21fbbd0f1
--- /dev/null
+++ b/regression-test/data/mv_p0/partition_prune/partition_prune.out
@@ -0,0 +1,43 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !query1_before --
+aa bc 2024-07-03T01:15:30 1
+ad be 2024-07-03T07:06:30 1
+af bf 2024-07-04T10:01:30 1
+ag bc 2024-07-04T10:01:35 1
+as bd 2024-07-03T01:15:30 1
+
+-- !query1_after --
+aa bc 2024-07-03T01:15:30 1
+ad be 2024-07-03T07:06:30 1
+af bf 2024-07-04T10:01:30 1
+ag bc 2024-07-04T10:01:35 1
+as bd 2024-07-03T01:15:30 1
+
+-- !query2_before --
+2024-07-03T01:00 aa bc
+2024-07-03T06:00 as bd
+2024-07-03T07:00 ad be
+2024-07-04T10:00 af bf
+2024-07-04T12:00 ag bc
+
+-- !query2_after --
+2024-07-03T01:00 aa bc
+2024-07-03T06:00 as bd
+2024-07-03T07:00 ad be
+2024-07-04T10:00 af bf
+2024-07-04T12:00 ag bc
+
+-- !query3_before --
+aa bc 2024-07-03T01:00 2.1
+ad be 2024-07-03T07:00 3.1
+af bf 2024-07-04T10:00 4.1
+ag bc 2024-07-04T12:00 5.1
+as bd 2024-07-03T06:00 1.1
+
+-- !query3_after --
+aa bc 2024-07-03T01:00 2.1
+ad be 2024-07-03T07:00 3.1
+af bf 2024-07-04T10:00 4.1
+ag bc 2024-07-04T12:00 5.1
+as bd 2024-07-03T06:00 1.1
+
diff --git a/regression-test/suites/auth_p0/test_select_column_auth.groovy
b/regression-test/suites/auth_p0/test_select_column_auth.groovy
index 8b3e9529551..3c3216d0500 100644
--- a/regression-test/suites/auth_p0/test_select_column_auth.groovy
+++ b/regression-test/suites/auth_p0/test_select_column_auth.groovy
@@ -39,8 +39,8 @@ suite("test_select_column_auth","p0,auth") {
def validCluster = clusters[0][0]
sql """GRANT USAGE_PRIV ON CLUSTER ${validCluster} TO ${user}""";
}
-
sql """create database ${dbName}"""
+ sql("""use ${dbName}""")
sql """
CREATE TABLE IF NOT EXISTS ${dbName}.`${tableName}` (
id BIGINT,
@@ -55,7 +55,7 @@ suite("test_select_column_auth","p0,auth") {
sql """create view ${dbName}.${mv_name} as select * from
${dbName}.${tableName};"""
sql """alter table ${dbName}.${tableName} add rollup
${rollup_name}(username)"""
sleep(5 * 1000)
- sql """create materialized view ${mtmv_name} as select username from
${dbName}.${tableName}"""
+ createMV("""create materialized view ${mtmv_name} as select username from
${dbName}.${tableName}""")
sleep(5 * 1000)
sql """CREATE MATERIALIZED VIEW ${dbName}.${mtmv_name}
BUILD IMMEDIATE REFRESH AUTO ON MANUAL
diff --git
a/regression-test/suites/mv_p0/partition_prune/partition_prune.groovy
b/regression-test/suites/mv_p0/partition_prune/partition_prune.groovy
new file mode 100644
index 00000000000..759c6a540d7
--- /dev/null
+++ b/regression-test/suites/mv_p0/partition_prune/partition_prune.groovy
@@ -0,0 +1,257 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("partition_prune") {
+ String db = context.config.getDbNameByFile(context.file)
+ sql "use ${db}"
+ sql "set runtime_filter_mode=OFF";
+ sql "SET ignore_shape_nodes='PhysicalDistribute,PhysicalProject'"
+
+ sql """
+ drop table if exists test_duplicate;
+ """
+
+ sql """
+ CREATE TABLE IF NOT EXISTS test_duplicate (
+ `app_name` VARCHAR(64) NULL COMMENT '标识',
+ `event_id` VARCHAR(128) NULL COMMENT '标识',
+ `decision` VARCHAR(32) NULL COMMENT '枚举值',
+ `time` DATETIME NULL COMMENT '查询时间',
+ `id` VARCHAR(35) NOT NULL COMMENT 'od',
+ `code` VARCHAR(64) NULL COMMENT '标识',
+ `event_type` VARCHAR(32) NULL COMMENT '事件类型'
+ )
+ DUPLICATE KEY(app_name, event_id)
+ PARTITION BY RANGE(time)
+ (
+ FROM ("2024-07-01 00:00:00") TO ("2024-07-15 00:00:00") INTERVAL 1
HOUR
+ )
+ DISTRIBUTED BY HASH(event_id)
+ BUCKETS 3 PROPERTIES ("replication_num" = "1");
+ """
+
+ sql """
+ insert into test_duplicate values
+ ('aa', 'bc', 'cc', '2024-07-03 01:15:30', 'dd', 'ee', 'ff'),
+ ('as', 'bd', 'cd', '2024-07-03 01:15:30', 'dd', 'ee', 'ff'),
+ ('ad', 'be', 'cc', '2024-07-03 07:06:30', 'dd', 'ee', 'ff'),
+ ('af', 'bf', 'ce', '2024-07-04 10:01:30', 'dd', 'ee', 'ff'),
+ ('ag', 'bc', 'cc', '2024-07-04 10:01:35', 'dd', 'ee', 'ff'),
+ ('aa', 'bc', 'cc', '2024-07-05 01:15:30', 'dd', 'ee', 'ff'),
+ ('as', 'bd', 'cd', '2024-07-05 06:09:30', 'dd', 'ee', 'ff'),
+ ('ad', 'be', 'cc', '2024-07-06 07:06:30', 'dd', 'ee', 'ff'),
+ ('af', 'bf', 'ce', '2024-07-07 10:01:30', 'dd', 'ee', 'ff'),
+ ('ag', 'bc', 'cc', '2024-07-08 12:55:30', 'dd', 'ee', 'ff');
+ """
+
+ sql """
+ drop table if exists test_unique;
+ """
+
+ sql """
+ CREATE TABLE IF NOT EXISTS test_unique (
+ `time` DATETIME NULL COMMENT '查询时间',
+ `app_name` VARCHAR(64) NULL COMMENT '标识',
+ `event_id` VARCHAR(128) NULL COMMENT '标识',
+ `decision` VARCHAR(32) NULL COMMENT '枚举值',
+ `id` VARCHAR(35) NOT NULL COMMENT 'od',
+ `code` VARCHAR(64) NULL COMMENT '标识',
+ `event_type` VARCHAR(32) NULL COMMENT '事件类型'
+ )
+ UNIQUE KEY(time)
+ PARTITION BY RANGE(time)
+ (
+ FROM ("2024-07-01 00:00:00") TO ("2024-07-15 00:00:00") INTERVAL 1
HOUR
+ )
+ DISTRIBUTED BY HASH(time)
+ BUCKETS 3 PROPERTIES ("replication_num" = "1");
+ """
+
+ sql """
+ insert into test_unique values
+ ('2024-07-03 01:00:00', 'aa', 'bc', 'cc', 'dd', 'ee', 'ff'),
+ ('2024-07-03 06:00:00', 'as', 'bd', 'cd', 'dd', 'ee', 'ff'),
+ ('2024-07-03 07:00:00', 'ad', 'be', 'cc', 'dd', 'ee', 'ff'),
+ ('2024-07-04 10:00:00', 'af', 'bf', 'ce', 'dd', 'ee', 'ff'),
+ ('2024-07-04 12:00:00', 'ag', 'bc', 'cc', 'dd', 'ee', 'ff'),
+ ('2024-07-05 01:00:00', 'aa', 'bc', 'cc', 'dd', 'ee', 'ff'),
+ ('2024-07-05 06:00:00', 'as', 'bd', 'cd', 'dd', 'ee', 'ff'),
+ ('2024-07-06 07:00:00', 'ad', 'be', 'cc', 'dd', 'ee', 'ff'),
+ ('2024-07-07 10:00:00', 'af', 'bf', 'ce', 'dd', 'ee', 'ff'),
+ ('2024-07-08 12:00:00', 'ag', 'bc', 'cc', 'dd', 'ee', 'ff');
+ """
+
+ sql """
+ drop table if exists test_aggregate;
+ """
+
+ sql """
+ CREATE TABLE IF NOT EXISTS test_aggregate (
+ `app_name` VARCHAR(64) NULL COMMENT '标识',
+ `event_id` VARCHAR(128) NULL COMMENT '标识',
+ `time` DATETIME NULL COMMENT '查询时间',
+ `price` DOUBLE SUM DEFAULT '0' COMMENT '价格'
+ )
+ AGGREGATE KEY(app_name, event_id, time)
+ PARTITION BY RANGE(time)
+ (
+ FROM ("2024-07-01 00:00:00") TO ("2024-07-15 00:00:00") INTERVAL 1
HOUR
+ )
+ DISTRIBUTED BY HASH(event_id)
+ BUCKETS 3 PROPERTIES ("replication_num" = "1");
+ """
+
+ sql """
+ insert into test_aggregate values
+ ('aa', 'bc', '2024-07-03 01:00:00', 2.1),
+ ('as', 'bd', '2024-07-03 06:00:00', 1.1),
+ ('ad', 'be', '2024-07-03 07:00:00', 3.1),
+ ('af', 'bf', '2024-07-04 10:00:00', 4.1),
+ ('ag', 'bc', '2024-07-04 12:00:00', 5.1),
+ ('aa', 'bc', '2024-07-05 01:00:00', 6.1),
+ ('as', 'bd', '2024-07-05 06:00:00', 7.1),
+ ('ad', 'be', '2024-07-06 07:00:00', 8.1),
+ ('af', 'bf', '2024-07-07 10:00:00', 9.1),
+ ('ag', 'bc', '2024-07-08 12:00:00', 10.1);
+ """
+
+ // test partition prune in duplicate table
+
+ def mv1 = """
+ select
+ app_name,
+ event_id,
+ time,
+ count(*)
+ from
+ test_duplicate
+ group by
+ app_name,
+ event_id,
+ time;
+ """
+
+ def query1 = """
+ select
+ app_name,
+ event_id,
+ time,
+ count(*)
+ from
+ test_duplicate
+ where time < '2024-07-05 01:00:00'
+ group by
+ app_name,
+ time,
+ event_id;
+ """
+
+ order_qt_query1_before "${query1}"
+ createMV("""
+ CREATE MATERIALIZED VIEW mv1
+ AS
+ ${mv1}
+ """)
+ explain {
+ sql("""${query1}""")
+ check {result ->
+ result.contains("(mv1)") && result.contains("partitions=3")
+ }
+ }
+ order_qt_query1_after "${query1}"
+
+ // test partition prune in unique table
+ def mv2 = """
+ select
+ time,
+ app_name,
+ event_id
+ from
+ test_unique;
+ """
+
+ def query2 = """
+ select
+ time,
+ app_name,
+ event_id
+ from
+ test_unique
+ where time < '2024-07-05 01:00:00';
+ """
+
+ order_qt_query2_before "${query2}"
+ createMV("""
+ CREATE MATERIALIZED VIEW mv2
+ AS
+ ${mv2}
+ """)
+ explain {
+ sql("""${query2}""")
+ check {result ->
+ result.contains("(mv2)") && result.contains("partitions=5")
+ }
+ }
+ order_qt_query2_after "${query2}"
+
+ // test partition prune in aggregate table
+ def mv3 = """
+ select
+ app_name,
+ event_id,
+ time,
+ sum(price)
+ from
+ test_aggregate
+ where time < '2024-07-11 01:00:00'
+ group by
+ app_name,
+ event_id,
+ time;
+ """
+
+ def query3 = """
+ select
+ app_name,
+ event_id,
+ time,
+ sum(price)
+ from
+ test_aggregate
+ where time < '2024-07-05 01:00:00'
+ group by
+ app_name,
+ time,
+ event_id;
+ """
+
+
+ order_qt_query3_before "${query3}"
+ createMV("""
+ CREATE MATERIALIZED VIEW mv3
+ AS
+ ${mv3}
+ """)
+ explain {
+ sql("""${query3}""")
+ check {result ->
+ result.contains("(mv3)") && result.contains("partitions=5")
+ }
+ }
+ order_qt_query3_after "${query3}"
+
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
