[Bug](High) Table Stream DELETE events are visible in SELECT but not emitted by INSERT SELECT

[MoanasDaddyXu]

Tracking

• Tracking issue: [Tracking][Binlog] Binlog feature umbrella — all sub-issues tracked here #65265

Reference

• Similar issue format: [Bug](Low) ALTER TABLE changing ROW binlog.format returns wrong error message #65383
• Regression log: /mnt/disk1/jianxu/selectdb-cases/output/regression-test/log/doris-regression-test.20260716.152407.log
• Failed case: /mnt/disk1/jianxu/selectdb-cases/regression-test/suites/stress/binlog/test_binlog_routine_load_merge_types.groovy

Search before asking

I searched the existing Apache Doris issue format and used issue #65383 as the description template.

Version

The failure was reproduced on version: e7803ce

The exact Doris build version was not printed in the regression log. The regression framework connected to:

What's Wrong

The regression case test_binlog_routine_load_merge_types failed after the base table DELETE phase.

The case flow is:

1. Create a source MOW unique-key table with row binlog enabled.
2. Create a target MOW unique-key table.
3. Create a MIN_DELTA table stream on the source table.
4. Insert 100,000,000 rows into the source table through Routine Load and consume the table stream into the target table.
5. Update the same 100,000,000 rows and consume the table stream into the target table.
6. Execute DELETE FROM routine_load_source WHERE id <= 20000000.
7. Consume DELETE events from the table stream into the target table by writing __DORIS_DELETE_SIGN__ = 1.

The source table was correctly reduced to 80,000,000 rows, but the target table still had 100,000,000 rows.

Regression log excerpt:

DELETE row count: source=80000000, target=100000000, expected=80000000
org.opentest4j.AssertionFailedError: target row count mismatch after DELETE ==> expected: <80000000> but was: <100000000>

The table stream lag was consumed to zero after the DELETE phase, but the downstream target table did not receive the DELETE rows:

table stream lag after DELETE: [[routine_load_min_delta_stream, routine_load_source, 14005305344, 467714225933910017]]
table stream lag after consuming DELETE: [[routine_load_min_delta_stream, routine_load_source, 0, 467714239939215361]]

The case consumes the stream with:

SET show_hidden_columns = true;

INSERT INTO routine_load_target (id, version_no, payload, __DORIS_DELETE_SIGN__)
SELECT id, version_no, payload,
       if(__DORIS_STREAM_CHANGE_TYPE_COL__ = 'DELETE', 1, 0)
FROM routine_load_min_delta_stream
WHERE __DORIS_STREAM_CHANGE_TYPE_COL__ IN
      ('APPEND', 'INSERT', 'UPDATE_AFTER', 'DELETE');

This works for INSERT and UPDATE events, but not for DELETE events.

Expected Behavior

When a MIN_DELTA table stream contains DELETE events, INSERT INTO target SELECT ... FROM stream should emit those DELETE rows to the target insert pipeline.

For this case, writing the stream DELETE events into the target table with __DORIS_DELETE_SIGN__ = 1 should reduce the target table from 100,000,000 rows to 80,000,000 rows, matching the source table.

How to Reproduce

The following reduced SQL reproduces the product behavior without Kafka or Routine Load.

DROP DATABASE IF EXISTS codex_stream_delete_probe3;
CREATE DATABASE codex_stream_delete_probe3;
USE codex_stream_delete_probe3;

CREATE TABLE src (
    id BIGINT NOT NULL,
    version_no BIGINT NOT NULL,
    payload VARCHAR(32) NOT NULL
) ENGINE=OLAP
UNIQUE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 1
PROPERTIES (
    'replication_num' = '1',
    'enable_unique_key_merge_on_write' = 'true',
    'function_column.sequence_col' = 'version_no',
    'binlog.enable' = 'true',
    'binlog.format' = 'ROW',
    'binlog.need_historical_value' = 'true'
);

CREATE TABLE audit (
    id BIGINT,
    version_no BIGINT,
    payload VARCHAR(32),
    change_type VARCHAR(32)
) ENGINE=OLAP
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 1
PROPERTIES (
    'replication_num' = '1'
);

