@Rocky we had an interesting SQL query produced different result in starRocks version 4.1 v/s 3.5. We had initially 3.5 version which does produce right result when querying data from Iceberg tables however recently planned to upgrade to 4.1 version to get leveraged with latest features however same query is not begin returned same set of data hence we had to rollback to previous version. could you please help me to understand what's wrong here. 3.5 this query worked however it didn't produce the right result in 4.1- select *DATE*( *convert_tz*(act.datetime__v, 'UTC', 'America/Detroit')) AS _act_date_ , sent.col5, act.col6, *COUNT*(*) AS _activity_count_ FROM ( SELECT col1, col2, col3, col4 FROM

catalog1

.

db1

.

table1

WHERE datetime__v >= *DATE_SUB*(*CURRENT_DATE*(), INTERVAL 5 DAY) AND datetime__v < *CURRENT_DATE*() ) AS act JOIN

catalog1

.

db1

.

table2

AS sent ON act.col1 = sent.col2 AND sent.col4 = '1-b23673fe-cbff-462d-b3af-b2e7832a452c' and act.datetime__v BETWEEN '2026-06-08' AND '2026-06-10')t group by 1,2,3 we have to make this way to work in 4.1 same as 3.5 like below mentioned - select _act_date_, col5, col3, *count*(*) from ( select *DATE*( *convert_tz*(act.datetime__v, 'UTC', 'America/Detroit')) AS _act_date_ , sent.col5, act.col6 FROM ( SELECT col1, col2, col3, col4 FROM

catalog1

.

db1

.

table1

WHERE datetime__v >= *DATE_SUB*(*CURRENT_DATE*(), INTERVAL 5 DAY) AND datetime__v < *CURRENT_DATE*() ) AS act JOIN

catalog1

.

db1

.

table2

AS sent ON act.col1 = sent.col2 AND sent.col4 = '1-b23673fe-cbff-462d-b3af-b2e7832a452c' and act.datetime__v BETWEEN '2026-06-08' AND '2026-06-10')t group by 1,2,3

It appears you have encountered a regression or a change in optimizer behavior introduced in StarRocks version 4.1 (and the 4.x branch) regarding how Iceberg metadata and aggregations are handled. There are a few known reasons why the same query would return incorrect results in version 4.1 compared to 3.5, particularly when using

GROUP BY

on expressions with `JOIN`s: 1. Iceberg Metadata and Data Cache Staleness (Bug #70522 / #65827) Version 4.1 introduced more aggressive caching for Iceberg metadata and data files. A known issue (found in Issue #70522) reported that the

iceberg_table_cache

could occasionally serve stale or partial data, leading to "silent wrong results." * Why the subquery works: By wrapping the logic in a subquery, you often force the optimizer to materialize a temporary result set or change the way it interacts with the cache, which can bypass certain "stale" plan paths. * Action: Try disabling the Iceberg table cache temporarily to see if results match 3.5:

sql
    SET enable_iceberg_table_cache = false;

2. Aggregate-Join Pushdown Optimization StarRocks 4.1 features an enhanced optimizer that attempts to push aggregations (like your

COUNT(*)

) through joins to improve performance on data lakes. However, bugs were identified (e.g., #71856) where this rewrite produced incorrect results specifically for Iceberg base tables when complex expressions or timezones were involved. * Why the subquery works: The subquery structure creates a "barrier" for the optimizer. In Query 1, the optimizer sees a flat JOIN + GROUP BY and tries to push the count into the scan. In Query 2, the aggregation happens on top of the subquery result, preventing the buggy pushdown. * Action: Test by disabling aggregation pushdown:

sql
    SET enable_materialized_view_agg_pushdown_rewrite = false;

3. Low Cardinality Optimization (#66768) If your columns

col5

or

col6

have low cardinality (few unique values), StarRocks 4.1 may apply an optimization to speed up the

GROUP BY

. A bug was fixed in the 4.1 cycle where local-shuffle aggregations produced wrong results under this optimization. * Action: You can test if this is the cause by disabling it:

sql
    SET enable_low_cardinality_optimize = false;

4. Timezone/Date Evaluation in Pruning In 4.1, there were changes to how

convert_tz

and

DATE

expressions are evaluated during partition pruning for external catalogs. If

datetime__v

is a partition column in Iceberg, 4.1 might be pruning partitions differently than 3.5 due to timezone interpretation. * Note: Since your subquery works, it's more likely a grouping/aggregation logic issue (like point 2) than a data access/pruning issue. Recommended Steps To confirm which optimization is causing the discrepancy without rolling back, run your original query after setting these session variables: 1.

SET enable_iceberg_table_cache = false;

(Check for cache staleness) 2.

SET enable_materialized_view_agg_pushdown_rewrite = false;

(Check for buggy aggregate pushdown) 3.

SET enable_low_cardinality_optimize = false;

(Check for grouping optimization issues) If any of these resolve the issue, it is a confirmed bug in the 4.1 optimizer. It is recommended to stay on the version where your results are verified (3.5) or upgrade to the latest patch release (e.g., 4.1.1 or 4.1.2) where many of these Iceberg-specific result issues were addressed. References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • release_notes/release-4.0.md • release_notes/release-3.4.md

@Rocky We also observed that performance degraded for the same query from 3.5 to 4.1. How do we debug that and fix it?.