@Rocky Do you know what's reason that got wrong result from github.com/StarRocks/starrocks/pull/63849?

StarRocks pull request #63849 addressed a bug where incorrect results were returned during sort-based aggregations in shared-data clusters. The specific cause was related to Sorted-by-key Scans being executed with multiple I/O tasks. Under these conditions, the data stream processing could fail to maintain the expected order or correctly handle the parallel data chunks, leading to incorrect aggregation outcomes. This fix was included in several maintenance releases, including: * StarRocks v3.3.x (e.g., v3.3.9) * StarRocks v3.4.x (e.g., v3.4.2) If you are encountering this issue, it is recommended to upgrade to the latest patch version of StarRocks 3.3, 3.4, or 3.5.

@Rocky is it safe to enable this back in share data mode?

// Cloud Native Table is not yet optimized for bucket aggregation.

if (scan.getTable().isCloudNativeTableOrMaterializedView()) {

return null;

}

The pull request #63849 was a critical fix for a bug where Sorted-by-key Scans with multiple I/O tasks could return wrong results during sort-based aggregations in shared-data clusters. Regarding your question on whether it is safe to enable bucket aggregation for Cloud Native Tables by removing that code block: The Reason for the Block The code you quoted is a safety guard in the Frontend (FE) optimizer. It was likely put in place because: 1. Correctness Risks: Shared-data (Lake) tables handle data distribution differently than shared-nothing (OLAP) tables. Early versions had issues ensuring that the data stream remained correctly ordered when processed by multiple I/O tasks in parallel, which is essential for "local" or bucket-based aggregations. 2. Lack of Optimization: The shared-data architecture relies on Compute Nodes (CN) which are stateless. Ensuring "locality" for bucket-level optimizations (like avoiding shuffles because data is already partitioned by the grouping key) requires the scheduler to consistently map specific buckets to specific CNs. This "tablet-aware" scheduling was not fully optimized or vetted for all aggregation types when that block was written. Is it safe to enable now? While PR #63849 fixes the specific "wrong result" bug related to sorted-by-key scans, enabling this optimization manually is still considered experimental for the following reasons: * Sorted Streaming Aggregation: Even after #63849, other related issues (such as #67376) have been identified and fixed regarding Sorted Streaming Aggregation in shared-data mode. If you are on an older version (pre-3.3.20 or pre-3.4.2), you might still hit correctness issues. * Planner Vetting: The block exists in the

GlobalAggregateOptimizer

because the planner might not yet correctly estimate the costs or handle edge cases where Lake tables are involved in complex

JOIN

+

AGGREGATE

pipelines that expect specific data distributions. * Version Dependency: If you are running StarRocks v3.3.9+, v3.4.2+, or v3.5, the most severe correctness bugs (including #63849) have been addressed. Recommendation If you choose to remove the block for testing: 1. Verify Results: Run your queries with and without the optimization and compare the results carefully using large datasets. 2. Check Your Version: Ensure you have at least the fixes from v3.3.9 or v3.4.2 to avoid the "wrong result" bug you originally referenced. 3. Monitor Performance: "Optimized" in the comment also refers to performance. Without proper tablet-affinity scheduling on CNs, enabling this might actually result in suboptimal task distribution or excessive remote I/O if the cache is cold. For production environments, it is best to wait for an official release where this optimization is enabled by default, indicating it has passed the community's full regression suite for the shared-data architecture. References • faq/shared_data_faq.md • table_design/data_distribution/Data_distribution.md • best_practices/bucketing.md

@Rocky i'm on 4.0.17 try Sorted-by-key Scans with multiple I/O tasks, still don't see any wrong result..

The reason you are no longer seeing wrong results in StarRocks v4.0.17 is that PR #63849 (and subsequent fixes like #67376) successfully addressed the core correctness issues with Sorted-by-key Scans and Sorted Streaming Aggregations in shared-data mode. However, the code block you found is still there because Bucket Aggregation (local aggregation that skips the network shuffle) involves more than just correct scanning; it requires the planner and scheduler to guarantee "bucket locality" across a potentially elastic set of Compute Nodes (CN). Why the block remains in v4.0.x Even though the "wrong result" bug from sorted scans is fixed, the StarRocks optimizer keeps this guardrail for Cloud Native tables for a few reasons: 1. Scheduler Locality: In shared-data mode, CN nodes are stateless. For bucket aggregation to be safe, the LakeScheduler must ensure that all tablets belonging to the same bucket (across different partitions) are consistently routed to the same CN. While tablet affinity exists, it may not yet be considered "hard" or robust enough in v4.0 to bypass the shuffle phase in all scenarios (e.g., during node scaling or tablet migrations). 2. Performance Vetting: "Not yet optimized" in the comment suggests that without the proper scheduling guarantees, enabling this could lead to performance regressions or uneven load distribution if the cache isn't hit correctly. 3. Materialized View Complexity: Cloud Native Materialized Views have additional metadata layers. Enabling bucket-aware optimizations for them requires extensive verification to ensure the optimizer doesn't make incorrect assumptions about the underlying data distribution. Is it safe to enable? * For Testing: If you are in a dev environment and want to test the performance gains, you can experiment with removing the block. Since you are on v4.0.17, you are protected against the known correctness bugs (#63849, #67376) that plagued earlier versions. * For Production: It is not recommended to remove this guardrail manually. The official StarRocks roadmap (specifically for v4.1+) indicates that full support for "Local aggregation / bucket-shuffle joins" across all table types (including shared-data) is a primary feature. Summary: You don't see wrong results because the specific bug in PR #63849 was fixed. The block remains because the Bucket Aggregation optimization itself is still being finalized and certified for the elastic, shared-data architecture. It is best to wait for a version (like v4.1) where this block is removed by the maintainers. References • faq/shared_data_faq.md • best_practices/bucketing.md • table_design/data_distribution/Data_distribution.md • table_design/data_distribution/feature-support-data-distribution.md