ORDER BY with one field doesn't work as expected.

[penghuo]

Issue

The ORDER BY clause doesn't sort the result in alphabet order.

POST /_opendistro/_sql/
{
  "query": """
      SELECT Carrier AS c, COUNT(*) AS count, MAX(AvgTicketPrice) AS m
      FROM kibana_sample_data_flights
      GROUP BY Carrier
      ORDER BY Carrier
      LIMIT 2500
  """
}

{
  "schema": [
    {
      "name": "c",
      "alias": "c",
      "type": "keyword"
    },
    {
      "name": "count",
      "alias": "count",
      "type": "integer"
    },
    {
      "name": "m",
      "alias": "m",
      "type": "float"
    }
  ],
  "total": 4,
  "datarows": [
    [
      "Logstash Airways",
      3331,
      1199.72900390625
    ],
    [
      "JetBeats",
      3274,
      1199.642822265625
    ],
    [
      "Kibana Airlines",
      3234,
      1199.109130859375
    ],
    [
      "ES-Air",
      3220,
      1199.5123291015625
    ]
  ],
  "size": 4,
  "status": 200
}

[penghuo]

Analysis

In the DSL query, the term is ordered by _count in desc order and key in asc order. There are two choice to fix this issue
1> remove the _count desc
2> make "_key" : "asc" prior to the _cound desc

POST /_opendistro/_sql/_explain
{
  "query": """
      SELECT Carrier AS c, COUNT(*) AS count, MAX(AvgTicketPrice) AS m
      FROM kibana_sample_data_flights
      GROUP BY Carrier
      ORDER BY Carrier
      LIMIT 2500
  """
}

{
  "from" : 0,
  "size" : 0,
  "_source" : {
    "includes" : [
      "Carrier",
      "COUNT",
      "MAX"
    ],
    "excludes" : [ ]
  },
  "stored_fields" : "Carrier",
  "sort" : [
    {
      "Carrier" : {
        "order" : "asc"
      }
    }
  ],
  "aggregations" : {
    "c" : {
      "terms" : {
        "field" : "Carrier",
        "size" : 2500,
        "min_doc_count" : 1,
        "shard_min_doc_count" : 0,
        "show_term_doc_count_error" : false,
        "order" : [
          {
            "_count" : "desc"
          },
          {
            "_key" : "asc"
          }
        ]
      },
      "aggregations" : {
        "count" : {
          "value_count" : {
            "field" : "_index"
          }
        },
        "m" : {
          "max" : {
            "field" : "AvgTicketPrice"
          }
        }
      }
    }
  }
}