# Kibana Canvas SQL Error

**URL:** <https://discuss.elastic.co/t/kibana-canvas-sql-error/187637>\
**Category:** Kibana\
**Tags:** canvas\
**Created:** [June 26, 2019, 5:19pm UTC](https://discuss.elastic.co/t/kibana-canvas-sql-error/187637 "2019-06-26T17:19:15Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![AdrianSanchezLopez](https://avatars.discourse-cdn.com/v4/letter/a/f19dbf/32.png) [@AdrianSanchezLopez](https://discuss.elastic.co/u/AdrianSanchezLopez)\
**Post date:** [June 26, 2019, 5:19pm UTC](https://discuss.elastic.co/t/kibana-canvas-sql-error/187637/1 "2019-06-26T17:19:15Z")

</div>

I just Updated to Elasticsearch 7.2 and now I'm getting this error when using Canvas with Elasticsearch SQL data:

```auto
Whoops! Expression failed
Expression failed with the message:

[essql] > Unexpected error from Elasticsearch: [illegal_argument_exception] Illegal Capacity: -1

```

Before the update it was working correctly, not sure if it's directly connected to the new update or not, but seems probable.  
The SQL data query must correct because it use to work (yesterday and before)

I could not find any help regarding to this error, does anyone have a clue?

---

<div class="post-metadata">

**Author:** ![tims](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tims/32/46750_2.png) [@tims](https://discuss.elastic.co/u/tims)\
**Post date:** [June 28, 2019, 1:36pm UTC](https://discuss.elastic.co/t/kibana-canvas-sql-error/187637/2 "2019-06-28T13:36:21Z")

</div>

Hi @AdrianSanchezLopez,

Can you share your SQL query?

---

<div class="post-metadata">

**Author:** ![tarkin67](https://avatars.discourse-cdn.com/v4/letter/t/8797f3/32.png) [@tarkin67](https://discuss.elastic.co/u/tarkin67)\
**Post date:** [July 1, 2019, 9:07am UTC](https://discuss.elastic.co/t/kibana-canvas-sql-error/187637/3 "2019-07-01T09:07:03Z")

</div>

Hi @tims

I am getting the same error for the following essql query:

> `SELECT "updateDate", "searchBaseType", "summary", "hostmessage.messageString", "hostmessage.responseString", "transaction.fromOrderId", "transaction.toOrderId", "transaction.fromLocationId", "transaction.toLocationId", "transaction.transactionType" FROM "wm6-orderstatusaudittrail" order by canvasTimefilterField desc`

Elastic throws:

```
{
  "error": {
    "root_cause": [
      {
        "type": "illegal_argument_exception",
        "reason": "Illegal Capacity: -5"
      }
    ],
    "type": "illegal_argument_exception",
    "reason": "Illegal Capacity: -5"
  },
  "status": 400
}

```

This happens regardless of wheter I perform the query from Canvas or from the Kibana Management Console.

After setting the logging level of Elasticsearch to DEBUG I got the following Stacktrace:

```
[2019-07-01T11:00:13,668][DEBUG][r.suppressed] [SCHB7M-000461] path: /_sql, params: {pretty=, format=txt}
java.lang.IllegalArgumentException: Illegal Capacity: -5
        at java.util.ArrayList.<init>(ArrayList.java:157) ~[?:1.8.0_202]
        at org.elasticsearch.xpack.sql.analysis.index.IndexResolver.buildIndices(IndexResolver.java:463) ~[?:?]
        at org.elasticsearch.xpack.sql.analysis.index.IndexResolver.mergedMappings(IndexResolver.java:259) ~[?:?]
        at org.elasticsearch.xpack.sql.analysis.index.IndexResolver.lambda$resolveAsMergedMapping$3(IndexResolver.java:249) ~[?:?]
        at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:62) ~[elasticsearch-7.2.0.jar:7.2.0]
        at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:68) ~[elasticsearch-7.2.0.jar:7.2.0]
        at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:64) ~[elasticsearch-7.2.0.jar:7.2.0]
        at org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesAction.lambda$doExecute$1(TransportFieldCapabilitiesAction.java:88) ~[elasticsearch-7.2.0.jar:7.2.0]
        at org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesAction$1.onResponse(TransportFieldCapabilitiesAction.java:101) [elasticsearch-7.2.0.jar:7.2.0]
        at org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesAction$1.onResponse(TransportFieldCapabilitiesAction.java:97) [elasticsearch-7.2.0.jar:7.2.0]
        at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:68) [elasticsearch-7.2.0.jar:7.2.0]
        at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:64) [elasticsearch-7.2.0.jar:7.2.0]
        at org.elasticsearch.action.support.single.shard.TransportSingleShardAction$AsyncSingleAction$2.handleResponse(TransportSingleShardAction.java:266) [elasticsearch-7.2.0.jar:7.2.0]
        at org.elasticsearch.action.support.single.shard.TransportSingleShardAction$AsyncSingleAction$2.handleResponse(TransportSingleShardAction.java:252) [elasticsearch-7.2.0.jar:7.2.0]
        at org.elasticsearch.transport.TransportService$ContextRestoreResponseHandler.handleResponse(TransportService.java:1101) [elasticsearch-7.2.0.jar:7.2.0]
        at org.elasticsearch.transport.TransportService$DirectResponseChannel.processResponse(TransportService.java:1182) [elasticsearch-7.2.0.jar:7.2.0]
        at org.elasticsearch.transport.TransportService$DirectResponseChannel.sendResponse(TransportService.java:1162) [elasticsearch-7.2.0.jar:7.2.0]
        at org.elasticsearch.transport.TaskTransportChannel.sendResponse(TaskTransportChannel.java:54) [elasticsearch-7.2.0.jar:7.2.0]
        at org.elasticsearch.action.support.ChannelActionListener.onResponse(ChannelActionListener.java:47) [elasticsearch-7.2.0.jar:7.2.0]
        at org.elasticsearch.action.support.ChannelActionListener.onResponse(ChannelActionListener.java:30) [elasticsearch-7.2.0.jar:7.2.0]
        at org.elasticsearch.action.support.single.shard.TransportSingleShardAction$1.doRun(TransportSingleShardAction.java:113) [elasticsearch-7.2.0.jar:7.2.0]
        at org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.doRun(ThreadContext.java:758) [elasticsearch-7.2.0.jar:7.2.0]
        at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37) [elasticsearch-7.2.0.jar:7.2.0]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_202]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_202]
        at java.lang.Thread.run(Thread.java:748) [?:1.8.0_202]

```

Hope this helps!

Thanks and best regards,  
Kasimir

---

<div class="post-metadata">

**Author:** ![tarkin67](https://avatars.discourse-cdn.com/v4/letter/t/8797f3/32.png) [@tarkin67](https://discuss.elastic.co/u/tarkin67)\
**Post date:** [July 1, 2019, 9:18am UTC](https://discuss.elastic.co/t/kibana-canvas-sql-error/187637/4 "2019-07-01T09:18:21Z")

</div>

Also it seems to be a problem with index aliases: Queries which query only one index seem to work fine but the "wm6-orderstatusaudittrail" index from above is an alias over three different indices and any essql query directed at that alias seems to throw the exeption above..

---

<div class="post-metadata">

**Author:** ![Sereno\_Polveroso](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sereno_polveroso/32/46276_2.png) [@Sereno\_Polveroso](https://discuss.elastic.co/u/Sereno_Polveroso)\
**Post date:** [July 1, 2019, 10:56am UTC](https://discuss.elastic.co/t/kibana-canvas-sql-error/187637/5 "2019-07-01T10:56:08Z")

</div>

Hi all,  
same problem here even with the simplest search on index patterns.  
Same problem even with Elasticsearch Docs datasource.

And I confirm that with a single index instead of an index pattern everything works fine

---

<div class="post-metadata">

**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\
**Post date:** [July 2, 2019, 7:08am UTC](https://discuss.elastic.co/t/kibana-canvas-sql-error/187637/6 "2019-07-02T07:08:29Z")

</div>

> [@tarkin67](#):
>
> IndexResolver.java:463

@tarkin67 any chance you could share the mapping of the indices (all three of them) you used in the test, please? This is definitely an ES SQL issue, judging by the stacktrace. But, a simple test I performed didn't reproduce this issue.

---

<div class="post-metadata">

**Author:** ![tarkin67](https://avatars.discourse-cdn.com/v4/letter/t/8797f3/32.png) [@tarkin67](https://discuss.elastic.co/u/tarkin67)\
**Post date:** [July 2, 2019, 7:58am UTC](https://discuss.elastic.co/t/kibana-canvas-sql-error/187637/7 "2019-07-02T07:58:23Z")

</div>

Sure, you can access it [here](https://textuploader.com/1dwsc), had to upload it there because of the char limit on posts.. As you can see, the alias is directed at more than three indices, sorry for the confusion.

Hope this helps!

---

<div class="post-metadata">

**Author:** ![AdrianSanchezLopez](https://avatars.discourse-cdn.com/v4/letter/a/f19dbf/32.png) [@AdrianSanchezLopez](https://discuss.elastic.co/u/AdrianSanchezLopez)\
**Post date:** [July 2, 2019, 9:31am UTC](https://discuss.elastic.co/t/kibana-canvas-sql-error/187637/8 "2019-07-02T09:31:17Z")

</div>

I can confirm that it is related to the number of index on the same index template.  
When using SQL on this index pattern:  
`index-2019*`  
on theses indexes:  
`index-2019-03`  
`index-2019-04`  
`index-2019-05`  
`index-2019-06`  
I get the error. But if I do a query to a single index without pattern it works.  
I did a workaround by re-indexing 03, 04, 05 onto the same index, and deleting the old ones.  
So I end up with "index-2019" and "index-2019-06" and with only those it seems to work.  
But the problem is still there until I get 4 indexes again ..

---

<div class="post-metadata">

**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\
**Post date:** [July 2, 2019, 12:12pm UTC](https://discuss.elastic.co/t/kibana-canvas-sql-error/187637/9 "2019-07-02T12:12:16Z")

</div>

@tarkin67 thank you, I can reproduce it now.

---

<div class="post-metadata">

**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\
**Post date:** [July 2, 2019, 1:36pm UTC](https://discuss.elastic.co/t/kibana-canvas-sql-error/187637/10 "2019-07-02T13:36:07Z")

</div>

> <https://github.com/elastic/elasticsearch/issues/43876>

---

<div class="post-metadata">

**Author:** ![Brian\_Frantz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/brian_frantz/32/15787_2.png) [@Brian\_Frantz](https://discuss.elastic.co/u/Brian_Frantz)\
**Post date:** [July 12, 2019, 9:13pm UTC](https://discuss.elastic.co/t/kibana-canvas-sql-error/187637/11 "2019-07-12T21:13:19Z")

</div>

I've created this bug since I am also seeing this issue with non-SQL queries, and Andrei's fix appears to be SQL-specific: [https://github.com/elastic/elasticsearch/issues/44296](https://github.com/elastic/elasticsearch/issues/44296)

---

<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:** [August 9, 2019, 9:25pm UTC](https://discuss.elastic.co/t/kibana-canvas-sql-error/187637/12 "2019-08-09T21:25:37Z")

</div>

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