[Bug] Sync materialized view is never chosen by transparent rewrite on 4.1.3, even for queries identical to the MV definition

[luosm]

Search before asking

• I had searched in the issues and found no similar issues.

Version

4.1.3

What's Wrong?

On Doris 4.1.3 (1 FE / 2 BE), synchronous materialized views in FINISHED state are never selected by transparent rewrite. EXPLAIN / EXPLAIN VERBOSE always show the base table being scanned — including for queries whose shape is character-for-character identical to the MV definition.

Case 1: aggregate sync MV on a DUPLICATE KEY table is never hit

-- Base table (simplified; real table uses AUTO PARTITION BY RANGE on DATE_TRUNC(ingest_time, 'day')):
CREATE TABLE tick_dirty_minute (
  source_topic VARCHAR(255) NOT NULL,
  source_partition INT NOT NULL,
  source_offset BIGINT NOT NULL,
  ...
)
DUPLICATE KEY(source_topic, source_partition, source_offset, ...)
AUTO PARTITION BY RANGE (DATE_TRUNC(ingest_time, 'day')) ()
DISTRIBUTED BY HASH(source_topic, source_partition) BUCKETS AUTO;

CREATE MATERIALIZED VIEW mv_tick_dirty_source_watermark AS
SELECT
    source_topic AS dirty_source_topic,
    source_partition AS dirty_source_partition,
    MAX(source_offset) AS latest_source_offset
FROM tick_dirty_minute
GROUP BY source_topic, source_partition
ORDER BY dirty_source_topic, dirty_source_partition;
-- SHOW ALTER TABLE MATERIALIZED VIEW  =>  State: FINISHED

-- Query with the same shape as the MV definition:
EXPLAIN SELECT
    source_topic AS dirty_source_topic,
    source_partition AS dirty_source_partition,
    MAX(source_offset) AS latest_source_offset
FROM tick_dirty_minute
GROUP BY source_topic, source_partition
ORDER BY dirty_source_topic, dirty_source_partition;
-- => TABLE: db.tick_dirty_minute(tick_dirty_minute), PREAGGREGATION: ON
-- The 20M-row base table is fully scanned; the MV index is never chosen.

Also tried, with no effect:

• SET pre_materialized_view_rewrite_strategy = FORCE_IN_RBO;
• enable_materialized_view_rewrite is true (default)
• A second aggregate sync MV (MIN_BY/MAX_BY/SUM/COUNT OHLC rollup grouped by day/symbol/minute) on another DUPLICATE KEY table shows the same behavior: FINISHED but never selected, even for a query textually identical to the MV definition.

Case 2: creating a minimal sync MV fails with a confusing error

CREATE TABLE tmp_mv_probe (k1 VARCHAR(64) NOT NULL, k2 INT NOT NULL, v BIGINT NOT NULL)
DUPLICATE KEY(k1, k2)
DISTRIBUTED BY HASH(k1) BUCKETS 1
PROPERTIES('replication_num'='2');

CREATE MATERIALIZED VIEW mv_probe AS
SELECT k1, k2, MAX(v) AS max_v FROM tmp_mv_probe GROUP BY k1, k2;
-- => ERROR 1105 (HY000): errCode = 2, detailMessage = Duplicate column name 'k1'

There is no duplicate column in the statement. (If un-aliased GROUP BY key columns are intentionally rejected here, the error message is misleading.)

Anything else: the production base tables use AUTO PARTITION BY RANGE(DATE_TRUNC(...)) and one of them has a GENERATED ALWAYS AS column — unclear whether these affect materialized index selection, but Case 2 shows the issue also reproduces on a plain table without either feature (at MV creation time).

What You Expected?

1. A query with the same shape as a FINISHED sync MV (or a roll-up-compatible aggregate over its dimensions) should be transparently rewritten to the MV index; at minimum EXPLAIN should show the MV index selected instead of the base table.
2. Creating a simple aggregate sync MV over a plain DUPLICATE KEY table should succeed, or fail with a clear error message.

How to Reproduce?

Cluster: Doris 4.1.3, 1 FE / 2 BE, default session variables except where noted.

Case 1 (rewrite never selects a FINISHED sync MV): run the DDL + MV + EXPLAIN from "What's Wrong". The base table in our cluster has ~20M rows loaded via Routine Load; SHOW ALTER TABLE MATERIALIZED VIEW shows FINISHED, but EXPLAIN always scans the base table.

Case 2 (minimal MV creation failure), fully self-contained:

CREATE TABLE tmp_mv_probe (k1 VARCHAR(64) NOT NULL, k2 INT NOT NULL, v BIGINT NOT NULL)
DUPLICATE KEY(k1, k2)
DISTRIBUTED BY HASH(k1) BUCKETS 1
PROPERTIES('replication_num'='2');

CREATE MATERIALIZED VIEW mv_probe AS
SELECT k1, k2, MAX(v) AS max_v FROM tmp_mv_probe GROUP BY k1, k2;
-- ERROR 1105 (HY000): errCode = 2, detailMessage = Duplicate column name 'k1'

Anything Else?

No response

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

[zclllyybb]

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

I looked at this against the 4.1.3 FE sync-MV code path. The two reported symptoms look related at the feature level, but they are probably two different problems.

1. The minimal CREATE MATERIALIZED VIEW failure is explained by the current sync-MV DDL validation.

