Sql query cannot be closed

sql query cannot be closed, resulting in cluster exception,Restart Cluster Recovery
Elasticsearch version: 7.9.3

query

GET _sql?format=txt
{"query": """
SELECT app,tag,count(1) as c FROM "test"
group by app,tag
order by c desc
"""}

error

{
  "error" : {
    "root_cause" : [
      {
        "type" : "sql_illegal_argument_exception",
        "reason" : "The default limit [65535] for aggregate sorting has been reached; please specify a LIMIT"
      }
    ],
    "type" : "sql_illegal_argument_exception",
    "reason" : "The default limit [65535] for aggregate sorting has been reached; please specify a LIMIT"
  },
  "status" : 500
}

cluster _cat/tasks?v

action                         task_id                      parent_task_id               type      start_time    timestamp running_time ip            node
indices:data/read/sql          dWoWAYBgTIS7-HQeKmKwrQ:75066 -                            transport 1640862717973 11:11:57  6.1m         172.20.124.55 test-7-9-3-es-default-0
indices:data/read/search       dWoWAYBgTIS7-HQeKmKwrQ:76457 -                            transport 1640863084733 11:18:04  1.1s         172.20.124.55 test-7-9-3-es-default-0

_tasks?

        {
  "completed" : false,
  "task" : {
    "node" : "dWoWAYBgTIS7-HQeKmKwrQ",
    "id" : 75066,
    "type" : "transport",
    "action" : "indices:data/read/sql",
    "description" : "SQL [\nSELECT app,tag,count(1) as c FROM \"test\"\ngroup by app,tag\norder by c desc\n][null]",
    "start_time_in_millis" : 1640862717973,
    "running_time_in_nanos" : 199331352367,
    "cancellable" : false,
    "headers" : { }
  }
}
{
  "completed" : false,
  "task" : {
    "node" : "dWoWAYBgTIS7-HQeKmKwrQ",
    "id" : 75820,
    "type" : "transport",
    "action" : "indices:data/read/search",
    "description" : "indices[test], types[], search_type[QUERY_THEN_FETCH], source[{\"size\":0,\"timeout\":\"45s\",\"_source\":false,\"stored_fields\":\"_none_\",\"aggregations\":{\"groupby\":{\"composite\":{\"size\":1000,\"sources\":[{\"4a3c589\":{\"terms\":{\"field\":\"app\",\"missing_bucket\":true,\"order\":\"asc\"}}},{\"918103b0\":{\"terms\":{\"field\":\"tag\",\"missing_bucket\":true,\"order\":\"asc\"}}}],\"after\":{\"918103b0\":\"17111\",\"4a3c589\":\"17110\"}}}}}]",
    "start_time_in_millis" : 1640862899924,
    "running_time_in_nanos" : 17403653173,
    "cancellable" : true,
    "headers" : { }
  }
}

Elasticsearch logs

