Repository navigation
[Bug] Sequence column: base/full compaction drops delete-sign rows, so a later write with a smaller sequence value brings a deleted row back #69409
Description
Search before asking
- I had searched in the issues and found no similar issues.
Version
- master 15eb3f4 (2026-10-18), built from source (
build.sh --fe --be), 1 FE + 1 BE - 4.1.5 (official
apache/doris:fe-4.1.5/be-4.1.5images, build 917b20b): same result
What's Wrong?
On a UNIQUE KEY table with function_column.sequence_col, a delete (a row with __DORIS_DELETE_SIGN__ = 1 and sequence 900) correctly rejects a later write of the same key with a smaller sequence value (500): the key stays deleted. If a base or full compaction runs between the delete and the late write, the same late write is stored and the deleted key comes back.
Base and full compaction remove rows with the delete sign (enable_prune_delete_sign_when_base_compaction, default true). For a table with a sequence column, the delete-sign row is the only record that the key was deleted at sequence 900. Once it is gone, nothing is left to compare the late write against. So the result of the same writes depends on whether a background compaction happened to run in between. Merge-on-write and merge-on-read tables both behave this way, and so does a plain DELETE FROM ... WHERE statement.
This breaks the out-of-order guarantee that the sequence column exists for (for example, CDC where a delete and an older update arrive out of order). #17739 shows the intended behaviour: after a delete, a re-insert with a smaller sequence value is ignored, and a maintainer confirmed that this is expected.
What You Expected?
Key 1 stays deleted whether or not a compaction ran, as it does without the compaction. Compaction should not change query results.
How to Reproduce?
1 FE + 1 BE, default configuration, any MySQL client:
CREATE DATABASE seqdel; USE seqdel;
-- run once with t_plain and once with t_compacted
CREATE TABLE t_compacted (k INT, v VARCHAR(10), s BIGINT)
UNIQUE KEY(k) DISTRIBUTED BY HASH(k) BUCKETS 1
PROPERTIES ('replication_num' = '1',
'enable_unique_key_merge_on_write' = 'true', -- 'false' (merge-on-read) gives the same result
'function_column.sequence_col' = 's',
'disable_auto_compaction' = 'true'); -- only to make the timing deterministic
INSERT INTO t_compacted VALUES (1, 'a', 100), (2, 'b', 100);
INSERT INTO t_compacted (k, v, s, __DORIS_DELETE_SIGN__) VALUES (1, NULL, 900, 1); -- delete k=1 at seq 900
SELECT k, v, s FROM t_compacted ORDER BY k; -- (2,'b',100)
-- only for t_compacted: full compaction, then wait until the tablet has a single rowset
-- (SHOW TABLETS FROM t_compacted -> CompactionStatus URL, "last full success time" set)
ADMIN COMPACT TABLE t_compacted PARTITION t_compacted WHERE type = 'full';
INSERT INTO t_compacted VALUES (1, 'late', 500); -- older than the delete, must be ignored
SELECT k, v, s FROM t_compacted ORDER BY k;Observed on master:
after the delete after the late INSERT (seq 500)
mow, no compaction (2,'b',100) (2,'b',100) <- correct
mow, full compaction (2,'b',100) (1,'late',500), (2,'b',100) <- k=1 is back
mor, no compaction (2,'b',100) (2,'b',100) <- correct
mor, full compaction (2,'b',100) (1,'late',500), (2,'b',100) <- k=1 is back
After the full compaction the tablet has one rowset [0-3] with 1 row: the delete-sign row for k=1 is gone. With skip_delete_sign, skip_delete_bitmap and skip_storage_engine_merge set to true, the physical rows are (1,'a',100,0), (1,NULL,900,1), (2,'b',100,0) before the compaction and only (2,'b',100,0) after it.
Base compaction gives the same result. Sequence: two loads and a full compaction (base rowset [0-3]), then the delete-sign row, 5 more loads, type = 'cumulative', then type = 'base' (new base rowset [0-8]). The late write is then stored.
A plain DELETE statement on a merge-on-write table gives the same result. Insert (27,'old',30), run DELETE FROM t WHERE k = 27, then INSERT (27,'new',7). Without a compaction in between, the INSERT reports VISIBLE but is ignored (sequence 7 < 30). After a full compaction, the row (27,'new',7) appears.
The same thing happens with stream load merge_type=DELETE and columns: k,s.
Anything Else?
Likely cause (master 15eb3f4):
be/src/storage/tablet/tablet_reader.cpp:457-461(TabletReader::_init_delete_condition):_delete_sign_availableis true forREADER_BASE_COMPACTION/READER_FULL_COMPACTIONwhenconfig::enable_prune_delete_sign_when_base_compaction(be/src/common/config.cpp:1987, default true). There is no check for a sequence column. The compaction readers then drop the delete-sign rows (be/src/storage/iterator/vertical_block_reader.cpp:530,block_reader.cpp:850).- Merge-on-write: the publish of the late write (
BaseTablet::calc_segment_delete_bitmap->lookup_row_key,be/src/storage/tablet/base_tablet.cpp:518,:648) no longer finds a row with a larger sequence value for the key, so the late row is not marked deleted. Merge-on-read: the read-time merge by sequence has no delete row to compare with.
A possible fix: for tables with a sequence column, keep the newest delete-sign row of each key in base/full compaction, or keep the (key, sequence) of deleted keys. Pruning would still be safe for tables without a sequence column.
Related but different: #48241 added the config that controls the pruning of delete-sign rows in base compaction, and #51874 extended the pruning to full compaction. Neither mentions sequence columns. #59441 (closed, not merged) proposed a time condition for that pruning. #24011 and #26721 cover delete bitmaps for delete-sign rows without a sequence column. #32574 and #29468 cover delete sign plus sequence column in partial update and replica consistency. #17739 shows the expected behaviour without compaction.
Are you willing to submit PR?
- Yes I am willing to submit a PR!
Code of Conduct
- I agree to follow this project's Code of Conduct
Activity
Doris-Breakwater commentedon Oct 21, 2026
Breakwater-GitHub-Analysis-Slot: slot_d814bb63ac39
The report warrants storage/compaction correctness triage: source inspection supports the proposed loss of the deleted key's sequence boundary. The important distinction is that compaction need not change the immediate SELECT result; it changes which subsequent writes are accepted for that key.
Evidence and confidence
15eb3f4e432b1d4a39f89457956135da80b90940and 4.1.5 build917b20be8. SQL DELETE and stream-load DELETE are also reported affected. I have not independently executed these reproductions.917b20be83878e98f94e3ebf7efccc80346e3e50, which the local4.1.5-releasetag resolves to. Both contain the relevant pruning and sequence-comparison paths described below. The issue currently has no labels; suggested classification is Bug / storage / compaction, with elevated priority because older data can become visible after a delete.Verified code mechanism
TabletReader::_init_delete_conditionenables delete-sign pruning for base and full compaction whenenable_prune_delete_sign_when_base_compactionis true, without checking for a sequence column.VerticalBlockReaderandBlockReaderthen filter the winning delete-sign row out of the compaction output.Segment::lookup_row_keyreturnsKEY_ALREADY_EXISTSwhen the incoming sequence is smaller than the stored sequence.BaseTablet::calc_segment_delete_bitmapmarks that incoming row deleted;KEY_NOT_FOUNDinstead skips this conflict handling. Once compaction removes the last row for key 1, the lookup has no sequence-900 entry against which to reject sequence 500. This explains why a successful/visible load can be ignored before compaction and become query-visible afterward.The mechanism is therefore strongly supported by static inspection. Independent runtime confirmation, the complete scope across other compaction paths, and a validated fix remain outstanding. #17739 supports the expected suppression of older writes while the delete record exists; it does not establish a tombstone garbage-collection boundary. #51874 extended pruning to full compaction. #59441 is closed and unmerged, so it should not be treated as an available fix.
Remaining verification and next steps
enable_prune_delete_sign_when_base_compaction=false, set before the delete/compaction, for MOW/MOR and base/full compaction. Record the effective BE setting, actual successful compaction status and input/output version ranges, physical tombstone presence, and the final normal SELECT. The expected distinguishing result is that retaining the sequence-900 tombstone continues to suppress sequence 500. The report already provides enough version, DDL, and sequencing information to begin reproduction; logs around publish/compaction are useful if this control behaves differently._delete_sign_availablealso serves checksum and cold-data readers, so a blanket change to this shared flag needs separate review.test_config_prune_delete_sign.groovycovers a MOW table without a sequence column and does not exercise the late-write boundary here.No code changes were made during this investigation.
RoanHeNaN commentedon Nov 4, 2026
I met a same problem.