"all shards fail" error when functions/operators are applied to null-value field

[chloe-zh]

Example index:

{"index":{"_index": "people", "_id":"1"}}
{"account_number":1,"balance":39225,"firstname":"Amber","lastname":"Duke","age":32,"gender":"M"}
{"index":{"_index": "people", "_id":"2"}}
{"account_number":6,"balance":5686,"firstname":"Hattie","lastname":"Bond","age":36,"gender":"M"}
{"index":{"_index": "people", "_id":"3"}}
{"account_number":13,"balance":32838,"firstname":"Nanette","age":28,"gender":"F"}
{"index":{"_index": "people", "_id":"4"}}
{"account_number":18,"firstname":"Dale","lastname":"Adams","age":33,"gender":"M"}

Example query 1:

SELECT abs(balance) FROM people

Expected result:

abs(balance)
39225
5686
32838
NULL

Actual output:

{
  "error": {
    "reason": "There was internal problem at backend",
    "details": "all shards failed",
    "type": "SearchPhaseExecutionException"
  },
  "status": 500
}

Example query 2:

SELECT substring(lastname, 1, 3) FROM people

Expected result:

{
  "error": {
    "reason": "There was internal problem at backend",
    "details": "all shards failed",
    "type": "SearchPhaseExecutionException"
  },
  "status": 500
}

Actual result:

substring(lastname, 1, 3)
Duk
Bon
Null
Ada

[dai-chen]

Related: #144

[chloe-zh]

Related: #320

[dai-chen]

This has been taken care of in new query engine: https://github.com/opendistro-for-elasticsearch/sql/blob/develop/docs/user/dql/expressions.rst#null-handling