Skip to content

[BUG] PPL bin command fails with param bins on time-related fields #4578

Open
Open
[BUG] PPL bin command fails with param bins on time-related fields#4578
Assignees
ahkcs
Labels
PPLPiped processing languageerror-experienceIssues related to how we handle failure cases in the plugin.feature
@yuancu

Description

@yuancu
yuancu
opened on Oct 16, 2025

What is the bug?

When performing bin command on a timestamp field with bins parameter specified, it fails to prepare the plan.

How can one reproduce the bug?

  1. disable pushdown
PUT _cluster/settings
{
   "persistent":{
      "plugins.calcite.enabled": true,
      "plugins.calcite.fallback.allowed": false,
      "plugins.calcite.pushdown.enabled": true
   }
}
  1. execute the query
POST /_plugins/_ppl
{
  "query" : "source=time_test |  bin @timestamp bins=2"
}

The time_test index can be substituted with any index that contains a @timestamp field. It throws the following error:

{
  "error": {
    "reason": "There was internal problem at backend",
    "details": "java.sql.SQLException: Error while preparing plan [LogicalSystemLimit(fetch=[10000], type=[QUERY_SIZE_LIMIT])\n  LogicalProject(category=[$1], value=[$2], timestamp=[$3], @timestamp=[WIDTH_BUCKET($0, 2, -(MAX($0) OVER (), MIN($0) OVER ()), MAX($0) OVER ())])\n    CalciteLogicalIndexScan(table=[[OpenSearch, time_test]])\n]",
    "type": "RuntimeException"
  },
  "status": 500
}
complete stacktrace 
[2025-10-16T10:14:29,805][ERROR][o.o.s.p.r.RestPPLQueryAction] [7cf34de73b85] Error happened during query handling
java.lang.RuntimeException: java.sql.SQLException: Error while preparing plan [LogicalSystemLimit(fetch=[10000], type=[QUERY_SIZE_LIMIT])
  LogicalProject(category=[$1], value=[$2], timestamp=[$3], @timestamp=[WIDTH_BUCKET($0, 2, -(MAX($0) OVER (), MIN($0) OVER ()), MAX($0) OVER ())])
    CalciteLogicalIndexScan(table=[[OpenSearch, time_test]])
]
        at org.apache.calcite.util.Util.throwAsRuntime(Util.java:956) ~[?:?]
        at org.opensearch.sql.calcite.utils.CalciteToolsHelper$OpenSearchRelRunners.run(CalciteToolsHelper.java:358) ~[?:?]
        at org.opensearch.sql.opensearch.executor.OpenSearchExecutionEngine.lambda$execute$6(OpenSearchExecutionEngine.java:206) ~[?:?]
        at java.base/java.security.AccessController.doPrivileged(AccessController.java:319) ~[?:?]
        at org.opensearch.sql.opensearch.executor.OpenSearchExecutionEngine.lambda$execute$7(OpenSearchExecutionEngine.java:203) ~[?:?]
        at org.opensearch.sql.opensearch.client.OpenSearchNodeClient.schedule(OpenSearchNodeClient.java:222) ~[?:?]
        at org.opensearch.sql.opensearch.executor.OpenSearchExecutionEngine.execute(OpenSearchExecutionEngine.java:201) ~[?:?]
        at org.opensearch.sql.executor.QueryService.lambda$executeWithCalcite$0(QueryService.java:105) ~[?:?]
        at java.base/java.security.AccessController.doPrivileged(AccessController.java:319) ~[?:?]
        at org.opensearch.sql.executor.QueryService.lambda$executeWithCalcite$1(QueryService.java:96) ~[?:?]
        at org.opensearch.sql.calcite.CalcitePlanContext.run(CalcitePlanContext.java:121) ~[?:?]
        at org.opensearch.sql.executor.QueryService.executeWithCalcite(QueryService.java:93) ~[?:?]
        at org.opensearch.sql.executor.QueryService.execute(QueryService.java:70) ~[?:?]
        at org.opensearch.sql.executor.execution.QueryPlan.execute(QueryPlan.java:66) ~[?:?]
        at org.opensearch.sql.opensearch.executor.OpenSearchQueryManager.lambda$submit$0(OpenSearchQueryManager.java:28) ~[?:?]
        at org.opensearch.sql.opensearch.executor.OpenSearchQueryManager.lambda$withCurrentContext$1(OpenSearchQueryManager.java:42) ~[?:?]
        at org.opensearch.common.util.concurrent.ThreadContext$ContextPreservingRunnable.run(ThreadContext.java:916) ~[opensearch-3.3.0-SNAPSHOT.jar:3.3.0-SNAPSHOT]
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144) ~[?:?]
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) ~[?:?]
        at java.base/java.lang.Thread.run(Thread.java:1575) [?:?]
