"select `column` from `table` group by `column` order by `column` LIMIT 5" is not supported

[rupal-bq]

Following query fails for columns with data type text,nested and object
Query:

"select `type_text` from `kibana_sample_data_types` group by `type_text` order by `type_text` LIMIT 5"

Response:

{
  "error": {
    "reason": "Error occurred in Elasticsearch engine: all shards failed",
    "details": "Shard[0]: java.lang.IllegalArgumentException: field [type_object] isn't a leaf field\n\nFor more details, please send request for Json format to see the raw response from elasticsearch engine.",
    "type": "SearchPhaseExecutionException"
  },
  "status": 400
}

The same query works for data type keyword.
Query:

"select `type_keyword` from `kibana_sample_data_types` group by `type_keyword` order by `type_keyword` LIMIT 5"

Response:

{
  "schema": [
    {
      "name": "type_keyword",
      "type": "keyword"
    }
  ],
  "total": 2,
  "datarows": [
    [
      "goodbye"
    ],
    [
      "hello"
    ]
  ],
  "size": 2,
  "status": 200
}

[dai-chen]

For text field, I tested it by accounts index. I think this is the same issue as #401 (comment). The root cause is that text field name (if it's a multi-field) is rewritten to its inner keyword field name because aggregation on text field requires fielddata enabled otherwise. However, field name in SELECT is not rewritten accordingly.

POST _opendistro/_sql
{  
  "query": """
      select `address` from `accounts` group by `address` order by `address` LIMIT 5
      """
}
{
  "error": {
    "reason": "There was internal problem at backend",
    "details": "invalid value operation on MISSING_VALUE",
    "type": "IllegalStateException"
  },
  "status": 500
}

This worked if we rewrote address in SELECT to address.keyword manually to match GROUP BY item:

POST _opendistro/_sql
{  
  "query": """
      select `address`.`keyword` from `accounts` group by `address`.`keyword` order by `address`.`keyword` LIMIT 5
      """
}
{
  "schema": [{
    "name": "address.keyword",
    "type": "keyword"
  }],
  "total": 5,
  "datarows": [
    ["100 Gatling Place"],
    ["101 Bush Street"],
    ["102 Barwell Terrace"],
    ["104 Harbor Court"],
    ["104 Lewis Avenue"]
  ],
  "size": 5,
  "status": 200
}

[dai-chen]

The reason why we didn't rewrite multi-field in SELECT is that the DSL is wrong and returns nothing in this case. We put whatever in SELECT on _source (which includes original doc). Only text is present and stored in source when indexing, text.keyword is generated on the fly. DSL request as below doesn't get what expected.

POST _opendistro/_sql/_explain
{  
  "query": """ SELECT address.keyword FROM accounts """
}
{
  "from" : 0,
  "size" : 200,
  "_source" : {
    "includes" : [
      "address.keyword"
    ],
    "excludes" : [ ]
  }
}

[dai-chen]

The issue has been fixed in new query engine (which is experimental and disabled by default). You can enable it to preview by changing the plugin setting: https://github.com/opendistro-for-elasticsearch/sql/blob/master/docs/user/admin/settings.rst#opendistro-sql-engine-new-enabled. Thanks!