max()/min() not supported for String and Date types

[jordanw-bq]

Using Docker image amazon/opendistro-for-elasticsearch:1.1.0
Dataset: Flights sample data (loaded through Kibana)
SQL Plugin version: 1.1.0.1

Example Query:
SELECT max(Origin) AS usr_Calculation_462181953493630977_nk FROM kibana_sample_data_flights

Result:
Raw response received: {
"error": {
"reason": "Invalid SQL query",
"details": "Function [MAX] cannot work with [KEYWORD]. Usage: MAX(NUMBER T) -> T",
"type": "SemanticAnalysisException"
},
"status": 400
}

Expectation:
Tableau, using other SQL connectors, allows users to use these functions on these types. Adding support for this in Elasticsearch will allow for full support for these functions when used through Tableau.

[dai-chen]

This exception is thrown from the type checker in semantic analysis. Will disable it to see if we do have the support for max/min on String and Date. If so, that means the type check is too strict and needs to relax for this case. Otherwise we need to add the support before relaxing the restriction.

[dai-chen]

Just disabled semantic analysis but found ES max aggregation can only work with numeric value. Need to add support for this and add overloaded function spec to type checker in new parser.

Explain for MAX with string:

{
  "from" : 0,
  "size" : 0,
  "_source" : {
    "includes" : [
      "MAX"
    ],
    "excludes" : [ ]
  },
  "aggregations" : {
    "MAX(firstname.keyword)" : {
      "max" : {
        "field" : "firstname.keyword"
      }
    }
  }
}

Exception thrown from ES engine:

{
  "error": {
    "root_cause": [
      {
        "type": "illegal_argument_exception",
        "reason": "Expected numeric type on field [firstname.keyword], but got [keyword]"
      }
    ],
    "type": "search_phase_execution_exception",
    "reason": "all shards failed",
    "phase": "query",
    "grouped": true,
    "failed_shards": [
      {
        "shard": 0,
        "index": "accounts",
        "node": "jn9G8trERRikgYiF3_eVng",
        "reason": {
          "type": "illegal_argument_exception",
          "reason": "Expected numeric type on field [firstname.keyword], but got [keyword]"
        }
      }
    ],
    "caused_by": {
      "type": "illegal_argument_exception",
      "reason": "Expected numeric type on field [firstname.keyword], but got [keyword]",
      "caused_by": {
        "type": "illegal_argument_exception",
        "reason": "Expected numeric type on field [firstname.keyword], but got [keyword]"
      }
    }
  },
  "status": 400
}