luosm opened a new issue, #65775: URL: https://github.com/apache/doris/issues/65775
### 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 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** ```sql -- 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** ```sql 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: ```sql 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 - [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]
