SQL: Sort is does not apply in SQL query

[deepminimal]

Elasticsearch version (bin/elasticsearch --version): Version: 6.4.0, Build: default/rpm/595516e/2018-08-17T23:18:47.308994Z, JVM: 1.8.0_171

Plugins installed: [none]

JVM version (java -version): openjdk version "1.8.0_171" OpenJDK Runtime Environment (build 1.8.0_171-b10) OpenJDK 64-Bit Server VM (build 25.171-b10, mixed mode)

OS version (uname -a if on a Unix-like system):Linux masterelk001 3.10.0-862.6.3.el7.x86_64 #1 SMP Tue Jun 26 16:32:21 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux

Description of the problem including expected versus actual behavior: I trying to sort column GB DESC, but sort is does not apply. If i short column Name sort is ok.

SELECT "GB", "Name" FROM (
    select max(system.network.out.bytes)/1024/1024/1024 as "GB", "beat.hostname" as "Name" FROM "metricbeat-6.4.2-2018.10.23" group by "beat.hostname"
) 
ORDER BY "GB" DESC

Result:

        GB        |     Name
------------------+---------------
22.18141655717045 |data1elk001
23.536200148053467|data2elk001
141.9572846684605 |grafana001
28.310986624099314|grafana1001
0.5050704898312688|kibana001
1047.3980827750638|logstash001
20.01751151494682 |logstash002
53.34926394466311 |lstash1002
76.17146196588874 |masterelk001
112.53103559277952|othertasks001
29.300186527892947|oxidized001
33.97281537298113 |prometheus001
3247.9724201112986|zabbixdb001
465.4921508645639 |zabbixsrv001
129.22536268923432|zabbixweb001

[elasticmachine]

Pinging @elastic/es-search-aggs

[astefan]

@deepminimal thank you for giving SQL a go and reporting your findings. Indeed, ordering by that specific field doesn't work and it's a limitation of ES SQL, not necessarily a bug. Let me explain a bit.

Behind the scene, ES SQL uses composite aggregations whenever it has a grouping to compute. We use this because it has the big advantage of allowing paginations of results, which is really important (see cursors) in the SQL world. But, composite aggregation (due to being impossible to have an efficient way of sorting) cannot order on something else other than the key itself. In your specific case, the SELECT results can only be ordered by beat.hostname or name. So, you can do either of these:

SELECT "GB", "Name" FROM (
    select max(system.network.out.bytes)/1024/1024/1024 as "GB", "beat.hostname" as "Name" FROM "metricbeat-6.4.2-2018.10.23" group by "beat.hostname"
) 
ORDER BY "Name" DESC

select max(system.network.out.bytes)/1024/1024/1024 as "GB", "beat.hostname" as "Name" FROM "metricbeat-6.4.2-2018.10.23" group by "beat.hostname" order by "beat.hostname"

[astefan]

@deepminimal I'll go ahead and close this issue. If you have any confirmed bugs or enhancements to report, feel free to open another github issue. For any other questions or how-tos, please use our (very active) forum.

[deepminimal]

Thank you for your time!