SQL API VerificationException when index contains rank_feature or rank_features field mapping

[russcam]

Elasticsearch version (bin/elasticsearch --version):

Version: 7.2.0, Build: default/zip/508c38a/2019-06-20T15:54:18.811730Z, JVM: 12.0.1

But tested against 7.0.0, 7.1.0 and 7.2.0

Plugins installed: []

JVM version (java -version):

java version "1.8.0_201"
Java(TM) SE Runtime Environment (build 1.8.0_201-b09)
Java HotSpot(TM) 64-Bit Server VM (build 25.201-b09, mixed mode)

OS version (uname -a if on a Unix-like system):

Windows 10 Professional 64 Bit

 [System.Environment]::OSVersion.Version

Major  Minor  Build  Revision
-----  -----  -----  --------
10     0      18362  0

Description of the problem including expected versus actual behavior:

When an index contains a rank_feature or rank_features field mapping, and a SQL query is executed using the SQL REST API, the request fails with a 400 Bad Request response

HTTP/1.1 400 Bad Request
content-type: application/json; charset=UTF-8
content-length: 6894

{
  "error" : {
    "root_cause" : [
      {
        "type" : "verification_exception",
        "reason" : "Found 1 problem(s)\nline 2:6: Unknown index [project]",
        "stack_trace" : "VerificationException[Found 1 problem(s)\nline 2:6: Unknown index [project]]\r\n\tat org.elasticsearch.xpack.sql.analysis.analyzer.Analyzer.verify(Analyzer.java:142)\r\n\tat org.elasticsearch.xpack.sql.analysis.analyzer.Analyzer.analyze(Analyzer.java:132)\r\n\tat org.elasticsearch.xpack.sql.session.SqlSession.lambda$analyzedPlan$0(SqlSession.java:108)\r\n\tat org.elasticsearch.xpack.sql.session.SqlSession.lambda$preAnalyze$2(SqlSession.java:140)\r\n\tat org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:62)\r\n\tat org.elasticsearch.xpack.sql.analysis.index.IndexResolver.lambda$resolveAsMergedMapping$3(IndexResolver.java:249)\r\n\tat org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:62)\r\n\tat org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:68)\r\n\tat org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:64)\r\n\tat org.elasticsearch.action.support.ContextPreservingActionListener.onResponse(ContextPreservingActionListener.java:43)\r\n\tat org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesAction.lambda$doExecute$1(TransportFieldCapabilitiesAction.java:88)\r\n\tat org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesAction$1.onFailure(TransportFieldCapabilitiesAction.java:107)\r\n\tat org.elasticsearch.action.support.TransportAction$1.onFailure(TransportAction.java:74)\r\n\tat org.elasticsearch.action.support.ContextPreservingActionListener.onFailure(ContextPreservingActionListener.java:50)\r\n\tat org.elasticsearch.action.support.single.shard.TransportSingleShardAction$AsyncSingleAction.perform(TransportSingleShardAction.java:234)\r\n\tat org.elasticsearch.action.support.single.shard.TransportSingleShardAction$AsyncSingleAction.onFailure(TransportSingleShardAction.java:215)\r\n\tat org.elasticsearch.action.support.single.shard.TransportSingleShardAction$AsyncSingleAction.access$1100(TransportSingleShardAction.java:141)\r\n\tat org.elasticsearch.action.support.single.shard.TransportSingleShardAction$AsyncSingleAction$2.handleException(TransportSingleShardAction.java:271)\r\n\tat org.elasticsearch.transport.TransportService$ContextRestoreResponseHandler.handleException(TransportService.java:1111)\r\n\tat org.elasticsearch.transport.TransportService$DirectResponseChannel.processException(TransportService.java:1223)\r\n\tat org.elasticsearch.transport.TransportService$DirectResponseChannel.sendResponse(TransportService.java:1197)\r\n\tat org.elasticsearch.transport.TaskTransportChannel.sendResponse(TaskTransportChannel.java:60)\r\n\tat org.elasticsearch.action.support.ChannelActionListener.onFailure(ChannelActionListener.java:56)\r\n\tat org.elasticsearch.action.ActionRunnable.onFailure(ActionRunnable.java:42)\r\n\tat org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.onFailure(ThreadContext.java:743)\r\n\tat org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:39)\r\n\tat java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)\r\n\tat java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)\r\n\tat java.base/java.lang.Thread.run(Thread.java:835)\r\n"
      }
    ],
    "type" : "verification_exception",
    "reason" : "Found 1 problem(s)\nline 2:6: Unknown index [project]",
    "stack_trace" : "VerificationException[Found 1 problem(s)\nline 2:6: Unknown index [project]]\r\n\tat org.elasticsearch.xpack.sql.analysis.analyzer.Analyzer.verify(Analyzer.java:142)\r\n\tat org.elasticsearch.xpack.sql.analysis.analyzer.Analyzer.analyze(Analyzer.java:132)\r\n\tat org.elasticsearch.xpack.sql.session.SqlSession.lambda$analyzedPlan$0(SqlSession.java:108)\r\n\tat org.elasticsearch.xpack.sql.session.SqlSession.lambda$preAnalyze$2(SqlSession.java:140)\r\n\tat org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:62)\r\n\tat org.elasticsearch.xpack.sql.analysis.index.IndexResolver.lambda$resolveAsMergedMapping$3(IndexResolver.java:249)\r\n\tat org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:62)\r\n\tat org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:68)\r\n\tat org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:64)\r\n\tat org.elasticsearch.action.support.ContextPreservingActionListener.onResponse(ContextPreservingActionListener.java:43)\r\n\tat org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesAction.lambda$doExecute$1(TransportFieldCapabilitiesAction.java:88)\r\n\tat org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesAction$1.onFailure(TransportFieldCapabilitiesAction.java:107)\r\n\tat org.elasticsearch.action.support.TransportAction$1.onFailure(TransportAction.java:74)\r\n\tat org.elasticsearch.action.support.ContextPreservingActionListener.onFailure(ContextPreservingActionListener.java:50)\r\n\tat org.elasticsearch.action.support.single.shard.TransportSingleShardAction$AsyncSingleAction.perform(TransportSingleShardAction.java:234)\r\n\tat org.elasticsearch.action.support.single.shard.TransportSingleShardAction$AsyncSingleAction.onFailure(TransportSingleShardAction.java:215)\r\n\tat org.elasticsearch.action.support.single.shard.TransportSingleShardAction$AsyncSingleAction.access$1100(TransportSingleShardAction.java:141)\r\n\tat org.elasticsearch.action.support.single.shard.TransportSingleShardAction$AsyncSingleAction$2.handleException(TransportSingleShardAction.java:271)\r\n\tat org.elasticsearch.transport.TransportService$ContextRestoreResponseHandler.handleException(TransportService.java:1111)\r\n\tat org.elasticsearch.transport.TransportService$DirectResponseChannel.processException(TransportService.java:1223)\r\n\tat org.elasticsearch.transport.TransportService$DirectResponseChannel.sendResponse(TransportService.java:1197)\r\n\tat org.elasticsearch.transport.TaskTransportChannel.sendResponse(TaskTransportChannel.java:60)\r\n\tat org.elasticsearch.action.support.ChannelActionListener.onFailure(ChannelActionListener.java:56)\r\n\tat org.elasticsearch.action.ActionRunnable.onFailure(ActionRunnable.java:42)\r\n\tat org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.onFailure(ThreadContext.java:743)\r\n\tat org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:39)\r\n\tat java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)\r\n\tat java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)\r\n\tat java.base/java.lang.Thread.run(Thread.java:835)\r\n"
  },
  "status" : 400
}

