This is an automated email from the ASF dual-hosted git repository.
yujun777 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 e14fa04db02 [fix](ivm) Mask base table sequence column in IVM base
scan (#69210)
e14fa04db02 is described below
commit e14fa04db0256406e948bf02062a1431f6639cd6
Author: yujun <[email protected]>
AuthorDate: Mon Oct 19 15:16:37 2026 +0800
[fix](ivm) Mask base table sequence column in IVM base scan (#69210)
An incremental materialized view whose base table carries a BIGINT
sequence column (`function_column.sequence_col`) can silently drop an
updated row. After an `UPDATE` on a base table and an `INCREMENTAL`
refresh, the refresh task reports SUCCESS, but the row is left with
`__DORIS_DELETE_SIGN__ = 1` and disappears; a later `INCREMENTAL`
refresh cannot restore it.
Root cause: the snapshot-side base `OlapScan` leaks its own hidden
`__DORIS_SEQUENCE_COL__` into the normalized join plan. When the delta
side is the RIGHT child of a join, the leaked base sequence appears
before the delta-allocated one, so the merge-on-write merge binds the
base value and the delete sign wins.
Fix: `IvmNormalizeUniqKeysMTMV` now also drops the base table's
`__DORIS_SEQUENCE_COL__` from the base scan output, next to the existing
IVM hidden columns. Only the sequence column needs masking: the base
delete sign, version and commit tso are recomputed by the plan (and
pruned when unused), and the base row lsn is the row identity IVM reads
for DUP detail tables.
Reproduction before the fix (base `dim1` carries a BIGINT sequence
column, INNER JOIN, refresh INCREMENTAL):
```
UPDATE test_ivm_base_seq_dim1 SET name = 'a2' WHERE id = 1;
REFRESH MATERIALIZED VIEW test_ivm_base_seq_mv_inner INCREMENTAL;
-- expected: 1 100 a2 actual (bug): the row is gone
---
.../main/java/org/apache/doris/mtmv/ivm/AGENTS.md | 18 ++
.../doris/mtmv/ivm/IvmNormalizeUniqKeysMTMV.java | 3 +-
.../java/org/apache/doris/mtmv/ivm/IvmUtil.java | 14 +
.../org/apache/doris/mtmv/ivm/IvmUtilTest.java | 14 +
.../ivm/test_ivm_base_sequence_isolation.out | 81 ++++++
.../ivm/test_ivm_base_sequence_isolation.groovy | 321 +++++++++++++++++++++
6 files changed, 450 insertions(+), 1 deletion(-)
diff --git a/fe/fe-core/src/main/java/org/apache/doris/mtmv/ivm/AGENTS.md
b/fe/fe-core/src/main/java/org/apache/doris/mtmv/ivm/AGENTS.md
index c60d063fb2d..8b0a0ea1953 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/mtmv/ivm/AGENTS.md
+++ b/fe/fe-core/src/main/java/org/apache/doris/mtmv/ivm/AGENTS.md
@@ -57,6 +57,24 @@ Run all IVM regression suites under
`regression-test/suites/mtmv_p0/ivm/`:
This runs every suite in the `ivm` subdirectory. New IVM suites are added over
time — the `-d mtmv_p0/ivm` flag automatically picks them up.
+### Disable Transparent Rewrite
+
+A newly added IVM suite must turn both transparent-rewrite paths off before it
runs any statement:
+
+```groovy
+sql "SET enable_materialized_view_rewrite = false"
+sql "SET enable_dml_materialized_view_rewrite = false"
+```
+
+Both default to true. The first is what makes an MV-verifying suite
meaningful: such a suite checks the MV by
+re-running the MV's own definition SQL over the base tables, and with the
rewrite on that query is answered
+from the MV itself, so both sides of the comparison move together and the
suite can no longer fail. The second
+stops a base-table `INSERT`/`UPDATE`/`DELETE` from being redirected into the
MV, which would leave the
+incremental path the suite exists to exercise unused.
+
+The one exception is a suite whose subject *is* the rewrite: it keeps whatever
setting it needs to assert that
+an MV is hit, and says so where it does.
+
### FE Unit Tests
Run all IVM-related FE unit tests. The relevant test files are located in:
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/mtmv/ivm/IvmNormalizeUniqKeysMTMV.java
b/fe/fe-core/src/main/java/org/apache/doris/mtmv/ivm/IvmNormalizeUniqKeysMTMV.java
index 414e613ea22..36ed9203892 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/mtmv/ivm/IvmNormalizeUniqKeysMTMV.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/mtmv/ivm/IvmNormalizeUniqKeysMTMV.java
@@ -307,7 +307,8 @@ public class IvmNormalizeUniqKeysMTMV extends
DefaultPlanRewriter<IvmNormalizeUn
outputsBuilder.add(renamedBaseRowIdAlias);
}
outputsBuilder.addAll(scan.getOutput().stream()
- .filter(slot -> !IvmUtil.isIvmHiddenColumn(slot.getName()))
+ .filter(slot -> !IvmUtil.isIvmHiddenColumn(slot.getName())
+ &&
!IvmUtil.isBaseScanStorageColumnToMask(slot.getName()))
.collect(ImmutableList.toImmutableList()));
List<NamedExpression> outputs = outputsBuilder.build();
LogicalProject<?> result = new LogicalProject<>(outputs, scan);
diff --git a/fe/fe-core/src/main/java/org/apache/doris/mtmv/ivm/IvmUtil.java
b/fe/fe-core/src/main/java/org/apache/doris/mtmv/ivm/IvmUtil.java
index e1f82e9acdb..dc602278819 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/mtmv/ivm/IvmUtil.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/mtmv/ivm/IvmUtil.java
@@ -84,6 +84,20 @@ public class IvmUtil {
return columnName != null &&
columnName.startsWith(Column.IVM_HIDDEN_COLUMN_PREFIX);
}
+ /**
+ * A base scan's own storage column that must not be carried into an IVM
plan, because its name
+ * collides with a column the delta rewrite binds by name and it is read
by the plan above.
+ * <p>
+ * The base table's sequence column ({@code Column.SEQUENCE_COL}) is
exactly such a column: IVM owns
+ * the MV's sequence and binds it by name, so a base sequence surviving
normalization shadows the
+ * delta-allocated one and the merge-on-write merge retracts an updated
row. The base delete sign,
+ * version and commit tso are recomputed by the plan and pruned when
unused, and the base row lsn is
+ * the row identity IVM reads for DUP detail tables, so none of them is
masked here.
+ */
+ public static boolean isBaseScanStorageColumnToMask(String columnName) {
+ return Column.SEQUENCE_COL.equalsIgnoreCase(columnName);
+ }
+
/**
* Sanitizes an identity key name before embedding it into a sink hidden
key column
* (__DORIS_IVM_KEY_{n}_{name}_COL__): strips leading IVM/Doris hidden
prefixes and the
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/mtmv/ivm/IvmUtilTest.java
b/fe/fe-core/src/test/java/org/apache/doris/mtmv/ivm/IvmUtilTest.java
index baa8f1687d7..5f7ce468948 100644
--- a/fe/fe-core/src/test/java/org/apache/doris/mtmv/ivm/IvmUtilTest.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/mtmv/ivm/IvmUtilTest.java
@@ -78,6 +78,20 @@ class IvmUtilTest {
Assertions.assertInstanceOf(BitmapEmpty.class, skipBitmap);
}
+ @Test
+ void testBaseScanStorageColumnToMask() {
+
Assertions.assertTrue(IvmUtil.isBaseScanStorageColumnToMask(Column.SEQUENCE_COL));
+
Assertions.assertTrue(IvmUtil.isBaseScanStorageColumnToMask(Column.SEQUENCE_COL.toLowerCase()));
+
Assertions.assertFalse(IvmUtil.isBaseScanStorageColumnToMask(Column.DELETE_SIGN));
+
Assertions.assertFalse(IvmUtil.isBaseScanStorageColumnToMask(Column.VERSION_COL));
+
Assertions.assertFalse(IvmUtil.isBaseScanStorageColumnToMask(Column.COMMIT_TSO_COL));
+
Assertions.assertFalse(IvmUtil.isBaseScanStorageColumnToMask(Column.ROW_LSN_COL));
+
Assertions.assertFalse(IvmUtil.isBaseScanStorageColumnToMask(Column.ROW_STORE_COL));
+
Assertions.assertFalse(IvmUtil.isBaseScanStorageColumnToMask(Column.IVM_ROW_ID_COL));
+ Assertions.assertFalse(IvmUtil.isBaseScanStorageColumnToMask("id"));
+ Assertions.assertFalse(IvmUtil.isBaseScanStorageColumnToMask(null));
+ }
+
private SlotReference intSlot(String name, boolean nullable) {
return new SlotReference(name, IntegerType.INSTANCE, nullable);
}
diff --git
a/regression-test/data/mtmv_p0/ivm/test_ivm_base_sequence_isolation.out
b/regression-test/data/mtmv_p0/ivm/test_ivm_base_sequence_isolation.out
new file mode 100644
index 00000000000..4d30ae35583
--- /dev/null
+++ b/regression-test/data/mtmv_p0/ivm/test_ivm_base_sequence_isolation.out
@@ -0,0 +1,81 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !base_seq_inner_after_update --
+1 100 a2
+2 200 b
+3 300 c
+
+-- !base_seq_inner_after_update_reference --
+1 100 a2
+2 200 b
+3 300 c
+
+-- !base_seq_inner_after_batch --
+1 100 a2
+2 200 b2
+4 400 d
+
+-- !base_seq_inner_after_batch_reference --
+1 100 a2
+2 200 b2
+4 400 d
+
+-- !base_seq_inner_after_reinsert --
+1 100 a2
+2 200 b2
+3 111 c2
+4 400 d
+
+-- !base_seq_left_after_unmatch --
+1 100 a2 a1x
+2 200 b2 \N
+3 111 c2 c1
+4 400 d \N
+
+-- !base_seq_left_after_unmatch_reference --
+1 100 a2 a1x
+2 200 b2 \N
+3 111 c2 c1
+4 400 d \N
+
+-- !base_seq_left_after_rematch --
+1 100 a2 a1x
+2 200 b2 b1y
+3 111 c2 c1
+4 400 d \N
+
+-- !base_seq_plain_after_update --
+1 100 a1z
+2 200 b1z
+3 111 c1
+
+-- !base_seq_plain_after_update_reference --
+1 100 a1z
+2 200 b1z
+3 111 c1
+
+-- !base_seq_l2_dim2_only_update --
+1 100 a2 a1w
+2 200 b2 b1y
+3 111 c2 c1
+
+-- !base_seq_l2_dim2_only_update_reference --
+1 100 a2 a1w
+2 200 b2 b1y
+3 111 c2 c1
+
+-- !base_seq_l2_both_update --
+1 100 a3 a1v
+2 200 b2 b1y
+3 111 c2 c1
+
+-- !base_seq_l2_both_update_reference --
+1 100 a3 a1v
+2 200 b2 b1y
+3 111 c2 c1
+
+-- !base_seq_inner_after_complete --
+1 100 a3
+2 200 b2
+3 111 c2
+4 400 d
+
diff --git
a/regression-test/suites/mtmv_p0/ivm/test_ivm_base_sequence_isolation.groovy
b/regression-test/suites/mtmv_p0/ivm/test_ivm_base_sequence_isolation.groovy
new file mode 100644
index 00000000000..950bc5840af
--- /dev/null
+++ b/regression-test/suites/mtmv_p0/ivm/test_ivm_base_sequence_isolation.groovy
@@ -0,0 +1,321 @@
+// 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.
+
+// Regression coverage for the IVM defect where a base table's own hidden
+// __DORIS_SEQUENCE_COL__ shadowed the delta-allocated sequence. An UPSERT on a
+// base table that carries a BIGINT sequence column reported SUCCESS but the
+// updated row was left with __DORIS_DELETE_SIGN__ = 1 (the row disappeared),
+// and a later INCREMENTAL refresh could not restore it.
+//
+// Covers the scenarios from the defect:
+// * a single-layer IVM over plain MOW base tables that carry a BIGINT
+// sequence column (INNER and LEFT JOIN);
+// * the plain MOW control without a source sequence;
+// * a cascading IVM (L2 reads L1) where the updated dimension is not read by
+// the upstream MV, and where the upstream and the dimension are updated
+// together;
+// * INSERT / UPDATE / DELETE, several updates in one batch, the LEFT JOIN
+// match/unmatch transition, and a full column comparison against the base
+// tables.
+
+suite("test_ivm_base_sequence_isolation") {
+ sql "SET enable_nereids_planner = true"
+ sql "SET enable_fallback_to_original_planner = false"
+ // Disable both transparent-rewrite paths: the reference queries below are
+ // the MV definition SQL itself and would otherwise be answered from the
MV,
+ // and the base-table DML would otherwise be redirected into the MV, hiding
+ // the defect this suite exists to catch.
+ sql "SET enable_materialized_view_rewrite = false"
+ sql "SET enable_dml_materialized_view_rewrite = false"
+
+ sql "DROP MATERIALIZED VIEW IF EXISTS test_ivm_base_seq_mv_inner"
+ sql "DROP MATERIALIZED VIEW IF EXISTS test_ivm_base_seq_mv_left"
+ sql "DROP MATERIALIZED VIEW IF EXISTS test_ivm_base_seq_mv_plain"
+ sql "DROP MATERIALIZED VIEW IF EXISTS test_ivm_base_seq_mv_l2"
+ sql "DROP MATERIALIZED VIEW IF EXISTS test_ivm_base_seq_mv_l1"
+ sql "DROP TABLE IF EXISTS test_ivm_base_seq_fact"
+ sql "DROP TABLE IF EXISTS test_ivm_base_seq_dim1"
+ sql "DROP TABLE IF EXISTS test_ivm_base_seq_dim2"
+ sql "DROP TABLE IF EXISTS test_ivm_base_seq_plain"
+
+ // ---- Case D / A / C base tables: plain MOW tables that carry a BIGINT
+ // sequence column, so every OlapScan leaks a hidden
__DORIS_SEQUENCE_COL__.
+ sql """
+ CREATE TABLE test_ivm_base_seq_fact (
+ id INT NOT NULL,
+ amount INT NOT NULL,
+ seq BIGINT
+ )
+ UNIQUE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1",
+ "enable_unique_key_merge_on_write" = "true",
+ "binlog.enable" = "true",
+ "binlog.format" = "ROW",
+ "binlog.need_historical_value" = "true",
+ "function_column.sequence_col" = "seq"
+ )
+ """
+ sql """
+ CREATE TABLE test_ivm_base_seq_dim1 (
+ id INT NOT NULL,
+ name VARCHAR(32) NOT NULL,
+ seq BIGINT
+ )
+ UNIQUE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1",
+ "enable_unique_key_merge_on_write" = "true",
+ "binlog.enable" = "true",
+ "binlog.format" = "ROW",
+ "binlog.need_historical_value" = "true",
+ "function_column.sequence_col" = "seq"
+ )
+ """
+ sql """
+ CREATE TABLE test_ivm_base_seq_dim2 (
+ id INT NOT NULL,
+ note VARCHAR(32) NOT NULL,
+ seq BIGINT
+ )
+ UNIQUE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1",
+ "enable_unique_key_merge_on_write" = "true",
+ "binlog.enable" = "true",
+ "binlog.format" = "ROW",
+ "binlog.need_historical_value" = "true",
+ "function_column.sequence_col" = "seq"
+ )
+ """
+ // ---- Case B control: the same business shape, but no source sequence.
+ sql """
+ CREATE TABLE test_ivm_base_seq_plain (
+ id INT NOT NULL,
+ note VARCHAR(32) NOT NULL
+ )
+ UNIQUE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1",
+ "enable_unique_key_merge_on_write" = "true",
+ "binlog.enable" = "true",
+ "binlog.format" = "ROW",
+ "binlog.need_historical_value" = "true"
+ )
+ """
+
+ sql "INSERT INTO test_ivm_base_seq_fact VALUES (1, 100, 0), (2, 200, 0),
(3, 300, 0)"
+ sql "INSERT INTO test_ivm_base_seq_dim1 VALUES (1, 'a', 0), (2, 'b', 0),
(3, 'c', 0)"
+ sql "INSERT INTO test_ivm_base_seq_dim2 VALUES (1, 'a1', 0), (2, 'b1', 0),
(3, 'c1', 0)"
+ sql "INSERT INTO test_ivm_base_seq_plain VALUES (1, 'a1'), (2, 'b1'), (3,
'c1')"
+
+ // ============================ Case D: single-layer INNER JOIN
=============
+ // Updating the joined dimension used to leave the updated row at
+ // DELETE_SIGN = 1 while the refresh task reported SUCCESS.
+ sql """
+ CREATE MATERIALIZED VIEW test_ivm_base_seq_mv_inner
+ BUILD DEFERRED REFRESH INCREMENTAL ON MANUAL
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES ("replication_num" = "1")
+ AS
+ SELECT f.id AS id, f.amount AS amount, d1.name AS name
+ FROM test_ivm_base_seq_fact f
+ JOIN test_ivm_base_seq_dim1 d1 ON f.id = d1.id
+ """
+ sql "REFRESH MATERIALIZED VIEW test_ivm_base_seq_mv_inner INCREMENTAL"
+ waitingMTMVTaskFinishedByMvName("test_ivm_base_seq_mv_inner")
+
+ // UPDATE of a joined base table carrying a BIGINT sequence column.
+ sql "UPDATE test_ivm_base_seq_dim1 SET name = 'a2' WHERE id = 1"
+ sql "REFRESH MATERIALIZED VIEW test_ivm_base_seq_mv_inner INCREMENTAL"
+ waitingMTMVTaskFinishedByMvName("test_ivm_base_seq_mv_inner")
+ order_qt_base_seq_inner_after_update """
+ SELECT id, amount, name FROM test_ivm_base_seq_mv_inner
+ """
+ order_qt_base_seq_inner_after_update_reference """
+ SELECT f.id AS id, f.amount AS amount, d1.name AS name
+ FROM test_ivm_base_seq_fact f
+ JOIN test_ivm_base_seq_dim1 d1 ON f.id = d1.id
+ """
+
+ // INSERT / UPDATE / DELETE plus several updates in one batch, on the
+ // sequence-carrying base tables.
+ sql "UPDATE test_ivm_base_seq_dim1 SET name = 'b2' WHERE id = 2"
+ sql "UPDATE test_ivm_base_seq_fact SET amount = 111 WHERE id = 3"
+ sql "INSERT INTO test_ivm_base_seq_fact VALUES (4, 400, 0)"
+ sql "INSERT INTO test_ivm_base_seq_dim1 VALUES (4, 'd', 0)"
+ sql "DELETE FROM test_ivm_base_seq_dim1 WHERE id = 3"
+ sql "REFRESH MATERIALIZED VIEW test_ivm_base_seq_mv_inner INCREMENTAL"
+ waitingMTMVTaskFinishedByMvName("test_ivm_base_seq_mv_inner")
+ order_qt_base_seq_inner_after_batch """
+ SELECT id, amount, name FROM test_ivm_base_seq_mv_inner
+ """
+ order_qt_base_seq_inner_after_batch_reference """
+ SELECT f.id AS id, f.amount AS amount, d1.name AS name
+ FROM test_ivm_base_seq_fact f
+ JOIN test_ivm_base_seq_dim1 d1 ON f.id = d1.id
+ """
+
+ // Re-INSERT the deleted key: the row must come back through INCREMENTAL.
+ sql "INSERT INTO test_ivm_base_seq_dim1 VALUES (3, 'c2', 0)"
+ sql "REFRESH MATERIALIZED VIEW test_ivm_base_seq_mv_inner INCREMENTAL"
+ waitingMTMVTaskFinishedByMvName("test_ivm_base_seq_mv_inner")
+ order_qt_base_seq_inner_after_reinsert """
+ SELECT id, amount, name FROM test_ivm_base_seq_mv_inner
+ """
+
+ // ============================ Case C: single-layer LEFT JOIN
==============
+ // Match / unmatch transition: deleting the right side must turn the row
+ // into a NULL-extended match, and re-inserting must rematch it.
+ sql """
+ CREATE MATERIALIZED VIEW test_ivm_base_seq_mv_left
+ BUILD DEFERRED REFRESH INCREMENTAL ON MANUAL
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES ("replication_num" = "1")
+ AS
+ SELECT f.id AS id, f.amount AS amount, d1.name AS name, d2.note AS note
+ FROM test_ivm_base_seq_fact f
+ LEFT JOIN test_ivm_base_seq_dim1 d1 ON f.id = d1.id
+ LEFT JOIN test_ivm_base_seq_dim2 d2 ON f.id = d2.id
+ """
+ sql "REFRESH MATERIALIZED VIEW test_ivm_base_seq_mv_left INCREMENTAL"
+ waitingMTMVTaskFinishedByMvName("test_ivm_base_seq_mv_left")
+
+ sql "UPDATE test_ivm_base_seq_dim2 SET note = 'a1x' WHERE id = 1"
+ sql "DELETE FROM test_ivm_base_seq_dim2 WHERE id = 2"
+ sql "REFRESH MATERIALIZED VIEW test_ivm_base_seq_mv_left INCREMENTAL"
+ waitingMTMVTaskFinishedByMvName("test_ivm_base_seq_mv_left")
+ order_qt_base_seq_left_after_unmatch """
+ SELECT id, amount, name, note FROM test_ivm_base_seq_mv_left
+ """
+ order_qt_base_seq_left_after_unmatch_reference """
+ SELECT f.id AS id, f.amount AS amount, d1.name AS name, d2.note AS note
+ FROM test_ivm_base_seq_fact f
+ LEFT JOIN test_ivm_base_seq_dim1 d1 ON f.id = d1.id
+ LEFT JOIN test_ivm_base_seq_dim2 d2 ON f.id = d2.id
+ """
+
+ sql "INSERT INTO test_ivm_base_seq_dim2 VALUES (2, 'b1y', 0)"
+ sql "REFRESH MATERIALIZED VIEW test_ivm_base_seq_mv_left INCREMENTAL"
+ waitingMTMVTaskFinishedByMvName("test_ivm_base_seq_mv_left")
+ order_qt_base_seq_left_after_rematch """
+ SELECT id, amount, name, note FROM test_ivm_base_seq_mv_left
+ """
+
+ // ============================ Case B: plain control (no sequence)
=========
+ sql """
+ CREATE MATERIALIZED VIEW test_ivm_base_seq_mv_plain
+ BUILD DEFERRED REFRESH INCREMENTAL ON MANUAL
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES ("replication_num" = "1")
+ AS
+ SELECT f.id AS id, f.amount AS amount, p.note AS note
+ FROM test_ivm_base_seq_fact f
+ JOIN test_ivm_base_seq_plain p ON f.id = p.id
+ """
+ sql "REFRESH MATERIALIZED VIEW test_ivm_base_seq_mv_plain INCREMENTAL"
+ waitingMTMVTaskFinishedByMvName("test_ivm_base_seq_mv_plain")
+
+ sql "UPDATE test_ivm_base_seq_plain SET note = 'a1z' WHERE id = 1"
+ sql "UPDATE test_ivm_base_seq_plain SET note = 'b1z' WHERE id = 2"
+ sql "REFRESH MATERIALIZED VIEW test_ivm_base_seq_mv_plain INCREMENTAL"
+ waitingMTMVTaskFinishedByMvName("test_ivm_base_seq_mv_plain")
+ order_qt_base_seq_plain_after_update """
+ SELECT id, amount, note FROM test_ivm_base_seq_mv_plain
+ """
+ order_qt_base_seq_plain_after_update_reference """
+ SELECT f.id AS id, f.amount AS amount, p.note AS note
+ FROM test_ivm_base_seq_fact f
+ JOIN test_ivm_base_seq_plain p ON f.id = p.id
+ """
+
+ // ============================ Case A: cascading IVM
======================
+ // L1 reads fact + dim1; L2 reads L1 (whose snapshot carries L1's own
hidden
+ // sequence) plus dim2. Updating dim2 -- which L1 does not read -- and
+ // refreshing only L2 used to drop the row.
+ sql """
+ CREATE MATERIALIZED VIEW test_ivm_base_seq_mv_l1
+ BUILD DEFERRED REFRESH INCREMENTAL ON MANUAL
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1",
+ "binlog.enable" = "true",
+ "binlog.format" = "ROW",
+ "binlog.need_historical_value" = "true"
+ )
+ AS
+ SELECT f.id AS id, f.amount AS amount, d1.name AS name
+ FROM test_ivm_base_seq_fact f
+ JOIN test_ivm_base_seq_dim1 d1 ON f.id = d1.id
+ """
+ sql "REFRESH MATERIALIZED VIEW test_ivm_base_seq_mv_l1 INCREMENTAL"
+ waitingMTMVTaskFinishedByMvName("test_ivm_base_seq_mv_l1")
+
+ sql """
+ CREATE MATERIALIZED VIEW test_ivm_base_seq_mv_l2
+ BUILD DEFERRED REFRESH INCREMENTAL ON MANUAL
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES ("replication_num" = "1")
+ AS
+ SELECT l1.id AS id, l1.amount AS amount, l1.name AS name, d2.note AS
note
+ FROM test_ivm_base_seq_mv_l1 l1
+ JOIN test_ivm_base_seq_dim2 d2 ON l1.id = d2.id
+ """
+ sql "REFRESH MATERIALIZED VIEW test_ivm_base_seq_mv_l2 COMPLETE"
+ waitingMTMVTaskFinishedByMvName("test_ivm_base_seq_mv_l2")
+
+ // Update a dimension that L1 does not read, then refresh only L2.
+ sql "UPDATE test_ivm_base_seq_dim2 SET note = 'a1w' WHERE id = 1"
+ sql "REFRESH MATERIALIZED VIEW test_ivm_base_seq_mv_l2 INCREMENTAL"
+ waitingMTMVTaskFinishedByMvName("test_ivm_base_seq_mv_l2")
+ order_qt_base_seq_l2_dim2_only_update """
+ SELECT id, amount, name, note FROM test_ivm_base_seq_mv_l2
+ """
+ order_qt_base_seq_l2_dim2_only_update_reference """
+ SELECT l1.id AS id, l1.amount AS amount, l1.name AS name, d2.note AS
note
+ FROM test_ivm_base_seq_mv_l1 l1
+ JOIN test_ivm_base_seq_dim2 d2 ON l1.id = d2.id
+ """
+
+ // Update the upstream and the dimension together, then refresh L1 and L2.
+ sql "UPDATE test_ivm_base_seq_dim1 SET name = 'a3' WHERE id = 1"
+ sql "UPDATE test_ivm_base_seq_dim2 SET note = 'a1v' WHERE id = 1"
+ sql "REFRESH MATERIALIZED VIEW test_ivm_base_seq_mv_l1 INCREMENTAL"
+ waitingMTMVTaskFinishedByMvName("test_ivm_base_seq_mv_l1")
+ sql "REFRESH MATERIALIZED VIEW test_ivm_base_seq_mv_l2 INCREMENTAL"
+ waitingMTMVTaskFinishedByMvName("test_ivm_base_seq_mv_l2")
+ order_qt_base_seq_l2_both_update """
+ SELECT id, amount, name, note FROM test_ivm_base_seq_mv_l2
+ """
+ order_qt_base_seq_l2_both_update_reference """
+ SELECT l1.id AS id, l1.amount AS amount, l1.name AS name, d2.note AS
note
+ FROM test_ivm_base_seq_mv_l1 l1
+ JOIN test_ivm_base_seq_dim2 d2 ON l1.id = d2.id
+ """
+
+ // A full COMPLETE refresh must converge to the same result as INCREMENTAL:
+ // the same business key with a new value must never keep DELETE_SIGN = 1.
+ sql "REFRESH MATERIALIZED VIEW test_ivm_base_seq_mv_inner COMPLETE"
+ waitingMTMVTaskFinishedByMvName("test_ivm_base_seq_mv_inner")
+ order_qt_base_seq_inner_after_complete """
+ SELECT id, amount, name FROM test_ivm_base_seq_mv_inner
+ """
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
