StarRocks / starrocks

The world's fastest open query engine for sub-second analytics both on and off the data lakehouse. With the flexibility to support nearly any scenario, StarRocks provides best-in-class performance for multi-dimensional analytics, real-time analytics, and ad-hoc queries. A Linux Foundation project.
https://starrocks.io
Apache License 2.0
8.86k stars 1.77k forks source link

amv cannot referesh table when base table hava data (time < partition_ttl_number) #29768

Closed MyLanPangzi closed 1 year ago

MyLanPangzi commented 1 year ago

Steps to reproduce the behavior (Required)

when table hava data transact_day < 2023-08-20 ,amv cannot referesh data.

create table dwd.dwd_trd_contract_trade_realtime
(
    `transact_day`                date,
    `parent_id`                   bigint,
    `user_id`                     bigint,
    `account_id`                  bigint,
    `side`                        tinyint,
    `exec_id`                     string,
    `exec_qty`                          bigint,
--   UNIQUE KEY `ui_tradinglog_account_execid_side` (`account_id`,`exec_id`,`side`),
)PRIMARY KEY (`transact_day`,`parent_id`,`user_id`,`account_id`,`side`,`exec_id`)
PARTITION BY date_trunc('day', transact_day)
DISTRIBUTED BY HASH(`transact_day`,`parent_id`,`user_id`,`account_id`,`side`,`exec_id`)
PROPERTIES(
    "replication_num" = "1"
    , "enable_persistent_index" = "true"
    , "partition_live_number" = "60"
    );

CREATE MATERIALIZED VIEW dwd.dwd_trd_contract_trade_union
DISTRIBUTED BY HASH(calday,parent_id,user_id,currency,symbol)
REFRESH ASYNC
START
    ('2023-08-16 08:23:59')
    EVERY (interval 60 second)
PARTITION BY date_trunc('day',calday)
PROPERTIES (
              "partition_ttl_number"="1",
              "replication_num"="1"

              )
AS
select transact_day                                                         calday
     , parent_id
     , user_id
     , currency
     , symbol
     , exec_qty       as INVP_take_vol_usd 
from dwd.dwd_trd_contract_trade_realtime d
where exec_status in (6, 7)
  and d.parent_id > 60000 
;

Expected behavior (Required)

amv can correctly referesh data.

Real behavior (Required)

Error: The row is out of partition ranges. Please add a new partition.. Row: [2023-06-14, 4829407, 4829407, 1, 1, 6375, 0, 0, 0, 6375, 0, 0.00000000, 0.00000000, 3.82516]

StarRocks version (Required)

3.1.0-1778465

MyLanPangzi commented 1 year ago

troubleshooting sql

select * from information_schema.tasks  order by CREATE_TIME desc limit 1\G;
select * from information_schema.task_runs where task_name='mv-961278' order by CREATE_TIME \G;
select tracking_log from information_schema.load_tracking_logs where job_id=959687;