[BUG] Date range filter on a date field falls back to a per-document script instead of a native range when the field is wrapped in timestamp() / CAST(... AS TIMESTAMP)

[burnthm]

Query Information

PPL Command/Query:

# Filtering a `date`-mapped field where the field is wrapped in timestamp()/CAST(... AS TIMESTAMP).
# This wrapped shape is what clients such as the Grafana OpenSearch data source generate
# automatically for their dashboard time filter.
source=my-logs
| where timestamp(event_time) >= cast('2024-01-15 12:00:00' as timestamp)
    and timestamp(event_time) <= cast('2024-01-15 15:00:00' as timestamp)
| stats count() as c

# The logically-identical BARE-field form behaves correctly (pushes down to a native range):
# source=my-logs
# | where event_time >= '2024-01-15 12:00:00' and event_time <= '2024-01-15 15:00:00'
# | stats count() as c

Expected Result:
The wrapped-field range predicate should push down to a native OpenSearch range query (BKD/points accelerated), exactly like the bare-field form. POST _plugins/_ppl/_explain should show a range query on event_time.

Actual Result:
The predicate is emitted as a per-document ScriptQueryBuilder ("lang": "opensearch_compounded_script") whose decoded body is gte(timestamp(event_time), cast_to_timestamp(...)), i.e. it calls TypeCastOperators.castToTimestamp → LocalDateTime.parse for every scanned document. _explain shows a script filter instead of range. Because the script has no index acceleration, the timestamp is string-parsed per document across all shards, saturating the search thread pool on large indices. The serialized script also embeds a per-request timestamp, so each execution is a unique script that must be recompiled, which can trip the script-compilation-rate circuit breaker and surface as all shards failed.

Dataset Information

Dataset/Schema Type

• OpenTelemetry (OTEL)
• Simple Schema for Observability (SS4O)
• Open Cybersecurity Schema Framework (OCSF)
• Custom (details below) — time-series log records with a date-typed timestamp field

Index Mapping

{
  "mappings": {
    "properties": {
      "event_time":  { "type": "date" },
      "event_type":  { "type": "keyword" },
      "request_id":  { "type": "text" },
      "group_id":    { "type": "keyword" }
    }
  }
}

Sample Data

{
  "event_time": "2024-01-15T13:30:00.000Z",
  "event_type": "type_a",
  "request_id": "req-000042",
  "group_id": "group-42"
}

Bug Description

Issue Summary:
When a PPL/SQL predicate compares a date-mapped field to a timestamp literal and the field is wrapped in a date function (timestamp(<field>) or CAST(<field> AS TIMESTAMP)), the SQL engine does not push the comparison down to a native range query. It falls back to a per-document script filter. The same filter written against the bare field pushes down to range and is orders of magnitude cheaper. Wrapping an already-date/timestamp-typed field in timestamp()/CAST(... AS TIMESTAMP) is a no-op for a range comparison (monotonic, range-preserving), so it should push down identically.

Steps to Reproduce:

1. Create an index with a plain date field (mapping above) and index a few documents (sample above).
2. Run _explain on the bare-field form and confirm it pushes down to range:

   POST _plugins/_ppl/_explain
   { "query": "source=my-logs | where event_time >= '2024-01-15 12:00:00' and event_time <= '2024-01-15 15:00:00' | stats count() as c" }

   → {"range":{"event_time":{...}}}.
3. Run _explain on the wrapped-field form and observe the fallback to a script filter:

   POST _plugins/_ppl/_explain
   { "query": "source=my-logs | where timestamp(event_time) >= cast('2024-01-15 12:00:00' as timestamp) and timestamp(event_time) <= cast('2024-01-15 15:00:00' as timestamp) | stats count() as c" }

   → two script filters ("lang": "opensearch_compounded_script"). Both queries return the same count; only the execution plan differs.

Impact:
On large time-series indices the per-document LocalDateTime.parse runs as a linear scan across all shards, pinning search threads at high CPU and driving allocation/GC pressure; repeated executions can trip the script-compilation-rate breaker (all shards failed). Because common clients (e.g. the Grafana OpenSearch data source) auto-generate the wrapped timestamp(<timeField>) time filter, users hit this without writing it themselves, so the impact is broad for time-series dashboards and alerts backed by PPL/SQL.

Environment Information

OpenSearch Version: 2.19 (reproduced on the released distribution). Reproducible via both PPL and SQL; independent of security settings.

Additional Details:

Root cause

In opensearch/src/main/java/org/opensearch/sql/opensearch/storage/script/filter/lucene/LuceneQuery.java, canSupport() only allows push-down when the left operand is a bare ReferenceExpression:

public boolean canSupport(FunctionExpression func) {
  return (func.getArguments().size() == 2)
          && (func.getArguments().get(0) instanceof ReferenceExpression)
          && (func.getArguments().get(1) instanceof LiteralExpression
              || literalExpressionWrappedByCast(func))
      || isMultiParameterQuery(func);
}

A timestamp(field) / cast(field as timestamp) left operand is a FunctionExpression, so canSupport returns false and FilterQueryBuilder.visitFunction falls back to buildScriptQuery(func).

Proposed fix

Fold a redundant date/time cast on the field side to the underlying field reference so the predicate pushes down to a native range. In LuceneQuery:

/**
 * True if the operand is a date/time cast (or timestamp()/date()/time() builtin) applied to a
 * reference whose field type is already an OpenSearchDateType. Such a wrap is redundant for a
 * range comparison and can be unwrapped so the predicate pushes down instead of scripting.
 */
protected boolean referenceWrappedByRedundantDateCast(Expression arg) {
  if (arg instanceof FunctionExpression) {
    FunctionExpression fn = (FunctionExpression) arg;
    FunctionName name = fn.getFunctionName();
    boolean isDateCast =
        name.equals(BuiltinFunctionName.CAST_TO_TIMESTAMP.getName())
            || name.equals(BuiltinFunctionName.CAST_TO_DATE.getName())
            || name.equals(BuiltinFunctionName.CAST_TO_TIME.getName())
            || name.equals(BuiltinFunctionName.TIMESTAMP.getName())
            || name.equals(BuiltinFunctionName.DATE.getName())
            || name.equals(BuiltinFunctionName.TIME.getName());
    return isDateCast
        && fn.getArguments().size() == 1
        && fn.getArguments().get(0) instanceof ReferenceExpression
        && fn.getArguments().get(0).type() instanceof OpenSearchDateType;
  }
  return false;
}

private ReferenceExpression unwrapReference(Expression arg) {
  if (arg instanceof ReferenceExpression) {
    return (ReferenceExpression) arg;
  }
  return (ReferenceExpression) ((FunctionExpression) arg).getArguments().get(0);
}

canSupport() accepts the wrapped-but-redundant left operand:

public boolean canSupport(FunctionExpression func) {
  return (func.getArguments().size() == 2)
          && (func.getArguments().get(0) instanceof ReferenceExpression
              || referenceWrappedByRedundantDateCast(func.getArguments().get(0)))
          && (func.getArguments().get(1) instanceof LiteralExpression
              || literalExpressionWrappedByCast(func))
      || isMultiParameterQuery(func);
}

build() unwraps before building the range:

public QueryBuilder build(FunctionExpression func) {
  ReferenceExpression ref = unwrapReference(func.getArguments().get(0));
  Expression expr = func.getArguments().get(1);
  ExprValue literalValue =
      expr instanceof LiteralExpression ? expr.valueOf() : cast((FunctionExpression) expr, ref);
  return doBuild(ref.getAttr(), ref.type(), literalValue);
}

The fold is restricted to OpenSearchDateType references, so it only applies where the cast is genuinely redundant and range-preserving. The equivalent normalization (timestamp($dateRef) → $dateRef) should also be added to the Calcite/v3 predicate path.

Validation

Built from source and run as single-node OpenSearch 2.19 containers on the same host (100k docs, event_time mapped as native date, 8 iterations each). Three builds are compared so the behavior change can be traced end to end:

• 2.19 (pre-Support Limit pushdown #3615) — a clean released 2.19 with neither the limit-pushdown change (PR Support Limit pushdown #3615) nor this fold. Included as a sanity check of the pre-upgrade behavior.
• 2.19 + Support Limit pushdown #3615 — adds the limit-pushdown correctness change (PR Support Limit pushdown #3615), no date-cast fold.
• 2.19 + Support Limit pushdown #3615 + fold — adds the date-cast fold proposed here.

_explain for the wrapped-field predicate is a script (opensearch_compounded_script, per-document castToTimestamp) on both the pre-#3615 and the #3615 builds, and a native range on the fold build.

Full numbers across query shapes. DATE = timestamp(event_time) >= cast(… as timestamp) and timestamp(event_time) <= cast(… as timestamp); SEL = a selective term filter (e.g. event_type="type_a" or event_type="type_b"). Each cell is plan · median latency:

Query shape	2.19 (pre-#3615)	2.19 + #3615	2.19 + #3615 + fold
where DATE | head 10000 | where SEL | stats count()	script · 51 ms	script · 228 ms	range · 177 ms
where DATE | where SEL | head 10000 | stats count()	script · 44 ms	script · 46 ms	range · 36 ms
where DATE | where SEL | stats count() (no head)	script · 36 ms	script · 33 ms	range · 25 ms
where DATE | stats count() (pure date)	script · 248 ms	script · 249 ms	range · 99 ms

Each build holds its own independently randomized data, so absolute totals differ slightly: the DATE window holds ~37k docs and the selective result is ~1.5k.

Reading the table:

• The date predicate is a per-document script on both the pre-Support Limit pushdown #3615 and Support Limit pushdown #3615 builds; the fold is what turns it into a native range. The pure-date row — no selective filter to front the script — is the clearest view of the per-document cost: ~248 ms → 99 ms (~2.5×) here, widening with data volume and shard count.
• The head-before shape (… | head 10000 | where SEL | …) is where behavior changed across Support Limit pushdown #3615. Pre-Support Limit pushdown #3615, the trailing selective filter was pushed below the head, so it narrowed the script's candidate set and head-before ran fast (51 ms) and returned the same count as the other shapes (~1473). Support Limit pushdown #3615 correctly stops pushing a filter that follows a head/LIMIT (a limit-then-filter correctness fix), so the selective filter now runs after the limit: head-before returns a truncated-then-filtered count (~407) and the date script is left to run unnarrowed over the whole window (228 ms). This is the regression a user sees after upgrading to a 2.19 patch that includes Support Limit pushdown #3615.
• The fold fixes that regression without undoing Support Limit pushdown #3615's correctness fix. With the date predicate pushed down to a native range, head-before drops to 177 ms and every shape uses range; results match the Support Limit pushdown #3615 build.
• head placed after the filters (head-after / no-head) is unaffected across all three builds — the selective filter fronts the script there, so those shapes are already cheap and stay cheap; they simply move from script to range with the fold.

Screenshots

Not applicable — the issue is fully reproducible from the CLI and is demonstrated by the _explain plan output shown under Actual Result and Steps to Reproduce (native range for the bare field vs opensearch_compounded_script for the wrapped field).

[burnthm]

Fix for v3 added to the same v2 PR; commit: 24d4949

---

Also reproduces on the Calcite (v3) engine — 3.7.0 numbers

The original report was measured on the v2 engine. Since 3.x runs PPL through Calcite by default, I re-tested there: the bug reproduces on the v3 path too, in a different class (PredicateAnalyzer rather than LuceneQuery), so it needs its own fix. Both are now in #5681.

Confirmed on a stock 3.7.0 node — the wrapped predicate pushes down as SCRIPT->SEARCH(...) with a "script" query (langType: calcite), while the bare-field form pushes down as a native range.

Performance — stock 3.7.0 vs patched, identical dataset

Single node, 100k docs, eventDateTime mapped as native date, median of 5 runs. Both nodes hold the same data (fixed RNG seed and insertion order; event_action histogram identical on both). DATE = timestamp(eventDateTime) >= cast(… as timestamp) and timestamp(eventDateTime) <= cast(… as timestamp); SEL = a selective term filter.

Query shape	Unpatched plan	Unpatched median	Patched plan	Patched median	Speedup
where DATE | stats count() (pure date)	script	628 ms	range	20 ms	31×
where CAST-DATE | stats count() (pure date, CAST wrap)	script	550 ms	range	18 ms	31×
where BARE-DATE | stats count() (bare field, control)	range	25 ms	range	18 ms	—
where DATE | where SEL | stats count() (no head)	script	53 ms	range	19 ms	2.8×
where DATE | where SEL | head 10000 | stats count()	script	122 ms	range	58 ms	2.1×
where DATE | head 10000 | where SEL | stats count()	script	396 ms	range	218 ms	1.8×

Result counts are unchanged by the fix in every shape (37507 for the pure-date shapes, 1488 with the selective filter, 385 for the head-before-where shape) — only the execution plan changes.

The clearest read on the cost of the wrap is the third row. On the unpatched node the bare-field form of the same filter runs in 25 ms while the wrapped form takes 628 ms — a ~25× penalty caused purely by wrapping the field. After the fix the wrapped form runs in 18–20 ms, i.e. back to bare-field parity. The gap is far larger here than the ~2.5× I originally measured on 2.19, because the pure-date shape has no selective filter fronting the script and the Calcite compounded script is not cheaper per document.

As before, the rows with a selective filter look milder only because that filter pushes down natively and leads the Lucene conjunction, so the per-document script runs on a small matching subset (~1.5k of 100k). The plan is still a per-document script that degrades as that subset grows.

Note on the date() / time() variants

Worth recording for anyone else looking at this: only a cast that preserves the type is safe to fold. date(<timestamp field>) truncates the time component and time(<timestamp field>) extracts the time of day, so neither is a no-op and neither can become a range query. Concretely, with docs at 2024-01-15 08:00 and 2024-01-15 23:00, date(ts) <= '2024-01-15' correctly matches 2 rows but would match 0 if folded to ts <= '2024-01-15'. The fix in #5681 folds only when the cast target equals the field's own type, and keeps these on the script path.