[BUG] Correct bin command implementation to pass validation

[yuancu]

Query Information

Status: pending, proceed after #4892

PPL Command/Query:

source=opensearch-sql_test_index_account | eval age_str = CAST(age AS STRING) | bin age_str bins=3 | stats count() by age_str | sort age_str

Expected Result:

count	age_str
1	20-30
3	30-40

Actual Result:

Error stack:

java.lang.UnsupportedOperationException: The 'bins' parameter on timestamp fields requires: (1) pushdown to be enabled (controlled by plugins.calcite.pushdown.enabled, enabled by default), and (2) the timestamp field to be used as an aggregation bucket (e.g., 'stats count() by @timestamp').
        at org.opensearch.sql.calcite.utils.CalciteToolsHelper$OpenSearchRelRunners.run(CalciteToolsHelper.java:368) ~[core-3.4.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.opensearch.executor.OpenSearchExecutionEngine.lambda$explain$1(OpenSearchExecutionEngine.java:198) [opensearch-3.4.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.opensearch.client.OpenSearchNodeClient.schedule(OpenSearchNodeClient.java:223) [opensearch-3.4.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.opensearch.executor.OpenSearchExecutionEngine.explain(OpenSearchExecutionEngine.java:177) [opensearch-3.4.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.executor.QueryService.lambda$explainWithCalcite$1(QueryService.java:168) [core-3.4.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.calcite.CalcitePlanContext.run(CalcitePlanContext.java:211) [core-3.4.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.executor.QueryService.lambda$explainWithCalcite$0(QueryService.java:161) [core-3.4.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.calcite.CalcitePlanContext.run(CalcitePlanContext.java:211) [core-3.4.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.executor.QueryService.explainWithCalcite(QueryService.java:155) [core-3.4.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.executor.QueryService.explain(QueryService.java:105) [core-3.4.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.executor.execution.QueryPlan.explain(QueryPlan.java:78) [core-3.4.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.executor.execution.ExplainPlan.execute(ExplainPlan.java:37) [core-3.4.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.opensearch.executor.OpenSearchQueryManager.lambda$schedule$0(OpenSearchQueryManager.java:64) [opensearch-3.4.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.opensearch.executor.OpenSearchQueryManager.lambda$withCurrentContext$0(OpenSearchQueryManager.java:89) [opensearch-3.4.0.0-SNAPSHOT.jar:?]
        at org.opensearch.common.util.concurrent.ThreadContext$ContextPreservingRunnable.run(ThreadContext.java:916) [opensearch-3.4.0-SNAPSHOT.jar:3.4.0-SNAPSHOT]
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1095) [?:?]
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:619) [?:?]
        at java.base/java.lang.Thread.run(Thread.java:1447) [?:?]
Caused by: java.sql.SQLException: Error while preparing plan [LogicalSystemLimit(sort0=[$1], dir0=[ASC-nulls-first], fetch=[10000], type=[QUERY_SIZE_LIMIT])
  LogicalSort(sort0=[$1], dir0=[ASC-nulls-first])
    LogicalProject(count()=[$1], age_str=[$0])
      LogicalAggregate(group=[{0}], count()=[COUNT()])
        LogicalProject(age_str=[WIDTH_BUCKET(SAFE_CAST($8), 3, -(MAX(SAFE_CAST($8)) OVER (), MIN(SAFE_CAST($8)) OVER ()), MAX(SAFE_CAST($8)) OVER ())])
          CalciteLogicalIndexScan(table=[[OpenSearch, opensearch-sql_test_index_account]])
]
        at org.apache.calcite.avatica.Helper.createException(Helper.java:56) ~[avatica-core-1.27.0.jar:1.27.0]
        at org.apache.calcite.avatica.Helper.createException(Helper.java:41) ~[avatica-core-1.27.0.jar:1.27.0]
        at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:236) ~[calcite-core-1.41.0.jar:1.41.0]
        at org.apache.calcite.jdbc.CalciteConnectionImpl.lambda$unwrap$0(CalciteConnectionImpl.java:196) ~[calcite-core-1.41.0.jar:1.41.0]
        at org.opensearch.sql.calcite.utils.CalciteToolsHelper$OpenSearchRelRunners.run(CalciteToolsHelper.java:361) ~[core-3.4.0.0-SNAPSHOT.jar:?]
        ... 17 more
Caused by: java.lang.IllegalStateException: Unable to implement EnumerableLimit(fetch=[10000]): rowcount = 1000.0, cumulative cost = {43125.0 rows, 273155.1055796427 cpu, 0.0 io}, id = 153
  EnumerableSort(sort0=[$1], dir0=[ASC-nulls-first]): rowcount = 1000.0, cumulative cost = {42125.0 rows, 272155.1055796427 cpu, 0.0 io}, id = 151
    EnumerableCalc(expr#0..1=[{inputs}], count()=[$t1], age_str=[$t0]): rowcount = 1000.0, cumulative cost = {41125.0 rows, 134000.0 cpu, 0.0 io}, id = 155
      EnumerableAggregate(group=[{0}], count()=[COUNT()]): rowcount = 1000.0, cumulative cost = {40125.0 rows, 130000.0 cpu, 0.0 io}, id = 147
        EnumerableCalc(expr#0..2=[{inputs}], expr#3=[3], expr#4=[-($t1, $t2)], expr#5=[WIDTH_BUCKET($t0, $t3, $t4, $t1)], age_str=[$t5]): rowcount = 10000.0, cumulative cost = {39000.0 rows, 130000.0 cpu, 0.0 io}, id = 157
          EnumerableWindow(window#0=[window(aggs [MAX($0), MIN($0)])]): rowcount = 10000.0, cumulative cost = {29000.0 rows, 60000.0 cpu, 0.0 io}, id = 143
            EnumerableCalc(expr#0=[{inputs}], expr#1=[SAFE_CAST($t0)], $f0=[$t1]): rowcount = 10000.0, cumulative cost = {19000.0 rows, 30000.0 cpu, 0.0 io}, id = 159
              CalciteEnumerableIndexScan(table=[[OpenSearch, opensearch-sql_test_index_account]], PushDownContext=[[PROJECT->[age]], OpenSearchRequestBuilder(sourceBuilder={"from":0,"timeout":"1m","_source":{"includes":["age"],"excludes":[]}}, requestedTotalSize=2147483647, pageSize=null, startFrom=0)]): rowcount = 10000.0, cumulative cost = {9000.0 rows, 0.0 cpu, 0.0 io}, id = 102

        at org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:117) ~[calcite-core-1.41.0.jar:1.41.0]
        at org.apache.calcite.adapter.enumerable.EnumerableInterpretable.toBindable(EnumerableInterpretable.java:113) ~[calcite-core-1.41.0.jar:1.41.0]
        at org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.implement(CalcitePrepareImpl.java:1190) ~[calcite-core-1.41.0.jar:1.41.0]
        at org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepare_(CalcitePrepareImpl.java:1087) ~[calcite-core-1.41.0.jar:1.41.0]
        at org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepareRel(CalcitePrepareImpl.java:1042) ~[calcite-core-1.41.0.jar:1.41.0]
        at org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:694) ~[calcite-core-1.41.0.jar:1.41.0]
        at org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:524) ~[calcite-core-1.41.0.jar:1.41.0]
        at org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:492) ~[calcite-core-1.41.0.jar:1.41.0]
        at org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:246) ~[calcite-core-1.41.0.jar:1.41.0]
        at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:226) ~[calcite-core-1.41.0.jar:1.41.0]
        at org.apache.calcite.jdbc.CalciteConnectionImpl.lambda$unwrap$0(CalciteConnectionImpl.java:196) ~[calcite-core-1.41.0.jar:1.41.0]
        at org.opensearch.sql.calcite.utils.CalciteToolsHelper$OpenSearchRelRunners.run(CalciteToolsHelper.java:361) ~[core-3.4.0.0-SNAPSHOT.jar:?]
        ... 17 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) ~[calcite-linq4j-1.41.0.jar:1.41.0]
                at org.apache.calcite.linq4j.tree.Expressions.call(Expressions.java:453) ~[calcite-linq4j-1.41.0.jar:1.41.0]
                at org.apache.calcite.adapter.enumerable.RexImpTable$BinaryImplementor.implementSafe(RexImpTable.java:3334) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.adapter.enumerable.RexImpTable$AbstractRexCallImplementor.genValueStatement(RexImpTable.java:4303) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.adapter.enumerable.RexImpTable$AbstractRexCallImplementor.implement(RexImpTable.java:4265) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitCall(RexToLixTranslator.java:1471) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitCall(RexToLixTranslator.java:110) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.rex.RexCall.accept(RexCall.java:208) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitLocalRef(RexToLixTranslator.java:1347) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitLocalRef(RexToLixTranslator.java:110) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.rex.RexLocalRef.accept(RexLocalRef.java:78) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.adapter.enumerable.RexToLixTranslator.implementCallOperand(RexToLixTranslator.java:1480) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitCall(RexToLixTranslator.java:1467) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitCall(RexToLixTranslator.java:110) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.rex.RexCall.accept(RexCall.java:208) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitLocalRef(RexToLixTranslator.java:1347) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitLocalRef(RexToLixTranslator.java:110) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.rex.RexLocalRef.accept(RexLocalRef.java:78) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:262) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:256) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateList(RexToLixTranslator.java:1171) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateProjects(RexToLixTranslator.java:210) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.adapter.enumerable.EnumerableCalc.implement(EnumerableCalc.java:192) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:107) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.adapter.enumerable.EnumerableAggregate.implement(EnumerableAggregate.java:105) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:107) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.adapter.enumerable.EnumerableCalc.implement(EnumerableCalc.java:118) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:107) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.adapter.enumerable.EnumerableSort.implement(EnumerableSort.java:74) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:107) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.adapter.enumerable.EnumerableLimit.implement(EnumerableLimit.java:98) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:114) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.adapter.enumerable.EnumerableInterpretable.toBindable(EnumerableInterpretable.java:113) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.implement(CalcitePrepareImpl.java:1190) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepare_(CalcitePrepareImpl.java:1087) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepareRel(CalcitePrepareImpl.java:1042) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:694) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:524) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:492) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:246) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:226) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.apache.calcite.jdbc.CalciteConnectionImpl.lambda$unwrap$0(CalciteConnectionImpl.java:196) ~[calcite-core-1.41.0.jar:1.41.0]
                at org.opensearch.sql.calcite.utils.CalciteToolsHelper$OpenSearchRelRunners.run(CalciteToolsHelper.java:361) ~[core-3.4.0.0-SNAPSHOT.jar:?]
                at org.opensearch.sql.opensearch.executor.OpenSearchExecutionEngine.lambda$explain$1(OpenSearchExecutionEngine.java:198) [opensearch-3.4.0.0-SNAPSHOT.jar:?]
                at org.opensearch.sql.opensearch.client.OpenSearchNodeClient.schedule(OpenSearchNodeClient.java:223) [opensearch-3.4.0.0-SNAPSHOT.jar:?]
                at org.opensearch.sql.opensearch.executor.OpenSearchExecutionEngine.explain(OpenSearchExecutionEngine.java:177) [opensearch-3.4.0.0-SNAPSHOT.jar:?]
                at org.opensearch.sql.executor.QueryService.lambda$explainWithCalcite$1(QueryService.java:168) [core-3.4.0.0-SNAPSHOT.jar:?]
                at org.opensearch.sql.calcite.CalcitePlanContext.run(CalcitePlanContext.java:211) [core-3.4.0.0-SNAPSHOT.jar:?]
                at org.opensearch.sql.executor.QueryService.lambda$explainWithCalcite$0(QueryService.java:161) [core-3.4.0.0-SNAPSHOT.jar:?]
                at org.opensearch.sql.calcite.CalcitePlanContext.run(CalcitePlanContext.java:211) [core-3.4.0.0-SNAPSHOT.jar:?]
                at org.opensearch.sql.executor.QueryService.explainWithCalcite(QueryService.java:155) [core-3.4.0.0-SNAPSHOT.jar:?]
                at org.opensearch.sql.executor.QueryService.explain(QueryService.java:105) [core-3.4.0.0-SNAPSHOT.jar:?]
                at org.opensearch.sql.executor.execution.QueryPlan.explain(QueryPlan.java:78) [core-3.4.0.0-SNAPSHOT.jar:?]
                at org.opensearch.sql.executor.execution.ExplainPlan.execute(ExplainPlan.java:37) [core-3.4.0.0-SNAPSHOT.jar:?]
                at org.opensearch.sql.opensearch.executor.OpenSearchQueryManager.lambda$schedule$0(OpenSearchQueryManager.java:64) [opensearch-3.4.0.0-SNAPSHOT.jar:?]
                at org.opensearch.sql.opensearch.executor.OpenSearchQueryManager.lambda$withCurrentContext$0(OpenSearchQueryManager.java:89) [opensearch-3.4.0.0-SNAPSHOT.jar:?]
                at org.opensearch.common.util.concurrent.ThreadContext$ContextPreservingRunnable.run(ThreadContext.java:916) [opensearch-3.4.0-SNAPSHOT.jar:3.4.0-SNAPSHOT]
                at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1095) [?:?]
                at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:619) [?:?]
                at java.base/java.lang.Thread.run(Thread.java:1447) [?:?]
        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:2168) ~[?:?]
                at org.apache.calcite.linq4j.tree.Types.lookupMethod(Types.java:288) ~[calcite-linq4j-1.41.0.jar:1.41.0]
                ... 59 more

