You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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.
Search before asking
Description
When creating an asynchronous materialized view, if a synchronous materialized view is selected, the following error will be reported:
reproduce
The error is as follows:
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?
Code of Conduct
The text was updated successfully, but these errors were encountered: