[BUG] [Draft] Correct bin command implementation to pass validation
Query Information
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
- [x] 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
๐ CodeRabbit Plan Mode
Generate an implementation plan and prompts that you can use with your favorite coding agent.
- [ ] Create Plan
๐ Similar Issues
Possible Duplicates
- https://github.com/opensearch-project/sql/issues/4578
๐ Related PRs
opensearch-project/sql#4830 - Support sort expression pushdown for SortMergeJoin [merged]
opensearch-project/sql#4841 - [BugFix] Fix Memory Exhaustion for Multiple Filtering Operations in PPL [merged]
opensearch-project/sql#4867 - Remove count aggregation for sort on aggregate measure [merged]
opensearch-project/sql#4926 - Replace duplicated aggregation logic with aggregateWithTrimming() [merged]
opensearch-project/sql#4929 - Pushdown join with max=n option to TopHits aggregation [merged]
๐ค Suggested Assignees
๐งช 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!