VerificationException: When using multiple indices, different mappings are not allowed for the same field

[thincal]

Environment

• ES: 7.2.0
• sql plugin: 1.2.0

Reproduce step

Create two docs in different index as bellow:

POST test_multi_mapping_01/_doc/
{
  "a": {
    "b": 1
  }
}

POST test_multi_mapping_02/_doc/
{
  "a": {
    "b": 1,
    "c": 2
  }
}

Report error when apply the multi-index query via. SQL:

GET _opendistro/_sql
{
  "query": "SELECT * FROM test_multi_mapping_*"
}

{
  "error": {
    "reason": "Invalid SQL query",
    "details": "Different mappings are not allowed for the same field[a]: found [{properties:{b:{type:long},c:{type:long}}}] and [{properties:{b:{type:long}}}] ",
    "type": "VerificationException"
  },
  "status": 400
}

#46 is trying to fix the top level field missing, this case is happened in a nested field which should still be common.

[penghuo]

@thincal Thanks for reporting the issue. We will investigate the issue and get back to you asap.

[horacimacias]

I'm having the same issue. I opened sometime ago #46 and apparently that was closed?
I just saw the exact same problem in opendistro 1.6.0.

Also in my case, in case this helps, I'm doing a "select field from index" query, but the error response I get is complaining about another field I'm not selecting.

E.g.

com.amazon.opendistroforelasticsearch.sql.rewriter.matchtoterm.VerificationException: Different mappings are not allowed for the same field[kubernetes]

but I'm not searching on that field ?! Shouldn't elastic just ignore that field completely if it's not used in the select query?

[penghuo]

Hi,
This is our current implementation tradeoff. The type compatibility check will failed when same field name have different type in different index regardless the field in select or not.

https://github.com/opendistro-for-elasticsearch/sql/blame/master/src/main/java/com/amazon/opendistroforelasticsearch/sql/rewriter/matchtoterm/TermFieldRewriter.java#L294

[horacimacias]

thanks.
Doesn't the comment in #46 (comment) mention that both cases should be working?

Case-1: Selecting field common to all documents/indices.
"select field from myindex*" returns "When using multiple indices, the mappings must be identical."

Case-2 : Selecting both fields on "myindex*".
"select field,field2 from myindex*" returns "When using multiple indices, the mappings must be identical."

That looks like the current scenario and the fix was meant to be coming in 1.1.0+ ?

Independently of previous issues/fixes, doesn't it make sense to only verifySingleFieldMapping for the fields that have been specified in the select query?
At least queries would work if you select fields whose mapping is identical.

[nika11]

Do you know if this can be supported in elasticsearch 6.8? I am hitting same problem in this version.

[penghuo]

@nika11 I do not think so. We didn't back-porting feature to old version.