The implementation creates the following logical plan:

LogicalSort(sort0=[$1], dir0=[ASC-nulls-first])
  LogicalProject(count()=[$1], age_str=[$0])
    LogicalAggregate(group=[{0}], count()=[COUNT()])
      LogicalProject(age_str=[WIDTH_BUCKET(SAFE_CAST($8), 3, -(MAX(SAFE_CAST($8)) OVER (), MIN(SAFE_CAST($8)) OVER ()), MAX(SAFE_CAST($8)) OVER ())])
        CalciteLogicalIndexScan(table=[[OpenSearch, opensearch-sql_test_index_account]])

which is translated to SQL as

SELECT COUNT(*) AS `count()`, WIDTH_BUCKET(SAFE_CAST(`age` AS STRING), 3, (MAX(SAFE_CAST(`age` AS STRING)) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) - (MIN(SAFE_CAST(`age` AS STRING)) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)), MAX(SAFE_CAST(`age` AS STRING)) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) AS `age_str`
FROM `opensearch-sql_test_index_account`
GROUP BY WIDTH_BUCKET(SAFE_CAST(`age` AS STRING), 3, (MAX(SAFE_CAST(`age` AS STRING)) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) - (MIN(SAFE_CAST(`age` AS STRING)) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)), MAX(SAFE_CAST(`age` AS STRING)) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING))
ORDER BY 2