CREATE STREAM s ON TABLE src
PROPERTIES (
    'type' = 'min_delta',
    'show_initial_rows' = 'false'
);

INSERT INTO src VALUES (1, 1, 'a1'), (2, 1, 'a2'), (3, 1, 'a3');
SYNC;

INSERT INTO audit
SELECT id, version_no, payload, __DORIS_STREAM_CHANGE_TYPE_COL__
FROM s
WHERE __DORIS_STREAM_CHANGE_TYPE_COL__ IN ('APPEND', 'INSERT', 'UPDATE_AFTER', 'DELETE');

SELECT change_type, count(*) FROM audit GROUP BY change_type ORDER BY change_type;

INSERT INTO src VALUES (1, 2, 'b1'), (2, 2, 'b2'), (3, 2, 'b3');
SYNC;

INSERT INTO audit
SELECT id, version_no, payload, __DORIS_STREAM_CHANGE_TYPE_COL__
FROM s
WHERE __DORIS_STREAM_CHANGE_TYPE_COL__ IN ('APPEND', 'INSERT', 'UPDATE_AFTER', 'DELETE');

SELECT change_type, count(*) FROM audit GROUP BY change_type ORDER BY change_type;

DELETE FROM src WHERE id <= 2;
SYNC;

SELECT id, version_no, payload, __DORIS_STREAM_CHANGE_TYPE_COL__
FROM s
ORDER BY id;

INSERT INTO audit
SELECT id, version_no, payload, __DORIS_STREAM_CHANGE_TYPE_COL__
FROM s
WHERE __DORIS_STREAM_CHANGE_TYPE_COL__ IN ('APPEND', 'INSERT', 'UPDATE_AFTER', 'DELETE');

SELECT change_type, count(*) FROM audit GROUP BY change_type ORDER BY change_type;

Observed output:

change_type  count(*)
APPEND       3

change_type  count(*)
APPEND       3
UPDATE_AFTER 3

id  version_no  payload  __DORIS_STREAM_CHANGE_TYPE_COL__
1   2           b1       DELETE
2   2           b2       DELETE

OK (0 rows affected)

change_type  count(*)
APPEND       3
UPDATE_AFTER 3

The direct SELECT FROM s can see two DELETE events, but the following INSERT INTO audit SELECT ... FROM s inserts zero rows.

Regression Test Result

The failing regression command generated by the framework:

-cf /mnt/disk1/jianxu/selectdb-cases/regression-test/conf/regression-conf.groovy -d stress/binlog -s test_binlog_routine_load_merge_types --flowIds stress/binlog/test_binlog_routine_load_merge_types.groovy#test_binlog_routine_load_merge_types

Failed suite:

/mnt/disk1/jianxu/selectdb-cases/regression-test/suites/stress/binlog/test_binlog_routine_load_merge_types.groovy
group=binlog,p0
name=test_binlog_routine_load_merge_types
elapsed=1450043ms

Why this looks like a product issue instead of a case issue

I also checked whether the target table delete-sign write itself was invalid.

A reduced MOW table test showed that direct INSERT INTO ... __DORIS_DELETE_SIGN__ works and can delete visible rows, even with the same sequence value:

c1
1
c2
0
c3
0

So the delete-sign write path is valid. The problem only appears when DELETE rows are read from a table stream through INSERT SELECT.

This points to the table stream INSERT SELECT consumption path for DELETE events, not to the regression case assertion.

Anything Else

Temporary probe databases used during analysis were cleaned up:

codex_delete_sign_seq_probe
codex_stream_delete_probe
codex_stream_delete_probe2
codex_stream_delete_probe3

[zclllyybb]

Breakwater-GitHub-Analysis-Slot: slot_c897704dfc83
This content is generated by AI for reference only.

Initial assessment:

This should be treated as a real table-stream INSERT SELECT product bug, not as a Routine Load issue, target-table delete-sign issue, or regression assertion issue.

