ES SQL :: Syntax for "sum( Value1 * Value2)"?

redapplesonly · October 23, 2019, 4:37pm

Hi Elasticsearch Ninja Masters,

I’ve recently spun up an Elasticsearch v7.4.0 Docker container, and I’m using the “elasticsearch-sql-cli” script to run SQL-like queries off my ES instance. Which is awesome, thank you for implementing that.

I have a specific syntax question, however. I need to run the below query:

select Value1, sum( Value2 * Value3 )
from \"myindex2019.10.09\"
group by Value1;

Pretty simple, really. For every record in the “myindex2019.10.09” index, multiply the values of Value2 and Value3 together. Then sum up all the (Value2 * Value3)’s together, grouped my Value1. Should look like this:

Value1 | sum(Value2 * Value3)
-------+---------------------
1      |  100
2      |  200
3      |  ...etc...

But from experimentation, I can tell you that the “elasticsearch-sql-cli” script likes Value2 * Value3 or sum(Value2), but not sum(Value2 * Value3) The full output I see when I try this query is below. The “elasticsearch-sql-cli” looks like it throws a Java exception, but does not crash.

Is there a solution here? Any suggestion would be useful. Thanks!

sql> select Value1, sum( Value2 * Value3 ) from \"myindex2019.10.09\";
Server error [Server encountered an error [Does not know how to convert argument Mul[] for function Sum[]]. [SqlIllegalArgumentException[Does not know how to convert argument Mul[] for function Sum[]]
        at org.elasticsearch.xpack.sql.planner.QueryTranslator.field(QueryTranslator.java:470)
        at org.elasticsearch.xpack.sql.planner.QueryTranslator$Sums.toAgg(QueryTranslator.java:854)
        at org.elasticsearch.xpack.sql.planner.QueryTranslator$Sums.toAgg(QueryTranslator.java:850)
        at org.elasticsearch.xpack.sql.planner.QueryTranslator$SingleValueAggTranslator.asAgg(QueryTranslator.java:971)
        at org.elasticsearch.xpack.sql.planner.QueryTranslator$AggTranslator.apply(QueryTranslator.java:961)
        at org.elasticsearch.xpack.sql.planner.QueryTranslator.toAgg(QueryTranslator.java:198)
        at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldAggregate.addAggFunction(QueryFolder.java:445)
        at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldAggregate.rule(QueryFolder.java:359)
        at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldAggregate.rule(QueryFolder.java:204)
        at org.elasticsearch.xpack.sql.tree.Node.lambda$transformUp$11(Node.java:196)
        at org.elasticsearch.xpack.sql.tree.Node.transformUp(Node.java:190)
        at org.elasticsearch.xpack.sql.tree.Node.transformUp(Node.java:196)
        at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldingRule.apply(QueryFolder.java:593)
        at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldingRule.apply(QueryFolder.java:589)
        at org.elasticsearch.xpack.sql.rule.RuleExecutor$Transformation.<init>(RuleExecutor.java:82)
        at org.elasticsearch.xpack.sql.rule.RuleExecutor.executeWithInfo(RuleExecutor.java:158)
        at org.elasticsearch.xpack.sql.rule.RuleExecutor.execute(RuleExecutor.java:130)
        at org.elasticsearch.xpack.sql.planner.QueryFolder.fold(QueryFolder.java:82)
        at org.elasticsearch.xpack.sql.planner.Planner.foldPlan(Planner.java:38)
        at org.elasticsearch.xpack.sql.planner.Planner.plan(Planner.java:28)
        at org.elasticsearch.xpack.sql.session.SqlSession.lambda$physicalPlan$4(SqlSession.java:160)
        at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:62)
        at org.elasticsearch.xpack.sql.session.SqlSession.lambda$optimizedPlan$3(SqlSession.java:156)
        at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:62)
        at org.elasticsearch.xpack.sql.session.SqlSession.lambda$preAnalyze$2(SqlSession.java:144)
        at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:62)
        at org.elasticsearch.xpack.sql.analysis.index.IndexResolver.lambda$resolveAsMergedMapping$3(IndexResolver.java:277)
        at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:62)
        at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:70)
        at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:64)
        at org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesAction.lambda$doExecute$1(TransportFieldCapabilitiesAction.java:88)
        at org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesAction$1.onResponse(TransportFieldCapabilitiesAction.java:101)
        at org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesAction$1.onResponse(TransportFieldCapabilitiesAction.java:97)
        at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:70)
        at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:64)
        at org.elasticsearch.action.support.single.shard.TransportSingleShardAction$AsyncSingleAction$2.handleResponse(TransportSingleShardAction.java:261)
        at org.elasticsearch.action.support.single.shard.TransportSingleShardAction$AsyncSingleAction$2.handleResponse(TransportSingleShardAction.java:247)
        at org.elasticsearch.transport.TransportService$ContextRestoreResponseHandler.handleResponse(TransportService.java:1110)
        at org.elasticsearch.transport.TransportService$DirectResponseChannel.processResponse(TransportService.java:1188)
        at org.elasticsearch.transport.TransportService$DirectResponseChannel.sendResponse(TransportService.java:1168)
        at org.elasticsearch.transport.TaskTransportChannel.sendResponse(TaskTransportChannel.java:54)
        at org.elasticsearch.action.support.ChannelActionListener.onResponse(ChannelActionListener.java:47)
        at org.elasticsearch.action.support.ChannelActionListener.onResponse(ChannelActionListener.java:30)
        at org.elasticsearch.action.support.single.shard.TransportSingleShardAction.lambda$asyncShardOperation$0(TransportSingleShardAction.java:110)
        at org.elasticsearch.action.ActionRunnable$1.doRun(ActionRunnable.java:45)
        at org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.doRun(ThreadContext.java:773)
        at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
        at java.base/java.lang.Thread.run(Thread.java:830)
]]
sql>

William_Brafford · October 23, 2019, 8:11pm

Pete,

Unfortunately, I think you're hitting a documented limitation of our SQL support:

Using aggregation functions on top of scalar functions

Aggregation functions like MIN , MAX , etc. can only be used directly on fields, and so queries like SELECT MAX(abs(age)) FROM test are not possible.

For what it's worth, I popped the following documents into an index called myindex:

{
    "key": "value",
    "length": 2,
    "width": 3
}
{
    "key": "value",
    "length": 5,
    "width": 7
}

And used the Elasticsearch query DSL to get the sum of areas using the following query:

GET myindex/_search
{
  "query": {
    "match_all": {}
  },
  "aggs": {
    "keys": {
      "terms": {
        "field": "key.keyword"
      }, 
      "aggs": {
        "areas": {
          "sum": {
            "script": "doc['length'].value * doc['width'].value"
          }
        }
      }
    }
  },
  "size": 0
}

Result:

{
  [...],
  "aggregations" : {
    "keys" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "value",
          "doc_count" : 2,
          "areas" : {
            "value" : 41.0
          }
        }
      ]
    }
  }
}

I wish I had been able to find a SQL solution for you.

-William

Andrei_Stefan · October 24, 2019, 8:36am

Indeed, this is not supported at the moment.
There is a github issue created for something very similar: https://github.com/elastic/elasticsearch/issues/37271

redapplesonly · October 24, 2019, 3:11pm

No problem, I appreciate the information. Sometimes its nice to know that the thing you want to do isn't possible at the moment. Much appreciated!

system · November 21, 2019, 3:12pm

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.