Dataset Information

Dataset/Schema Type

• Custom (details below)

Index Mapping

{
  "mappings": {
    "properties": {
      "age": {
        "type": "integer"
      }
  }
}

Bug Description

It fails to pass SQL validation as it creates calls like string - string and timestamp - timestamp, which does not make sense to Calcite. Previously, it works because directly rex call does not trigger validation.

Please refrain from solving the issue by allowing substraction operation on strings.

Environment Information

OpenSearch Version: 3.4 with PR #4892

[coderabbitai]

📝 CodeRabbit Plan Mode

Generate an implementation plan and prompts that you can use with your favorite coding agent.

• Create Plan

Examples

• Example 1
• Example 2

_{You can configure auto-planning by selecting labels in the issue_enrichment configuration.}

🔗 Similar Issues

Possible Duplicates

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

🔗 Related PRs

#4830 - Support sort expression pushdown for SortMergeJoin [merged]
#4841 - [BugFix] Fix Memory Exhaustion for Multiple Filtering Operations in PPL [merged]
#4867 - Remove count aggregation for sort on aggregate measure [merged]
#4926 - Replace duplicated aggregation logic with aggregateWithTrimming() [merged]
#4929 - Pushdown join with max=n option to TopHits aggregation [merged]

👤 Suggested Assignees

• LantaoJin
• yuancu
• songkant-aws
• RyanL1997
• ishaoxy

---

🧪 Issue enrichment is currently in open beta.

To disable automatic issue enrichment, add the following to your .coderabbit.yaml:

issue_enrichment:
  auto_enrich:
    enabled: false

💬 Have feedback or questions? Drop into our discord or schedule a call!