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.