Did I stumble across another bug? SQL select ordering on 2 fields

Elastic Stack Elasticsearch
1

I tried to order an SQL query result using two fields, but it seems the ordering is only done on one field.

Example data:

PUT /myindex
POST _bulk
{ "index": { "_index": "myindex" }}
{ "traphit": 1, "customer": "A", "domain": "a1" }
{ "index": { "_index": "myindex" }}
{ "traphit": 2, "customer": "A", "domain": "a2" }
{ "index": { "_index": "myindex" }}
{ "traphit": 3, "customer": "C", "domain": "a3" }
{ "index": { "_index": "myindex" }}
{ "traphit": 4, "customer": "B", "domain": "b1" }
{ "index": { "_index": "myindex" }}
{ "traphit": 5, "customer": "B", "domain": "b2" }
{ "index": { "_index": "myindex" }}
{ "traphit": 6, "customer": "A", "domain": "b3" }
{ "index": { "_index": "myindex" }}
{ "traphit": 7, "customer": "C", "domain": "c1" }
{ "index": { "_index": "myindex" }}
{ "traphit": 8, "customer": "C", "domain": "c2" }
{ "index": { "_index": "myindex" }}
{ "traphit": 9, "customer": "B", "domain": "c3" }

The query is

POST _sql?format=txt
{
  "query":"""
    select sum(traphit) as traphits
         , customer
         , domain
    from myindex
    group by customer, domain
    having traphits > 0
    order by customer asc, traphits desc
  """
}

The result is

   traphits    |   customer    |    domain     
---------------+---------------+---------------
9.0            |B              |c3             
8.0            |C              |c2             
7.0            |C              |c1             
6.0            |A              |b3             
5.0            |B              |b2             
4.0            |B              |b1             
3.0            |C              |a3             
2.0            |A              |a2             
1.0            |A              |a1

While it should be

   traphits    |   customer    |    domain     
---------------+---------------+---------------
6.0            |A              |b3             
2.0            |A              |a2             
1.0            |A              |a1             
9.0            |B              |c3             
5.0            |B              |b2             
4.0            |B              |b1             
8.0            |C              |c2             
7.0            |C              |c1             
3.0            |C              |a3
2

@Skeeve thank you for posting. You've actually uncovered a bug. I created an issue here, where there are more details about it.

1 Like
3

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.