luosm commented on issue #65775:
URL: https://github.com/apache/doris/issues/65775#issuecomment-5009615427
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.
--
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]