In CreateMaterializedViewCommand.validate() -> MaterializedViewHandler.processCreateMaterializedView(), Doris builds the MV output items and then checks every MV output column name against the base table full schema. Therefore:

SELECT k1, k2, MAX(v) AS max_v FROM tmp_mv_probe GROUP BY k1, k2

produces MV output names k1, k2, max_v; k1 and k2 already exist in the base table, so the handler raises ERR_DUP_FIELDNAME. This is not caused by AUTO PARTITION or generated columns. It is a name-collision check before the rollup schema is built.

If this restriction is intended, the error should be clearer, for example that sync MV output column names must not duplicate base table column names. If it is not intended, the duplicate check should be narrowed so valid grouped key outputs do not collide with their own base columns. A current workaround is to alias all MV outputs that would otherwise reuse base column names:

CREATE MATERIALIZED VIEW mv_probe AS
SELECT k1 AS mv_k1, k2 AS mv_k2, MAX(v) AS max_v
FROM tmp_mv_probe
GROUP BY k1, k2;

2. The rewrite miss for the already-created aliased MV is not explained by the minimal DDL failure.

The production MV aliases the key columns, so it avoids the duplicate-name check above. In 4.1.3, EXPLAIN with enable_materialized_view_rewrite=true installs InitMaterializationContextHook; visible non-base sync MV indexes are collected as candidates, the stored CREATE MV SQL is reparsed, and SyncMaterializationContext can generate a scan using the MV index id. So a FINISHED MV still scanning the base table means one of these happened:

• the sync MV context was not created or was marked unavailable, for example a parse/init exception in FE logs;
• the MV rewrite failed to match the query shape;
• the rewrite succeeded, but the final costed plan did not choose the MV index.

Normal EXPLAIN only shows the final scan, so it does not identify which of those happened. Please attach:

• EXPLAIN MEMO <query> or EXPLAIN OPTIMIZED <query> for the failing query, with the same enable_materialized_view_rewrite and pre_materialized_view_rewrite_strategy settings. The ========== MATERIALIZATIONS ========== section should show whether mv_tick_dirty_source_watermark was chose, not chose, or fail.
• SHOW CREATE TABLE tick_dirty_minute;
• SHOW CREATE MATERIALIZED VIEW mv_tick_dirty_source_watermark ON tick_dirty_minute;
• SHOW ALTER TABLE MATERIALIZED VIEW WHERE TableName='tick_dirty_minute' ORDER BY CreateTime DESC LIMIT 5;
• whether ANALYZE TABLE tick_dirty_minute WITH SYNC; has been run after the MV reached FINISHED and after the Routine Load data was visible. Existing sync-MV rewrite regression checks normally make row counts/statistics ready before asserting that the MV is finally chosen.
• any FE WARN log around the EXPLAIN query id containing createSyncMvContexts, can't parse, MaterializationContext, or the MV name.

If EXPLAIN MEMO says the MV fails for the identical aliased query, this should be treated as a rewrite matching bug. If it says not chose, the next step is to inspect stale/missing table/index statistics and CBO costing. If the MV is absent from the materialization summary, focus on candidate initialization or stored MV metadata.

[luosm]

Thanks @zclllyybb — both pointers were spot on. Follow-up with new findings:

Case 2 — confirmed as the name-collision check. Aliasing every output column works (SELECT k1 AS mv_k1, k2 AS mv_k2, MAX(v) AS max_v ... succeeds). Agreed the error message should state explicitly that sync-MV output names must not duplicate base-table column names.

Case 1 — important new data point: DROP + re-CREATE with the identical definition fixes the rewrite immediately.

The broken MV had already been dropped on our side, so to collect the requested artifacts I recreated it with exactly the same aliased definition. The recreated MV is chosen right away, without ever running ANALYZE:

EXPLAIN MEMO PLAN <same query>
========== MATERIALIZATIONS ==========
MaterializedView
MaterializedViewRewriteSuccessAndChose:
 CBO.internal.zuper_data_doris.tick_dirty_minute.mv_tick_dirty_source_watermark chose
MaterializedViewRewriteSuccessButNotChose:
MaterializedViewRewriteFail:

Plain EXPLAIN now shows TABLE: zuper_data_doris.tick_dirty_minute(mv_tick_dirty_source_watermark).

So the failing symptom was: the original FINISHED MV was never chosen across ~20h and many EXPLAIN attempts, while a textually identical recreation is chosen instantly. That points to your first hypothesis (sync MV context not created / marked unavailable) rather than shape matching or costing. Notes on the broken instance:

• It was created immediately after the base table DDL, when the table was empty and Routine Load had just been set up; the table then grew to ~20M rows (3s batches) while EXPLAIN kept scanning the base table the whole time.
• No createSyncMvContexts / can't parse / MaterializationContext WARNs in the FE log window still available (last 24h; the broken period is only partially covered).
• ANALYZE was never run in either state, so statistics cannot explain the difference.

I tried to reproduce the broken state via "empty table → create MV → load data → EXPLAIN" on a scratch table with the same DUP KEY + AUTO PARTITION shape, but the MV is chosen correctly there — so the trigger seems to be something else in the original instance's history. Since the broken instance is gone, I can no longer produce EXPLAIN MEMO for the failing state. If there is a plausible path where a sync MV context gets initialized once (e.g. cached as unavailable) and never re-validated, that would fit what we observed. Happy to run further experiments if you have a direction to try.