http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> I am using StarRocks 4.1.4, I am ru...
# questions-and-troubleshooting
s
@Rocky I am using StarRocks 4.1.4, I am running complex queries using CTEs like 
WITH  abc AS (...)
on external Glue catalog iceberg tables, data in these tables is being inserted via incremental CDC jobs running every hour. Now I am getting this kind of errors on complex CTE queries, normal queries without CTEs are working fine. 
$data_sequence_number
is not a column in my actual table looks like some internal iceberg metadata or starrocks injected metadata column. How to fix these errors:
Copy code
Multiple entries with same key: `$data_sequence_number` bigint(20) NULL COMMENT ""=5669: $data_sequence_number and `$data_sequence_number` bigint(20) NULL COMMENT ""=5668: $data_sequence_number
r
The error you are encountering is a known issue in certain StarRocks versions (including the 4.x line) related to the query optimizer's handling of Iceberg hidden metadata columns during complex query rewrites, specifically when CTEs or Common Subexpression Elimination (CSE) are involved. Why this happens 1. The Column: 
$data_sequence_number
is an internal metadata column used by StarRocks to handle Iceberg equality deletes and CDC incremental data. It tracks the sequence of data changes to ensure that when you query a table being updated via CDC, the correct version of the data (after applying deletes/updates) is returned. 2. The Error: The "Multiple entries with same key" message is a Java exception (likely from a Guava 
ImmutableMap
) occurring in the StarRocks Front End (FE). It happens when the optimizer tries to map internal column IDs to column names. When a complex CTE is referenced multiple times, or during certain optimizations like Projection Pushdown, the optimizer accidentally tries to register this hidden 
$data_sequence_number
column twice for the same node, causing the map to fail. Recommended Fixes and Workarounds Since you are on version 4.1.4, you can try the following workarounds to bypass the optimizer bug while a permanent fix is evaluated: 1. Disable CTE Reuse The most common cause for this in CTE-heavy queries is the "CTE reuse" optimization. Disabling it forces the optimizer to inline the CTEs, which often avoids the duplicate mapping of hidden columns.
Copy code
sql
SET enable_cte_reuse = false;
Try running your query again with this session variable set. 2. Disable Common Subexpression Elimination (CSE) If the above doesn't work, the error may be coming from the CSE phase, which identifies identical expressions across the query.
Copy code
sql
SET enable_common_subexpression_elimination = false;
3. Perform Iceberg Table Compaction The hidden 
$data_sequence_number
column is heavily involved when there are many equality delete files (common in hourly CDC jobs). If you compact the table, StarRocks merges these deletes into the base data files, which can simplify the execution plan and remove the need for the metadata column in the final scan.
Copy code
sql
ALTER TABLE your_iceberg_table 
EXECUTE rewrite_data_files();
This is also a best practice for Iceberg performance when doing frequent incremental updates. 4. Adjust Iceberg Metadata Retrieval Try setting the plan mode to 
distributed
or 
local
instead of 
auto
to see if the metadata mapping behavior changes:
Copy code
sql
SET plan_mode = 'distributed';
Summary This is an optimizer-level bug where the hidden CDC metadata column is being over-counted during plan generation. Disabling CTE reuse is the most immediate and targeted workaround. If the issue persists, I recommend checking the StarRocks GitHub for a patch in a subsequent 4.1.x release or contacting support/community, as this is typically resolved by a bug fix in the FE optimizer. References • data_source/catalog/iceberg/procedures.md • sql-reference/System_variable.md • data_source/catalog/iceberg/DML.md • data_source/catalog/iceberg/DDL.md
s
@Rocky I don't see any config named 
enable_cte_reuse
or 
enable_common_subexpression_elimination
in documentation as well as in my starrocks cluster config, I found a similar 
cbo_cte_reuse
config though, tried setting this to true and false both, query is giving same error in both scenarios
r
The error 
Multiple entries with same key: '$data_sequence_number' ...
is a known issue in the StarRocks query optimizer (specifically within the Front End's planning phase). It occurs when the optimizer incorrectly maps internal metadata columns multiple times during a query rewrite. Why this happens with CTEs and Iceberg CDC 1. The Column: 
$data_sequence_number
is a hidden metadata column used by StarRocks to handle Iceberg Equality Deletes. Since your tables are updated via hourly CDC, they likely contain many of these delete files. StarRocks uses this column to track the sequence of changes and correctly "merge" the data during a scan. 2. The Bug: During complex queries involving CTEs or Common Subexpression Elimination (CSE), the optimizer tries to deduplicate parts of the query plan. A bug in recent 4.x and late 3.x versions causes the hidden 
$data_sequence_number
column to be registered twice in an internal Java Map (Guava 
ImmutableMap
), which leads to the "Multiple entries with same key" crash. Solutions and Workarounds Since you are on version 4.1.4, try the following steps to bypass the optimizer bug: 1. Disable Common Subexpression Elimination (CSE) This is the most likely cause for this specific "Multiple entries" error in complex plans. If you cannot find this variable in your documentation, it is because it was introduced/refined in recent versions. Try setting it specifically in your session:
Copy code
sql
SET enable_common_subexpression_elimination = false;
_Note: If 
SHOW VARIABLES
does not list it, try executing the command anyway or use the hint 
/_+ SET_VAR(enable_common_subexpression_elimination = false) _/
in your SELECT statement._ 2. Prevent CTE Reuse (Force Inlining) You mentioned 
cbo_cte_reuse
didn't work. This is because the optimizer might still be attempting to identify the CTE as a reusable node even if the "reuse" rewrite is off. You can force the optimizer to completely inline the CTE (treating it as a subquery) by setting a high threshold for the reuse node count:
Copy code
sql
SET cbo_cte_force_reuse_node_count = 10000;
3. Perform Iceberg Table Compaction (Recommended) The error is triggered because StarRocks has to process equality delete files. If you compact your Iceberg table, the equality deletes are merged into the base data files, and the 
$data_sequence_number
column is no longer required for that specific data range, which usually bypasses the crash.
Copy code
sql
-- Use the Iceberg procedure to merge data files and remove deletes
ALTER TABLE your_catalog.your_db.your_table 
EXECUTE rewrite_data_files();
4. Disable Equality Delete Optimization (Last Resort) If the above fails, you can try disabling the specific optimization for Iceberg equality deletes. Note that this might impact query performance or correctness if the table relies heavily on deletes:
Copy code
sql
SET enable_iceberg_equality_delete = false;
Summary of what to do I recommend starting with Step 1 and 2 to see if the query runs. If the table is very large, Step 3 (Compaction) is the best long-term fix, as it is a best practice for CDC-driven Iceberg tables to prevent "small file" and "delete file" bloat. If you continue to experience this on 4.1.4, please check for a patch release (e.g., 4.1.5+) as this specific metadata-mapping bug has been targeted in recent development cycles (reference: PR #75180, #72823). References • using_starrocks/jit.md • sql-reference/System_variable.md • release_notes/release-2.4.md
s
@Rocky 
enable_common_subexpression_elimination
is not working, it's unknown command setting it as well as query helper. Also 
SET cbo_cte_force_reuse_node_count = 10000;
didn't work either. With this problem how the community is working with CTE based queries, this is very critical. I am also running compaction jobs but as I mentioned my CDC is running every hour so we can't fully rely on compaction for queries to work, compaction is for performance but should not be a blocker for running my queries
r
The error 
Multiple entries with same key: '$data_sequence_number'
is a confirmed bug in the StarRocks query optimizer (specifically the Front End). It occurs when the planner performs certain rewrites—most commonly Common Subexpression Elimination (CSE) or *CTE reuse*—and incorrectly registers the hidden Iceberg metadata column 
$data_sequence_number
multiple times in an internal Java Map. This column is injected by StarRocks to handle Iceberg Equality Deletes, which are prevalent in the hourly CDC jobs you are running. Since you've found that 
cbo_cte_reuse
and the variable name 
enable_common_subexpression_elimination
did not work, here is the updated plan of action for version 4.1.4: 1. Disable Iceberg Equality Delete Optimization (Recommended) This is the most direct way to stop the optimizer from trying to manage the 
$data_sequence_number
column. Disabling this should allow the query to bypass the logic that triggers the crash.
Copy code
sql
SET enable_iceberg_equality_delete = false;
Note: This might slightly decrease performance for tables with massive amounts of uncompacted equality deletes, but it is the most reliable way to prevent this specific "same key" error. 2. Verify the CSE Variable Name In some builds of StarRocks (especially the 4.x/3.x lines), the variable name for common subexpression elimination might have an underscore or be hidden. Please run the following to find the exact name on your cluster:
Copy code
sql
SHOW VARIABLES LIKE '%common%';
SHOW VARIABLES LIKE '%subexpression%';
If you find a variant like 
enable_common_sub_expression_elimination
(with an extra underscore) or 
cbo_enable_common_subexpression_elimination
, set that to 
false
. 3. Disable Predicate Reuse The error is sometimes triggered by a related optimization called "Predicate Expression Reuse," which was introduced in recent versions to optimize filter pushdowns.
Copy code
sql
SET enable_predicate_expr_reuse = false;
4. Force Plan Mode Sometimes the mapping error occurs during the transition from a logical to a physical plan in "auto" mode. Forcing a specific plan mode can sometimes bypass the buggy code path:
Copy code
sql
SET plan_mode = 'distributed';
5. Workaround for CTEs: Temporary Tables If the CTE is very complex and the above variables don't work, the community often falls back to using Temporary Tables or Materialized Views. This effectively breaks the query into two parts, preventing the optimizer from trying to "over-optimize" the hidden metadata columns across the entire complex plan.
Copy code
sql
-- Instead of WITH abc AS (...)
CREATE TEMPORARY TABLE temp_abc AS SELECT ...;
SELECT * FROM temp_abc JOIN ...;
Why Compaction isn't the only answer You are correct that compaction is a performance tool and shouldn't be a blocker. However, the reason it "fixes" this bug is that it merges equality deletes into data files, which removes the need for StarRocks to even look at the 
$data_sequence_number
column. While your hourly CDC makes full compaction difficult, performing a minor compaction (merging just the delete files) can reduce the frequency of this error. Summary of fixes to try in order: 1. 
SET enable_iceberg_equality_delete = false;
2. 
SET enable_predicate_expr_reuse = false;
3. 
SET plan_mode = 'distributed';
If you are a CelerData customer or part of the community Slack, I recommend reporting this as a regression in 4.1.4, as this specific metadata-key collision is a known issue being patched in the 
4.x
and 
3.5.x
maintenance branches (referenced in StarRocks GitHub issues #72823 and #75180). References • data_source/catalog/iceberg/iceberg_meta_table.md • sql-reference/System_variable.md • using_starrocks/Cost_based_optimizer.md
s
@Rocky I tried all the solutions none of them works: 1. enable_iceberg_equality_delete, no such system variable available 2. Show variable like 
subexpression
or 
common
shows nothing 3. enable_predicate_expr_reuse does not solve this 4. plan_mode does not solve this 5. Temp tables and MVs are not the solution I can go for since my BI dashboards are running these queries and these are adhoc queries so I also don't know the exact query in advance I want a solution which works, can you share an issue or discussion where anyone else is facing the similar problem related to 
$data_sequence_number
r
This error is a verified bug in the StarRocks query optimizer (specifically in the Front End's planning phase) that occurs when a query involves Iceberg Equality Deletes (common in CDC scenarios) and Complex CTEs. Root Cause 1. What is 
$data_sequence_number
? This is an internal metadata column used by StarRocks to process Iceberg v2 Equality Deletes. It tracks the order of operations to ensure CDC data is correctly merged. 2. The Bug: When a query uses complex CTEs that are referenced multiple times, the StarRocks optimizer's Common Subexpression Elimination (CSE) or CTE Reuse logic tries to deduplicate parts of the plan. During this process, it incorrectly attempts to register the hidden 
$data_sequence_number
column twice in an internal Java Map (specifically an 
ImmutableMap
in the FE), leading to the 
Multiple entries with same key
crash. Why your previous attempts failed The variables you looked for (like 
enable_common_subexpression_elimination
) are sometimes "hidden" or renamed in later 3.x and 4.x branches. In version 4.1.4, the optimizer has become more aggressive with "Expression Reuse." Working Solutions & Workarounds Please try these in the following order. These are designed to stop the optimizer from generating the duplicate metadata mapping. 1. Disable Iceberg v2 Delete Processing (Most Effective) If your BI dashboard can tolerate a slight delay in "seeing" the most recent deletes or if you want to bypass the logic entirely, disable the v2 delete optimization. This stops StarRocks from injecting the 
$data_sequence_number
column into the plan.
Copy code
sql
-- This variable is specifically for Iceberg v2 delete logic
SET enable_iceberg_v2_delete = false;
_If this variable is not found, try 
SET enable_iceberg_equality_delete = false;
(ensure no typos)._ 2. Disable Specific Optimizer Rewrites Since the standard CSE variable didn't show up in your search, try these alternative names or related "Expression Reuse" variables that often trigger this mapping bug:
Copy code
sql
-- Disable the logic that tries to find identical expressions across CTEs
SET enable_expression_reuse = false;

-- Disable the optimization that reuses filters/predicates
SET enable_predicate_expr_reuse = false;
3. Bypass via 
plan_mode
and 
CTE
Inlining Even if 
cbo_cte_reuse
didn't work, you can force the optimizer to treat CTEs as simple subqueries, which prevents the "Shared Plan" logic that causes the duplicate key error.
Copy code
sql
SET cbo_cte_force_reuse_node_count = 1000; -- Set very high to prevent reuse
SET enable_cte_rewrite = false;
4. The "Query Hint" approach If you cannot change global/session variables for the BI tool, you can often inject these into the query itself via a hint. This is useful for ad-hoc queries:
Copy code
sql
WITH abc AS (
  SELECT /*+ SET_VAR(enable_expression_reuse = false) */ ...
)
SELECT ...
Reference to the Bug This issue is related to StarRocks GitHub Issue #72823 and #75180, where the optimizer failed to handle "Deduplicated commutative expressions" and "synthetic columns" during rewrites. These fixes are being rolled into maintenance releases (e.g., check for 4.1.5 or 4.2.x when available). Recommendations for CDC * Compaction is not just for speed: While you mentioned it's not a blocker, for Iceberg tables receiving hourly CDC, Equality Deletes are the trigger for this bug. Performing a 
REWRITE DATA FILES
procedure on the table reduces the number of equality delete files, which reduces the reliance on 
$data_sequence_number
and often makes this error disappear for that specific data range. Final Check: If none of the above variables are accepted by your cluster, please run 
SELECT version();
and verify if you are on a specific "CelerData" or custom build, as the variable names can occasionally differ in enterprise distributions. References
3 Views
Open in Slack
PreviousNext
Powered by