@Rocky I run StarRocks 4.0.0. I noticed that in sometimes some filters in WHERE close are not applied and I have inconsistent results. like 1st query returns 175 rows ( whichc is correct), second can return 178 with rows that supposed to be filtered. What can be the reason?

In StarRocks 4.0.0, encountering inconsistent query results where filters in the

WHERE

clause appear to be ignored is often tied to specific optimization features or known bugs in the initial 4.0.0 release. Since version 4.0 is a major release with significant architectural changes (like the introduction of unified metadata and cache improvements), several issues were identified and fixed in subsequent patches (v4.0.1, v4.0.2, and v4.0.3). Here are the most likely reasons for this behavior and how to troubleshoot them: 1. Runtime Filter Bugs (Most Likely) Several bugs related to Runtime Filters were fixed in version 4.0.3 that could cause incorrect result sets or filters to be bypassed: * Nested OR Predicates: A bug where pushing down runtime bitset filters into nested

OR

predicates caused incorrect results. * Bloom Filter Checks: An incorrect

can_use_bf

check when merging runtime filters could lead to filters being misapplied. * Troubleshooting: Try disabling runtime filters for your session to see if the results become consistent:

sql
    SET enable_runtime_filter = false;

2. MetaScan Rewrite Issues The

enable_rewrite_simple_agg_to_meta_scan

optimization can sometimes cause incorrect row counts or results if there are issues with the cached tablet metadata. * Troubleshooting: Disable this optimization to verify if it's the culprit:

sql
    SET enable_rewrite_simple_agg_to_meta_scan = false;

Note: In v4.0.2, it was recommended to disable this before downgrading due to potential metadata incompatibility. 3. Inconsistent Tablet Metadata Cache Version 4.0.0 introduced a new caching mechanism for tablet metadata. A bug was found where the

latest_cached_tablet_metadata

could cause some data versions to be incorrectly skipped during a query, or conversely, stale data to be read if the cache didn't invalidate correctly. * Fixed in: v4.0.3 (specifically PR #66558 and #66575). 4. Query Cache or Result Cache If you have the Query Cache enabled, it might be returning a stale or "semantically equivalent" but practically different result if the multi-version caching mechanism fails to account for a recent delete or update. * Troubleshooting: Disable the query cache for your session:

sql
    SET enable_query_cache = false;

5. Primary Key Table Consistency If you are using a Primary Key table, StarRocks ensures eventual consistency across replicas. If queries are hitting different replicas during a heavy load or right after an update, you might see slight variations. * Troubleshooting: Use the

SYNC

command before your query to ensure all updates are synchronized, or check if the table is currently undergoing significant compaction or schema changes. Recommended Next Steps 1. Check for specific patterns: Does this happen only on tables with complex types (ARRAY/MAP/STRUCT) or specifically when using

DISTINCT

or

GROUP BY

? (A bug in 4.0.0 related to

DISTINCT

and

GROUP BY

returning incorrect results was also fixed). 2. Upgrade: If possible, upgrade to v4.0.3 or later. The 4.0.3 patch specifically addresses several "incorrect result" bugs that match your description. 3. Compare Plans: Run

EXPLAIN

