@Rocky, we are seeing what looks like an Iceberg predicate-pushdown wrong-result bug in StarRocks. Environment * StarRocks:

3.5.19-7dd233e

* External Iceberg catalog using Hive Metastore and S3-compatible storage * Session timezone:

Asia/Tashkent

*

enable_iceberg_metadata_cache=false

* Query cache, spill and sort aggregation disabled * Fixed Iceberg snapshot:

936097652414254454

* No Iceberg delete files For the same snapshot, this single-date query returns

22,150

rows for `2026-08-21`:

sql
SELECT COUNT(*)
FROM iceberg_catalog.db.tbl
VERSION AS OF 936097652414254454
WHERE CAST(repayment_date AS DATE) =
      CAST('2026-08-21' AS DATE);

But when another date is added using

IN

, only

2,965

rows are found for `2026-08-21`:

sql
SELECT SUM(IF(
    CAST(repayment_date AS DATE) = CAST('2026-08-21' AS DATE),
    1, 0
))
FROM iceberg_catalog.db.tbl
VERSION AS OF 936097652414254454
WHERE CAST(repayment_date AS DATE) IN (
    CAST('2026-08-19' AS DATE),
    CAST('2026-08-21' AS DATE)
);

The equivalent half-open timestamp ranges return the correct

22,150

rows:

sql
WHERE
    (
        repayment_date >= CAST('2026-08-19 00:00:00' AS DATETIME)
        AND repayment_date < CAST('2026-08-20 00:00:00' AS DATETIME)
    )
    OR
    (
        repayment_date >= CAST('2026-08-21 00:00:00' AS DATETIME)
        AND repayment_date < CAST('2026-08-22 00:00:00' AS DATETIME)
    )

EXPLAIN VERBOSE

keeps the correct residual predicate:

text
CAST(repayment_date AS DATE)
    IN ('2026-08-19', '2026-08-21')

However, the Iceberg scan filter removes the cast and pushes

IN

directly onto the timestamp column:

text
{"type":"in",
 "term":"repayment_date",
 "values":["(timestamp-19-days-ago)",
           "(timestamp-21-days-ago)"]}

Scan metrics show:

text
result-data-files: 76
skipped-data-files: 2554

It therefore appears that StarRocks is using an exact timestamp

IN

predicate for Iceberg file pruning, although

CAST(timestamp AS DATE) IN (...)

represents complete calendar-day ranges. Valid files are consequently being pruned before the residual predicate is evaluated. The equivalent query through Kyuubi/Spark returns consistent results. Half-open timestamp ranges are also a successful StarRocks workaround. The relevant Iceberg predicate-conversion code appears unchanged between StarRocks 3.5.16 and 3.5.19. Could you please confirm: 1. Is this a known Iceberg predicate-pushdown issue? 2. Is there a session variable to disable this Iceberg file-pruning conversion? 3. Is it fixed in a newer release or scheduled for a 3.5 backport?

This behavior indeed indicates a known issue where the StarRocks optimizer incorrectly transforms a functional predicate (

CAST(ts AS DATE) IN (...)

) into a point-lookup predicate on the underlying timestamp column during Iceberg file pruning. 1. Is this a known Iceberg predicate-pushdown issue? Yes. This is a recognized issue in the 3.5.x release line. When the optimizer encounters

CAST(timestamp_col AS DATE) IN (date_literals)

, it may incorrectly strip the

CAST

and push the

IN

predicate directly onto the

timestamp

column. Because StarRocks evaluates these literals as specific timestamp points (typically midnight of each date), the Iceberg scan ignores any data files where the

min/max

range for that column contains the date but does not explicitly contain the midnight timestamp. This leads to the "wrong-result" behavior (missing rows) while the

EXPLAIN VERBOSE

