Type not supported exception, index mapping ignored


I am encountering an issue with canvas: when querying all fields of a particular index with essql or esdocs the canvas element fails with the following error message:

[esdocs] > Unexpected error from Elasticsearch: [sql_illegal_argument_exception] Type Integer (returned by [telegramlog.telegram.loadInformation]) is not supported

This seems odd to me since the field in question is explicitly mapped as a text field in the index mapping which we configure via template:

"telegramlog.telegram.loadInformation": {
      "type": "text",
      "fields": {
        "keyword": {
          "type": "keyword",
          "ignore_above": 256

And also appears as text in the discover view:

Any idea what might cause this or how to work around it?

Thanks and regards

@tarkin67 if you run a request straight to ES-SQL (bypassing Canvas) do you get an error?

Yes, executing the elastic sql request from the management console throws the same error.

@tarkin67 I have few questions to help with the investigation:

  • what is the query you are running?
  • Is it against one index or multiple?
    • If multiple, are you sure all the indices have telegramlog.telegram.loadInformation mapped as text?
  • what ES version is this?
  • can you share the error stacktrace from ES logs?


Sure, I run the following from the management console:

GET _xpack/sql
      "query":"select * from \"wm6-telegramlog-*\""

This matches exactly one index (wm6-telegramlog-2019.02-v1) that has the telegramlog.telegram.loadInformation field mapped as text. Replacing the wildcard index name in the query with the exact index name does not make any difference.

Running a standard query from the console (GET wm6-telegramlog-*/_search) works fine. However, when querying the index from canvas, it does not make any difference whether I am using the essql or esdocs function, both will throw the same error as the ES-SQL query above.

I am running ES and Kibana 6.6.1. The character limit does not let me paste the stacktrace here so I uploaded it to textuploader.com, you can access it here.

Thanks for your help

@tarkin67 thank you for the additional details. One more ask, if possible: can you provide the complete mapping and settings (GET /wm6-telegramlog-2019.02-v1) of this index?

Sure! I had to upload it to TextUploader again, sorry for the inconvenience: https://textuploader.com/15rfs


@tarkin67 I've performed some tests and I can only get that error if:

  • I create a new index called wm6-telegramlog-2019.02-v2 and I remove from the mapping the loadInformation subfield
  • I index the following document, forcing Elasticsearch to create to auto-create the loadInformation subfield with the type long (yes, not integer):
  • run this query select * from \"wm6-telegramlog-*\" so that my query touches both these indices with different mappings.
  • are you very sure that the scenario above doesn't happen in your case? Can you, please, double check the index being queried, its mapping and the query itself?

Leaving aside the way you tested this, I think there is an issue here. ES-SQL should have thrown an error for a situation where the mappings for the same field are different (considering my scenario above - telegramlog.telegram.loadInformation being both text and long in two different indices). Still looking into this one...

@tarkin67 after some investigation, I've created this issue: https://github.com/elastic/elasticsearch/issues/39547. Still waiting to hear from you if the query is actually somehow touching two different indices, but even if not (which is a different problem somehow), the issue I just opened is something we need to fix.

Thank you for reporting it.

@Andrei_Stefan thank you for looking into this and creating the issue!

I just quickly double checked that the query only touches one index and to my knowledge it does. There exists exactly one wm6-telegramlog-2019.02-v1 index and none of the other indices have any aliases configured that would match the wm6-telegramlog-* pattern in the query. Plus I also get the error if I specify the exact index name like so:

GET _xpack/sql
  "query":"select * from \"wm6-telegramlog-2019.02-v1\""

The indexed documents themselves contain integers in the loadInformation field

But the field is mapped as text in the wm6-telegramlog index mapping:

So as long as elasticsearch does not do some kind of voodoo I don't understand, only that one index is touched by the query which has the field mapped as text.


@tarkin67 that bug is already fixed and will be available first in the versions mentioned in the corresponding PR. If you could test this when any of those versions get released, I would be interested in seeing if it's fixing this issue you have. Thanks.

Sure, I'll let you know once I got to test it.

So I finally got around to test with elastic and kibana 6.6.2.

Turns out I still get the exact same error when querying the telegramlog index:

  "error": {
    "root_cause": [
        "type": "sql_illegal_argument_exception",
        "reason": "Type Integer (returned by [telegramlog.telegram.loadInformation]) is not supported"
    "type": "sql_illegal_argument_exception",
    "reason": "Type Integer (returned by [telegramlog.telegram.loadInformation]) is not supported"
  "status": 500

Here's the stacktrace. Hope this helps!

Thanks and regards

@tarkin67 you are correct. Apologies. It was a slight overlook from our side. The fix was backported to 6.6 now. It should be available in 6.6.3. Sorry about that.

alright, I'll check again once 6.6.3 is available :slight_smile:

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