opensearch-project / opensearch-project/sql

[BUG] Correct bin command implementation to pass validation

Open
#4,973 2 comments 1 reaction 0 assignees View on GitHub

Nobody has claimed this yet.

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

Description

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

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.

Research direction

First review the dependency on issue #4892, then trace the bin query through CalciteToolsHelper.java and OpenSearchExecutionEngine.java using the provided stack trace. Re-run the PPL query against opensearch-sql_test_index_account and confirm it returns the two expected age_str buckets without the validation error.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, sql
Domain
backend-api-design, databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.