ScalaFirst opened a new issue, #59238: URL: https://github.com/apache/doris/issues/59238
### 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 3.1.3 ### What's Wrong? <img width="1392" height="921" alt="Image" src="https://github.com/user-attachments/assets/07a6221e-28fe-4aed-b57d-9817b2a9e22c"; /> select based table will rewrite to first view, but next view dwd_view_stat_engine_agg_hour_demension_normal_channel rewrite failed. BUT replace only table to view, rewrite will success soon. <img width="1387" height="813" alt="Image" src="https://github.com/user-attachments/assets/ad6fd997-72e0-48e1-9e0c-df6f2b0a402d"; /> I try to use session config ```sql SET enable_materialized_view_nest_rewrite = true; ``` BUT it is not useful. Query is: ```sql explain SELECT channel_placement_name, event_hour, channel_id, channel_placement_id, channel_name, pub_bid_floor_range, pub_bid_price_range, adv_bid_floor_range, adv_bid_price_range, ((SUM(`income`)/SUM(`ssp_forward_num`))*1000000) AS `sumrequ-83f`, SUM(`income`) AS `sum_income`, ((SUM(`income`)/SUM(`impr_num`))*1000) AS `suminco-4f4`, ((SUM(`earnings`)/SUM(`impr_num`))*1000) AS `suminco-5f8`, SUM(`earnings`) AS `sum_earnings`, SUM(`request_num`) AS `sumimpr-604`, SUM(`fill_num`) AS `sumimpr-850`, ((SUM(`fill_num`)/SUM(`request_num`))*100) AS `sumfill-233`, SUM(`impr_num`) AS `count`, ((SUM(`impr_num`)/SUM(`fill_num`))*100) AS `sumimpr-52a`, SUM(`click_num`) AS `sumimpr-c22`, ((SUM(`adv_bid_price`)/SUM(`ssp_fill_num`))/100) AS `sum_adv_bid_price`, (SUM(`income`)-SUM(`upstream_income`)) AS `sumearn-78f`, ((SUM(`income`)-SUM(`earnings`))-SUM(`upstream_income`)) AS `suminco-19e`, (1-(SUM(`earnings`)/SUM(`income`))) AS `suminco-1e0` FROM stat_engine -- FROM adx_stat.dwd_view_stat_engine_agg_hour_demension_normal where event_hour > '2025-12-21' and event_hour < '2025-12-22' GROUP BY 1,2,3,4,5,6,7,8,9 ``` ### What You Expected? select base table will rewrite to view by view ### How to Reproduce? view by view ```sql CREATE MATERIALIZED VIEW IF NOT EXISTS `dwd_view_stat_engine_agg_hour_demension_normal_channel` BUILD IMMEDIATE REFRESH AUTO ON COMMIT DUPLICATE KEY(`channel_placement_name`) PARTITION BY (event_hour) DISTRIBUTED BY HASH(`channel_placement_name`) BUCKETS AUTO PROPERTIES ( "replication_allocation" = "tag.location.default: 1", "use_for_rewrite" = "true", "workload_group" = "scheduler_group" ) as select channel_placement_name as channel_placement_name, event_hour, channel_id, channel_placement_id, channel_name, pub_bid_floor_range, pub_bid_price_range, adv_bid_floor_range, adv_bid_price_range, max(user_id) as user_id, max(user_name) as user_name, SUM(request_num) AS request_num, SUM(fill_num) AS fill_num, SUM(pub_bid_floor) AS pub_bid_floor, SUM(adv_bid_floor) AS adv_bid_floor, SUM(adv_bid_price) AS adv_bid_price, SUM(pub_bid_price) AS pub_bid_price, SUM(ssp_request_num) AS ssp_request_num, SUM(ssp_forward_num) AS ssp_forward_num, SUM(ssp_forbidden_num) AS ssp_forbidden_num, SUM(ssp_timeout_num) AS ssp_timeout_num, SUM(ssp_fill_num) AS ssp_fill_num, SUM(impr_num) AS impr_num, SUM(raw_impr_num) AS raw_impr_num, SUM(click_num) AS click_num, SUM(raw_click_num) AS raw_click_num, SUM(drop_click_num) AS drop_click_num, SUM(raw_drop_click_num) AS raw_drop_click_num, SUM(invoke_num) AS invoke_num, SUM(raw_invoke_num) AS raw_invoke_num, SUM(income) AS income, SUM(upstream_income) AS upstream_income, SUM(earnings) AS earnings, SUM(upstream_ratio) AS upstream_ratio, SUM(qh_invoke_num) AS qh_invoke_num, SUM(qh_dau_invoke_num) AS qh_dau_invoke_num, SUM(qh_no_client_cvr_num) AS qh_no_client_cvr_num, SUM(qh_delayed_attributed_purchase_num) AS qh_delayed_attributed_purchase_num, SUM(qh_delayed_unattributed_purchase_num) AS qh_delayed_unattributed_purchase_num, SUM(qh_no_delay_attributed_purchase_num) AS qh_no_delay_attributed_purchase_num, SUM(qh_no_delay_invoke_num) AS qh_no_delay_invoke_num, SUM(qh_client_cvr_attributed_purchase_num) AS qh_client_cvr_attributed_purchase_num, SUM(qh_flash_mac_num) AS qh_flash_mac_num, SUM(br_callback) AS br_callback, SUM(cvr79_num) AS cvr79_num, SUM(qh_flash110_num) AS qh_flash110_num from dwd_view_stat_engine_agg_hour_demension_normal group by 1,2,3,4,5,6,7,8,9 ; ``` query ```sql explain SELECT channel_placement_name, event_hour, channel_id, channel_placement_id, channel_name, pub_bid_floor_range, pub_bid_price_range, adv_bid_floor_range, adv_bid_price_range, ((SUM(`income`)/SUM(`ssp_forward_num`))*1000000) AS `sumrequ-83f`, SUM(`income`) AS `sum_income`, ((SUM(`income`)/SUM(`impr_num`))*1000) AS `suminco-4f4`, ((SUM(`earnings`)/SUM(`impr_num`))*1000) AS `suminco-5f8`, SUM(`earnings`) AS `sum_earnings`, SUM(`request_num`) AS `sumimpr-604`, SUM(`fill_num`) AS `sumimpr-850`, ((SUM(`fill_num`)/SUM(`request_num`))*100) AS `sumfill-233`, SUM(`impr_num`) AS `count`, ((SUM(`impr_num`)/SUM(`fill_num`))*100) AS `sumimpr-52a`, SUM(`click_num`) AS `sumimpr-c22`, ((SUM(`adv_bid_price`)/SUM(`ssp_fill_num`))/100) AS `sum_adv_bid_price`, (SUM(`income`)-SUM(`upstream_income`)) AS `sumearn-78f`, ((SUM(`income`)-SUM(`earnings`))-SUM(`upstream_income`)) AS `suminco-19e`, (1-(SUM(`earnings`)/SUM(`income`))) AS `suminco-1e0` FROM stat_engine -- base table -- FROM adx_stat.dwd_view_stat_engine_agg_hour_demension_normal -- first view where event_hour > '2025-12-21' and event_hour < '2025-12-22' GROUP BY 1,2,3,4,5,6,7,8,9 ``` ### 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]
