[inner_hits] already contains an entry for key [xxx]

[Yuan_Hong]

Hi,

I have an index worktop_process with nested field:

{
  "orderId": {
    "type": "integer"
  },
  "shipments": {
    "type": "nested",
    "dynamic": "strict",
    "properties": {
      "estimatedReceivingDate": {
        "type": "date"
      },
      "packageId": {
        "type": "keyword"
      },
      "receivingDate": {
        "type": "date"
      }
    }
  }
}

And I have the following query to search for all packages that should have been received but not:

POST _sql?format=txt
{
"query": "select orderId, shipments.receivingDate from worktop_process where shipments.receivingDate is null and shipments.estimatedReceivingDate < now()"
}

It returns an exception:

"root_cause": [
  {
    "type": "illegal_argument_exception",
    "reason": "[inner_hits] already contains an entry for key [shipments]"
  }
]

But when I removed the nested field from sql return column list, the query works:

POST _sql?format=txt
{
"query": "select orderId from worktop_process where shipments.receivingDate is null and shipments.estimatedReceivingDate < now()"
}

How is this error to be understood and is there any way work around it?

Thanks in advance.

[Andrei_Stefan]

@Yuan_Hong thank you for posting the issue you have. Indeed, it's a bug. We were aware of it, but when we discovered it, it was behaving differently: the results of the query were incorrect. Now, there are no results and an error is thrown instead.

I've created an issue here.