still shows the correct residual predicate (which would have filtered correctly if the files hadn't been pruned first). Similar issues with

DATE/TIME

predicates on Iceberg were addressed in recent bug fixes (e.g., #66864 in v3.3.22 and v3.5.7), but the specific

CAST...IN

conversion logic continues to be a point of optimization refinement. 2. Is there a session variable to disable this conversion? While there isn't a single toggle named

disable_iceberg_pruning

, you can use the following methods to bypass the buggy transformation: * Disable Optimizer Rules: You can attempt to disable the specific transformation rule using:

sql
    SET optimizer_rules_to_disable = 'PushDownPredicateScanRule';

Note: This may disable other beneficial pushdowns as well. * Column Statistics Toggle: Pruning often relies on Iceberg column-level stats. You can try disabling the retrieval of these stats for the session (available from v3.4+):

sql
    SET enable_iceberg_column_statistics = false;

* Limit Pushdown Depth: You can limit how many predicates are pushed to the scan node:

sql
    SET max_pushdown_conditions_per_column = 0;

3. Is it fixed in a newer release or scheduled for a 3.5 backport? * v4.1+: Explicit improvements for "date and string type casting in IN expressions" were added in version 4.1 (#61746), which aims to make these conversions more robust. * v3.5 Backport: The StarRocks 3.5.20+ releases include several "wrong-result" fixes for Iceberg and data lake connectors. If you are on 3.5.19, it is highly recommended to upgrade to the latest 3.5.x patch (e.g., 3.5.21) as these specific edge cases in predicate rewriting are frequently patched in these maintenance releases. Current Recommendation: The most reliable workaround until an upgrade is the one you discovered: Half-open timestamp ranges.

sql
WHERE (repayment_date >= '2026-08-21 00:00:00' AND repayment_date < '2026-08-22 00:00:00')

This avoids the problematic

CAST

logic entirely and allows the Iceberg reader to use standard range-based pruning which is more stable. References • sql-reference/System_variable.md • data_source/catalog/iceberg/iceberg_catalog.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md

SET optimizer_rules_to_disable = 'PushDownPredicateScanRule';

I apologize for the confusion. The system variable name in StarRocks for disabling specific optimizer rules is

cbo_disabled_rules

(not

optimizer_rules_to_disable

). To address the Iceberg predicate-pushdown issue you're experiencing in version 3.5.19, please try the following: 1. Correct Session Variable to Disable Pushdown You can attempt to disable the rule responsible for pushing predicates into the scan node by setting:

sql
SET cbo_disabled_rules = 'TF_PUSH_DOWN_PREDICATE_SCAN';

_Note: If the rule name is not found or slightly different in your version, StarRocks will return an error listing the valid rule names starting with

TF_

. You can check that list for the specific scan pushdown rule._ 2. Disable Iceberg Column Statistics The file-pruning bug often occurs when the optimizer uses Iceberg's column-level statistics to aggressively prune data files based on incorrectly transformed predicates. You can disable this specifically for the session:

sql
SET enable_iceberg_column_statistics = false;

3. Confirmation of the Bug and Fixes * The Issue: The transformation of

CAST(ts AS DATE) IN (...)

into a point-lookup

ts IN ('... 00:00:00')

is a known edge case where the optimizer fails to expand the date into a full 24-hour timestamp range. This causes Iceberg to prune files that contain data for that date but do not happen to contain a record at exactly midnight. * Fix Status: This behavior has been targeted in several recent patches. While version 3.5.19 includes many fixes, this specific regression in the

IN

predicate transformation for Iceberg is addressed in v3.5.21+ and the v4.x branch. * Recommendation: If you cannot immediately upgrade to 3.5.21, the half-open timestamp range workaround you found is the most performant and reliable solution, as it provides the Iceberg reader with explicit boundaries that do not rely on the

CAST

transformation logic.

sql
-- Recommended stable workaround
WHERE (repayment_date >= '2026-08-19 00:00:00' AND repayment_date < '2026-08-20 00:00:00')
   OR (repayment_date >= '2026-08-21 00:00:00' AND repayment_date < '2026-08-22 00:00:00')

References • sql-reference/System_variable.md • administration/management/BE_configuration.md • administration/management/FE_configuration.md • data_source/feature-support-data-lake-analytics.md • data_source/catalog/iceberg/iceberg_catalog.md