Skip to content

SQL: impossible to apply a REPLACE function on a aggregated value #41597

@fbaligand

Description

@fbaligand

Elasticsearch version (bin/elasticsearch --version): 7.0

JVM version (java -version): Oracle JDK 1.8

OS version (uname -a if on a Unix-like system): Windows 10

Description of the problem including expected versus actual behavior:
When I do a SQL query, and try to apply a REPLACE function on a aggregated function, it throws an exception :
{ "error": { "root_cause": [ { "type": "sql_illegal_argument_exception", "reason": "Unexpected value reference class org.elasticsearch.xpack.sql.querydsl.container.GroupByRef" } ], "type": "sql_illegal_argument_exception", "reason": "Unexpected value reference class org.elasticsearch.xpack.sql.querydsl.container.GroupByRef" }, "status": 500 }

The expected result is simply the SQL response.

Steps to reproduce:

Please include a minimal but complete recreation of the problem, including
(e.g.) index creation, mappings, settings, query etc. The easier you make for
us to reproduce it, the more likely that somebody will take the time to look at it.

  1. install Kibana sample named "Sample web logs"
  2. execute this SQL query in Kibana Dev Tools :
POST _sql?format=csv
{
  "query": "SELECT response, REPLACE(CONVERT(AVG(phpmemory), text), '.', ',') as phpmemory_avg FROM kibana_sample_data_logs WHERE phpmemory IS NOT NULL AND response IS NOT NULL GROUP BY response LIMIT 2"
}
  1. To give more context, if I do this SQL query, it works fine:
POST _sql?format=csv
{
  "query": "SELECT response, CONVERT(AVG(phpmemory), text) as phpmemory_avg FROM kibana_sample_data_logs WHERE phpmemory IS NOT NULL AND response IS NOT NULL GROUP BY response LIMIT 2"
}

**Provide logs (if relevant)**:
Here's the server log error:

[2019-04-26T19:43:43,768][WARN ][r.suppressed ] [DWM1107442] path: /_sql, params: {pretty=, format=csv}
org.elasticsearch.xpack.sql.SqlIllegalArgumentException: Unexpected value reference class org.elasticsearch.xpack.sql.querydsl.container.GroupByRef
at org.elasticsearch.xpack.sql.execution.search.Querier$ScrollActionListener.createExtractor(Querier.java:550) ~[x-pack-sql-7.0.0.jar:7.0.0]
at org.elasticsearch.xpack.sql.execution.search.Querier$ScrollActionListener.handleResponse(Querier.java:488) ~[x-pack-sql-7.0.0.jar:7.0.0]
at org.elasticsearch.xpack.sql.execution.search.Querier$BaseActionListener.onResponse(Querier.java:584) [x-pack-sql-7.0.0.jar:7.0.0]
at org.elasticsearch.xpack.sql.execution.search.Querier$BaseActionListener.onResponse(Querier.java:558) [x-pack-sql-7.0.0.jar:7.0.0]
at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:68) [elasticsearch-7.0.0.jar:7.0.0]
at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:64) [elasticsearch-7.0.0.jar:7.0.0]
at org.elasticsearch.action.search.AbstractSearchAsyncAction.onResponse(AbstractSearchAsyncAction.java:316) [elasticsearch-7.0.0.jar:7.0.0]
at org.elasticsearch.action.search.AbstractSearchAsyncAction.onResponse(AbstractSearchAsyncAction.java:51) [elasticsearch-7.0.0.jar:7.0.0]
at org.elasticsearch.action.search.FetchSearchPhase$3.run(FetchSearchPhase.java:213) [elasticsearch-7.0.0.jar:7.0.0]
at org.elasticsearch.action.search.AbstractSearchAsyncAction.executePhase(AbstractSearchAsyncAction.java:166) [elasticsearch-7.0.0.jar:7.0.0]
at org.elasticsearch.action.search.AbstractSearchAsyncAction.executeNextPhase(AbstractSearchAsyncAction.java:159) [elasticsearch-7.0.0.jar:7.0.0]
at org.elasticsearch.action.search.ExpandSearchPhase.run(ExpandSearchPhase.java:120) [elasticsearch-7.0.0.jar:7.0.0]
at org.elasticsearch.action.search.AbstractSearchAsyncAction.executePhase(AbstractSearchAsyncAction.java:166) [elasticsearch-7.0.0.jar:7.0.0]
at org.elasticsearch.action.search.AbstractSearchAsyncAction.executeNextPhase(AbstractSearchAsyncAction.java:159) [elasticsearch-7.0.0.jar:7.0.0]
at org.elasticsearch.action.search.FetchSearchPhase.moveToNextPhase(FetchSearchPhase.java:206) [elasticsearch-7.0.0.jar:7.0.0]
at org.elasticsearch.action.search.FetchSearchPhase.lambda$innerRun$2(FetchSearchPhase.java:104) [elasticsearch-7.0.0.jar:7.0.0]
at org.elasticsearch.action.search.FetchSearchPhase$$Lambda$3622/304215410.run(Unknown Source) [elasticsearch-7.0.0.jar:7.0.0]
at org.elasticsearch.action.search.FetchSearchPhase.innerRun(FetchSearchPhase.java:110) [elasticsearch-7.0.0.jar:7.0.0]
at org.elasticsearch.action.search.FetchSearchPhase.access$000(FetchSearchPhase.java:44) [elasticsearch-7.0.0.jar:7.0.0]
at org.elasticsearch.action.search.FetchSearchPhase$1.doRun(FetchSearchPhase.java:86) [elasticsearch-7.0.0.jar:7.0.0]
at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37) [elasticsearch-7.0.0.jar:7.0.0]
at org.elasticsearch.common.util.concurrent.TimedRunnable.doRun(TimedRunnable.java:41) [elasticsearch-7.0.0.jar:7.0.0]
at org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.doRun(ThreadContext.java:751) [elasticsearch-7.0.0.jar:7.0.0]
at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37) [elasticsearch-7.0.0.jar:7.0.0]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [?:1.8.0_25]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [?:1.8.0_25]
at java.lang.Thread.run(Thread.java:745) [?:1.8.0_25]

Metadata

Metadata

Assignees

Type

No type
No fields configured for issues without a type.

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions