This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-2.1
in repository https://gitbox.apache.org/repos/asf/doris.git
commit cc11e50200f092006c287684384c2e85f7d94995
Author: seawinde <149132972+seawi...@users.noreply.github.com>
AuthorDate: Fri May 17 11:39:39 2024 +0800
[fix](mtmv)Fix slot desc wrong in query rewrite by materialized view when
query is complex (#34904)
---
.../main/java/org/apache/doris/catalog/MTMV.java | 8 +-
.../mv/AbstractMaterializedViewRule.java | 2 +-
.../mv/InitMaterializationContextHook.java | 5 +-
.../exploration/mv/MaterializationContext.java | 13 +-
.../exploration/mv/MaterializedViewUtils.java | 7 +-
.../trees/plans/visitor/TableCollector.java | 14 +-
.../mv/nested/ddl/customer_create.sql | 15 +
.../mv/nested/ddl/customer_delete.sql | 1 +
.../nereids_rules_p0/mv/nested/ddl/date_create.sql | 24 +
.../nereids_rules_p0/mv/nested/ddl/date_delete.sql | 1 +
.../mv/nested/ddl/lineorder_create.sql | 24 +
.../mv/nested/ddl/lineorder_delete.sql | 1 +
.../mv/nested/ddl/lineorder_flat_create.sql | 45 ++
.../mv/nested/ddl/lineorder_flat_delete.sql | 1 +
.../nereids_rules_p0/mv/nested/ddl/part_create.sql | 16 +
.../nereids_rules_p0/mv/nested/ddl/part_delete.sql | 1 +
.../mv/nested/ddl/supplier_create.sql | 14 +
.../mv/nested/ddl/supplier_delete.sql | 1 +
.../mv/nested/nested_materialized_view.groovy | 750 ++++++++++++++++++++-
19 files changed, 920 insertions(+), 23 deletions(-)
diff --git a/fe/fe-core/src/main/java/org/apache/doris/catalog/MTMV.java
b/fe/fe-core/src/main/java/org/apache/doris/catalog/MTMV.java
index a3050bc3ac6..fb32741afa3 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/catalog/MTMV.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/catalog/MTMV.java
@@ -40,6 +40,7 @@ import org.apache.doris.mtmv.MTMVRefreshSnapshot;
import org.apache.doris.mtmv.MTMVRelation;
import org.apache.doris.mtmv.MTMVStatus;
import org.apache.doris.persist.gson.GsonUtils;
+import org.apache.doris.qe.ConnectContext;
import com.google.common.collect.Maps;
import com.google.common.collect.Sets;
@@ -267,12 +268,15 @@ public class MTMV extends OlapTable {
}
}
- public MTMVCache getOrGenerateCache() throws AnalysisException {
+ /**
+ * Called when in query, Should use one connection context in query
+ */
+ public MTMVCache getOrGenerateCache(ConnectContext connectionContext)
throws AnalysisException {
if (cache == null) {
writeMvLock();
try {
if (cache == null) {
- this.cache = MTMVCache.from(this,
MTMVPlanUtil.createMTMVContext(this));
+ this.cache = MTMVCache.from(this, connectionContext);
}
} finally {
writeMvUnlock();
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 a88362e6697..2274ad441f8 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
@@ -323,9 +323,9 @@ public abstract class AbstractMaterializedViewRule
implements ExplorationRuleFac
continue;
}
recordIfRewritten(queryStructInfo.getOriginalPlan(),
materializationContext);
+ rewriteResults.add(rewrittenPlan);
// if rewrite successfully, try to regenerate mv scan because it
maybe used again
materializationContext.tryReGenerateMvScanPlan(cascadesContext);
- rewriteResults.add(rewrittenPlan);
}
return rewriteResults;
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/InitMaterializationContextHook.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/InitMaterializationContextHook.java
index 311932fa1b7..d91fa5cb1a9 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/InitMaterializationContextHook.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/InitMaterializationContextHook.java
@@ -63,6 +63,9 @@ public class InitMaterializationContextHook implements
PlannerHook {
}
Plan rewritePlan = cascadesContext.getRewritePlan();
TableCollectorContext collectorContext = new
TableCollectorContext(Sets.newHashSet(), true);
+ // Keep use one connection context when in query, if new connect
context,
+ // the ConnectionContext.get() will change
+
collectorContext.setConnectContext(cascadesContext.getConnectContext());
rewritePlan.accept(TableCollector.INSTANCE, collectorContext);
Set<TableIf> collectedTables = collectorContext.getCollectedTables();
if (collectedTables.isEmpty()) {
@@ -80,7 +83,7 @@ public class InitMaterializationContextHook implements
PlannerHook {
for (MTMV materializedView : availableMTMVs) {
MTMVCache mtmvCache = null;
try {
- mtmvCache = materializedView.getOrGenerateCache();
+ mtmvCache =
materializedView.getOrGenerateCache(cascadesContext.getConnectContext());
} catch (AnalysisException e) {
LOG.warn("MaterializationContext init mv cache generate fail",
e);
}
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 2f0d04e1141..c2c2448b24c 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
@@ -134,15 +134,14 @@ public abstract class MaterializationContext {
* Try to generate scan plan for materialization
* if MaterializationContext is already rewritten successfully, then
should generate new scan plan in later
* query rewrite, because one plan may hit the materialized view
repeatedly and the mv scan output
- * should be different
+ * should be different.
+ * This method should be called when query rewrite successfully
*/
public void tryReGenerateMvScanPlan(CascadesContext cascadesContext) {
- if (!this.matchedSuccessGroups.isEmpty()) {
- this.mvScanPlan = doGenerateMvPlan(cascadesContext);
- // mv output expression shuttle, this will be used to expression
rewrite
- this.mvExprToMvScanExprMapping =
ExpressionMapping.generate(this.mvPlanOutputShuttledExpressions,
- this.mvScanPlan.getExpressions());
- }
+ this.mvScanPlan = doGenerateMvPlan(cascadesContext);
+ // mv output expression shuttle, this will be used to expression
rewrite
+ this.mvExprToMvScanExprMapping =
ExpressionMapping.generate(this.mvPlanOutputShuttledExpressions,
+ this.mvScanPlan.getOutput());
}
public void addSlotMappingToCache(RelationMapping relationMapping,
SlotMapping slotMapping) {
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 608f3b5f5f0..f1a90fb940c 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
@@ -184,12 +184,13 @@ public class MaterializedViewUtils {
LogicalOlapScan mvScan = new LogicalOlapScan(
cascadesContext.getStatementContext().getNextRelationId(),
materializedView,
- ImmutableList.of(materializedView.getQualifiedDbName()),
- // this must be empty, or it will be used to sample
+ materializedView.getFullQualifiers(),
ImmutableList.of(),
+ materializedView.getBaseIndexId(),
+ PreAggStatus.on(),
+ // this must be empty, or it will be used to sample
ImmutableList.of(),
Optional.empty());
- mvScan = mvScan.withMaterializedIndexSelected(PreAggStatus.on(),
materializedView.getBaseIndexId());
List<NamedExpression> mvProjects =
mvScan.getOutput().stream().map(NamedExpression.class::cast)
.collect(Collectors.toList());
return new LogicalProject<Plan>(mvProjects, mvScan);
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/visitor/TableCollector.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/visitor/TableCollector.java
index 94b1eb89c2f..5ab6b7ef015 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/visitor/TableCollector.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/visitor/TableCollector.java
@@ -26,6 +26,7 @@ import org.apache.doris.nereids.trees.plans.Plan;
import org.apache.doris.nereids.trees.plans.logical.LogicalCatalogRelation;
import org.apache.doris.nereids.trees.plans.physical.PhysicalCatalogRelation;
import
org.apache.doris.nereids.trees.plans.visitor.TableCollector.TableCollectorContext;
+import org.apache.doris.qe.ConnectContext;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
@@ -72,7 +73,9 @@ public class TableCollector extends DefaultPlanVisitor<Plan,
TableCollectorConte
if (!context.isExpand()) {
return;
}
- MTMVCache expandedMv = MTMVCache.from(mtmv,
MTMVPlanUtil.createMTMVContext(mtmv));
+ // Make sure use only one connection context when in query to avoid
ConnectionContext.get() wrong
+ MTMVCache expandedMv = MTMVCache.from(mtmv,
context.getConnectContext() == null
+ ? MTMVPlanUtil.createMTMVContext(mtmv) :
context.getConnectContext());
expandedMv.getLogicalPlan().accept(this, context);
}
@@ -85,6 +88,7 @@ public class TableCollector extends DefaultPlanVisitor<Plan,
TableCollectorConte
private final Set<TableType> targetTableTypes;
// if expand the mv or not
private final boolean expand;
+ private ConnectContext connectContext;
public TableCollectorContext(Set<TableType> targetTableTypes, boolean
expand) {
this.targetTableTypes = targetTableTypes;
@@ -102,5 +106,13 @@ public class TableCollector extends
DefaultPlanVisitor<Plan, TableCollectorConte
public boolean isExpand() {
return expand;
}
+
+ public ConnectContext getConnectContext() {
+ return connectContext;
+ }
+
+ public void setConnectContext(ConnectContext connectContext) {
+ this.connectContext = connectContext;
+ }
}
}
diff --git
a/regression-test/suites/nereids_rules_p0/mv/nested/ddl/customer_create.sql
b/regression-test/suites/nereids_rules_p0/mv/nested/ddl/customer_create.sql
new file mode 100644
index 00000000000..d13101b70e0
--- /dev/null
+++ b/regression-test/suites/nereids_rules_p0/mv/nested/ddl/customer_create.sql
@@ -0,0 +1,15 @@
+CREATE TABLE IF NOT EXISTS `customer` (
+ `c_custkey` int(11) NOT NULL COMMENT "",
+ `c_name` varchar(26) NOT NULL COMMENT "",
+ `c_address` varchar(41) NOT NULL COMMENT "",
+ `c_city` varchar(11) NOT NULL COMMENT "",
+ `c_nation` varchar(16) NOT NULL COMMENT "",
+ `c_region` varchar(13) NOT NULL COMMENT "",
+ `c_phone` varchar(16) NOT NULL COMMENT "",
+ `c_mktsegment` varchar(11) NOT NULL COMMENT ""
+)
+UNIQUE KEY (`c_custkey`)
+DISTRIBUTED BY HASH(`c_custkey`) BUCKETS 1
+PROPERTIES (
+"replication_num" = "1"
+);
\ No newline at end of file
diff --git
a/regression-test/suites/nereids_rules_p0/mv/nested/ddl/customer_delete.sql
b/regression-test/suites/nereids_rules_p0/mv/nested/ddl/customer_delete.sql
new file mode 100644
index 00000000000..fe22a226fed
--- /dev/null
+++ b/regression-test/suites/nereids_rules_p0/mv/nested/ddl/customer_delete.sql
@@ -0,0 +1 @@
+truncate table customer;
\ No newline at end of file
diff --git
a/regression-test/suites/nereids_rules_p0/mv/nested/ddl/date_create.sql
b/regression-test/suites/nereids_rules_p0/mv/nested/ddl/date_create.sql
new file mode 100644
index 00000000000..cfab87b1d8b
--- /dev/null
+++ b/regression-test/suites/nereids_rules_p0/mv/nested/ddl/date_create.sql
@@ -0,0 +1,24 @@
+CREATE TABLE IF NOT EXISTS `date` (
+ `d_datekey` int(11) NOT NULL COMMENT "",
+ `d_date` varchar(20) NOT NULL COMMENT "",
+ `d_dayofweek` varchar(10) NOT NULL COMMENT "",
+ `d_month` varchar(11) NOT NULL COMMENT "",
+ `d_year` int(11) NOT NULL COMMENT "",
+ `d_yearmonthnum` int(11) NOT NULL COMMENT "",
+ `d_yearmonth` varchar(9) NOT NULL COMMENT "",
+ `d_daynuminweek` int(11) NOT NULL COMMENT "",
+ `d_daynuminmonth` int(11) NOT NULL COMMENT "",
+ `d_daynuminyear` int(11) NOT NULL COMMENT "",
+ `d_monthnuminyear` int(11) NOT NULL COMMENT "",
+ `d_weeknuminyear` int(11) NOT NULL COMMENT "",
+ `d_sellingseason` varchar(14) NOT NULL COMMENT "",
+ `d_lastdayinweekfl` int(11) NOT NULL COMMENT "",
+ `d_lastdayinmonthfl` int(11) NOT NULL COMMENT "",
+ `d_holidayfl` int(11) NOT NULL COMMENT "",
+ `d_weekdayfl` int(11) NOT NULL COMMENT ""
+)
+UNIQUE KEY (`d_datekey`)
+DISTRIBUTED BY HASH(`d_datekey`) BUCKETS 1
+PROPERTIES (
+"replication_num" = "1"
+);
\ No newline at end of file
diff --git
a/regression-test/suites/nereids_rules_p0/mv/nested/ddl/date_delete.sql
b/regression-test/suites/nereids_rules_p0/mv/nested/ddl/date_delete.sql
new file mode 100644
index 00000000000..12933cbbad9
--- /dev/null
+++ b/regression-test/suites/nereids_rules_p0/mv/nested/ddl/date_delete.sql
@@ -0,0 +1 @@
+truncate table `date`;
\ No newline at end of file
diff --git
a/regression-test/suites/nereids_rules_p0/mv/nested/ddl/lineorder_create.sql
b/regression-test/suites/nereids_rules_p0/mv/nested/ddl/lineorder_create.sql
new file mode 100644
index 00000000000..c066ea8a0d7
--- /dev/null
+++ b/regression-test/suites/nereids_rules_p0/mv/nested/ddl/lineorder_create.sql
@@ -0,0 +1,24 @@
+CREATE TABLE IF NOT EXISTS `lineorder` (
+ `lo_orderkey` bigint(20) NOT NULL COMMENT "",
+ `lo_linenumber` bigint(20) NOT NULL COMMENT "",
+ `lo_custkey` int(11) NOT NULL COMMENT "",
+ `lo_partkey` int(11) NOT NULL COMMENT "",
+ `lo_suppkey` int(11) NOT NULL COMMENT "",
+ `lo_orderdate` int(11) NOT NULL COMMENT "",
+ `lo_orderpriority` varchar(16) NOT NULL COMMENT "",
+ `lo_shippriority` int(11) NOT NULL COMMENT "",
+ `lo_quantity` bigint(20) NOT NULL COMMENT "",
+ `lo_extendedprice` bigint(20) NOT NULL COMMENT "",
+ `lo_ordtotalprice` bigint(20) NOT NULL COMMENT "",
+ `lo_discount` bigint(20) NOT NULL COMMENT "",
+ `lo_revenue` bigint(20) NOT NULL COMMENT "",
+ `lo_supplycost` bigint(20) NOT NULL COMMENT "",
+ `lo_tax` bigint(20) NOT NULL COMMENT "",
+ `lo_commitdate` bigint(20) NOT NULL COMMENT "",
+ `lo_shipmode` varchar(11) NOT NULL COMMENT ""
+)
+UNIQUE KEY (`lo_orderkey`, `lo_linenumber`)
+DISTRIBUTED BY HASH(`lo_orderkey`) BUCKETS 1
+PROPERTIES (
+"replication_num" = "1"
+);
\ No newline at end of file
diff --git
a/regression-test/suites/nereids_rules_p0/mv/nested/ddl/lineorder_delete.sql
b/regression-test/suites/nereids_rules_p0/mv/nested/ddl/lineorder_delete.sql
new file mode 100644
index 00000000000..329e040060e
--- /dev/null
+++ b/regression-test/suites/nereids_rules_p0/mv/nested/ddl/lineorder_delete.sql
@@ -0,0 +1 @@
+truncate table lineorder;
\ No newline at end of file
diff --git
a/regression-test/suites/nereids_rules_p0/mv/nested/ddl/lineorder_flat_create.sql
b/regression-test/suites/nereids_rules_p0/mv/nested/ddl/lineorder_flat_create.sql
new file mode 100644
index 00000000000..35faee0786c
--- /dev/null
+++
b/regression-test/suites/nereids_rules_p0/mv/nested/ddl/lineorder_flat_create.sql
@@ -0,0 +1,45 @@
+CREATE TABLE IF NOT EXISTS `lineorder_flat` (
+ `LO_ORDERDATE` date NOT NULL COMMENT "",
+ `LO_ORDERKEY` int(11) NOT NULL COMMENT "",
+ `LO_LINENUMBER` tinyint(4) NOT NULL COMMENT "",
+ `LO_CUSTKEY` int(11) NOT NULL COMMENT "",
+ `LO_PARTKEY` int(11) NOT NULL COMMENT "",
+ `LO_SUPPKEY` int(11) NOT NULL COMMENT "",
+ `LO_ORDERPRIORITY` varchar(100) NOT NULL COMMENT "",
+ `LO_SHIPPRIORITY` tinyint(4) NOT NULL COMMENT "",
+ `LO_QUANTITY` tinyint(4) NOT NULL COMMENT "",
+ `LO_EXTENDEDPRICE` int(11) NOT NULL COMMENT "",
+ `LO_ORDTOTALPRICE` int(11) NOT NULL COMMENT "",
+ `LO_DISCOUNT` tinyint(4) NOT NULL COMMENT "",
+ `LO_REVENUE` int(11) NOT NULL COMMENT "",
+ `LO_SUPPLYCOST` int(11) NOT NULL COMMENT "",
+ `LO_TAX` tinyint(4) NOT NULL COMMENT "",
+ `LO_COMMITDATE` date NOT NULL COMMENT "",
+ `LO_SHIPMODE` varchar(100) NOT NULL COMMENT "",
+ `C_NAME` varchar(100) NOT NULL COMMENT "",
+ `C_ADDRESS` varchar(100) NOT NULL COMMENT "",
+ `C_CITY` varchar(100) NOT NULL COMMENT "",
+ `C_NATION` varchar(100) NOT NULL COMMENT "",
+ `C_REGION` varchar(100) NOT NULL COMMENT "",
+ `C_PHONE` varchar(100) NOT NULL COMMENT "",
+ `C_MKTSEGMENT` varchar(100) NOT NULL COMMENT "",
+ `S_NAME` varchar(100) NOT NULL COMMENT "",
+ `S_ADDRESS` varchar(100) NOT NULL COMMENT "",
+ `S_CITY` varchar(100) NOT NULL COMMENT "",
+ `S_NATION` varchar(100) NOT NULL COMMENT "",
+ `S_REGION` varchar(100) NOT NULL COMMENT "",
+ `S_PHONE` varchar(100) NOT NULL COMMENT "",
+ `P_NAME` varchar(100) NOT NULL COMMENT "",
+ `P_MFGR` varchar(100) NOT NULL COMMENT "",
+ `P_CATEGORY` varchar(100) NOT NULL COMMENT "",
+ `P_BRAND` varchar(100) NOT NULL COMMENT "",
+ `P_COLOR` varchar(100) NOT NULL COMMENT "",
+ `P_TYPE` varchar(100) NOT NULL COMMENT "",
+ `P_SIZE` tinyint(4) NOT NULL COMMENT "",
+ `P_CONTAINER` varchar(100) NOT NULL COMMENT ""
+) ENGINE=OLAP
+UNIQUE KEY(`LO_ORDERDATE`, `LO_ORDERKEY`, `LO_LINENUMBER`)
+DISTRIBUTED BY HASH(`LO_ORDERKEY`) BUCKETS 1
+PROPERTIES (
+"replication_num" = "1"
+);
\ No newline at end of file
diff --git
a/regression-test/suites/nereids_rules_p0/mv/nested/ddl/lineorder_flat_delete.sql
b/regression-test/suites/nereids_rules_p0/mv/nested/ddl/lineorder_flat_delete.sql
new file mode 100644
index 00000000000..13f6c32bb65
--- /dev/null
+++
b/regression-test/suites/nereids_rules_p0/mv/nested/ddl/lineorder_flat_delete.sql
@@ -0,0 +1 @@
+truncate table lineorder_flat;
\ No newline at end of file
diff --git
a/regression-test/suites/nereids_rules_p0/mv/nested/ddl/part_create.sql
b/regression-test/suites/nereids_rules_p0/mv/nested/ddl/part_create.sql
new file mode 100644
index 00000000000..2405026a3cd
--- /dev/null
+++ b/regression-test/suites/nereids_rules_p0/mv/nested/ddl/part_create.sql
@@ -0,0 +1,16 @@
+CREATE TABLE IF NOT EXISTS `part` (
+ `p_partkey` int(11) NOT NULL COMMENT "",
+ `p_name` varchar(23) NOT NULL COMMENT "",
+ `p_mfgr` varchar(7) NOT NULL COMMENT "",
+ `p_category` varchar(8) NOT NULL COMMENT "",
+ `p_brand` varchar(10) NOT NULL COMMENT "",
+ `p_color` varchar(12) NOT NULL COMMENT "",
+ `p_type` varchar(26) NOT NULL COMMENT "",
+ `p_size` int(11) NOT NULL COMMENT "",
+ `p_container` varchar(11) NOT NULL COMMENT ""
+)
+UNIQUE KEY (`p_partkey`)
+DISTRIBUTED BY HASH(`p_partkey`) BUCKETS 1
+PROPERTIES (
+"replication_num" = "1"
+);
\ No newline at end of file
diff --git
a/regression-test/suites/nereids_rules_p0/mv/nested/ddl/part_delete.sql
b/regression-test/suites/nereids_rules_p0/mv/nested/ddl/part_delete.sql
new file mode 100644
index 00000000000..02c6abd2539
--- /dev/null
+++ b/regression-test/suites/nereids_rules_p0/mv/nested/ddl/part_delete.sql
@@ -0,0 +1 @@
+truncate table `part`;
\ No newline at end of file
diff --git
a/regression-test/suites/nereids_rules_p0/mv/nested/ddl/supplier_create.sql
b/regression-test/suites/nereids_rules_p0/mv/nested/ddl/supplier_create.sql
new file mode 100644
index 00000000000..1580a8fcca4
--- /dev/null
+++ b/regression-test/suites/nereids_rules_p0/mv/nested/ddl/supplier_create.sql
@@ -0,0 +1,14 @@
+CREATE TABLE IF NOT EXISTS `supplier` (
+ `s_suppkey` int(11) NOT NULL COMMENT "",
+ `s_name` varchar(26) NOT NULL COMMENT "",
+ `s_address` varchar(26) NOT NULL COMMENT "",
+ `s_city` varchar(11) NOT NULL COMMENT "",
+ `s_nation` varchar(16) NOT NULL COMMENT "",
+ `s_region` varchar(13) NOT NULL COMMENT "",
+ `s_phone` varchar(16) NOT NULL COMMENT ""
+)
+UNIQUE KEY (`s_suppkey`)
+DISTRIBUTED BY HASH(`s_suppkey`) BUCKETS 1
+PROPERTIES (
+"replication_num" = "1"
+);
\ No newline at end of file
diff --git
a/regression-test/suites/nereids_rules_p0/mv/nested/ddl/supplier_delete.sql
b/regression-test/suites/nereids_rules_p0/mv/nested/ddl/supplier_delete.sql
new file mode 100644
index 00000000000..39e663134ca
--- /dev/null
+++ b/regression-test/suites/nereids_rules_p0/mv/nested/ddl/supplier_delete.sql
@@ -0,0 +1 @@
+truncate table `supplier`;
\ No newline at end of file
diff --git
a/regression-test/suites/nereids_rules_p0/mv/nested/nested_materialized_view.groovy
b/regression-test/suites/nereids_rules_p0/mv/nested/nested_materialized_view.groovy
index 5a5ddc5b3c7..f7b47ef3959 100644
---
a/regression-test/suites/nereids_rules_p0/mv/nested/nested_materialized_view.groovy
+++
b/regression-test/suites/nereids_rules_p0/mv/nested/nested_materialized_view.groovy
@@ -16,22 +16,102 @@
// under the License.
suite("nested_materialized_view") {
+
+
+ def compare_res = { def stmt ->
+ sql "SET enable_materialized_view_rewrite=false"
+ def origin_res = sql stmt
+ logger.info("origin_res: " + origin_res)
+ sql "SET enable_materialized_view_rewrite=true"
+ def mv_origin_res = sql stmt
+ logger.info("mv_origin_res: " + mv_origin_res)
+ assertTrue((mv_origin_res == [] && origin_res == []) ||
(mv_origin_res.size() == origin_res.size()))
+ for (int row = 0; row < mv_origin_res.size(); row++) {
+ assertTrue(mv_origin_res[row].size() == origin_res[row].size())
+ for (int col = 0; col < mv_origin_res[row].size(); col++) {
+ assertTrue(mv_origin_res[row][col] == origin_res[row][col])
+ }
+ }
+ }
+
+ // ssb_sf1_p1 is writted to test unique key table merge correctly.
+ // It creates unique key table and sets bucket num to 1 in order to make
sure that
+ // many rowsets will be created during loading and then the merge process
will be triggered.
+
+ def tables = ["customer", "lineorder", "part", "date", "supplier"]
+ def columns =
["""c_custkey,c_name,c_address,c_city,c_nation,c_region,c_phone,c_mktsegment,no_use""",
+
"""lo_orderkey,lo_linenumber,lo_custkey,lo_partkey,lo_suppkey,lo_orderdate,lo_orderpriority,
+
lo_shippriority,lo_quantity,lo_extendedprice,lo_ordtotalprice,lo_discount,
+
lo_revenue,lo_supplycost,lo_tax,lo_commitdate,lo_shipmode,lo_dummy""",
+
"""p_partkey,p_name,p_mfgr,p_category,p_brand,p_color,p_type,p_size,p_container,p_dummy""",
+
"""d_datekey,d_date,d_dayofweek,d_month,d_year,d_yearmonthnum,d_yearmonth,
+
d_daynuminweek,d_daynuminmonth,d_daynuminyear,d_monthnuminyear,d_weeknuminyear,
+
d_sellingseason,d_lastdayinweekfl,d_lastdayinmonthfl,d_holidayfl,d_weekdayfl,d_dummy""",
+
"""s_suppkey,s_name,s_address,s_city,s_nation,s_region,s_phone,s_dummy"""]
+
+ for (String table in tables) {
+ sql new File("""${context.file.parent}/ddl/${table}_create.sql""").text
+ sql new File("""${context.file.parent}/ddl/${table}_delete.sql""").text
+ }
+ def i = 0
+ for (String tableName in tables) {
+ streamLoad {
+ // a default db 'regression_test' is specified in
+ // ${DORIS_HOME}/conf/regression-conf.groovy
+ table tableName
+
+ // default label is UUID:
+ // set 'label' UUID.randomUUID().toString()
+
+ // default column_separator is specify in doris fe config, usually
is '\t'.
+ // this line change to ','
+ set 'column_separator', '|'
+ set 'compress_type', 'GZ'
+ set 'columns', columns[i]
+
+
+ // relate to
${DORIS_HOME}/regression-test/data/demo/streamload_input.csv.
+ // also, you can stream load a http stream, e.g.
http://xxx/some.csv
+ file """${getS3Url()}/regression/ssb/sf1/${tableName}.tbl.gz"""
+
+ time 10000 // limit inflight 10s
+
+ // stream load action will check result, include Success status,
and NumberTotalRows == NumberLoadedRows
+
+ // if declared a check callback, the default check condition will
ignore.
+ // So you must check all condition
+ check { result, exception, startTime, endTime ->
+ if (exception != null) {
+ throw exception
+ }
+ log.info("Stream load result: ${result}".toString())
+ def json = parseJson(result)
+ assertEquals("success", json.Status.toLowerCase())
+ assertEquals(json.NumberTotalRows, json.NumberLoadedRows)
+ assertTrue(json.NumberLoadedRows > 0 && json.LoadBytes > 0)
+ }
+ }
+ i++
+ }
+ sql """ sync """
+
+
String db = context.config.getDbNameByFile(context.file)
sql "use ${db}"
sql "SET enable_nereids_planner=true"
sql "set runtime_filter_mode=OFF"
sql "SET enable_fallback_to_original_planner=false"
- sql "SET enable_materialized_view_rewrite=true"
sql "SET enable_nereids_timeout = false"
+ sql "SET enable_materialized_view_rewrite=true"
sql "SET enable_materialized_view_nest_rewrite = true"
def create_mtmv = { db_name, mv_name, mv_sql ->
sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name}"""
sql"""
- CREATE MATERIALIZED VIEW ${mv_name}
+ CREATE MATERIALIZED VIEW ${mv_name}
BUILD IMMEDIATE REFRESH COMPLETE ON MANUAL
DISTRIBUTED BY RANDOM BUCKETS 2
- PROPERTIES ('replication_num' = '1')
+ PROPERTIES ('replication_num' = '1')
AS ${mv_sql}
"""
@@ -50,8 +130,8 @@ suite("nested_materialized_view") {
o_orderstatus CHAR(1) NOT NULL,
o_totalprice DECIMALV3(15,2) NOT NULL,
o_orderdate DATE NOT NULL,
- o_orderpriority CHAR(15) NOT NULL,
- o_clerk CHAR(15) NOT NULL,
+ o_orderpriority CHAR(15) NOT NULL,
+ o_clerk CHAR(15) NOT NULL,
o_shippriority INTEGER NOT NULL,
o_comment VARCHAR(79) NOT NULL
)
@@ -104,7 +184,7 @@ suite("nested_materialized_view") {
ps_suppkey INTEGER NOT NULL,
ps_availqty INTEGER NOT NULL,
ps_supplycost DECIMALV3(15,2) NOT NULL,
- ps_comment VARCHAR(199) NOT NULL
+ ps_comment VARCHAR(199) NOT NULL
)
DUPLICATE KEY(ps_partkey, ps_suppkey)
DISTRIBUTED BY HASH(ps_partkey) BUCKETS 3
@@ -130,7 +210,7 @@ suite("nested_materialized_view") {
(3, 1, 'o', 33.5, '2023-12-10', 'a', 'b', 1, 'yy'),
(4, 2, 'o', 43.2, '2023-12-11', 'c','d',2, 'mm'),
(5, 2, 'o', 56.2, '2023-12-12', 'c','d',2, 'mi'),
- (5, 2, 'o', 1.2, '2023-12-12', 'c','d',2, 'mi');
+ (5, 2, 'o', 1.2, '2023-12-12', 'c','d',2, 'mi');
"""
sql """
@@ -192,7 +272,661 @@ suite("nested_materialized_view") {
sql("${query1_0}")
contains("mv1_0_inner_mv(mv1_0_inner_mv)")
}
-
order_qt_query1_1_after "${query1_0}"
+
+ // complex nest mv rewrite
+ create_mtmv(db, "mv1_a", """
+ select
+ lo_custkey,
+ lo_partkey,
+ lo_orderkey,
+ lo_orderdate,
+ sum(lo_extendedprice * lo_discount) as sum_value1
+ FROM
+ lineorder
+ inner join date on lo_orderdate = d_datekey
+ where
+ d_daynuminweek > 0 and lo_orderdate = 19930423
+ group by
+ lo_custkey,
+ lo_partkey,
+ lo_orderkey,
+ lo_orderdate;""")
+
+ create_mtmv(db, "mv2_a", """
+ select
+ lo_custkey,
+ lo_orderdate,
+ sum(lo_revenue * lo_discount) as sum_value2
+ FROM
+ lineorder
+ inner join customer on lo_custkey = c_custkey
+ inner join date on lo_orderdate = d_datekey
+ where
+ d_daynuminweek > 0 and lo_orderdate = 19930423
+ group by
+ lo_custkey,
+ lo_orderdate;""")
+
+ create_mtmv(db, "mv4_a", """
+ select
+ lo_partkey,
+ lo_orderdate,
+ sum(lo_extendedprice * lo_discount) as sum_value4
+ FROM
+ lineorder
+ inner join supplier on lo_suppkey = s_suppkey
+ inner join date on lo_orderdate = d_datekey
+ where
+ d_daynuminweek > 0 and lo_orderdate = 19930423
+ group by
+ lo_partkey,
+ lo_orderdate;""")
+
+ create_mtmv(db, "mv_all_6_a", """
+ select
+ '测试1' as nm,
+ '测试2' as t_nm,
+ t1.sum_value1,
+ t1.sum_value1 - t2.sum_value2,
+ t1.sum_value1 - t3.sum_value4,
+ t1.sum_value1 - t4.sum_value4,
+ t1.lo_custkey,
+ t5.p_name,
+ t6.d_yearmonthnum
+from
+ mv1_a t1
+ left join mv2_a t2 on t1.lo_custkey = t2.lo_custkey
+ and t1.lo_orderdate = t2.lo_orderdate
+ left join mv4_a t3 on t1.lo_partkey = t3.lo_partkey
+ and t1.lo_orderdate = t3.lo_orderdate
+ left join mv4_a t4 on t1.lo_partkey = t4.lo_partkey
+ and t1.lo_orderdate = t4.lo_orderdate
+ left join part t5 on t1.lo_partkey = t5.p_partkey
+ and t5.p_name = 'forest chiffon'
+ left join date t6 on t1.lo_orderdate = t6.d_datekey
+ and t6.d_sellingseason = 'Spring';
+ """)
+
+ create_mtmv(db, "mv1_b", """
+select
+ lo_custkey,
+ lo_partkey,
+ lo_orderkey,
+ lo_orderdate,
+ sum(lo_extendedprice * lo_discount) as sum_value1
+ FROM
+ lineorder
+ inner join date on lo_orderdate = d_datekey
+ where
+ d_daynuminweek > 0 and lo_orderdate = 19980421
+ group by
+ lo_custkey,
+ lo_partkey,
+ lo_orderkey,
+ lo_orderdate;""")
+
+ create_mtmv(db, "mv2_b", """
+ select
+ lo_custkey,
+ lo_orderdate,
+ sum(lo_revenue * lo_discount) as sum_value2
+ FROM
+ lineorder
+ inner join customer on lo_custkey = c_custkey
+ inner join date on lo_orderdate = d_datekey
+ where
+ d_daynuminweek > 0 and lo_orderdate = 19980421
+ group by
+ lo_custkey,
+ lo_orderdate;""")
+
+ create_mtmv(db, "mv4_b", """
+ select
+ lo_partkey,
+ lo_orderdate,
+ sum(lo_extendedprice * lo_discount) as sum_value4
+ FROM
+ lineorder
+ inner join supplier on lo_suppkey = s_suppkey
+ inner join date on lo_orderdate = d_datekey
+ where
+ d_daynuminweek > 0 and lo_orderdate = 19980421
+ group by
+ lo_partkey,
+ lo_orderdate;""")
+
+ create_mtmv(db, "mv_all_6_b", """
+ select
+ '测试1' as nm,
+ '测试2' as t_nm,
+ t1.sum_value1,
+ t1.sum_value1 - t2.sum_value2,
+ t1.sum_value1 - t3.sum_value4,
+ t1.sum_value1 - t4.sum_value4,
+ t1.lo_custkey,
+ t5.p_name,
+ t6.d_yearmonthnum
+from
+ mv1_b t1
+ left join mv2_b t2 on t1.lo_custkey = t2.lo_custkey
+ and t1.lo_orderdate = t2.lo_orderdate
+ left join mv4_b t3 on t1.lo_partkey = t3.lo_partkey
+ and t1.lo_orderdate = t3.lo_orderdate
+ left join mv4_b t4 on t1.lo_partkey = t4.lo_partkey
+ and t1.lo_orderdate = t4.lo_orderdate
+ left join part t5 on t1.lo_partkey = t5.p_partkey
+ and t5.p_name = 'forest chiffon'
+ left join date t6 on t1.lo_orderdate = t6.d_datekey
+ and t6.d_sellingseason = 'Spring';
+ """)
+
+
+ create_mtmv(db, "mv1_c", """
+ select
+ lo_custkey,
+ lo_partkey,
+ lo_orderkey,
+ lo_orderdate,
+ sum(lo_extendedprice * lo_discount) as sum_value1
+ FROM
+ lineorder
+ inner join date on lo_orderdate = d_datekey
+ where
+ d_daynuminweek > 0 and lo_orderdate = 19940413
+ group by
+ lo_custkey,
+ lo_partkey,
+ lo_orderkey,
+ lo_orderdate;""")
+
+ create_mtmv(db, "mv2_c", """
+ select
+ lo_custkey,
+ lo_orderdate,
+ sum(lo_revenue * lo_discount) as sum_value2
+ FROM
+ lineorder
+ inner join customer on lo_custkey = c_custkey
+ inner join date on lo_orderdate = d_datekey
+ where
+ d_daynuminweek > 0 and lo_orderdate = 19940413
+ group by
+ lo_custkey,
+ lo_orderdate;""")
+
+ create_mtmv(db, "mv4_c", """
+ select
+ lo_partkey,
+ lo_orderdate,
+ sum(lo_extendedprice * lo_discount) as sum_value4
+ FROM
+ lineorder
+ inner join supplier on lo_suppkey = s_suppkey
+ inner join date on lo_orderdate = d_datekey
+ where
+ d_daynuminweek > 0 and lo_orderdate = 19940413
+ group by
+ lo_partkey,
+ lo_orderdate;""")
+
+ create_mtmv(db, "mv_all_6_c", """
+ select
+ '测试1' as nm,
+ '测试2' as t_nm,
+ t1.sum_value1,
+ t1.sum_value1 - t2.sum_value2,
+ t1.sum_value1 - t3.sum_value4,
+ t1.sum_value1 - t4.sum_value4,
+ t1.lo_custkey,
+ t5.p_name,
+ t6.d_yearmonthnum
+from
+ mv1_c t1
+ left join mv2_c t2 on t1.lo_custkey = t2.lo_custkey
+ and t1.lo_orderdate = t2.lo_orderdate
+ left join mv4_c t3 on t1.lo_partkey = t3.lo_partkey
+ and t1.lo_orderdate = t3.lo_orderdate
+ left join mv4_c t4 on t1.lo_partkey = t4.lo_partkey
+ and t1.lo_orderdate = t4.lo_orderdate
+ left join part t5 on t1.lo_partkey = t5.p_partkey
+ and t5.p_name = 'forest chiffon'
+ left join date t6 on t1.lo_orderdate = t6.d_datekey
+ and t6.d_sellingseason = 'Spring';
+ """)
+
+
+ create_mtmv(db, "mv1_d", """
+ select
+ lo_custkey,
+ lo_partkey,
+ lo_orderkey,
+ lo_orderdate,
+ sum(lo_extendedprice * lo_discount) as sum_value1
+ FROM
+ lineorder
+ inner join date on lo_orderdate = d_datekey
+ where
+ d_daynuminweek > 0 and lo_orderdate = 19940218
+ group by
+ lo_custkey,
+ lo_partkey,
+ lo_orderkey,
+ lo_orderdate;""")
+
+ create_mtmv(db, "mv2_d", """
+ select
+ lo_custkey,
+ lo_orderdate,
+ sum(lo_revenue * lo_discount) as sum_value2
+ FROM
+ lineorder
+ inner join customer on lo_custkey = c_custkey
+ inner join date on lo_orderdate = d_datekey
+ where
+ d_daynuminweek > 0 and lo_orderdate = 19940218
+ group by
+ lo_custkey,
+ lo_orderdate;""")
+
+ create_mtmv(db, "mv4_d", """
+ select
+ lo_partkey,
+ lo_orderdate,
+ sum(lo_extendedprice * lo_discount) as sum_value4
+ FROM
+ lineorder
+ inner join supplier on lo_suppkey = s_suppkey
+ inner join date on lo_orderdate = d_datekey
+ where
+ d_daynuminweek > 0 and lo_orderdate = 19940218
+ group by
+ lo_partkey,
+ lo_orderdate;""")
+
+ create_mtmv(db, "mv_all_6_d", """
+ select
+ '测试1' as nm,
+ '测试2' as t_nm,
+ t1.sum_value1,
+ t1.sum_value1 - t2.sum_value2,
+ t1.sum_value1 - t3.sum_value4,
+ t1.sum_value1 - t4.sum_value4,
+ t1.lo_custkey,
+ t5.p_name,
+ t6.d_yearmonthnum
+from
+ mv1_d t1
+ left join mv2_d t2 on t1.lo_custkey = t2.lo_custkey
+ and t1.lo_orderdate = t2.lo_orderdate
+ left join mv4_d t3 on t1.lo_partkey = t3.lo_partkey
+ and t1.lo_orderdate = t3.lo_orderdate
+ left join mv4_d t4 on t1.lo_partkey = t4.lo_partkey
+ and t1.lo_orderdate = t4.lo_orderdate
+ left join part t5 on t1.lo_partkey = t5.p_partkey
+ and t5.p_name = 'forest chiffon'
+ left join date t6 on t1.lo_orderdate = t6.d_datekey
+ and t6.d_sellingseason = 'Spring';
+ """)
+
+ def query2_0 = """
+select * from (
+ select
+ '测试1' as nm,
+ '测试2' as t_nm,
+ t1.sum_value1,
+ t1.sum_value1 - t2.sum_value2,
+ t1.sum_value1 - t3.sum_value3,
+ t1.sum_value1 - t4.sum_value4,
+ t1.lo_custkey,
+ t5.p_name,
+ t6.d_yearmonthnum
+ from
+ (
+ select
+ lo_custkey,
+ lo_partkey,
+ lo_orderkey,
+ lo_orderdate,
+ sum(lo_extendedprice * lo_discount) as sum_value1
+ FROM
+ lineorder
+ inner join date on lo_orderdate = d_datekey
+ where
+ d_daynuminweek > 0
+ and lo_orderdate = 19930423
+ group by
+ lo_custkey,
+ lo_partkey,
+ lo_orderkey,
+ lo_orderdate
+ ) t1
+ left join (
+ select
+ lo_custkey,
+ lo_orderdate,
+ sum(lo_revenue * lo_discount) as sum_value2
+ FROM
+ lineorder
+ inner join customer on lo_custkey = c_custkey
+ inner join date on lo_orderdate = d_datekey
+ where
+ d_daynuminweek > 0
+ and lo_orderdate = 19930423
+ group by
+ lo_custkey,
+ lo_orderdate
+ ) t2 on t1.lo_custkey = t2.lo_custkey
+ and t1.lo_orderdate = t2.lo_orderdate
+ left join (
+ select
+ lo_partkey,
+ lo_orderdate,
+ sum(lo_extendedprice * lo_discount) as sum_value3
+ FROM
+ lineorder
+ inner join supplier on lo_suppkey = s_suppkey
+ inner join date on lo_orderdate = d_datekey
+ where
+ d_daynuminweek > 0
+ and lo_orderdate = 19930423
+ group by
+ lo_partkey,
+ lo_orderdate
+ ) t3 on t1.lo_partkey = t3.lo_partkey
+ and t1.lo_orderdate = t3.lo_orderdate
+ left join (
+ select
+ lo_partkey,
+ lo_orderdate,
+ sum(lo_extendedprice * lo_discount) as sum_value4
+ FROM
+ lineorder
+ inner join supplier on lo_suppkey = s_suppkey
+ inner join date on lo_orderdate = d_datekey
+ where
+ d_daynuminweek > 0
+ and lo_orderdate = 19930423
+ group by
+ lo_partkey,
+ lo_orderdate
+ ) t4 on t1.lo_partkey = t4.lo_partkey
+ and t1.lo_orderdate = t4.lo_orderdate
+ left join part t5 on t1.lo_partkey = t5.p_partkey
+ and t5.p_name = 'forest chiffon'
+ left join date t6 on t1.lo_orderdate = t6.d_datekey
+ and t6.d_sellingseason = 'Spring'
+ union all
+ select
+ '测试1' as nm,
+ '测试2' as t_nm,
+ t1.sum_value1,
+ t1.sum_value1 - t2.sum_value2,
+ t1.sum_value1 - t3.sum_value3,
+ t1.sum_value1 - t4.sum_value4,
+ t1.lo_custkey,
+ t5.p_name,
+ t6.d_yearmonthnum
+ from
+ (
+ select
+ lo_custkey,
+ lo_partkey,
+ lo_orderkey,
+ lo_orderdate,
+ sum(lo_extendedprice * lo_discount) as sum_value1
+ FROM
+ lineorder
+ inner join date on lo_orderdate = d_datekey
+ where
+ d_daynuminweek > 0
+ and lo_orderdate = 19980421
+ group by
+ lo_custkey,
+ lo_partkey,
+ lo_orderkey,
+ lo_orderdate
+ ) t1
+ left join (
+ select
+ lo_custkey,
+ lo_orderdate,
+ sum(lo_revenue * lo_discount) as sum_value2
+ FROM
+ lineorder
+ inner join customer on lo_custkey = c_custkey
+ inner join date on lo_orderdate = d_datekey
+ where
+ d_daynuminweek > 0
+ and lo_orderdate = 19980421
+ group by
+ lo_custkey,
+ lo_orderdate
+ ) t2 on t1.lo_custkey = t2.lo_custkey
+ and t1.lo_orderdate = t2.lo_orderdate
+ left join (
+ select
+ lo_partkey,
+ lo_orderdate,
+ sum(lo_extendedprice * lo_discount) as sum_value3
+ FROM
+ lineorder
+ inner join supplier on lo_suppkey = s_suppkey
+ inner join date on lo_orderdate = d_datekey
+ where
+ d_daynuminweek > 0
+ and lo_orderdate = 19980421
+ group by
+ lo_partkey,
+ lo_orderdate
+ ) t3 on t1.lo_partkey = t3.lo_partkey
+ and t1.lo_orderdate = t3.lo_orderdate
+ left join (
+ select
+ lo_partkey,
+ lo_orderdate,
+ sum(lo_extendedprice * lo_discount) as sum_value4
+ FROM
+ lineorder
+ inner join supplier on lo_suppkey = s_suppkey
+ inner join date on lo_orderdate = d_datekey
+ where
+ d_daynuminweek > 0
+ and lo_orderdate = 19980421
+ group by
+ lo_partkey,
+ lo_orderdate
+ ) t4 on t1.lo_partkey = t4.lo_partkey
+ and t1.lo_orderdate = t4.lo_orderdate
+ left join part t5 on t1.lo_partkey = t5.p_partkey
+ and t5.p_name = 'forest chiffon'
+ left join date t6 on t1.lo_orderdate = t6.d_datekey
+ and t6.d_sellingseason = 'Spring'
+ union ALL
+ select
+ '测试1' as nm,
+ '测试2' as t_nm,
+ t1.sum_value1,
+ t1.sum_value1 - t2.sum_value2,
+ t1.sum_value1 - t3.sum_value3,
+ t1.sum_value1 - t4.sum_value4,
+ t1.lo_custkey,
+ t5.p_name,
+ t6.d_yearmonthnum
+ from
+ (
+ select
+ lo_custkey,
+ lo_partkey,
+ lo_orderkey,
+ lo_orderdate,
+ sum(lo_extendedprice * lo_discount) as sum_value1
+ FROM
+ lineorder
+ inner join date on lo_orderdate = d_datekey
+ where
+ d_daynuminweek > 0
+ and lo_orderdate = 19940413
+ group by
+ lo_custkey,
+ lo_partkey,
+ lo_orderkey,
+ lo_orderdate
+ ) t1
+ left join (
+ select
+ lo_custkey,
+ lo_orderdate,
+ sum(lo_revenue * lo_discount) as sum_value2
+ FROM
+ lineorder
+ inner join customer on lo_custkey = c_custkey
+ inner join date on lo_orderdate = d_datekey
+ where
+ d_daynuminweek > 0
+ and lo_orderdate = 19940413
+ group by
+ lo_custkey,
+ lo_orderdate
+ ) t2 on t1.lo_custkey = t2.lo_custkey
+ and t1.lo_orderdate = t2.lo_orderdate
+ left join (
+ select
+ lo_partkey,
+ lo_orderdate,
+ sum(lo_extendedprice * lo_discount) as sum_value3
+ FROM
+ lineorder
+ inner join supplier on lo_suppkey = s_suppkey
+ inner join date on lo_orderdate = d_datekey
+ where
+ d_daynuminweek > 0
+ and lo_orderdate = 19940413
+ group by
+ lo_partkey,
+ lo_orderdate
+ ) t3 on t1.lo_partkey = t3.lo_partkey
+ and t1.lo_orderdate = t3.lo_orderdate
+ left join (
+ select
+ lo_partkey,
+ lo_orderdate,
+ sum(lo_extendedprice * lo_discount) as sum_value4
+ FROM
+ lineorder
+ inner join supplier on lo_suppkey = s_suppkey
+ inner join date on lo_orderdate = d_datekey
+ where
+ d_daynuminweek > 0
+ and lo_orderdate = 19940413
+ group by
+ lo_partkey,
+ lo_orderdate
+ ) t4 on t1.lo_partkey = t4.lo_partkey
+ and t1.lo_orderdate = t4.lo_orderdate
+ left join part t5 on t1.lo_partkey = t5.p_partkey
+ and t5.p_name = 'forest chiffon'
+ left join date t6 on t1.lo_orderdate = t6.d_datekey
+ and t6.d_sellingseason = 'Spring'
+ UNION ALL
+ select
+ '测试1' as nm,
+ '测试2' as t_nm,
+ t1.sum_value1,
+ t1.sum_value1 - t2.sum_value2,
+ t1.sum_value1 - t3.sum_value3,
+ t1.sum_value1 - t4.sum_value4,
+ t1.lo_custkey,
+ t5.p_name,
+ t6.d_yearmonthnum
+ from
+ (
+ select
+ lo_custkey,
+ lo_partkey,
+ lo_orderkey,
+ lo_orderdate,
+ sum(lo_extendedprice * lo_discount) as sum_value1
+ FROM
+ lineorder
+ inner join date on lo_orderdate = d_datekey
+ where
+ d_daynuminweek > 0
+ and lo_orderdate = 19940218
+ group by
+ lo_custkey,
+ lo_partkey,
+ lo_orderkey,
+ lo_orderdate
+ ) t1
+ left join (
+ select
+ lo_custkey,
+ lo_orderdate,
+ sum(lo_revenue * lo_discount) as sum_value2
+ FROM
+ lineorder
+ inner join customer on lo_custkey = c_custkey
+ inner join date on lo_orderdate = d_datekey
+ where
+ d_daynuminweek > 0
+ and lo_orderdate = 19940218
+ group by
+ lo_custkey,
+ lo_orderdate
+ ) t2 on t1.lo_custkey = t2.lo_custkey
+ and t1.lo_orderdate = t2.lo_orderdate
+ left join (
+ select
+ lo_partkey,
+ lo_orderdate,
+ sum(lo_extendedprice * lo_discount) as sum_value3
+ FROM
+ lineorder
+ inner join supplier on lo_suppkey = s_suppkey
+ inner join date on lo_orderdate = d_datekey
+ where
+ d_daynuminweek > 0
+ and lo_orderdate = 19940218
+ group by
+ lo_partkey,
+ lo_orderdate
+ ) t3 on t1.lo_partkey = t3.lo_partkey
+ and t1.lo_orderdate = t3.lo_orderdate
+ left join (
+ select
+ lo_partkey,
+ lo_orderdate,
+ sum(lo_extendedprice * lo_discount) as sum_value4
+ FROM
+ lineorder
+ inner join supplier on lo_suppkey = s_suppkey
+ inner join date on lo_orderdate = d_datekey
+ where
+ d_daynuminweek > 0
+ and lo_orderdate = 19940218
+ group by
+ lo_partkey,
+ lo_orderdate
+ ) t4 on t1.lo_partkey = t4.lo_partkey
+ and t1.lo_orderdate = t4.lo_orderdate
+ left join part t5 on t1.lo_partkey = t5.p_partkey
+ and t5.p_name = 'forest chiffon'
+ left join date t6 on t1.lo_orderdate = t6.d_datekey
+ and t6.d_sellingseason = 'Spring'
+) t order by 1,2,3,4,5,6,7,8,9;
+ """
+
+ sql "SET enable_materialized_view_rewrite= true"
+ sql "SET enable_materialized_view_nest_rewrite = true"
+ explain {
+ sql("${query2_0}")
+ check {result ->
+ result.contains("mv_all_6_a(mv_all_6_a)") &&
result.contains("mv_all_6_b(mv_all_6_b)")
+ && result.contains("mv_all_6_c(mv_all_6_c)") &&
result.contains("mv_all_6_d(mv_all_6_d)")
+ }
+ }
+ // Compare result when before and after mv rewrite
+ compare_res(query2_0)
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
