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>