[ES|QL] Heatmap color bands not synchronized with the legend always

[stratoula]

Kibana version:
8.11 +

Describe the bug:
I can also reproduce it with logstash-* (I have a lot of data so possibly this is the key here)

Steps to reproduce:

1. Install demo logstash dataset
2. Go to Discover esql mode
3. Write a query which produces a heatmap from logstash-* | limit 10 | stats var0 = avg(bytes) by geo.dest
4. Open the edit flyout
5. Toggle the coloring from percent to values and vice versa
6. Check that the legend values and that the flyout do not have the same values

Expected behavior:
They should be synced.

[elasticmachine]

Pinging @elastic/kibana-visualizations (Team:Visualizations)

[dej611]

It seems related to #172433 : can you reproduce it in #172602 ?

[stratoula]

@dej611 let me try!

[stratoula]

No Marco unfortunately is something else :(

[stratoula]

We know why it happens. The ES|QL queries with limit can return different results in every render. This happens when the user hasn't added a sort command. In that case by toggling the percent / values tabs the expression re- runs and different results are rendered.
With this we are going to improve the behavior in Discover side #167631 but in the dashboard it will persist.

We have addressed our concerns to ES and we think that the rows should be sorted by default. We are still in discussions. With that being said we could also improve the behavior in the Lens side with expression caching or other techniques but this is a quite big task.

[markov00]

@stratoula I believe that the expression (at least the query part) should not rerun if I'm not changing the query itself or the time picker (even if is set to relative time ranges). Switching between two possible configurations shouldn't trigger a complete re-run of the query, if really necessary just the "color range" configuration part.

[stratoula]

@markov00 this is exactly what I said to @dej611 :D I totally agree, it is very weird that we rerun the expression

[stratoula]

I am closing this as it is going to be resolved when this lands #177548 so it is a duplicate