Steps to reproduce:

1. Create an index without a rank_feature or rank_features field mapping

PUT http://127.0.0.1:9200/project?pretty
{ "mappings": { "properties": { "branches": { "type": "keyword" }}}}

2. Execute a SQL REST API query and note that the request succeeds and returns a 200 OK response

POST http://127.0.0.1:9200/_sql?pretty=true&error_trace=true
{"fetch_size":5,"query":"SELECT branches\r\nFROM project"}

// Response

HTTP/1.1 200 OK
content-type: application/json; charset=UTF-8
content-length: 60

{"columns":[{"name":"branches","type":"keyword"}],"rows":[]}

3. Delete the project index and recreate, this time including a rank_feature or rank_features field mapping

DELETE /project

PUT http://127.0.0.1:9200/project?pretty
{ "mappings": { "properties": { "branches": { "type": "keyword" }, "rank" : { "type": "rank_feature" }}}}

4. Execute the same SQL REST API query and note that the request now fails and returns a 400 Bad Request response

POST http://127.0.0.1:9200/_sql?pretty=true&error_trace=true
{"fetch_size":5,"query":"SELECT branches\r\nFROM project"}

// Response

HTTP/1.1 400 Bad Request
content-type: application/json; charset=UTF-8
content-length: 6396

{
  "error" : {
    "root_cause" : [
      {
        "type" : "verification_exception",
        "reason" : "Found 1 problem(s)\nline 2:6: Unknown index [project]",
        "stack_trace" : "VerificationException[Found 1 problem(s)\nline 2:6: Unknown index [project]]\r\n\tat org.elasticsearch.xpack.sql.analysis.analyzer.Analyzer.verify(Analyzer.java:142)\r\n\tat org.elasticsearch.xpack.sql.analysis.analyzer.Analyzer.analyze(Analyzer.java:132)\r\n\tat org.elasticsearch.xpack.sql.session.SqlSession.lambda$analyzedPlan$0(SqlSession.java:108)\r\n\tat org.elasticsearch.xpack.sql.session.SqlSession.lambda$preAnalyze$2(SqlSession.java:140)\r\n\tat org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:62)\r\n\tat org.elasticsearch.xpack.sql.analysis.index.IndexResolver.lambda$resolveAsMergedMapping$3(IndexResolver.java:249)\r\n\tat org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:62)\r\n\tat org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:68)\r\n\tat org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:64)\r\n\tat org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesAction.lambda$doExecute$1(TransportFieldCapabilitiesAction.java:88)\r\n\tat org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesAction$1.onFailure(TransportFieldCapabilitiesAction.java:107)\r\n\tat org.elasticsearch.action.support.TransportAction$1.onFailure(TransportAction.java:74)\r\n\tat org.elasticsearch.action.support.single.shard.TransportSingleShardAction$AsyncSingleAction.perform(TransportSingleShardAction.java:234)\r\n\tat org.elasticsearch.action.support.single.shard.TransportSingleShardAction$AsyncSingleAction.onFailure(TransportSingleShardAction.java:215)\r\n\tat org.elasticsearch.action.support.single.shard.TransportSingleShardAction$AsyncSingleAction.access$1100(TransportSingleShardAction.java:141)\r\n\tat org.elasticsearch.action.support.single.shard.TransportSingleShardAction$AsyncSingleAction$2.handleException(TransportSingleShardAction.java:271)\r\n\tat org.elasticsearch.transport.TransportService$ContextRestoreResponseHandler.handleException(TransportService.java:1111)\r\n\tat org.elasticsearch.transport.TransportService$DirectResponseChannel.processException(TransportService.java:1223)\r\n\tat org.elasticsearch.transport.TransportService$DirectResponseChannel.sendResponse(TransportService.java:1197)\r\n\tat org.elasticsearch.transport.TaskTransportChannel.sendResponse(TaskTransportChannel.java:60)\r\n\tat org.elasticsearch.action.support.ChannelActionListener.onFailure(ChannelActionListener.java:56)\r\n\tat org.elasticsearch.action.ActionRunnable.onFailure(ActionRunnable.java:42)\r\n\tat org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.onFailure(ThreadContext.java:743)\r\n\tat org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:39)\r\n\tat java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)\r\n\tat java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)\r\n\tat java.base/java.lang.Thread.run(Thread.java:835)\r\n"
      }
    ],
    "type" : "verification_exception",
    "reason" : "Found 1 problem(s)\nline 2:6: Unknown index [project]",
    "stack_trace" : "VerificationException[Found 1 problem(s)\nline 2:6: Unknown index [project]]\r\n\tat org.elasticsearch.xpack.sql.analysis.analyzer.Analyzer.verify(Analyzer.java:142)\r\n\tat org.elasticsearch.xpack.sql.analysis.analyzer.Analyzer.analyze(Analyzer.java:132)\r\n\tat org.elasticsearch.xpack.sql.session.SqlSession.lambda$analyzedPlan$0(SqlSession.java:108)\r\n\tat org.elasticsearch.xpack.sql.session.SqlSession.lambda$preAnalyze$2(SqlSession.java:140)\r\n\tat org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:62)\r\n\tat org.elasticsearch.xpack.sql.analysis.index.IndexResolver.lambda$resolveAsMergedMapping$3(IndexResolver.java:249)\r\n\tat org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:62)\r\n\tat org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:68)\r\n\tat org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:64)\r\n\tat org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesAction.lambda$doExecute$1(TransportFieldCapabilitiesAction.java:88)\r\n\tat org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesAction$1.onFailure(TransportFieldCapabilitiesAction.java:107)\r\n\tat org.elasticsearch.action.support.TransportAction$1.onFailure(TransportAction.java:74)\r\n\tat org.elasticsearch.action.support.single.shard.TransportSingleShardAction$AsyncSingleAction.perform(TransportSingleShardAction.java:234)\r\n\tat org.elasticsearch.action.support.single.shard.TransportSingleShardAction$AsyncSingleAction.onFailure(TransportSingleShardAction.java:215)\r\n\tat org.elasticsearch.action.support.single.shard.TransportSingleShardAction$AsyncSingleAction.access$1100(TransportSingleShardAction.java:141)\r\n\tat org.elasticsearch.action.support.single.shard.TransportSingleShardAction$AsyncSingleAction$2.handleException(TransportSingleShardAction.java:271)\r\n\tat org.elasticsearch.transport.TransportService$ContextRestoreResponseHandler.handleException(TransportService.java:1111)\r\n\tat org.elasticsearch.transport.TransportService$DirectResponseChannel.processException(TransportService.java:1223)\r\n\tat org.elasticsearch.transport.TransportService$DirectResponseChannel.sendResponse(TransportService.java:1197)\r\n\tat org.elasticsearch.transport.TaskTransportChannel.sendResponse(TaskTransportChannel.java:60)\r\n\tat org.elasticsearch.action.support.ChannelActionListener.onFailure(ChannelActionListener.java:56)\r\n\tat org.elasticsearch.action.ActionRunnable.onFailure(ActionRunnable.java:42)\r\n\tat org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.onFailure(ThreadContext.java:743)\r\n\tat org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:39)\r\n\tat java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)\r\n\tat java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)\r\n\tat java.base/java.lang.Thread.run(Thread.java:835)\r\n"
  },
  "status" : 400
}

