http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> we are seeing something very strang...
# questions-and-troubleshooting
r
@Rocky we are seeing something very strange with our data. why would the same values not be grouping in this query:
Copy code
mysql> select organization_uuid, count(*) from account_data where is_deleted = false and name is not null and domain is not null and organization_uuid = '0361fd07-d5d1-4ed3-a711-6800c19fdbec'
+--------------------------------------+----------+
| organization_uuid                    | count(*) |
+--------------------------------------+----------+
| 0361fd07-d5d1-4ed3-a711-6800c19fdbec |  1890072 |
| 0361fd07-d5d1-4ed3-a711-6800c19fdbec |  1889038 |
| 0361fd07-d5d1-4ed3-a711-6800c19fdbec |  1258244 |
+--------------------------------------+----------+
3 rows in set (0.27 sec)
r
This is definitely unexpected behavior. In a standard SQL query with an aggregate like 
count(*)
, you should either see a single merged result or an error (if 
GROUP BY
is missing and 
ONLY_FULL_GROUP_BY
is enabled). Seeing three separate rows for the same 
organization_uuid
typically points to one of the following issues: 1. Hidden Characters or Whitespaces The most common reason for "identical" strings not grouping is that they aren't actually identical. StarRocks is sensitive to trailing spaces, tabs, or newline characters. * Trailing Spaces: 
VARCHAR
strings with trailing spaces (e.g., 
'uuid '
vs 
'uuid'
) are treated as different values in a 
GROUP BY
. * Hidden Characters: Non-printable characters like 
\r
or 
\0
can make strings look identical in the console while being different internally. How to check: Run this query to see the actual length and hex representation of the values:
Copy code
sql
SELECT 
    organization_uuid, 
    LENGTH(organization_uuid) AS byte_len, 
    CHAR_LENGTH(organization_uuid) AS char_len,
    HEX(organization_uuid) AS hex_val,
    COUNT(*) 
FROM account_data 
WHERE organization_uuid = '0361fd07-d5d1-4ed3-a711-6800c19fdbec'
GROUP BY organization_uuid;
If the 
hex_val
or 
byte_len
differs between the rows, you have trailing spaces or hidden characters in your data. 2. Missing 
GROUP BY
Clause Your example query does not include a 
GROUP BY organization_uuid
clause. * Standard Behavior: StarRocks should usually return an error if you select a non-aggregated column without a 
GROUP BY
. * If it runs anyway: Depending on your 
sql_mode
settings (specifically if 
ONLY_FULL_GROUP_BY
is disabled), StarRocks might allow the query but return unpredictable results. However, even then, an aggregate query without 
GROUP BY
should return exactly one row. The fact that you are getting three rows suggests that either: 1. There is an implicit grouping happening (e.g., you are querying a View that has its own internal grouping). 2. The query engine is failing to "finalize" the aggregation across different tablets or fragments. 3. Data Consistency (Primary/Unique Key Tables) If 
account_data
is a Primary Key or Unique Key table, StarRocks performs a "merge-on-read" to deduplicate rows. * If there is a temporary inconsistency or a bug in the version merging (especially if you are on an older version of StarRocks), you might occasionally see duplicate entries for the same key before compaction finishes. * However, even in those cases, a 
GROUP BY
at the query level should still merge these into a single row. Recommended Next Steps: 1. Add Explicit Group By: Ensure your query ends with 
GROUP BY organization_uuid
. 2. Verify Whitespaces: Use 
TRIM(organization_uuid)
in the 
GROUP BY
and 
SELECT
to see if the rows merge:
Copy code
sql
    SELECT TRIM(organization_uuid), COUNT(*)
    FROM account_data
    WHERE organization_uuid = '0361fd07-d5d1-4ed3-a711-6800c19fdbec'
    GROUP BY TRIM(organization_uuid);
