Xpack SQL cannot find an index with "Join" relationship in mapping (ES 7.11.2)

[qliangGH]

I tried to SQL-Query (ES 7.11.2) an index with a mapping with a parent-children "join" relation, but got immediate error.

1. Create an index with mapping like this:
   PUT blog_index { "mappings": { "properties": { "blog_comment_join": { "type": "join", "relations": { "blog": "comment" } } } } }

2. Sql query (below) in Kibana dev tool:
   POST /_xpack/sql?format=csv { "query": "SELECT count(1) FROM blog_index" }

3. Got this immediate error:
   { "error" : { "root_cause" : [ { "type" : "verification_exception", "reason" : "Found 1 problem\nline 1:22: Unknown index [blog_index]" } ], "type" : "verification_exception", "reason" : "Found 1 problem\nline 1:22: Unknown index [blog_index]" }, "status" : 400 }

Of course the index exists, if you DSL query this:
GET blog_index/_search?size=250 { "query": { "match_all": {} } }

I think it's a bug but it's too apparent to believe.
Can anyone confirm it?

Thanks!

[matriv]

Hello @qliangGH !

So, indeed this behaviour is reproduced for 7.11 but it has been fixed from 7.12 onwards with: #68802

Please upgrade to the latest stable 7.12 (currently 7.12.1) to solve this issue.
This happens because the index is empty and therefore before the mentioned changed it was not recognised as a valid but empty index.

I'm closing this issue, thx a lot for reporting!

[elasticmachine]

Pinging @elastic/es-ql (Team:QL)