hanke580 opened a new issue, #69409: URL: https://github.com/apache/doris/issues/69409
### Search before asking - [x] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues. ### Version - master 15eb3f4e432b1d4a39f89457956135da80b90940 (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.5` images, build 917b20be8): 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: ```sql 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 15eb3f4e43): - `be/src/storage/tablet/tablet_reader.cpp:457-461` (`TabletReader::_init_delete_condition`): `_delete_sign_available` is true for `READER_BASE_COMPACTION` / `READER_FULL_COMPACTION` when `config::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? - [x] Yes I am willing to submit a PR! ### Code of Conduct - [x] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