3. Check Table Type: What is the engine type of 
account_data
(Duplicate, Aggregate, or Primary Key)? You can find this via 
SHOW CREATE TABLE account_data;
. 4. StarRocks Version: Which version are you running? (Run 
SELECT correspondence_version();
or 
SHOW FRONTENDS;
). Certain older versions had edge-case bugs with aggregation finalization. References • sql-reference/sql-functions/aggregate-functions/group_concat.md • sql-reference/sql-functions/string-functions/group_concat.md • sql-reference/sql-functions/string-functions/trim.md
r
@Rocky same byte_len, char_len, and hex_val on all three. we are running version 4.0.8-2090bfc. the table is PRIMARY KEY. trimming does nothing, still 3 entries. same with group by
r
This behavior—where an aggregate query returns multiple rows for the same group (or even multiple rows when no 
GROUP BY
is present)—indicates that the Global Aggregation step in the query plan is being bypassed or is failing to merge results from different tablets. In StarRocks, a query like 
SELECT count(_) FROM table
should _always* return exactly one row. Seeing three rows suggests that the Frontend (FE) is receiving partial results from three different tablets (or execution fragments) and simply passing them to you instead of performing the final merge. Given you are on version 4.0.8 (which, based on current known issues in that branch, has some specific aggregation rewrite bugs), here is how to diagnose and fix this: 1. Check for Optimization Bugs There are known issues in the 4.0.x/4.1.x series where "Simple Aggregate Rewrites" (optimizations that try to read counts directly from metadata or scanners) can fail to trigger a global merge or cause row-count mismatches. Try running the query with these optimizations disabled to see if the results merge:
Copy code
sql
-- Disable the simple aggregate rewrite to metadata/scanners
SET enable_rewrite_simple_agg_to_hdfs_scan = false;
SET enable_metascan = false;

