order by is not working as expected when more than one group by is used

[dheerajravindranath]

{"query": "select id, sum(marks) marks from mark_tables group by id order by marks desc"} This is working as expected! But when we give more than one column in group by is not working fine! {"query": "select id, name, sum(marks) marks from mark_tables group by id, name order by marks desc"}. Please, give me some suggestions on this. Is there any workaround for this?

[penghuo]

Thanks for reporting the issue.

Could you provide some test data for debuging? Have you try to explain the query by using the explain API. It will help to know what is DSL of this SQL.

[dai-chen]

It seems both Term Aggregation and Composite Aggregation API can only support sort inside bucket.

One workaround is to adjust the aggregation order based on order by clause:

SELECT OriginCountry, OriginCityName FROM flights GROUP BY OriginCountry, OriginCityName:
{
  "size" : 0,
  "_source" : false,
  "stored_fields" : "_none_",
  "aggregations" : {
    "groupby" : {
      "composite" : {
        "size" : 1000,
        "sources" : [
          {
            "da66945c" : {
              "terms" : {
                "field" : "OriginCountry.keyword",
                "missing_bucket" : true,
                "order" : "asc"
              }
            }
          },
          {
            "1af15150" : {
              "terms" : {
                "field" : "OriginCityName.keyword",
                "missing_bucket" : true,
                "order" : "asc"
              }
            }
          }
        ]
      }
    }
  }
}

SELECT OriginCountry, OriginCityName FROM flights GROUP BY OriginCountry, OriginCityName ORDER BY OriginCityName DESC
{
  "size" : 0,
  "_source" : false,
  "stored_fields" : "_none_",
  "aggregations" : {
    "groupby" : {
      "composite" : {
        "size" : 1000,
        "sources" : [
          {
            "1af15150" : {
              "terms" : {
                "field" : "OriginCityName.keyword",
                "missing_bucket" : true,
                "order" : "desc"
              }
            }
          },
          {
            "da66945c" : {
              "terms" : {
                "field" : "OriginCountry.keyword",
                "missing_bucket" : true,
                "order" : "asc"
              }
            }
          }
        ]
      }
    }
  }
}

[dai-chen]

The issue has been fixed in new query engine (which is for now 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!