Last() returns first point instead of last

Fluxlang
1

I have a problem with the last() function (InfluxDB 2.0.4-2, Arch Linux) when executed on a bucket stored on disk. The data ranging from 14 Feb 2021 - 14 Mar 2021 is from my smart energy meter.

The last data point was requested,

from(bucket: "last-test")
  |> range(start: -1mo)
  |> last()

but output of the incorrect first data point with timestamp 14 Feb 2021 (_time column) returned:

,result,table,_start,_stop,_time,_value,_field,_measurement
,,0,2021-02-13T10:38:06.059009644Z,2021-03-15T21:08:06.059009644Z,2021-02-14T23:00:00Z,1.565374469739827,bill,daily
,,1,2021-02-13T10:38:06.059009644Z,2021-03-15T21:08:06.059009644Z,2021-02-14T23:00:00Z,4.465795000000071,energy,daily
,,2,2021-02-13T10:38:06.059009644Z,2021-03-15T21:08:06.059009644Z,2021-02-14T23:00:00Z,0.2426,price,daily
,,3,2021-02-13T10:38:06.059009644Z,2021-03-15T21:08:06.059009644Z,2021-02-14T23:00:00Z,14.66,rate,daily
,,4,2021-02-13T10:38:06.059009644Z,2021-03-15T21:08:06.059009644Z,2021-02-14T23:00:00Z,4405.794864,total,daily

However, the correct last data point with timestamp 14 Mar 2021 is selected when the same dataset was not stored on disk, but directly given in a csv file embedded in the flux query:

import "csv"
data = csv.from(csv: "
...
...
")

data
  |> last()

Output:

,result,table,_start,_stop,_time,_value,_field,_measurement
,,0,2021-02-13T06:23:59.047896607Z,2021-03-15T16:53:59.047896607Z,2021-03-14T23:59:59.88Z,2.9910504875398063,bill,daily
,,1,2021-02-13T06:23:59.047896607Z,2021-03-15T16:53:59.047896607Z,2021-03-14T23:59:59.88Z,10.342448000000331,energy,daily
,,2,2021-02-13T06:23:59.047896607Z,2021-03-15T16:53:59.047896607Z,2021-03-14T23:59:59.88Z,0.2426,price,daily
,,3,2021-02-13T06:23:59.047896607Z,2021-03-15T16:53:59.047896607Z,2021-03-14T23:59:59.88Z,14.66,rate,daily
,,4,2021-02-13T06:23:59.047896607Z,2021-03-15T16:53:59.047896607Z,2021-03-14T23:59:59.88Z,4701.490756,total,daily

I have prepared an tar.gz archive (40 KB) with the dataset and all queries here (link valid until 31 Mar 2021).

Any ideas what could have gone wrong?

2

Found the reason, but still don’t understand why. It depends which column last() operates on. When I change

from(bucket: "last-test")
  |> range(start: -1mo)
  |> last()

to

from(bucket: "last-test")
  |> range(start: -1mo)
  |> last(column : "_time")

Then the correct last point in time is selected. I guess last() operates on _value by default.

However, I expect ´last()´ to operate on the ´_time´ column by default and ´min()´ and ´max()´ on the ´_value´ column. It also doesn’t explain why there is different behaviour of data stored in a bucket vs data stored in a variable. ´last()´ should return the same data point in both cases.

I suspect a subtle bug in the influxdb storage engine. Could someone please check this out.

3

Issue has been discussed here: https://community.influxdata.com/t/recent-edition-of-influxdb-2-0-2-last-function-not-work-as-expected/16942/5?u=pohl6532.

Seems last() function is broken since V2.0.2

4

Thank you for bringing this to our attention @pohl6532. A patch has been submitted that fixes this. The fix should be in the next release. Patch can be found here: fix(storage): Detect need for descending cursor in WindowAggregate by scbrickley · Pull Request #21140 · influxdata/influxdb · GitHub

5

Hi, @pohl6532 @scbrickley

I see this issue resolve in v2.0.2, I have used influxdb:2.5.1 image but also meet the similar issue.

I have two row data and the data show 2022-11-12 10:39:42 value is true, and 2022-11-12 10:39:41 value is false

1
11464×612 150 KB

from(bucket: “nxd-gateways”)
|> range(start: -1d, stop: now())
|> filter(fn: (r) => r[“_measurement”] == “gatewayonline”)
|> filter(fn: (r) => r[“uuid”] ==“08e9f65c-e7ec-011a-2b38-00013191ed22”)
|> group(columns:[“uuid”])
|> window(every:1h)

When I use aggregateWindow or window with last, it always return wrong row with value false.

2
21492×751 97.9 KB

from(bucket: “nxd-gateways”)
|> range(start: -1d, stop: now())
|> filter(fn: (r) => r[“_measurement”] == “gatewayonline”)
|> filter(fn: (r) => r[“uuid”] ==“08e9f65c-e7ec-011a-2b38-00013191ed22”)
|> group(columns:[“uuid”])
|> window(every:1h)
|> last()

When I use aggregateWindow or window with first, it always return wrong row with value true

3
31490×750 96.4 KB

from(bucket: “nxd-gateways”)
|> range(start: -1d, stop: now())
|> filter(fn: (r) => r[“_measurement”] == “gatewayonline”)
|> filter(fn: (r) => r[“uuid”] ==“08e9f65c-e7ec-011a-2b38-00013191ed22”)
|> group(columns:[“uuid”])
|> window(every:1h)
|> first()