-- Execute your query again
SELECT organization_uuid, COUNT(*) 
FROM account_data 
WHERE organization_uuid = '0361fd07-d5d1-4ed3-a711-6800c19fdbec'
GROUP BY organization_uuid;
2. Inspect the Query Plan The query plan will tell us if a GLOBAL aggregation step exists. Run:
Copy code
sql
EXPLAIN SELECT organization_uuid, COUNT(*) FROM account_data ... GROUP BY organization_uuid;
Look for the 
AGGREGATE
node. You should see two levels: * Local/Partitioned Aggregate: Happens on the BEs. * Global Aggregate: Should happen after an 
EXCHANGE
node to merge results into one. If you only see one 
AGGREGATE
node and no 
GATHER
or 
EXCHANGE
above it, the optimizer is incorrectly assuming the data is already aggregated. 3. Primary Key Tablet Inconsistency Since this is a Primary Key table, seeing three rows for the same UUID (even before aggregation) means the Merge-on-Read logic is failing. This usually happens if: * Persistent Index is out of sync: The index that tracks which tablet/rowset contains the latest version of a key might be corrupted. * Tablet Replication Issues: The same key might have ended up in three different tablets due to a previous change in bucket count or distribution key. Verify tablet distribution:
Copy code
sql
SHOW TABLETS FROM account_data WHERE organization_uuid = '0361fd07-d5d1-4ed3-a711-6800c19fdbec';
If the results show that this UUID exists in multiple different Tablet IDs, your distribution key is likely not 
organization_uuid
, or there was a critical failure during a schema change/rebalance. Summary of fixes to try: 1. Workaround: Set 
enable_rewrite_simple_agg_to_hdfs_scan = false
. 2. Force Merge: Try 
SELECT organization_uuid, SUM(cnt) FROM (SELECT organization_uuid, COUNT(*) as cnt FROM account_data ... GROUP BY organization_uuid) t GROUP BY organization_uuid;
. 3. Consistency Check: Run 
ADMIN SHOW REPLICA STATUS FROM account_data;
to see if any replicas are stuck or inconsistent. If disabling the optimizations fixes the row count, you have likely hit a regression in the 4.0.8 planner (related to Issue #74174 or #66967) and should consider upgrading to a newer hotfix. References • table_design/table_types/aggregate_table.md • sql-reference/sql-statements/table_bucket_part_index/SELECT.md • sql-reference/sql-functions/aggregate-functions/any_value.md • using_starrocks/Materialized_view-single_table.md
r
@Rocky im not seeing enable_metascan
Copy code
mysql> SET enable_metascan = false;
ERROR 1193 (HY000): Getting analyzing error. Detail message: Unknown system variable 'enable_metascan', the most similar variables are {'enable_shared_scan', 'enable_file_metacache', 'enable_tde'}.
r
The error you received confirms that 
enable_metascan
is not the correct name for your version (it is likely an internal optimization name rather than a session variable in 4.0.8). However, the "3 rows for the same value" behavior is a classic symptom of an execution plan where the Global Aggregation step is being skipped. In StarRocks, the query optimizer sometimes assumes that data is already grouped by the column in your 
GROUP BY
clause if that column is also the Distribution Key (the column in 
DISTRIBUTED BY HASH(...)
). If the Frontend (FE) incorrectly believes the data is already partitioned by that key, it will only perform a local aggregation on each tablet and skip the final merge. 1. Test for Plan Rewriting Bugs In the 4.0.x series, there are several optimizations that can cause this. Please try running your query with these specific variables disabled:
Copy code
sql
-- This is the most likely culprit for PK tables in 4.0.x
SET enable_rewrite_simple_agg_to_hdfs_scan = false;

-- Disable bucket-level optimizations that might skip the global shuffle
SET enable_per_bucket_optimize = false;

-- Force a multi-stage aggregation to ensure a global merge happens
SET new_planner_agg_stage = 2;

-- Re-run your query
SELECT organization_uuid, COUNT(*) 
FROM account_data 
WHERE organization_uuid = '0361fd07-d5d1-4ed3-a711-6800c19fdbec'
GROUP BY organization_uuid;
2. Check Table Distribution Consistency If the query above still returns 3 rows, it means the storage layer itself has duplicate keys across different tablets, which should never happen in a healthy Primary Key table. This can occur if: * The table was rebalanced or its bucket count was changed, but some data remained in old tablets. * The 
organization_uuid
is NOT the distribution key, but the FE thinks it is. Run this to see where the data lives:
Copy code
sql
-- This will show which tablets contain this specific UUID
SELECT 
    organization_uuid, 
    COUNT(*), 
    current_tablet_id() 
FROM account_data 
WHERE organization_uuid = '0361fd07-d5d1-4ed3-a711-6800c19fdbec'
GROUP BY organization_uuid, current_tablet_id();
* If you see 3 different `current_tablet_id`s: Your data for this single UUID is physically split across three tablets. If 
organization_uuid
is your distribution key, this is a data integrity issue (metadata corruption). * If you see 1 
current_tablet_id
but still 3 rows: The issue is strictly in the query execution/aggregation logic. 3. Verify Table Metadata Please provide the output of: 1. 
SHOW CREATE TABLE account_data;
(Specifically check the 
PRIMARY KEY(...)
and 
DISTRIBUTED BY HASH(...)
clauses). 2. 
EXPLAIN SELECT organization_uuid, COUNT(*) FROM account_data WHERE organization_uuid = '...' GROUP BY organization_uuid;
If 
enable_rewrite_simple_agg_to_hdfs_scan = false
fixes it, you have hit a known bug in the "Simple Aggregation Rewrite" logic where the optimizer incorrectly prunes the final merge node for Primary Key tables. We recommend checking for a newer hotfix in the 4.0 branch if this is the case.
r
the table is not distributed by organization_uuid, but organization_uuid is the first item in the PRIMARY KEY. disabling optimizations does not fix it
@Rocky the table is not distributed by organization_uuid, but organization_uuid is the first item in the PRIMARY KEY. disabling optimizations does not fix it
r
Given that the bytes/hex values are identical and disabling the "simple aggregation rewrite" didn't work, we are likely looking at a bug in the Query Planner's stage-splitting logic. In StarRocks 4.x, the optimizer uses several "bucket-aware" and "colocate" optimizations. If 
organization_uuid
is the first column of your Primary Key, the optimizer might be incorrectly inferring that the data is already partitioned by that column, even though it is not your Distribution Key. This causes it to skip the "Global Merge" (Shuffle) step, returning partial results from each tablet instead of one merged result. Please try the following steps to confirm and work around this: 1. Force a Multi-Stage Aggregation The most direct way to fix this at the query level is to force the engine to perform a shuffle. Use these session variables:
Copy code
sql
-- Force the planner to use at least 2 stages (Local + Global)
SET new_planner_agg_stage = 2;

-- Disable bucket-level execution optimizations introduced in v4.x
SET enable_group_execution = false;
SET enable_partition_bucket_optimize = false;

-- Disable the query cache to ensure you aren't seeing a stale result
SET enable_query_cache = false;

-- Re-run the query
SELECT organization_uuid, COUNT(*) 
FROM account_data 
WHERE organization_uuid = '0361fd07-d5d1-4ed3-a711-6800c19fdbec'
GROUP BY organization_uuid;
2. Inspect the 
EXPLAIN
Output Run the query with 
EXPLAIN
and look for the 
AGGREGATE
nodes. * Correct behavior: You should see a 
LOCAL
aggregate, followed by an 
EXCHANGE (SHUFFLE)
, followed by a 
GLOBAL
aggregate. * The Bug: If you see only a single 
AGGREGATE
node (often labeled 
FINALIZE
) without an 
EXCHANGE
or 
GATHER
node above it, the optimizer has incorrectly pruned the global merge step. 3. Check for "Orphan" Data (Data Integrity) Since this is a Primary Key table, and the UUID is part of the PK but not the distribution key, it is expected that different rows for the same UUID can live in different tablets. However, they should never fail to merge. If the variables in step 1 don't work, run this to see if the engine thinks these rows are actually in different "Execution Fragments":
Copy code
sql
SELECT 
    organization_uuid, 
    COUNT(*), 
    current_backend_id(), 
    current_tablet_id()
FROM account_data 
WHERE organization_uuid = '0361fd07-d5d1-4ed3-a711-6800c19fdbec'
GROUP BY organization_uuid, current_backend_id(), current_tablet_id();
If this returns 3 rows, it confirms that the data is physically distributed, and the failure is strictly in the Global Aggregation (the part that should be collecting these counts from different BEs/Tablets). Summary This appears to be a regression in the 4.0.8 planner's handling of Primary Key prefix columns during aggregation. Disabling 
enable_group_execution
and setting 
new_planner_agg_stage = 2
are the standard workarounds. If those don't fix it, it would be highly recommended to provide the 
EXPLAIN COSTS
output to the StarRocks community, as it implies the optimizer is ignoring the fact that 
organization_uuid
is not the distribution key.
r
@Rocky one of these fixed it...now what? what was wrong:
Copy code
mysql> SET new_planner_agg_stage = 2;
Query OK, 0 rows affected (0.04 sec)

mysql> SET enable_group_execution = false;
Query OK, 0 rows affected (0.09 sec)

mysql> SET enable_partition_bucket_optimize = false;
Query OK, 0 rows affected (0.03 sec)

mysql> SET enable_query_cache = false;
Query OK, 0 rows affected (0.06 sec)
r
The combination of variables you set targeted a known category of Planner/Optimizer bugs in the StarRocks 4.0.x branch. While any of them could have contributed, the primary reason you saw multiple rows for the same group is that the Optimizer incorrectly skipped the Global Aggregation stage. What was wrong? In StarRocks, aggregation usually happens in two stages: 1. Local Stage: Each tablet calculates a partial count/sum. 2. Global Stage: Results from all tablets are "shuffled" (sent to a central node) and merged into a single final result. Because 
organization_uuid
is the first column of your Primary Key, the Optimizer in version 4.0.8 incorrectly inferred that the data was already "pre-partitioned" or "pre-sorted" in a way that made the second (Global) stage unnecessary. It saw the PK prefix and thought: "I don't need to shuffle this data to merge it; each node already has all the data for any given UUID." However, since 
organization_uuid
was not your Distribution Key, this assumption was false. The data for one UUID was actually spread across 3 different tablets, and the Optimizer "optimized out" the very step needed to combine them. Which variable fixed it? It is highly likely that 
new_planner_agg_stage = 2
 or 
enable_group_execution = false
 did the heavy lifting: * 
new_planner_agg_stage = 2
: This is the "sledgehammer." It explicitly tells the planner: "I don't care what you think; you must use two stages for this aggregation." This forces the Global Merge step to reappear in the query plan. * 
enable_group_execution = false
: This disables "Colocate Group Execution," a v3.3+ feature that tries to process data in bucketed ranges to save memory. In v4.0.x, there have been bugs where this feature causes the engine to return one row per bucket instead of one row per group. * 
enable_query_cache = false
: There is a documented bug in some 4.0.x versions where the Query Cache stores the partial results of a failed aggregation and keeps serving them even after you change other settings. Recommendation Since this is a confirmed behavior in the 4.0.8 planner logic: 1. Upgrade: This class of "Aggregation Stage Pruning" bugs is actively being patched. Check for version 4.0.10+ or the latest 4.1.x release. 2. Temporary Fix: If you cannot upgrade immediately, you can apply the fix to your specific session or the whole cluster:
Copy code
sql
    -- Cluster-wide workaround (use with caution, may slightly impact performance of healthy queries)
    SET GLOBAL enable_group_execution = false;
3. Verify: Run 
EXPLAIN <your_query>
and look for the 
AGGREGATE
node. You should now see two distinct nodes (likely 
AGGREGATE (update)
and 
AGGREGATE (merge)
) separated by an 
EXCHANGE
. If you only see one, the optimization is still active. References • using_starrocks/sorted_aggregate.md • release_notes/release-2.5.md • using_starrocks/caching/query_cache.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md
r
@Rocky also should i re-enable those settings i changed? are my 
SET enable_partition_bucket_optimize = false;
changes permanent? also, is there a version where this is fixed
r
To answer your questions about the settings and future stability: 1. Are these changes permanent? No. Because you used the 
SET variable = value;
syntax, these changes are session-level only. * They will disappear as soon as you close your current MySQL/client connection. * If you open a new terminal or your application reconnects, the "buggy" behavior will likely return unless you apply them again or set them globally. If you want to make them permanent across all connections (until you upgrade), you must use:
Copy code
sql
SET GLOBAL enable_group_execution = false;
SET GLOBAL new_planner_agg_stage = 2;
Note: Only use 
SET GLOBAL
for the specific variable that actually fixed the issue. You can test this by re-enabling them one by one in your session. 2. Should you re-enable them? In a healthy cluster, yes, you should want these enabled because they provide significant performance optimizations: * 
enable_group_execution
: Allows StarRocks to process data in smaller, more memory-efficient batches. Disabling it can increase memory usage for large queries. * 
enable_partition_bucket_optimize
: Vital for pruning data in large partitioned tables. * 
new_planner_agg_stage
: Leaving this at 
0
(default) allows the optimizer to choose between 1-stage and 2-stage aggregation based on the data distribution. Forcing it to 
2
can make small queries slightly slower but ensures correctness when the optimizer is confused. My recommendation: Keep them disabled only for the specific queries/sessions that are showing the duplicate row bug (using 
/_+ SET_VAR(...) _/
hints) until you can upgrade. 3. Which version is this fixed in? This specific issue—where the optimizer incorrectly prunes the merge stage for Primary Key tables when the 
GROUP BY
column is a PK prefix but not the distribution key—has seen several fixes in recent patches: * Fixed in 4.0.10+: Several "MetaScan" and "Aggregation Rewrite" bugs were addressed in the 4.0.9 and 4.0.10 patch releases. * Fixed in 4.1.x: The 4.1 branch includes a more robust fix for how the planner handles "Property Derivation" (how it tracks if data is sorted or partitioned) for Primary Key tables. Action Plan: 1. Test the "Minimal Fix": Try running the query with only 
SET enable_group_execution = false;
. If that fixes it, it's a known bug with "Colocate Group Execution" in 4.0.8. 2. Upgrade: Plan an upgrade to 4.0.11 or move to the 4.1.x stable branch. These versions have significantly improved logic for Primary Key table aggregations. Key table aggregations. References • release_notes/release-2.1.md • release_notes/release-4.0.md • release_notes/release-3.4.md
r
@Rocky only 
SET enable_query_cache = false;
seems to fix it
r
thought The user found that 
SET enable_query_cache = false;
is the single fix. This is a classic issue where the Query Cache returns stale or incorrect results for aggregations. Key points: 1. Why Query Cache? StarRocks Query Cache (tablet-level cache) is powerful but can be prone to bugs when it doesn't correctly invalidate after certain operations or if it incorrectly caches a partial result of an aggregation as a "final" one. 2. Version Fixed? My search results show that StarRocks 4.0.10 was mentioned as a version where "Query results are incorrect after the query cache is enabled" was fixed (or similar issues). Specifically, 4.0.10 has fixes for low-cardinality rewrites and query cache consistency. 3. Is the change permanent? I'll reiterate that the 
SET
command is session-based. 4. Re-enabling other settings: Since only 
enable_query_cache
was the culprit, the user should re-enable the others to benefit from performance. Structure of response: • Confirm that 
enable_query_cache
was likely returning a stale or partial result. • Advice on permanence: session vs global. • Re-enabling the other settings. • Version fix details (4.0.10/4.0.11 or 4.1.x).Since only 
SET enable_query_cache = false;
fixed the issue, we have a very clear culprit: the Tablet-level Query Cache. What happened? The StarRocks Query Cache (specifically the "Computation Cache") works by caching intermediate results at the tablet level. There is a known class of bugs in version 4.0.8 where the cache can: 1. Store "Partial" results as "Final": It might have cached the counts from individual tablets but, due to a bug in the cache invalidation or the plan rewrite, it "remembered" those individual tablet counts as the final answer for that UUID. 2. Stale Metadata: If the data was updated or the table schema changed, the cache might not have invalidated correctly, continuing to serve the old (incorrect) results. Now what? You have two immediate paths depending on whether you want to "clean" the cache or just leave it off: 1. Re-enable the other optimizations: Since you've identified the Query Cache as the only issue, you should re-enable the other three settings. They are important for general performance and were not the cause of your duplicate rows:
Copy code
sql
    SET enable_partition_bucket_optimize = true;
    SET enable_group_execution = true;
    SET new_planner_agg_stage = 0; -- Set back to default (0)
2. Make the Query Cache fix permanent (Session vs. Global): Your previous 
SET
commands were session-only (they die when you disconnect). To solve this for your entire application without changing code, you can set it globally:
Copy code
sql
    SET GLOBAL enable_query_cache = false;
Note: This will disable the cache for all queries in the cluster. This is the safest way to ensure data correctness until you upgrade. 3. Try clearing the cache (Optional): If you want to keep the feature on but fix this specific query, you can try to "force" a refresh by updating a row in that organization or simply waiting for the cache to expire (default is 5 minutes for metadata). However, if it's a logic bug in 4.0.8, it will likely happen again. Is there a version where this is fixed? Yes. Several Query Cache consistency issues and "incorrect results" bugs were addressed in the following releases: * Version 4.0.10 / 4.0.11: These patch releases fixed specific "Query results are incorrect after query cache is enabled" bugs, particularly those interacting with aggregation rewrites. * Version 4.1.1+: The 4.1 branch has a significantly revamped Query Cache logic that handles "Local Shuffle" and "Global Aggregation" much more reliably. Final Recommendation: Keep 
enable_query_cache = false
at the global level for now. It is a "nice-to-have" for performance, but correctness is more important. Plan an upgrade to 4.0.11 or 4.1.x to safely turn it back on. References
2 Views
Open in Slack
PreviousNext
Powered by