Hi everyone I have a question about an issue I m seeing whil StarRocks #questions-and-troubleshooting

Hi everyone! I have a question about an issue I'm ...

이병우

11/26/2025, 2:50 PM

Hi everyone! I have a question about an issue I'm seeing while creating an async MV in StarRocks. When building an async MV on top of an external catalog (Glue Iceberg) table, I get a partition-mapping analyzing error. Could you help confirm whether this is my mistake or a limitation/bug? Environment • StarRocks 3.5.2 • External catalog:

glue_catalog

(Iceberg) Base table (Glue Iceberg)

Copy code

CREATE TABLE testtable (
  id varchar(1073741824) DEFAULT NULL,
  charge_amount decimal(20, 10) DEFAULT NULL,
  created_at datetime DEFAULT NULL,
  created_at_kst datetime DEFAULT NULL
)
PARTITION BY (day(created_at_kst))
PROPERTIES (
  "location" = "...",
  "write.format.default" = "parquet",
  "write.parquet.compression-codec" = "zstd",
  "write.upsert.enabled" = "true"
);

Schema

Copy code

DESCRIBE glue_catalog.test.testtable;

id               VARCHAR(...)
charge_amount    DECIMAL(20,10)
created_at       DATETIME
created_at_kst   DATETIME   partition key

MV I'm trying to create

Copy code

CREATE MATERIALIZED VIEW test_mv
PARTITION BY date_trunc("day", created_at_kst) 
DISTRIBUTED BY HASH(ad_id) BUCKETS 16
REFRESH ASYNC EVERY (INTERVAL 1 DAY)
AS
SELECT
  id,
  date_trunc('day', created_at_kst) AS created_at_kst_day,
  SUM(charge_amount) AS charge_amount
FROM glue_catalog.test.testtable
GROUP BY id, date_trunc('day', created_at_kst);

Actual error

Copy code

SQL Error [1064] [42000]: Getting analyzing error. Detail message: Materialized view partition exp column:created_at_kst is not found in query statement.

Even though the base Iceberg table is partitioned by

day(created_at_kst)

and the MV uses the matching expression

date_trunc('day', created_at_kst)

, the analyzer fails to map the partition column and throws the error above. Is this: 1. a current limitation (async MV doesn’t support external Iceberg transform partitions yet), or 2. is there a specific MV partition pattern/syntax I should use? Thanks in advance!

이병우

11/27/2025, 1:18 AM

I’ve resolved this. The root cause is that in StarRocks async MVs, the expression (or column) used in

PARTITION BY

must exist as a top-level output column of the MV query. When you write:

Copy code

PARTITION BY date_trunc('day', created_at_kst)

directly on the outer MV, the analyzer fails to map that expression to the base Iceberg transform partition (e.g.,

day(created_at_kst)

), and throws: >

Materialized view partition exp column: created_at_kst is not found in query statement.

Working pattern: compute the partition expression in a subquery, expose it as an aliased column, and then partition the MV by that alias in the outer query. Example:

Copy code

CREATE MATERIALIZED VIEW test_mv
PARTITION BY created_at_kst_day
DISTRIBUTED BY HASH(id) BUCKETS 16
REFRESH ASYNC EVERY (INTERVAL 1 DAY)
AS
SELECT
  id,
  created_at_kst_day,
  charge_amount
FROM (
    SELECT
      id,
      date_trunc('day', created_at_kst) AS created_at_kst_day,
      SUM(charge_amount) AS charge_amount
    FROM glue_catalog.test.testtable
    GROUP BY id, date_trunc('day', created_at_kst)
) t;

Open in Slack

Previous Next