opensearch-project / opensearch-project/sql

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

Open
#4,578 7 comments 0 reactions 1 assignee View on GitHub

@ahkcs is already working on this.

Since Oct 22, 2025.

error-experience feature PPL
Dominant language
Java
Stars
176
Forks
229
Avg merge
2d 21h
Merged PRs (30d)
43

Description

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
}

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.