This is an automated email from the ASF dual-hosted git repository.
starocean999 pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 24e34d3d021 [fix](sync mv) fix rewrite wrongly with sync mv (#39284)
24e34d3d021 is described below
commit 24e34d3d021c642e9ef2d6dddc937a89c021a5dd
Author: zhangyuan <ayuanzh...@tencent.com>
AuthorDate: Mon Nov 25 09:56:38 2024 +0800
[fix](sync mv) fix rewrite wrongly with sync mv (#39284)
Aggregate table rewrite wrongly with sync mv:
drop table if exists t1;
CREATE TABLE IF NOT EXISTS t1 (
k int,
a int,
int_value int sum,
char_value char(10) max,
date_value date max
)
ENGINE=OLAP
aggregate KEY(k,a)
DISTRIBUTED BY HASH(k) BUCKETS 2 properties("replication_num" = "1");
insert into t1 values
(1,1,1,'a', '2020-12-01'),
(2,2,2,'b', '2021-12-01'),
(3,2,2,'c', '2022-12-01'),
(4,2,4,'c', '2023-12-01');
mysql> select a from t1;
+------+
| a |
+------+
| 2 |
| 1 |
| 2 |
| 2 |
+------+
4 rows in set (0.01 sec)
CREATE MATERIALIZED VIEW v_t1
AS
select k%2 as kk,a,max(date_value) from t1 group by kk, a;
select JobName from mv_infos('database'='test') where Name = 'v_t1';
select sleep(2);
// after rewire with v_t1, the result only have 3 rows
mysql> select a from t1;
+------+
| a |
+------+
| 2 |
| 2 |
| 1 |
+------+
3 rows in set (0.01 sec)
## solution
When rewriting query with aggregate table's sync mv, it is not only
necessary to check the key size, but also to check all key columns;
---
.../mv/AbstractSelectMaterializedIndexRule.java | 12 ++
.../SelectMaterializedIndexWithoutAggregate.java | 1 +
.../mv/join/inner/inner_join_x.out | 25 ++++
.../mv/join/inner/inner_join_x.groovy | 128 +++++++++++++++++++++
4 files changed, 166 insertions(+)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/mv/AbstractSelectMaterializedIndexRule.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/mv/AbstractSelectMaterializedIndexRule.java
index f17ab1c96bd..357883d1f71 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/mv/AbstractSelectMaterializedIndexRule.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/mv/AbstractSelectMaterializedIndexRule.java
@@ -144,6 +144,18 @@ public abstract class AbstractSelectMaterializedIndexRule {
return prunedExpr;
}
+ protected static boolean containAllKeyColumns(OlapTable table,
MaterializedIndex index) {
+ Set<String> mvColNames =
table.getKeyColumnsByIndexId(index.getId()).stream()
+ .map(c ->
normalizeName(parseMvColumnToSql(c.getNameWithoutMvPrefix())))
+ .collect(Collectors.toCollection(() -> new
TreeSet<String>(String.CASE_INSENSITIVE_ORDER)));
+
+ Set<String> keyColNames = table.getBaseSchemaKeyColumns().stream()
+ .map(c ->
normalizeName(parseMvColumnToSql(c.getNameWithoutMvPrefix())))
+ .collect(Collectors.toCollection(() -> new
TreeSet<String>(String.CASE_INSENSITIVE_ORDER)));
+
+ return keyColNames.containsAll(mvColNames);
+ }
+
protected static boolean containAllRequiredColumns(MaterializedIndex
index, LogicalOlapScan scan,
Set<Slot> requiredScanOutput, Set<? extends Expression>
requiredExpr, Set<Expression> predicateExpr) {
OlapTable table = scan.getTable();
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/mv/SelectMaterializedIndexWithoutAggregate.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/mv/SelectMaterializedIndexWithoutAggregate.java
index 76278fe48ee..3a589108a09 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/mv/SelectMaterializedIndexWithoutAggregate.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/mv/SelectMaterializedIndexWithoutAggregate.java
@@ -234,6 +234,7 @@ public class SelectMaterializedIndexWithoutAggregate
extends AbstractSelectMater
// So only base index and indexes that have all the keys could be
used.
List<MaterializedIndex> candidates =
table.getVisibleIndex().stream()
.filter(index ->
table.getKeyColumnsByIndexId(index.getId()).size() == baseIndexKeySize)
+ .filter(index -> containAllKeyColumns(table, index))
.filter(index -> containAllRequiredColumns(index, scan,
requiredScanOutputSupplier.get(),
requiredExpr.get(), predicatesSupplier.get()))
.collect(Collectors.toList());
diff --git
a/regression-test/data/nereids_rules_p0/mv/join/inner/inner_join_x.out
b/regression-test/data/nereids_rules_p0/mv/join/inner/inner_join_x.out
new file mode 100644
index 00000000000..201aca1c08f
--- /dev/null
+++ b/regression-test/data/nereids_rules_p0/mv/join/inner/inner_join_x.out
@@ -0,0 +1,25 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !query_before --
+1
+2
+2
+2
+
+-- !query_after --
+1
+2
+2
+2
+
+-- !query_before --
+1
+2
+2
+2
+
+-- !query_after --
+1
+2
+2
+2
+
diff --git
a/regression-test/suites/nereids_rules_p0/mv/join/inner/inner_join_x.groovy
b/regression-test/suites/nereids_rules_p0/mv/join/inner/inner_join_x.groovy
new file mode 100644
index 00000000000..5f8e150385a
--- /dev/null
+++ b/regression-test/suites/nereids_rules_p0/mv/join/inner/inner_join_x.groovy
@@ -0,0 +1,128 @@
+// 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("inner_join_x") {
+ String db = context.config.getDbNameByFile(context.file)
+ sql "use ${db}"
+ sql "set runtime_filter_mode=OFF"
+ sql "set enable_sync_mv_cost_based_rewrite=false"
+
+
+ // ======================= test table with aggregate key
============================
+ sql """
+ drop table if exists t1;
+ """
+
+ sql """
+ CREATE TABLE IF NOT EXISTS t1 (
+ k int,
+ a int,
+ int_value int sum,
+ char_value char(10) max,
+ date_value date max
+ )
+ ENGINE=OLAP
+ aggregate KEY(k,a)
+ DISTRIBUTED BY HASH(k) BUCKETS 2 properties("replication_num" =
"1")
+
+ """
+
+ def mv_name="v_t1"
+ createMV ( """
+ create materialized view ${mv_name} as select k%2 as kk,a,
sum(int_value), max(date_value) from t1 group by kk, a;
+ """)
+
+ sql """
+ insert into t1 values
+ (1,1,1,'a', '2020-12-01'),
+ (2,2,2,'b', '2021-12-01'),
+ (3,2,2,'c', '2022-12-01'),
+ (4,2,4,'c', '2023-12-01');
+ """
+
+ def query = """
+ select a from t1
+ """
+
+ explain {
+ sql("${query}")
+ notContains("${mv_name}(${mv_name})")
+ }
+
+ order_qt_query_before "${query}"
+
+
+ sql """ DROP MATERIALIZED VIEW IF EXISTS ${mv_name} on t1"""
+
+ order_qt_query_after "${query}"
+
+ sql """
+ drop table if exists t1
+ """
+
+ // ======================= test table with duplicate key
============================
+ sql """
+ drop table if exists t1;
+ """
+
+ sql """
+ CREATE TABLE IF NOT EXISTS t1 (
+ k int,
+ a int,
+ int_value int,
+ char_value char(10),
+ date_value date
+ )
+ ENGINE=OLAP
+ duplicate KEY(k,a)
+ DISTRIBUTED BY HASH(k) BUCKETS 2 properties("replication_num" =
"1")
+
+ """
+
+ mv_name="v_t1"
+ createMV ( """
+ create materialized view ${mv_name} as select k%2 as kk,a,
sum(int_value), max(date_value) from t1 group by kk, a;
+ """)
+
+ sql """
+ insert into t1 values
+ (1,1,1,'a', '2020-12-01'),
+ (2,2,2,'b', '2021-12-01'),
+ (3,2,2,'c', '2022-12-01'),
+ (4,2,4,'c', '2023-12-01');
+ """
+
+ query = """
+ select a from t1
+ """
+
+ explain {
+ sql("${query}")
+ notContains("t1(${mv_name})")
+ }
+
+ order_qt_query_before "${query}"
+
+
+ sql """ DROP MATERIALIZED VIEW IF EXISTS ${mv_name} on t1"""
+
+ order_qt_query_after "${query}"
+
+ sql """
+ drop table if exists t1
+ """
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
