[Bug] Nested Materialized View Rewriting FAILED

[ScalaFirst]

Search before asking

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

Version

3.1.3

What's Wrong?

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.

I try to use session config in mysql client

SET enable_materialized_view_nest_rewrite = true;

plan failed reason changed: queryUsedPartition is null but needUnionRewrite

I try to close union_rewrite , It is funny that view rewrite success. But it is Terrible beacause this action will make query return outdated result. seems closed this config will skip partition check .

set enable_materialized_view_union_rewrite = false;

Query is:

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

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

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

• I agree to follow this project's Code of Conduct