sql
sql copied to clipboard
opensearch-project
[BUG] PPL `bin` command fails with param bins on time-related fields
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?
- disable pushdown
PUT _cluster/settings
{
"persistent":{
"plugins.calcite.enabled": true,
"plugins.calcite.fallback.allowed": false,
"plugins.calcite.pushdown.enabled": true
}
}
- 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
}
Duplicated with #4317, but it seems the problem is not solved yet when pushdown is disabled.
@ahkcs Please take a look at this issue. Do we have plan to implement bin command at time related fields?
After investigation, supporting the bins parameter for time-based fields without pushdown would require duplicating the entire auto_date_histogram logic from OpenSearch into the Calcite layer. This includes:
Replicating interval selection algorithms (e.g., 1s, 5s, 1m, 5m, 1h, etc.)
Implementing bucket boundary calculations
Matching the native semantic behavior where OpenSearch returns the minimum @timestamp from documents within each bucket, rather than bucket boundary values
In effect, the no-pushdown implementation would become a full reimplementation of OpenSearch’s native aggregation logic within Calcite, resulting in significant code redundancy with minimal user benefit. Since the pushdown path already handles this behavior correctly using OpenSearch’s native functionality, I recommend documenting this as a known limitation for the no-pushdown path.
cc @qianheng-aws
@ahkcs Agree with making it a limitation if there is no strong requirement cases from our users.