Hey folks 👋 Is this a bug? I cannot DELETE FROM a base table if there is a sync MV defined on it.

Hello! I'm Rocky, your StarRocks assistant. How can I help you today? If you have questions regarding StarRocks syntax, specific errors, or general usage, please let me know and I'll be happy to assist you!

@Rocky - is this sql showing a bug? It seems like update is failing when there's a sync MV on the table. CREATE DATABASE IF NOT EXISTS jdu; ​ CREATE TABLE IF NOT EXISTS jdu.orders ( order_id BIGINT NOT NULL, user_id INT NOT NULL, dt DATE NOT NULL, amount DECIMAL(10,2) NOT NULL ) DUPLICATE KEY (order_id) DISTRIBUTED BY HASH(order_id); ​ CREATE MATERIALIZED VIEW IF NOT EXISTS jdu.orders_daily_volume AS SELECT DATE_TRUNC('day', dt) as day, SUM(amount) as amount FROM jdu.orders GROUP BY day; ​ DESCRIBE jdu.orders ALL; ​ INSERT INTO jdu.orders VALUES ('1', '1', now(), 23); INSERT INTO jdu.orders VALUES ('2', '2', now(), 34); ​ DELETE FROM jdu.orders WHERE user_id = '2'; -- mysql error: Unknown column 'user_id' in 'index[orders_daily_volume]'

Hi, I'm Rocky! This behavior is not a bug; it is a known restriction when using Synchronous Materialized Views (Rollups) in StarRocks. Why this happens Synchronous materialized views (created using

CREATE MATERIALIZED VIEW

without the

REFRESH

keyword) are essentially indexes that are updated in real-time whenever the base table is modified. When you perform a

DELETE

(or

UPDATE

) on a base table that has a synchronous MV: 1. StarRocks must also update the data within that MV index to keep it consistent. 2. To perform this update efficiently, the engine requires that the columns used in the

WHERE

clause of your `DELETE`/`UPDATE` statement must exist in the synchronous materialized view. In your example: • Your base table

orders

has the column

user_id

. • Your MV

orders_daily_volume

is defined as:

sql
  SELECT
      DATE_TRUNC('day', dt) as day,
      SUM(amount) as amount
  FROM jdu.orders
  GROUP BY day;

• This MV only contains the columns

day

and

amount

. It does not contain

user_id

. • When you run

DELETE FROM jdu.orders WHERE user_id = '2'

, the engine tries to apply this filter to the

orders_daily_volume

index to identify which records to update/remove, but it fails because

user_id

is missing from that index. Solutions 1. Use Asynchronous Materialized Views (Recommended) If you need to perform frequent

DELETE

or

UPDATE

operations on arbitrary columns, it is recommended to use Asynchronous Materialized Views. Asynchronous MVs are independent physical tables. Deleting data from the base table does not require an immediate, synchronous update of the MV index, so there are no restrictions on the

WHERE

clause columns.

sql
CREATE MATERIALIZED VIEW jdu.orders_daily_volume
REFRESH ASYNC EVERY(INTERVAL 1 MINUTE) -- or REFRESH DEFERRED
AS
SELECT 
    DATE_TRUNC('day', dt) as day,
    SUM(amount) as amount
FROM jdu.orders
GROUP BY day;

2. Include the column in the Synchronous MV If you must use a Synchronous MV, you would need to include the filtering column (

user_id

) in the MV's

GROUP BY

clause. However, this changes the granularity of your aggregation:

sql
CREATE MATERIALIZED VIEW jdu.orders_daily_volume
AS
SELECT 
    DATE_TRUNC('day', dt) as day,
    user_id, -- Adding this allows DELETE WHERE user_id = ...
    SUM(amount) as amount
FROM jdu.orders
GROUP BY day, user_id;

3. Drop the MV before the operation If this is a one-time maintenance task, you can drop the synchronous MV, perform your

DELETE

, and then recreate the MV. For more details, you can check the StarRocks documentation on Synchronous Materialized Views. References • using_starrocks/Materialized_view-single_table.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • sql-reference/sql-statements/table_bucket_part_index/DELETE.md

Thanks Tony