Elasticsearch SQL - Error when using CASE WHEN with SUM aggregate

[Pramod_Kumar1 (Pramod Kumar)]

Hi Team,

I am getting an error in elasticsearch sql(in-built elasticsql within x-pack) query when using CASE WHEN with SUM aggregate function.

{ "type": "sql_illegal_argument_exception", "reason": "Does not know how to convert argument ScalarFunctionAttribute[V2,DOUBLE,X,UNKNOWN,122824,false,123043,InternalSqlScriptUtils.caseFunction([InternalSqlScriptUtils.and(InternalSqlSc riptUtils.gte(InternalSqlScriptUtils.docValue(doc,params.v0), params.v1), InternalSqlScriptUtils.lte(InternalSqlScriptUtils.docValue(doc,params.v2), params.v3)),params.a0,params.v4]), null,CasePipe[Case[[IfConditional[Range[monthSerial{f}#122963,201704,true,201706,true]#123005,Sum[valueUsd{f}#1 22994]#123040 ], 0]]#123043] |_BinaryLogicPipe[Range[monthSerial{f}#122963,201704,true,201706,true]#123005,AND] | |_BinaryComparisonPipe[Range[monthSerial{f}#122963,201704,true,201706,true]#123005,>=] | | |_AttributeInput[monthSerial{f}#122963,monthSerial{f}#122963] | | \_ConstantInput[201704,201704] | \_BinaryComparisonPipe[Range[monthSerial{f}#122963,201704,true,201706,true]#123005,<=] | |_AttributeInput[monthSerial{f}#122963,monthSerial{f}#122963] | \_ConstantInput[201706,201706] |_AggNameInput[Sum[valueUsd{f}#122994]#123040,SUM(valueUsd)] \_ConstantInput[0,0]] for function Sum[]" }

Below is the sample query and elastic version details -

http://localhost:9200/_sql?format=json
{
"query": "SELECT Id, SUM(S1) AS PreviousTotalRecords, SUM(S2) AS CurrentTotalRecords, SUM(V1) AS PreviousTotalPrice, SUM(V2) AS CurrentTotalPrice FROM (SELECT productId AS Id, (CASE WHEN monthSerial BETWEEN 201701 AND 201703 THEN COUNT(1) ELSE 0 END) S1, (CASE WHEN monthSerial BETWEEN 201701 AND 201703 THEN SUM(priceValue) ELSE 0 END) V1,(CASE WHEN monthSerial BETWEEN 201704 AND 201706 THEN COUNT(1) ELSE 0 END) S2, (CASE WHEN monthSerial BETWEEN 201704 AND 201706 THEN SUM(priceValue) ELSE 0 END) V2 FROM "my-index" WHERE monthSerial IN (201701,201702,201703,201704,201705,201706) GROUP BY productId,monthSerial) X GROUP BY Id"
}

Elastic version - 7.3.0

Can anyone help me out so that I can get aggregated data for comparison of two periods.

[matriv (Marios Trivyzas)]

First of all ES SQL has limited support for subqueries.
Secondly, the support for scalar functions (including conditionals such as the CASE expression has been added recently and will be available with 7.8 version.)

[Pramod_Kumar1 (Pramod Kumar)]

Hi Matriv,

Thanks for your reply. As I am using version 7.3.0, is it possible to get aggregate count in regular query? If that will be possible then I can change sub-query to regular query to get required result.

Thanks,

[matriv (Marios Trivyzas)]

Unfortunately, I don't see a workaround for your case.