[root@node16 tmp]# kubectl  logs -f test-7-9-3-es-default-0
"\tat org.elasticsearch.action.search.AbstractSearchAsyncAction.executeNextPhase(AbstractSearchAsyncAction.java:344) [elasticsearch-7.9.3.jar:7.9.3]",
"\tat org.elasticsearch.action.search.FetchSearchPhase.moveToNextPhase(FetchSearchPhase.java:231) [elasticsearch-7.9.3.jar:7.9.3]",
"\tat org.elasticsearch.action.search.FetchSearchPhase.lambda$innerRun$1(FetchSearchPhase.java:119) [elasticsearch-7.9.3.jar:7.9.3]",
"\tat org.elasticsearch.action.search.FetchSearchPhase.innerRun(FetchSearchPhase.java:125) [elasticsearch-7.9.3.jar:7.9.3]",
"\tat org.elasticsearch.action.search.FetchSearchPhase.access$000(FetchSearchPhase.java:47) [elasticsearch-7.9.3.jar:7.9.3]",
"\tat org.elasticsearch.action.search.FetchSearchPhase$1.doRun(FetchSearchPhase.java:95) [elasticsearch-7.9.3.jar:7.9.3]",
"\tat org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37) [elasticsearch-7.9.3.jar:7.9.3]",
"\tat org.elasticsearch.common.util.concurrent.TimedRunnable.doRun(TimedRunnable.java:44) [elasticsearch-7.9.3.jar:7.9.3]",
"\tat org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.doRun(ThreadContext.java:737) [elasticsearch-7.9.3.jar:7.9.3]",
"\tat org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37) [elasticsearch-7.9.3.jar:7.9.3]",
"\tat java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130) [?:?]",
"\tat java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630) [?:?]",
"\tat java.lang.Thread.run(Thread.java:832) [?:?]"] }
{"type": "server", "timestamp": "2021-12-30T11:22:13,442Z", "level": "WARN", "component": "r.suppressed", "cluster.name": "test-7-9-3", "node.name": "test-7-9-3-es-default-0", "message": "path: /_sql, params: {pretty=true, format=txt}", "cluster.uuid": "71JWVjUCRVGbAA9JCpZYWw", "node.id": "dWoWAYBgTIS7-HQeKmKwrQ" ,
"stacktrace": ["org.elasticsearch.xpack.sql.SqlIllegalArgumentException: The default limit [65535] for aggregate sorting has been reached; please specify a LIMIT",
"at org.elasticsearch.xpack.sql.execution.search.Querier$LocalAggregationSorterListener.consumeRowSet(Querier.java:252) [x-pack-sql-7.9.3.jar:7.9.3]",
"at org.elasticsearch.xpack.sql.execution.search.Querier$LocalAggregationSorterListener.onResponse(Querier.java:228) [x-pack-sql-7.9.3.jar:7.9.3]",
"at org.elasticsearch.xpack.sql.execution.search.Querier$LocalAggregationSorterListener.onResponse(Querier.java:184) [x-pack-sql-7.9.3.jar:7.9.3]",
"at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:63) [elasticsearch-7.9.3.jar:7.9.3]",
"at org.elasticsearch.xpack.sql.execution.search.CompositeAggCursor.handle(CompositeAggCursor.java:195) [x-pack-sql-7.9.3.jar:7.9.3]",
"at org.elasticsearch.xpack.sql.execution.search.CompositeAggCursor$1.onResponse(CompositeAggCursor.java:140) [x-pack-sql-7.9.3.jar:7.9.3]",
"at org.elasticsearch.xpack.sql.execution.search.CompositeAggCursor$1.onResponse(CompositeAggCursor.java:137) [x-pack-sql-7.9.3.jar:7.9.3]",
"at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:89) [elasticsearch-7.9.3.jar:7.9.3]",
"at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:83) [elasticsearch-7.9.3.jar:7.9.3]",
"at org.elasticsearch.action.support.ContextPreservingActionListener.onResponse(ContextPreservingActionListener.java:43) [elasticsearch-7.9.3.jar:7.9.3]",
"at org.elasticsearch.action.search.AbstractSearchAsyncAction.sendSearchResponse(AbstractSearchAsyncAction.java:545) [elasticsearch-7.9.3.jar:7.9.3]",
"at org.elasticsearch.action.search.ExpandSearchPhase.run(ExpandSearchPhase.java:117) [elasticsearch-7.9.3.jar:7.9.3]",
"at org.elasticsearch.action.search.AbstractSearchAsyncAction.executePhase(AbstractSearchAsyncAction.java:350) [elasticsearch-7.9.3.jar:7.9.3]",
"at org.elasticsearch.action.search.AbstractSearchAsyncAction.executeNextPhase(AbstractSearchAsyncAction.java:344) [elasticsearch-7.9.3.jar:7.9.3]",
"at org.elasticsearch.action.search.FetchSearchPhase.moveToNextPhase(FetchSearchPhase.java:231) [elasticsearch-7.9.3.jar:7.9.3]",
"at org.elasticsearch.action.search.FetchSearchPhase.lambda$innerRun$1(FetchSearchPhase.java:119) [elasticsearch-7.9.3.jar:7.9.3]",
"at org.elasticsearch.action.search.FetchSearchPhase.innerRun(FetchSearchPhase.java:125) [elasticsearch-7.9.3.jar:7.9.3]",
"at org.elasticsearch.action.search.FetchSearchPhase.access$000(FetchSearchPhase.java:47) [elasticsearch-7.9.3.jar:7.9.3]",
"at org.elasticsearch.action.search.FetchSearchPhase$1.doRun(FetchSearchPhase.java:95) [elasticsearch-7.9.3.jar:7.9.3]",
"at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37) [elasticsearch-7.9.3.jar:7.9.3]",
"at org.elasticsearch.common.util.concurrent.TimedRunnable.doRun(TimedRunnable.java:44) [elasticsearch-7.9.3.jar:7.9.3]",
"at org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.doRun(ThreadContext.java:737) [elasticsearch-7.9.3.jar:7.9.3]",
"at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37) [elasticsearch-7.9.3.jar:7.9.3]",
"at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130) [?:?]",
"at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630) [?:?]",
"at java.lang.Thread.run(Thread.java:832) [?:?]"] }

@bxl , thanks for the report. This has been a defect fixed in 7.10.

1 Like

:+1: , Thanks.

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