[ES|QL] When using `BUCKET` not in conjunction with `SORT` "weird" things happen

[BenB196]

Elasticsearch Version

8.17.1

Installed Plugins

No response

Java Version

bundled

OS Version

Docker

Problem Description

(Disclaimer, I'm not 100% sure if this is a problem with ES|QL or with how Kibana handles the data returned by ES|QL)

When running a query, and using the BUCKET function over a timestamp, if SORT is not applied to the timestamp, data/graphs become inconsistent

Steps to Reproduce

1. Run the following ES|QL query

FROM metrics-system.cpu-*
| STATS AVG(system.cpu.total.norm.pct) BY time = BUCKET(@timestamp, 30 seconds)

Note: I recommend using a "wide" time range (48 hours) seems to reproduce regularly

Observe that it will get graphed/visualized in "weird" ways:

2. Run the same query, but add a SORT at the end

FROM metrics-system.cpu-*
| STATS AVG(system.cpu.total.norm.pct) BY time = BUCKET(@timestamp, 30 seconds)
| SORT time

Observe that the data will now graph/visualize correctly/consistently

Logs (if relevant)

No response

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)

[astefan]

Since the description of the issue is exclusively UI related, a first step would be to establish if this is Kibana related or ES|QL related. @stratoula could you please have a look at this report and see if it rings any bells in terms of already fixed issues on the UI side?

[stratoula]

I think it is related with the limit 1000 which is the default at ES level.

I can also replicate it locally. It is how the data is coming. Check here:

FROM logstash*
  | STATS AVG(bytes) BY time = BUCKET(@timestamp, 30 seconds)
  | SORT time

This results to this:

The max time I get is: Feb 4, 2025 @ 08:41:30.000
and is correctly depicted in the histogram

When I dont sort:

FROM logstash*
 | STATS AVG(bytes) BY time = BUCKET(@timestamp, 30 seconds)

I get max time: Feb 4, 2025 @ 10:26:30.000

and is correctly depicted in the histogram

If I increase the limit (and remove the sort)

FROM logstash*
  | STATS AVG(bytes) BY time = BUCKET(@timestamp, 30 seconds)
 | limit 10000

Max time: Feb 4, 2025 @ 10:26:30.000

and is correctly depicted in the histogram

So my guess is that when you dont sort and you have the limit 1000 some values are missing resulting in a not so accurate histogram. But this is an ES|QL limitation atm. The UI draws the data points it gets

[stratoula]

I wonder if it makes sense to return the buckets sorted by default. Ideally we want to increase the limit 1000 too but I understand that atm there are some performance concerns (?)