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!

Topic		Replies	Views
How to multiply two columns and then sum the result in ElasticSQL? Elasticsearch elastic-stack-sql	3	1393	March 7, 2019
Sum() in elastic search Using SQL Plugin Elasticsearch elastic-stack-sql	2	459	May 24, 2019
SQL Query with CASE When condition and SUM Elasticsearch	2	1623	March 3, 2020
SQL interface to Elasticsearch & Aggregation functions Elasticsearch	3	1269	July 6, 2017
Elasticsearch SQL - Error when using CASE WHEN with SUM aggregate Elasticsearch elastic-stack-sql	4	1193	June 2, 2020

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

Related topics