Caused by: java.sql.SQLException: Error while preparing plan [LogicalSystemLimit(fetch=[10000], type=[QUERY_SIZE_LIMIT])
  LogicalProject(category=[$1], value=[$2], timestamp=[$3], @timestamp=[WIDTH_BUCKET($0, 2, -(MAX($0) OVER (), MIN($0) OVER ()), MAX($0) OVER ())])
    CalciteLogicalIndexScan(table=[[OpenSearch, time_test]])
]
        at org.apache.calcite.avatica.Helper.createException(Helper.java:56) ~[?:?]
        at org.apache.calcite.avatica.Helper.createException(Helper.java:41) ~[?:?]
        at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:227) ~[?:?]
        at org.apache.calcite.jdbc.CalciteConnectionImpl.lambda$unwrap$0(CalciteConnectionImpl.java:187) ~[?:?]
        at org.opensearch.sql.calcite.utils.CalciteToolsHelper$OpenSearchRelRunners.run(CalciteToolsHelper.java:356) ~[?:?]
        ... 18 more
Caused by: java.lang.IllegalStateException: Unable to implement EnumerableLimit(fetch=[10000]): rowcount = 10000.0, cumulative cost = {130000.0 rows, 310000.0 cpu, 0.0 io}, id = 1145
  EnumerableCalc(expr#0..5=[{inputs}], expr#6=[2], expr#7=[-($t4, $t5)], expr#8=[WIDTH_BUCKET($t0, $t6, $t7, $t4)], category=[$t1], value=[$t2], timestamp=[$t3], @timestamp=[$t8]): rowcount = 10000.0, cumulative cost = {120000.0 rows, 300000.0 cpu, 0.0 io}, id = 1147
    EnumerableWindow(window#0=[window(aggs [MAX($0), MIN($0)])]): rowcount = 10000.0, cumulative cost = {110000.0 rows, 170000.0 cpu, 0.0 io}, id = 1141
      EnumerableCalc(expr#0..9=[{inputs}], proj#0..3=[{exprs}]): rowcount = 10000.0, cumulative cost = {100000.0 rows, 140000.0 cpu, 0.0 io}, id = 1149
        CalciteEnumerableIndexScan(table=[[OpenSearch, time_test]]): rowcount = 10000.0, cumulative cost = {90000.0 rows, 0.0 cpu, 0.0 io}, id = 1108

        at org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:117) ~[?:?]
        at org.apache.calcite.adapter.enumerable.EnumerableInterpretable.toBindable(EnumerableInterpretable.java:113) ~[?:?]
        at org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.implement(CalcitePrepareImpl.java:1190) ~[?:?]
        at org.opensearch.sql.calcite.utils.CalciteToolsHelper$OpenSearchCalcitePreparingStmt.implement(CalciteToolsHelper.java:328) ~[?:?]
        at org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepare_(CalcitePrepareImpl.java:1087) ~[?:?]
        at org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepareRel(CalcitePrepareImpl.java:1042) ~[?:?]
        at org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:694) ~[?:?]
        at org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:524) ~[?:?]
        at org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:492) ~[?:?]
        at org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:237) ~[?:?]
        at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:217) ~[?:?]
        at org.apache.calcite.jdbc.CalciteConnectionImpl.lambda$unwrap$0(CalciteConnectionImpl.java:187) ~[?:?]
        at org.opensearch.sql.calcite.utils.CalciteToolsHelper$OpenSearchRelRunners.run(CalciteToolsHelper.java:356) ~[?:?]
        ... 18 more
        Suppressed: java.lang.RuntimeException: while resolving method 'minus[class java.lang.String, class java.lang.String]' in class class org.apache.calcite.runtime.SqlFunctions
                at org.apache.calcite.linq4j.tree.Types.lookupMethod(Types.java:297) ~[?:?]
                at org.apache.calcite.linq4j.tree.Expressions.call(Expressions.java:453) ~[?:?]
                at org.apache.calcite.adapter.enumerable.RexImpTable$BinaryImplementor.implementSafe(RexImpTable.java:3197) ~[?:?]
                at org.apache.calcite.adapter.enumerable.RexImpTable$AbstractRexCallImplementor.genValueStatement(RexImpTable.java:4151) ~[?:?]
                at org.apache.calcite.adapter.enumerable.RexImpTable$AbstractRexCallImplementor.implement(RexImpTable.java:4113) ~[?:?]
                at org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitCall(RexToLixTranslator.java:1343) ~[?:?]
                at org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitCall(RexToLixTranslator.java:105) ~[?:?]
                at org.apache.calcite.rex.RexCall.accept(RexCall.java:208) ~[?:?]
                at org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitLocalRef(RexToLixTranslator.java:1219) ~[?:?]
                at org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitLocalRef(RexToLixTranslator.java:105) ~[?:?]
                at org.apache.calcite.rex.RexLocalRef.accept(RexLocalRef.java:78) ~[?:?]
                at org.apache.calcite.adapter.enumerable.RexToLixTranslator.implementCallOperand(RexToLixTranslator.java:1352) ~[?:?]
                at org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitCall(RexToLixTranslator.java:1339) ~[?:?]
                at org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitCall(RexToLixTranslator.java:105) ~[?:?]
                at org.apache.calcite.rex.RexCall.accept(RexCall.java:208) ~[?:?]
                at org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitLocalRef(RexToLixTranslator.java:1219) ~[?:?]
                at org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitLocalRef(RexToLixTranslator.java:105) ~[?:?]
                at org.apache.calcite.rex.RexLocalRef.accept(RexLocalRef.java:78) ~[?:?]
                at org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:257) ~[?:?]
                at org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:251) ~[?:?]
                at org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateList(RexToLixTranslator.java:1051) ~[?:?]
                at org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateProjects(RexToLixTranslator.java:205) ~[?:?]
                at org.apache.calcite.adapter.enumerable.EnumerableCalc.implement(EnumerableCalc.java:192) ~[?:?]
                at org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:107) ~[?:?]
                at org.apache.calcite.adapter.enumerable.EnumerableLimit.implement(EnumerableLimit.java:98) ~[?:?]
                at org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:114) ~[?:?]
                at org.apache.calcite.adapter.enumerable.EnumerableInterpretable.toBindable(EnumerableInterpretable.java:113) ~[?:?]
                at org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.implement(CalcitePrepareImpl.java:1190) ~[?:?]
                at org.opensearch.sql.calcite.utils.CalciteToolsHelper$OpenSearchCalcitePreparingStmt.implement(CalciteToolsHelper.java:328) ~[?:?]
                at org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepare_(CalcitePrepareImpl.java:1087) ~[?:?]
                at org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepareRel(CalcitePrepareImpl.java:1042) ~[?:?]
                at org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:694) ~[?:?]
                at org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:524) ~[?:?]
                at org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:492) ~[?:?]
                at org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:237) ~[?:?]
                at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:217) ~[?:?]
                at org.apache.calcite.jdbc.CalciteConnectionImpl.lambda$unwrap$0(CalciteConnectionImpl.java:187) ~[?:?]
                at org.opensearch.sql.calcite.utils.CalciteToolsHelper$OpenSearchRelRunners.run(CalciteToolsHelper.java:356) ~[?:?]
                at org.opensearch.sql.opensearch.executor.OpenSearchExecutionEngine.lambda$execute$6(OpenSearchExecutionEngine.java:206) ~[?:?]
                at java.base/java.security.AccessController.doPrivileged(AccessController.java:319) ~[?:?]
                at org.opensearch.sql.opensearch.executor.OpenSearchExecutionEngine.lambda$execute$7(OpenSearchExecutionEngine.java:203) ~[?:?]
                at org.opensearch.sql.opensearch.client.OpenSearchNodeClient.schedule(OpenSearchNodeClient.java:222) ~[?:?]
                at org.opensearch.sql.opensearch.executor.OpenSearchExecutionEngine.execute(OpenSearchExecutionEngine.java:201) ~[?:?]
                at org.opensearch.sql.executor.QueryService.lambda$executeWithCalcite$0(QueryService.java:105) ~[?:?]
                at java.base/java.security.AccessController.doPrivileged(AccessController.java:319) ~[?:?]
                at org.opensearch.sql.executor.QueryService.lambda$executeWithCalcite$1(QueryService.java:96) ~[?:?]
                at org.opensearch.sql.calcite.CalcitePlanContext.run(CalcitePlanContext.java:121) ~[?:?]
                at org.opensearch.sql.executor.QueryService.executeWithCalcite(QueryService.java:93) ~[?:?]
                at org.opensearch.sql.executor.QueryService.execute(QueryService.java:70) ~[?:?]
                at org.opensearch.sql.executor.execution.QueryPlan.execute(QueryPlan.java:66) ~[?:?]
                at org.opensearch.sql.opensearch.executor.OpenSearchQueryManager.lambda$submit$0(OpenSearchQueryManager.java:28) ~[?:?]
                at org.opensearch.sql.opensearch.executor.OpenSearchQueryManager.lambda$withCurrentContext$1(OpenSearchQueryManager.java:42) ~[?:?]
                at org.opensearch.common.util.concurrent.ThreadContext$ContextPreservingRunnable.run(ThreadContext.java:916) ~[opensearch-3.3.0-SNAPSHOT.jar:3.3.0-SNAPSHOT]
                at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144) ~[?:?]
                at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) ~[?:?]
                at java.base/java.lang.Thread.run(Thread.java:1575) [?:?]
        Caused by: java.lang.NoSuchMethodException: org.apache.calcite.runtime.SqlFunctions.minus(java.lang.String,java.lang.String)
                at java.base/java.lang.Class.getMethod(Class.java:2454) ~[?:?]
                at org.apache.calcite.linq4j.tree.Types.lookupMethod(Types.java:288) ~[?:?]
                ... 55 more

What is the expected behavior?

Return correct binning results.

What is your host/environment?

  • OS: macOS
  • Version: 3.3.0
  • Plugins

Do you have any additional context?
When pushdown is enabled and the field binned is used in group-by clause, the problem is diminished as the query is pushed down:

source=time_test |  bin @timestamp bins=2 | stats avg(value) by @timestamp

{
  "schema": [
    {
      "name": "avg(value)",
      "type": "double"
    },
    {
      "name": "@timestamp",
      "type": "timestamp"
    }
  ],
  "datarows": [
    [
      7962.72,
      "2025-07-28 00:00:00"
    ]
  ],
  "total": 1,
  "size": 1
}

Metadata

Metadata

Assignees

Labels

PPLPiped processing languageerror-experienceIssues related to how we handle failure cases in the plugin.feature

Type

No type

Projects

PPL Tracker

Status

Not Started

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions