Elasticsearch SQL LIMIT returns last 10 results instead of first 10 results

Elastic Stack Elasticsearch
1

Hi,
I am trying to visualize some data in a Canvas and I am using elasticsearch sql to build some visualizations. I am importing data from winlogbeat and I have written this query:

SELECT count(*) as c, winlog.event_id 
FROM "winlogbeat-*" 
GROUP BY winlog.event_id ORDER BY c  DESC

This query works fine and shows the elements in the right order. However, when I add a LIMIT statement

SELECT count(*) as c, winlog.event_id 
FROM "winlogbeat-*"
GROUP BY winlog.event_id
ORDER BY c DESC
LIMIT 10

Instead of getting the first results I receive the last results.
(For reference I am using Elasticsearch 6.7)

My expected behavior is that it should return the events with the highest count

2

@herrBez thank you for posting this question.
Indeed, this is a bug. And fortunately, it has been fixed in the meantime.

The initial bug report: https://github.com/elastic/elasticsearch/issues/42851
And the versions (as labels added to the PR) where the fix is available are here: https://github.com/elastic/elasticsearch/pull/43154

Apologies for the inconvenience, if you have time and the possibility of testing the fix on any of those versions, that would be great.

3

Thank you very much for the explanation :slight_smile:

4

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