Z-SWEI opened a new issue, #34155: URL: https://github.com/apache/doris/issues/34155
### 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 ### What's Wrong? when rewrite query in AbstractMaterializedViewAggregateRule roll up, bad view is chosen table structure: ```sql CREATE TABLE IF NOT EXISTS orders ( o_orderkey integer not null, o_custkey integer not null, o_orderstatus char(1) not null, o_totalprice decimalv3(15,2) not null, o_orderdate date not null, o_orderpriority char(15) not null, o_clerk char(15) not null, o_shippriority integer not null, o_comment varchar(79) not null ) DUPLICATE KEY(o_orderkey, o_custkey) PARTITION BY RANGE(o_orderdate)( FROM ('2023-10-17') TO ('2023-10-20') INTERVAL 1 DAY) DISTRIBUTED BY HASH(o_orderkey) BUCKETS 3 PROPERTIES ("replication_num" = "1"); insert into orders values (1, 1, 'ok', 99.5, '2023-10-17', 'a', 'b', 1, 'yy'), (2, 2, 'ok', 109.2, '2023-10-18', 'c','d',2, 'mm'), (3, 3, 'ok', 99.5, '2023-10-19', 'a', 'b', 1, 'yy'); CREATE MATERIALIZED VIEW mv4 DISTRIBUTED BY RANDOM BUCKETS 3 PROPERTIES ('replication_num' = '1') AS SELECT sum(o_totalprice) FROM orders GROUP BY o_orderdate, o_orderpriority; CREATE MATERIALIZED VIEW mv5 DISTRIBUTED BY RANDOM BUCKETS 3 PROPERTIES ('replication_num' = '1') AS SELECT sum(o_totalprice) FROM orders GROUP BY o_orderstatus, o_orderpriority; ``` query: ```sql select sum(o_totalprice) from orders group by o_orderdate ``` ``` +----------------------------------------------------------------------+ | Explain String(Nereids Planner) | +----------------------------------------------------------------------+ | PLAN FRAGMENT 0 | | OUTPUT EXPRS: | | sum(o_totalprice)[#3] | | PARTITION: UNPARTITIONED | | | | HAS_COLO_PLAN_NODE: false | | | | VRESULT SINK | | MYSQL_PROTOCAL | | | | 3:VAGGREGATE (merge finalize)(369) | | | output: sum(partial_sum(__sum_0)[#1])[#2] | | | group by: | | | cardinality=3 | | | limit: 3 | | | final projections: non_nullable(sum(__sum_0)[#2]) | | | final project output tuple id: 3 | | | distribute expr lists: | | | | | 2:VEXCHANGE | | offset: 0 | | distribute expr lists: | | | | PLAN FRAGMENT 1 | | | | PARTITION: RANDOM | | | | HAS_COLO_PLAN_NODE: false | | | | STREAM DATA SINK | | EXCHANGE ID: 02 | | UNPARTITIONED | | | | 1:VAGGREGATE (update serialize)(363) | | | output: partial_sum(__sum_0[#0])[#1] | | | group by: | | | cardinality=1 | | | distribute expr lists: | | | | | 0:VOlapScanNode(360) | | TABLE: testdb.mv5(mv5), PREAGGREGATION: ON | | partitions=1/1 (mv5), tablets=3/3, tabletList=10505,10507,10509 | | cardinality=2, avgRowSize=0.0, numNodes=1 | | pushAggOp=NONE | | | | MaterializedView | | MaterializedViewRewriteSuccessAndChose: | | Names: mv5 | | MaterializedViewRewriteSuccessButNotChose: | | Names: mv4 | | MaterializedViewRewriteFail: | +----------------------------------------------------------------------+ ``` The result is both mv4 and mv5 rewrite success, I think maybe only mv4? ### What You Expected? chose mv4 without mv5 for the case above ### How to Reproduce? _No response_ ### Anything Else? _No response_ ### 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]
