# Sql query cannot be closed

**URL:** <https://discuss.elastic.co/t/sql-query-cannot-be-closed/293192>\
**Category:** Elasticsearch\
**Tags:** elastic-stack-sql\
**Created:** [December 30, 2021, 9:43am UTC](https://discuss.elastic.co/t/sql-query-cannot-be-closed/293192 "2021-12-30T09:43:29Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![bxl](https://avatars.discourse-cdn.com/v4/letter/b/aca169/32.png) [@bxl](https://discuss.elastic.co/u/bxl)\
**Post date:** [December 30, 2021, 9:43am UTC](https://discuss.elastic.co/t/sql-query-cannot-be-closed/293192/1 "2021-12-30T09:43:29Z")

</div>

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

query

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

```

error

```auto
{
  "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

```auto
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?

```auto
        {
  "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

```auto
[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) [?:?]"] }

```

---

<div class="post-metadata">

**Author:** ![bogdan.pintea](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bogdan.pintea/32/45740_2.png) [@bogdan.pintea](https://discuss.elastic.co/u/bogdan.pintea)\
**Post date:** [January 6, 2022, 3:52pm UTC](https://discuss.elastic.co/t/sql-query-cannot-be-closed/293192/2 "2022-01-06T15:52:06Z")

</div>

@bxl , thanks for the report. This has been a [defect fixed](https://github.com/elastic/elasticsearch/pull/65687) in 7.10.

---

<div class="post-metadata">

**Author:** ![bxl](https://avatars.discourse-cdn.com/v4/letter/b/aca169/32.png) [@bxl](https://discuss.elastic.co/u/bxl)\
**Post date:** [January 10, 2022, 8:29am UTC](https://discuss.elastic.co/t/sql-query-cannot-be-closed/293192/3 "2022-01-10T08:29:41Z")

</div>

👍 , Thanks.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [February 7, 2022, 8:29am UTC](https://discuss.elastic.co/t/sql-query-cannot-be-closed/293192/4 "2022-02-07T08:29:46Z")

</div>

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