SQL: SUM() and LIKE condition doesn't work anymore

[astefan]

Originally reported here. SUM() in combination with a different non-LIKE condition works, also the query without SUM() does also work. Also, other stats functions (AVG, MIN, MAX) have the same behavior as SUM.

Simple test data to reproduce this:

{"index":{}}                                                                                                           
{"star_demo_city":"MEMPHIS","star_demo_dbo_city":"NEW YORK","amt":100}
{"index":{}}                                                                                                           
{"star_demo_city":"DALLAS","star_demo_dbo_city":"SHELBY","amt":100}
{"index":{}}                                                                                                           
{"star_demo_city":"ORLANDO","star_demo_dbo_city":"BOULDER","amt":100}

Full stack trace of the error:

[elasticsearch] [2018-12-03T18:19:56,278][WARN ][r.suppressed             ] [node-0] path: /_xpack/sql, params: {}
[elasticsearch] org.elasticsearch.xpack.sql.SqlIllegalArgumentException: Cannot evaluate script for expression LikePattern[MEMPHIS%, ]
[elasticsearch]         at org.elasticsearch.xpack.sql.expression.gen.script.ScriptWeaver.asScript(ScriptWeaver.java:44) ~[?:?]
[elasticsearch]         at org.elasticsearch.xpack.sql.expression.function.scalar.BinaryScalarFunction.asScript(BinaryScalarFunction.java:53) ~[?:?]
[elasticsearch]         at org.elasticsearch.xpack.sql.expression.function.scalar.ScalarFunction.toAttribute(ScalarFunction.java:38) ~[?:?]
[elasticsearch]         at org.elasticsearch.xpack.sql.expression.function.scalar.ScalarFunction.toAttribute(ScalarFunction.java:23) ~[?:?]
[elasticsearch]         at org.elasticsearch.xpack.sql.expression.Expressions.attribute(Expressions.java:122) ~[?:?]
[elasticsearch]         at org.elasticsearch.xpack.sql.optimizer.Optimizer$ReplaceAggsWithStats.lambda$updateAggAttributes$3(Optimizer.java:499) ~[?:?]
[elasticsearch]         at org.elasticsearch.xpack.sql.tree.Node.anyMatch(Node.java:110) ~[?:?]
[elasticsearch]         at org.elasticsearch.xpack.sql.optimizer.Optimizer$ReplaceAggsWithStats.lambda$updateAggAttributes$4(Optimizer.java:498) ~[?:?]
[elasticsearch]         at org.elasticsearch.xpack.sql.tree.Node.transformUp(Node.java:182) ~[?:?]
[elasticsearch]         at org.elasticsearch.xpack.sql.tree.Node.lambda$transformUp$10(Node.java:180) ~[?:?]
[elasticsearch]         at org.elasticsearch.xpack.sql.tree.Node.transformChildren(Node.java:201) ~[?:?]
[elasticsearch]         at org.elasticsearch.xpack.sql.tree.Node.transformUp(Node.java:180) ~[?:?]
[elasticsearch]         at org.elasticsearch.xpack.sql.plan.QueryPlan.lambda$transformExpressionsUp$4(QueryPlan.java:64) ~[?:?]
[elasticsearch]         at org.elasticsearch.xpack.sql.plan.QueryPlan.doTransformExpression(QueryPlan.java:69) ~[?:?]
[elasticsearch]         at org.elasticsearch.xpack.sql.plan.QueryPlan.lambda$transformExpressionsUp$5(QueryPlan.java:64) ~[?:?]
[elasticsearch]         at org.elasticsearch.xpack.sql.tree.NodeInfo.lambda$transform$0(NodeInfo.java:55) ~[?:?]
[elasticsearch]         at org.elasticsearch.xpack.sql.tree.NodeInfo$3.innerTransform(NodeInfo.java:116) ~[?:?]
[elasticsearch]         at org.elasticsearch.xpack.sql.tree.NodeInfo.transform(NodeInfo.java:59) ~[?:?]
[elasticsearch]         at org.elasticsearch.xpack.sql.tree.Node.transformNodeProps(Node.java:245) ~[?:?]
[elasticsearch]         at org.elasticsearch.xpack.sql.tree.Node.lambda$transformPropertiesUp$13(Node.java:233) ~[?:?]
[elasticsearch]         at org.elasticsearch.xpack.sql.tree.Node.transformUp(Node.java:182) ~[?:?]
[elasticsearch]         at org.elasticsearch.xpack.sql.tree.Node.lambda$transformUp$10(Node.java:180) ~[?:?]
[elasticsearch]         at org.elasticsearch.xpack.sql.tree.Node.transformChildren(Node.java:201) ~[?:?]
[elasticsearch]         at org.elasticsearch.xpack.sql.tree.Node.transformUp(Node.java:180) ~[?:?]
[elasticsearch]         at org.elasticsearch.xpack.sql.tree.Node.transformPropertiesUp(Node.java:233) ~[?:?]
[elasticsearch]         at org.elasticsearch.xpack.sql.plan.QueryPlan.transformExpressionsUp(QueryPlan.java:64) ~[?:?]
[elasticsearch]         at org.elasticsearch.xpack.sql.optimizer.Optimizer$ReplaceAggsWithStats.updateAggAttributes(Optimizer.java:469) ~[?:?]
[elasticsearch]         at org.elasticsearch.xpack.sql.optimizer.Optimizer$ReplaceAggsWithStats.apply(Optimizer.java:403) ~[?:?]
[elasticsearch]         at org.elasticsearch.xpack.sql.optimizer.Optimizer$ReplaceAggsWithStats.apply(Optimizer.java:366) ~[?:?]
[elasticsearch]         at org.elasticsearch.xpack.sql.rule.RuleExecutor$Transformation.<init>(RuleExecutor.java:82) ~[?:?]
[elasticsearch]         at org.elasticsearch.xpack.sql.rule.RuleExecutor.executeWithInfo(RuleExecutor.java:155) ~[?:?]
[elasticsearch]         at org.elasticsearch.xpack.sql.rule.RuleExecutor.execute(RuleExecutor.java:130) ~[?:?]
[elasticsearch]         at org.elasticsearch.xpack.sql.optimizer.Optimizer.optimize(Optimizer.java:108) ~[?:?]
[elasticsearch]         at org.elasticsearch.xpack.sql.session.SqlSession.lambda$optimizedPlan$3(SqlSession.java:149) ~[?:?]
[elasticsearch]         at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:60) [elasticsearch-7.0.0-SNAPSHOT.jar:7.0.0-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.xpack.sql.session.SqlSession.lambda$preAnalyze$2(SqlSession.java:137) [x-pack-sql-7.0.0-SNAPSHOT.jar:7.0.0-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:60) [elasticsearch-7.0.0-SNAPSHOT.jar:7.0.0-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.xpack.sql.analysis.index.IndexResolver.lambda$resolveAsMergedMapping$3(IndexResolver.java:246) [x-pack-sql-7.0.0-SNAPSHOT.jar:7.0.0-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:60) [elasticsearch-7.0.0-SNAPSHOT.jar:7.0.0-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:64) [elasticsearch-7.0.0-SNAPSHOT.jar:7.0.0-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:60) [elasticsearch-7.0.0-SNAPSHOT.jar:7.0.0-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.action.support.ContextPreservingActionListener.onResponse(ContextPreservingActionListener.java:43) [elasticsearch-7.0.0-SNAPSHOT.jar:7.0.0-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesAction.lambda$doExecute$1(TransportFieldCapabilitiesAction.java:84) [elasticsearch-7.0.0-SNAPSHOT.jar:7.0.0-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesAction$1.onResponse(TransportFieldCapabilitiesAction.java:97) [elasticsearch-7.0.0-SNAPSHOT.jar:7.0.0-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesAction$1.onResponse(TransportFieldCapabilitiesAction.java:93) [elasticsearch-7.0.0-SNAPSHOT.jar:7.0.0-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:64) [elasticsearch-7.0.0-SNAPSHOT.jar:7.0.0-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:60) [elasticsearch-7.0.0-SNAPSHOT.jar:7.0.0-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.action.support.ContextPreservingActionListener.onResponse(ContextPreservingActionListener.java:43) [elasticsearch-7.0.0-SNAPSHOT.jar:7.0.0-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.action.support.single.shard.TransportSingleShardAction$AsyncSingleAction$2.handleResponse(TransportSingleShardAction.java:271) [elasticsearch-7.0.0-SNAPSHOT.jar:7.0.0-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.action.support.single.shard.TransportSingleShardAction$AsyncSingleAction$2.handleResponse(TransportSingleShardAction.java:255) [elasticsearch-7.0.0-SNAPSHOT.jar:7.0.0-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.transport.TransportService$ContextRestoreResponseHandler.handleResponse(TransportService.java:1119) [elasticsearch-7.0.0-SNAPSHOT.jar:7.0.0-SNAPSHOT] [elasticsearch]         at org.elasticsearch.transport.TransportService$DirectResponseChannel.processResponse(TransportService.java:1200) [elasticsearch-7.0.0-SNAPSHOT.jar:7.0.0-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.transport.TransportService$DirectResponseChannel.sendResponse(TransportService.java:1180) [elasticsearch-7.0.0-SNAPSHOT.jar:7.0.0-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.transport.TaskTransportChannel.sendResponse(TaskTransportChannel.java:54) [elasticsearch-7.0.0-SNAPSHOT.jar:7.0.0-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.action.support.HandledTransportAction$ChannelActionListener.onResponse(HandledTransportAction.java:93) [elasticsearch-7.0.0-SNAPSHOT.jar:7.0.0-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.action.support.HandledTransportAction$ChannelActionListener.onResponse(HandledTransportAction.java:77) [elasticsearch-7.0.0-SNAPSHOT.jar:7.0.0-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.action.support.single.shard.TransportSingleShardAction$1.doRun(TransportSingleShardAction.java:117) [elasticsearch-7.0.0-SNAPSHOT.jar:7.0.0-SNAPSHOT] [elasticsearch]         at org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.doRun(ThreadContext.java:759) [elasticsearch-7.0.0-SNAPSHOT.jar:7.0.0-SNAPSHOT][elasticsearch]         at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37) [elasticsearch-7.0.0-SNAPSHOT.jar:7.0.0-SNAPSHOT]
[elasticsearch]         at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
[elasticsearch]         at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
[elasticsearch]         at java.lang.Thread.run(Thread.java:834) [?:?]

[elasticmachine]

Pinging @elastic/es-search

[AngelaHsieh1027]

excuse me, is this bug fix yet?

[astefan]

@AngelaHsieh1027 not fixed yet. When this will happen there will be a PR referenced in this issue and, also, the issue will be closed stating the versions where the fix is applied to. Thank you for your patience.

[matriv]

The Issue has been fixed in #36672