on the query when it returns 175 rows and when it returns 178 rows. If the physical plans differ (e.g., one uses a Runtime Filter and the other doesn't), that confirms an optimization bug. References • release_notes/release-4.0.md

@Rocky I see only those params

enable_global_runtime_filter
enable_multicolumn_global_runtime_filter
enable_topn_runtime_filter
runtime_filter_on_exchange_node

what I need to tweak?

To troubleshoot the inconsistent results in StarRocks 4.0.0 (where some queries return 175 rows and others 178), you should adjust the following session variables one by one. This will help identify if an optimization or a known bug in the 4.0.0 release is causing filters to be bypassed. 1. Disable Global & Multi-column Runtime Filters The parameters you found are the correct ones to adjust. Start by disabling the global and specialized runtime filters, which are the most common causes of inconsistent results due to push-down bugs:

sql
SET enable_global_runtime_filter = false;
SET enable_multicolumn_global_runtime_filter = false;
SET enable_topn_runtime_filter = false;

2. Disable Metadata Rewrites (Highly Recommended for 4.0.0) Version 4.0.0 introduced an optimization that rewrites simple aggregations to use metadata scans. This has been known to cause issues with row counts and was a focus of fixes in later 4.0.x patches.

sql
SET enable_rewrite_simple_agg_to_meta_scan = false;

3. Disable Low Cardinality & Join Optimizations If the query involves

GROUP BY

or complex `JOIN`s, these optimizations might be incorrectly pruning or aggregating data:

sql
SET cbo_enable_low_cardinality_optimize = false;
SET disable_join_reorder = true;

4. Check the Query Cache If the inconsistent results happen on the same query executed twice, the Query Cache might be returning a stale result set:

sql
SET enable_query_cache = false;

Why this happens in v4.0.0 StarRocks 4.0.0 is the initial major release of the 4.0 series. Several critical bugs related to incorrect result sets (especially with Runtime Filters and Metadata Scans) were identified and fixed in subsequent patches (v4.0.3 and later). Recommended Action: 1. Compare Plans: Run

EXPLAIN

on the "correct" query and the "incorrect" query. If you see differences in the

RuntimeFilter

or

Scan

nodes, one of the toggles above will likely fix it. 2. Upgrade: It is highly recommended to upgrade to at least v4.0.3 or the latest v4.0.x patch, as these versions contain specific fixes for filter-bypass bugs and metadata inconsistency issues found in 4.0.0. References • sql-reference/System_variable.md

@Rocky what is potential performance degradation if I disable

enable_global_runtime_filter

?

Disabling

enable_global_runtime_filter

(GRF) can lead to significant performance degradation, particularly for Shuffle Joins on large datasets. Here is the breakdown of what happens when it is turned off: 1. Increased Network Traffic (Shuffle Stage) Global Runtime Filters are designed to filter data before it is shuffled across the network. * With GRF: StarRocks creates a filter (like a Bloom Filter) from the "build" side of a join and sends it to the "probe" side. The probe side uses this to drop rows that won't match the join before sending them over the network. * Without GRF: Every row from the probe table is sent across the network to the join nodes. For large tables, this dramatically increases the amount of data moved between BE nodes, often becoming a network bottleneck. 2. Higher CPU and Memory Overhead Without the filter, the Join Operator must receive and process every single row from the source. * The CPU has to perform many more hash calculations and comparisons for rows that would have been discarded early. * The memory pressure increases as more data is buffered in the exchange and join buffers. 3. Increased Disk I/O (Scan Stage) If the Global Runtime Filter can be pushed all the way down to the Scan level: * With GRF: The storage engine can skip reading entire data blocks or rows that don't match the filter. * Without GRF: The system must read all data from the disk (or cache) and pass it up the execution pipeline, only for it to be discarded later at the join stage. When will you notice it most? * Large Scale Joins: If you are joining two large tables (e.g., Fact to Fact) using a Shuffle Join. * High Selectivity Joins: If the join condition filters out 90%+ of the data, the performance hit from disabling GRF will be massive (queries could go from seconds to minutes). * Star Schema Queries: Queries joining a large fact table to multiple filtered dimension tables rely heavily on GRF to keep the "fact" scan lean. Summary for your 4.0.0 situation While disabling

enable_global_runtime_filter

is a valid troubleshooting step to confirm if a bug in the 4.0.0 filter logic is causing your inconsistent results, it is not recommended as a permanent fix due to the performance costs listed above. If disabling it fixes your row count discrepancy, it confirms you are hitting a known bug in the 4.0.0 optimizer/executor. You should look to upgrade to v4.0.3+ where these logic errors were addressed, allowing you to keep the performance benefits of GRF safely enabled. References • sql-reference/System_variable.md • best_practices/query_tuning/query_profile_overview.md • knowledge/usage/best-practice.md

@Rocky what is the best StarRocks version to upgrade to from 4.0.0 in production at this moment?

For a production environment running StarRocks 4.0.0, the best upgrade path depends on your tolerance for version jumps versus the need for immediate stability. 1. The Safest Immediate Choice: Latest v4.0.x Patch If you want to fix the inconsistent results with the least amount of change to your infrastructure, you should upgrade to the latest patch of the 4.0 series (v4.0.14 or newer). * Why: Version 4.0.0 was the initial major release and contained several "wrong result" bugs related to Runtime Filters, metadata caching, and the cost-based optimizer (CBO). These were progressively fixed in patches v4.0.3 through v4.0.14. * Key Fixes: Patch releases in the 4.0 series specifically addressed logic errors where filters were skipped during query execution and issues with the new tablet metadata cache. 2. The Current "Production" Standard: v4.1.x If you are open to a minor version upgrade, v4.1.3 (or the latest v4.1.x patch) is currently marketed as the "Built for Production" version of the 4.0-lineage. * Why: Version 4.1 includes all the stability fixes from the 4.0 patches plus significant hardening of the new features introduced in 4.0 (like native JSON and Iceberg support). * Note: If you move to 4.1, be aware that you can only downgrade back to v4.0.6 or later due to changes in metadata layout. 3. The Conservative "LTS" Option: v3.5.x If your primary concern is long-term stability over the new features of 4.0 (like native JSON columnar performance or 60% faster TPC-DS), the v3.5.x series (specifically v3.5.20+) is widely considered the current stable LTS (Long Term Support) branch for production workloads. * Warning: Downgrading from 4.0.0 to 3.5 is possible but requires you to land on v3.5.2 or later to avoid metadata incompatibility. Summary Recommendation * Immediate Fix: Upgrade to v4.0.14 (or latest 4.0 patch). This is the most direct way to resolve your "inconsistent results" bug without a significant architectural jump. * New Deployments: Use v4.1.x for the best balance of modern performance and production readiness. How to verify the fix: After upgrading, enable your runtime filters again (

SET enable_global_runtime_filter = true

) and run your query multiple times. The row counts should now be consistent (e.g., always 175).

@Rocky Can I rollback from 4.0.14 back to 4.0.0 if needed?

Yes, you can rollback from v4.0.14 to v4.0.0, but there is a specific safety step you must perform to prevent metadata errors or FE (Frontend) crashes. 1. Mandatory Pre-Rollback Command Before you shut down any nodes to downgrade, you must execute the following SQL statement. This disables a metadata-based optimization that exists in v4.0.14 but is not compatible with the 4.0.0/4.0.1 logic:

sql
SET GLOBAL enable_rewrite_simple_agg_to_meta_scan = false;

Note: You can re-enable this after the downgrade is complete if desired, though v4.0.0 has known bugs with this feature. 2. Create a Metadata Snapshot Always create a fresh image of your metadata before a downgrade to ensure you have a "point-of-no-return" recovery file:

sql
ALTER SYSTEM CREATE IMAGE;

Verify the image is synchronized to all FEs by checking the

fe.log

for the message:

"push image.* totally xx nodes, push successful xx nodes"

. 3. Downgrade Order StarRocks downgrades must be performed in the reverse order of upgrades: 1. FE Follower nodes: Stop the process, replace binaries, and restart. 2. FE Leader node: Stop the process, replace binaries, and restart. 3. BE (Backends) / CN (Compute Nodes): These can typically be downgraded one by one after the FEs are stable. Why this is possible StarRocks explicitly supports patch version downgrades (e.g., v4.0.x to v4.0.y). Unlike minor version jumps (like 4.1 to 4.0), patch versions within the same series share the same core metadata layout, provided you disable the specific behavioral toggles mentioned in the release notes (like the

meta_scan

toggle above). Summary Warning: While you can roll back, remember that v4.0.0 is significantly less stable than v4.0.14. If you encounter the "inconsistent results" issue in v4.0.14, it is highly likely a configuration or data issue rather than the fixed bugs that plagued v4.0.0. References • deployment/downgrade.md • release_notes/release-4.0.md References • release_notes/release-4.0.md • deployment/downgrade.md