The strongest evidence is the reduced SQL in the issue: after the base-table DELETE, a direct SELECT from the MIN_DELTA stream can see the two DELETE rows, but the following INSERT INTO audit SELECT ... FROM s WHERE __DORIS_STREAM_CHANGE_TYPE_COL__ IN (...) inserts zero rows. The large stress case also reports that the stream lag advances to zero while the downstream target remains unchanged.

Code-backed notes from current upstream/master inspection:

1. FE stream normalization maps row-binlog operations to the stream virtual change-type column in NormalizeOlapTableStreamScan: normal incremental stream reads are rewritten to a RowBinlogTableWrapper scan with TableScanParams.INCREMENTAL_READ and MIN_DELTA, then __DORIS_STREAM_CHANGE_TYPE_COL__ is synthesized from the binlog op. This matches the direct SELECT FROM s visibility reported in the issue.
2. BE BlockReader::_min_delta_next_block() has an explicit DELETE result branch that emits a row with STREAM_CHANGE_DELETE. That also supports the issue author's observation that the stream scan can materialize DELETE rows.
3. For INSERT SELECT, InsertIntoTableCommand extracts TableStreamUpdateInfo from the analyzed stream scan and attaches it to the transaction before execution. The offset update is based on the planned stream window, not on the number of rows that finally reach the target sink. This explains the dangerous split reported here: lag can be consumed to zero even if the target receives zero rows.
4. Existing upstream table-stream regression coverage already has a mixed MIN_DELTA batch where INSERT INTO target SELECT ... FROM stream inserts a DELETE change row into a duplicate-key audit table. The new gap is more specific: a DELETE-only incremental window, with a change-type filter/projection, consumed through INSERT SELECT.

Most likely boundary:

The narrow suspect is the stream INSERT SELECT plan/execution path when the current incremental window contains only DELETE events and the query filters or projects __DORIS_STREAM_CHANGE_TYPE_COL__. The issue is less likely to be target MOW delete-sign handling, because the reduced duplicate-key audit table also receives zero rows, and the issue already checked direct __DORIS_DELETE_SIGN__ writes separately.

This does not look like a duplicate of #65339: that issue is a BE crash while selecting stream change-type rows, while this issue has a successful direct stream SELECT but loses rows during INSERT SELECT consumption.

Missing information needed to pin the exact failing line:

1. The exact FE and BE build commit IDs for the failing cluster. The issue currently says the build version was not printed.
2. EXPLAIN VERBOSE for both of these statements after the DELETE-only window is present:
   • SELECT id, version_no, payload, __DORIS_STREAM_CHANGE_TYPE_COL__ FROM s WHERE __DORIS_STREAM_CHANGE_TYPE_COL__ IN ('APPEND','INSERT','UPDATE_AFTER','DELETE')
   • the corresponding INSERT INTO ... SELECT ... FROM s WHERE ...
3. A query profile for the failing INSERT SELECT, especially scan rows returned, rows after filter/project, and rows sent to the sink. This will distinguish "scan produced zero rows under insert context" from "scan produced DELETE rows but filter/project/sink dropped them."
4. A small variant of the reduced SQL that runs INSERT INTO audit SELECT id, version_no, payload, __DORIS_STREAM_CHANGE_TYPE_COL__ FROM s without the WHERE predicate, to isolate whether the change-type filter is required for the loss.
5. Stream lag/status before and after the failing INSERT SELECT, with the query id, so the offset update can be correlated to the zero inserted rows.

Suggested next steps:

1. Add a regression under table_stream_p0 for a DELETE-only MIN_DELTA incremental window after prior APPEND/UPDATE consumption. The test should assert that the same filtered SELECT returns DELETE rows, that INSERT INTO duplicate audit SELECT ... WHERE change_type IN (...) inserts those DELETE rows, and that writing __DORIS_DELETE_SIGN__ = 1 into a MOW target reduces the target row count.
2. Compare the normalized plan for standalone SELECT versus INSERT SELECT. If the standalone filtered SELECT returns DELETE rows but the insert plan does not, focus on stream-scan normalization, partition pruning, predicate pushdown through the synthesized change-type projection, and insert-target planning.
3. If the BE scan profile shows DELETE rows are produced but the sink receives zero rows, focus on the projection/filter/sink boundary rather than the MIN_DELTA reader itself.