[Enhancement] (mtmv) Support read sync materialized view in async materialized view

[liutang123]

Search before asking

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

Description

When creating an asynchronous materialized view, if a synchronous materialized view is selected, the following error will be reported：

ERROR 1105 (HY000): errCode = 2, detailMessage = Unable to find a suitable base table for partitioning, the fail reason is can't not find valid partition track column, because related base table partition column doesn't contain the mv partition or partition nullable check fail, the mvReferenceColumn is `mv_dt` DATEV2 NULL

reproduce

CREATE TABLE advertiser_view_record(
  time date not null,
  advertiser varchar(10),
  dt date not null,
  channel varchar(10),
  user_id int) 
DUPLICATE KEY(`time`, `advertiser`)
  PARTITION BY RANGE (dt)(FROM ("2024-07-02") TO ("2024-07-04") INTERVAL 1 DAY)
  -- AUTO PARTITION BY RANGE (date_trunc(`time`, 'day'))()
  distributed BY hash(time) 
  properties("replication_num" = "1");
CREATE materialized VIEW advertiser_uv AS
SELECT advertiser,
       channel,
       dt,
       bitmap_union(to_bitmap(user_id))
FROM advertiser_view_record
GROUP BY advertiser,
         channel,
         dt;
insert into advertiser_view_record values("2024-07-02",'a', "2024-07-02", 'a',1);
insert into advertiser_view_record values("2024-07-03",'b', "2024-07-03", 'b',1);
set enable_sync_mv_cost_based_rewrite=false; -- If setting enable_sync_mv_cost_based_rewrite to true, the mv will not be fit.
EXPLAIN
SELECT dt,advertiser,
       count(DISTINCT user_id)
FROM advertiser_view_record
GROUP BY dt,advertiser
>>>|   0:VOlapScanNode(155)                                                                                                                |
   |      TABLE: test.advertiser_view_record(advertiser_uv), PREAGGREGATION: ON

CREATE MATERIALIZED VIEW mv1
 BUILD DEFERRED REFRESH AUTO ON SCHEDULE EVERY 5 MINUTE STARTS "2024-07-03 11:53:09"
 partition by(dt)
 DISTRIBUTED BY RANDOM BUCKETS 1
 PROPERTIES ('replication_num' = '1') 
 AS 
 select dt, advertiser, count(distinct user_id)
     from advertiser_view_record
     group by dt, advertiser;

The error is as follows:

ERROR 1105 (HY000): errCode = 2, detailMessage = Unable to find a suitable base table for partitioning, the fail reason is can't not find valid partition track column, because related base table partition column doesn't contain the mv partition or partition nullable check fail, the mvReferenceColumn is `mv_dt` DATEV2 NOT NULL

Solution

Identify the column in async materialized view definition. If it selected a column from a sync materialized view, find the real column from the olap table and check if it is the partition column of the olap table.

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