Discover sorting is inconsistent

[JeffBolle]

Kibana version: 7.4.0

Elasticsearch version: 7.4.0

Server OS version: Ubuntu 16.04

Browser version: Google Chrome | 78.0.3904.70 (Official Build) (64-bit)

Browser OS version: ClearLinux 5.3.7-853.native (also OS X)

Original install method (e.g. download page, yum, from source, etc.): apt-get

Describe the bug:
When opening an index pattern without a time filter in the discover tab and choosing a timestamp field to sort on, sorting is out of order if a search term is provided. The initial search on loading the pattern and adding the field appears to be properly ordered. If additional fields are added to the table and some documents only have some of the fields displayed, the sorting appears to group some of the returned results, which within the grouping appear to be sorted correctly, but in the context of the entire result set, are not properly sorted.

Steps to reproduce:

1. Create index pattern with no time filter for an index that has some sparse fields in documents (not every document has every field), but that has a timestamp for every document.
2. Open the index pattern in the Discover tab with no query provided. Sort by the timestamp field. The results will be properly sorted.
3. Enter a search term that matches on different fields in the sample data set and sort on the timestamp
4. Results will not be in strict timestamp ordering.

Expected behavior:
Results are sorted by the requested field across all documents, regardless of matches, highlighting, and other fields in the documents.

Screenshots (if relevant):

Errors in browser console (if relevant):

Provide logs and/or server output (if relevant):

Any additional context:
In the second image I needed to redact our internal information, however you can see the timestamps that should be in decreasing order are effectively grouped. The second set of results does also appear to be sorted in decreasing order, but it is out of order for all results.

[elasticmachine]

Pinging @elastic/kibana-app (Team:KibanaApp)

[timroes]

Hi,

thanks for that thorough issue description. I am unfortunately currently not able to reproduce that. Could you perhaps provide the following two additional information:

1. In the case of the broken sorting, could you click Inspect in the top menu and copy/paste that request here (please feel free to blank out sensitive information, but please make sure to indicate clearly where you modified/blanked out information in the request.
2. Check the "Response" tab and check if inside the long hits.hits array in the response the items appear in the correct sorted order or in the same "wrong" order than shown in Discover. No need to paste the full response here, if you can just provide that information.

Thanks a lot for your engagement,
Tim

cc @kertal

[JeffBolle]

@timroes
Thanks for having a look.
I've put * in place where there was sensitive data.

{
  "version": true,
  "size": "2500",
  "sort": [
    {
      "_score": {
        "order": "desc"
      }
    },
    {
      "collection_info.ingest_time": {
        "order": "desc",
        "unmapped_type": "boolean"
      }
    },
    {
      "event_time": {
        "order": "desc",
        "unmapped_type": "boolean"
      }
    }
  ],
  "_source": {
    "excludes": []
  },
  "stored_fields": [
    "*"
  ],
  "script_fields": {
    "screenshotUrl": {
      "script": {
        "source": "if (params['_source']['screenshot'] != null && params['_source']['screenshot']['path'] != null)\n      params['_source']['screenshot']['path'].substring(5)",
        "lang": "painless"
      }
    },
    "screenshotAltUrl": {
      "script": {
        "source": "if (params['_source']['screenshot'] != null && params['_source']['screenshot']['path'] != null)\n      params['_source']['screenshot']['path'].substring(5)",
        "lang": "painless"
      }
    },
    "Screenshot Image": {
      "script": {
        "source": "if (params['_source']['screenshot'] != null && params['_source']['screenshot']['path'] != null)\n      params['_source']['screenshot']['path'].substring(5)",
        "lang": "painless"
      }
    },
    "Screenshot Alt Image": {
      "script": {
        "source": "if (params['_source']['screenshot'] != null && params['_source']['screenshot']['path'] != null)\n      params['_source']['screenshot']['path'].substring(5)",
        "lang": "painless"
      }
    }
  },
  "docvalue_fields": [
    {
      "field": "collection_info.collection_time",
      "format": "date_time"
    },
    {
      "field": "collection_info.ingest_time",
      "format": "date_time"
    },
    {
      "field": "event_time",
      "format": "date_time"
    },
    {
      "field": "notifications.***********************",
      "format": "date_time"
    },
    {
      "field": "notifications.***********************",
      "format": "date_time"
    },
    {
      "field": "notifications.***********************",
      "format": "date_time"
    },
    {
      "field": "notifications.***********************",
      "format": "date_time"
    },
    {
      "field": "notifications.***********************",
      "format": "date_time"
    },
    {
      "field": "notifications.***********************",
      "format": "date_time"
    },
    {
      "field": "notifications.***********************",
      "format": "date_time"
    },
    {
      "field": "notifications.***********************",
      "format": "date_time"
    },
    {
      "field": "notifications.***********************",
      "format": "date_time"
    }
  ],
  "query": {
    "bool": {
      "must": [
        {
          "match_all": {}
        }
      ],
      "filter": [],
      "should": [],
      "must_not": []
    }
  },
  "highlight": {
    "pre_tags": [
      "@kibana-highlighted-field@"
    ],
    "post_tags": [
      "@/kibana-highlighted-field@"
    ],
    "fields": {
      "*": {}
    },
    "fragment_size": 2147483647
  }
}

In the response, no, the hits are not sorted how I would expect. For example, heavily redacted:

    "hits": [
      {
        "_index": "*****",
        "_type": "_doc",
        "_id": "7cc55c92df7121d034a6537cb742a811cc80e80bb0cab64d291335b62c0909a8",
        "_version": 1,
        "_score": 1,
        "_source": {
          "id": "7cc55c92df7121d034a6537cb742a811cc80e80bb0cab64d291335b62c0909a8",
          "event_time": "2019-10-25T12:58:03Z",
        },
        "sort": [
          1,
          1572008869318,
          1572008283000
        ]
      },
      {
        "_index": "*****",,
        "_type": "_doc",
        "_id": "7c351d55cbb8dcd7d906da0d28d2b69ab252526e9229aee22f28bf2d371d43ad",
        "_version": 1,
        "_score": 1,
        "_source": {
          "id": "7c351d55cbb8dcd7d906da0d28d2b69ab252526e9229aee22f28bf2d371d43ad",
          "event_time": "2019-10-25T13:06:18Z",
        },
        "sort": [
          1,
          1572008869317,
          1572008778000
        ]
      },
      {
        "_index": "*****",
        "_type": "_doc",
        "_id": "d37bd0cc9e074753f55da5b5cbfe997b95b523a98dba0af8eb467a1f0d3ad677",
        "_version": 1,
        "_score": 1,
        "_source": {
          "id": "d37bd0cc9e074753f55da5b5cbfe997b95b523a98dba0af8eb467a1f0d3ad677",
          "event_time": "2019-10-25T13:04:51Z",
          },
        "sort": [
          1,
          1572008869317,
          1572008691000
        ]
      }
     ]

[JeffBolle]

Is there anything more I can provide to help get this issue resolved? Any suggested workarounds? This is having a substantial impact on my users.

[kertal]

sorry for the delay, thanks for you patience and for the sample you provided, so you want to sort by event time but according to the result you've provided, you're sorting by

"sort": [
    {
      "_score": {
        "order": "desc"
      }
    },
    {
      "collection_info.ingest_time": {
        "order": "desc",
        "unmapped_type": "boolean"
      }
    },
    {
      "event_time": {
        "order": "desc",
        "unmapped_type": "boolean"
      }
    }
  ],

1. _score 2) collection_info.ingest_time 3) event_time

since we've introduced the feature to sort on multiple columns in kibana in 7.4, could this be a result of the changed sorting behavior:

#41918

So when it's initially sorted by event time , you klick on another field, then this field is added to the sort fields with a lower priority. So I wonder if not the add-on of a field might be the cause, at least in the example you provided, it seems the result of the wrong sorting was caused by sorting on multiple columns

[JeffBolle]

@kertal Thank you for following up.

The user intention in the case I provided is to sort on event_time, though they may be displaying other fields as well. I don't believe the other fields had been clicked to attempt to sort on multiple fields. For the test case I provided I tried to keep it simple. For my user the intent would be for the sort order of fields to be reversed from the demonstrated behavior in Kibana. The preferred order would have been:

1. event_time
2. ingest_time
3. score
   I hope that helps get you all toward a solution.

[kertal]

I will check if there's a sorting issue in 7.4 but generally, in the example you've sent me there are 3 sort fields, and afaik it's not possible to set this without manual user input. since event_time is you most important field, why didn't you set it as Time Filter field name in your index pattern?

[JeffBolle]

Because we use the index pattern for lots of things other than the search that was done here. Regardless, when I did this, the only field I clicked on to sort was event_time. It really doesn't matter whether we use a time filter or not, once I've clicked one of the fields all sorting should be done by that field, and not by _score.

[JeffBolle]

I've run into this again and wanted to check and see if any additional feedback was needed from my end. It is clear that there is a problem with the sorter, especially when dealing with indexes that don't have a time filter and when trying to sort by a date field. This is a very common use case for us and something that we use daily. When looking at the request sent by Kibana to Elasticsearch it is clear that the request and the order of the sorters is not being properly updated for user's desired sorting based on they clicking of the sort arrows. Today I'm seeing a column that isn't even currently displayed as in the sort list, and the column I've clicked on to sort is last in the list.

[kertal]

so I made a test in kibana 7.4.0 with the testdata that's provided with Kibana, so you could test it the same way

• kibana_sample_data_ecommerce
• kibana_sample_data_flights
• kibana_sample_data_logs

I've added an index pattern kibana_sample* without setting a timestamp field, selected order_date from the available fields list.

When I do this, it's automatically ordered by _score, which is fine

I click on the order_date, now it's ordered by order_data ascending

I'm adding another column timestamp , ordered still by order_data ascending, which is fine

I click on the timestamp sort icon, now it's sorted 1) by order_data , and 2) by timestamp

So it works as expected. But what I encountered later on, which could lead in the right direction was this:

when I change to the index pattern kibana_sample_data_logs, there's no order_date here, it's initially sorted by 1) _score and 2) timestamp. that's not intended, there should be no sorting by _score and this might be the cause of your problem, I think it's the switching between different patterns, if think we should have a closer look here @Bargs

Thx @JeffBolle for reporting this!

[JeffBolle]

Outstanding. I will say that our users are constantly switching between different index patterns and this could very well be why it was difficult to replace. That would also likely explain why a field was still in the sort that I wasn't currently using or displaying when I re-encountered this today.

[Bargs]

Looks like a bug caused by logic still hanging around from when multi-sort was not an option. It used to be that when the sorted column did not exist in the current index pattern, we'd default to the time field or _score, which was fine when you could only sort on one field at a time.

Not sure what the most appropriate behavior would be here. In the example @kertal outlined, when switching to an index pattern that does not have order_date but it does have timestamp, should we switch to sorting only on timestamp? Or if the current multi-sort doesn't match exactly, should we throw it away and default to sorting only on the index patterns time field, or _score if that doesn't exist?

[JeffBolle]

To me, as a user, if I have just switched index patterns, I would expect the sorting to be "natural" (score, or by the time field if in an index pattern with a time filter). The previous sorting was for the previous index pattern. In my case, I switched index patterns, ran a query, and then clicked a column to sort, which had no impact.

[kertal]

@Bargs I think we should switch to the default sort of the switched index pattern, don't think it's necessary to keep the sort "state".

[Bargs]

Well if the new index pattern has all of the necessary fields, I think we should retain the sort. The selected columns are also retained. That's how Kibana has behaved for a long time and it was intentional because some users have multiple index patterns with the same fields and they want to be able to compare back and forth without reconfiguring the columns and sort each time.

If we did decide to reset the sort on index pattern change, then we should also reset the columns.

[kertal]

@Bargs that's a good point not to change long time behavior, so a fundamental change should also take care of the columns. I've suggested a solution that should solve the problem @JeffBolle reported, removing the default sort after _score from state for indexpatterns without timefield
#49050

[JeffBolle]

@kertal and @Bargs I really appreciate giving this issue some attention.
Would that change also help with the case where Kibana is trying to sort on fields that are no longer present in the index after changing an index pattern?

[kertal]

@JeffBolle those fields should be already filtered out, isn't that the case?

[JeffBolle]

@kertal Clearly I was mistaken. I had thought that had been mentioned as a possible issue as well, but I haven't been able to reproduce it. Sorry about that.