Rows not sorted by time when using last()

InfluxDB 2
1

Hi,

I wrote this query:

from(bucket: "statisticaldata")
  |> range(start: -24h) // we know that data is flushed daily
  |> last()
  |> filter(fn: (r) => r["_measurement"] == "systemStatus")
  |> filter(fn: (r) => r["_field"] == "conveyorSpeed")

The result I get is:

2021-06-07 01:37:26 0

If I change the query to include a sort:

from(bucket: "statisticaldata")
  |> range(start: -24h) // we know that data is flushed daily
  |> sort(columns: ["_time"], desc: false)
  |> last()
  |> filter(fn: (r) => r["_measurement"] == "systemStatus")
  |> filter(fn: (r) => r["_field"] == "conveyorSpeed")

I get a different reading:

2021-06-07 11:53:05 1502

Why is sort() necessary to obtain the latest value?
In the documentation it says:

By default, InfluxDB returns results sorted by time, however you can use the sort() function to change how results are sorted. first() and last() respect the sort order of input data and return records based on the order they are received in.

Thank you for your help!

P.s.: I’m using influxdb latest container.

2

Not a flux expert, but the docs says it all.

first() returns the first non-null record in an input table.

last() returns the last non-null record in an input table.

The data are (or should given your problem) sorted by time by default, the fact that you must explicitly sort by time in order to get right value is weird.

Therefore, in your query the rows are sorted by _value ASC, unless you specify a different sorting (by time ASC in your case), first and last just fetch the first or last row in the table

In this case looks like your table is not sorted and therefore first and last don’t get the expected records.

3

Thank you for your input.

As you can see in my example, the last value returned without sort() being used is 0. If rows were sorted by _value, last() would not return 0 as larger values do exist, e.g. the last reading sorted by time which is 1502.

4

My bad, I misread the docs, even without sorting it should be sorted by time.

The only idea I have is to just look at the raw data (maybe for a smaller time window) and check if the sorting is actually right.

1 Like
5

Thanks, actually It’s not right. I just refrained from pasting whole data here. To me the order looks random.

6

Just for completeness which version of influxDB are you running?

@Anaisdg do you have any idea about who to ask for more info about this weird behaviour?

7

Version 2.0.4 (4e7a59b)

8

Hello @ypnos,
First I would change my query to the pushdown pattern of from |> range |> filter

from(bucket: "statisticaldata")
  |> range(start: -24h) // we know that data is flushed daily
  |> filter(fn: (r) => r["_measurement"] == "systemStatus")
  |> filter(fn: (r) => r["_field"] == "conveyorSpeed")

Then add your last(). I’m guessing this is causing some issues.

What do you mean by “Thanks, actually It’s not right. I just refrained from pasting whole data here. To me the order looks random.”

What looks random? When you sort by time the data is random? Can you share a screenshot? You shouldn’t have to sort by time. Sort isn’t necessary to obtain the latest value. What you can do is sort by the numerical value (sort(columns:["_value"})) and get the first or last (highest or lowest value if desc=true) if this is the case then your timestamps will appear random.

You can test the functionality with these two queries:

import "array"

array.from(rows: [{_time: 2021-06-10T00:00:00Z, _value: 1},
                  {_time: 2021-06-11T00:00:00Z, _value: 2},
                  {_time: 2021-06-12T00:00:00Z, _value: 3}])

|> sort(columns: ["_time"], desc: false)
|> last()

and

import "array"

array.from(rows: [{_time: 2021-06-10T00:00:00Z, _value: 1},
                  {_time: 2021-06-11T00:00:00Z, _value: 2},
                  {_time: 2021-06-12T00:00:00Z, _value: 3}])
|> last()

The output is both 3

9

@ypnos v2.0.4 contained a bug in our implementation of the last() pushdown rule, I’d recommend upgrading to the newest version (now v2.0.7) as it’ll probably fix the problems you’re hitting.

2 Likes
10

Useful discussion, thanks, I appreciate that! :handshake: