[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)

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-#3615) — a clean released 2.19 with neither the limit-pushdown change (PR #3615) nor this fold. Included as a sanity check of the pre-upgrade behavior.
• 2.19 + #3615 — adds the limit-pushdown correctness change (PR #3615), no date-cast fold.
• 2.19 + #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-#3615 and #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 #3615. Pre-#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). #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 #3615.
• The fold fixes that regression without undoing #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 #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).

Start in opensearch/src/main/java/org/opensearch/sql/opensearch/storage/script/filter/lucene/LuceneQuery.java, reading canSupport() and build(), then trace the Calcite/v3 predicate path. Reproduce both queries with _plugins/_ppl/_explain; done means the wrapped date-field predicate produces a native range on event_time rather than an opensearch_compounded_script filter.