Provide logs (if relevant):

The Elasticsearch logs indicate that the issue may be in the field_caps API call that SQL uses

[2019-07-15T12:26:19,646][INFO ][o.e.c.m.MetaDataCreateIndexService] [FORLOOP-XTREME] [project] creating index, cause [api], templates [], shards [1]/[1], mappings [_doc]
[2019-07-15T12:27:16,775][DEBUG][o.e.a.f.TransportFieldCapabilitiesIndexAction] [FORLOOP-XTREME] null: failed to execute [org.elasticsearch.action.fieldcaps.FieldCapabilitiesIndexRequest@53d9c826]
org.elasticsearch.transport.RemoteTransportException: [FORLOOP-XTREME][127.0.0.1:9300][indices:data/read/field_caps[index][s]]
Caused by: java.lang.UnsupportedOperationException: [rank_feature] fields do not support sorting, scripting or aggregating
        at org.elasticsearch.index.mapper.RankFeatureFieldMapper$RankFeatureFieldType.fielddataBuilder(RankFeatureFieldMapper.java:167) ~[?:?]
        at org.elasticsearch.index.mapper.MappedFieldType.isAggregatable(MappedFieldType.java:303) ~[elasticsearch-7.2.0.jar:7.2.0]

        at org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesIndexAction.shardOperation(TransportFieldCapabilitiesIndexAction.java:89) ~[elasticsearch-7.2.0.jar:7.2.0]
        at org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesIndexAction.shardOperation(TransportFieldCapabilitiesIndexAction.java:46) ~[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:1128) [?:?]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
        at java.lang.Thread.run(Thread.java:835) [?:?]

[elasticmachine]

Pinging @elastic/es-search

[astefan]

Fails on master as well. The culprit is the output of fieldcaps_api which, if used outside the SQL context, fails to retrieve any result for the index containing a rank_feature field. Created #44330 to report this.

[astefan]

The Elasticsearch issue was fixed. Closing this one, as a consequence.

[codebrain]

Did this make it into 7.3? I am having trouble locating it on the release notes.

[astefan]

@codebrain judging by the commit it ended up on 7.3 branch. And by the version labels on the PR itself it should have been included in 7.3.0.