Strange behaviour of Sub-(Queries) with OFFSET

InfluxDB 2
, , ,
1

In my influx db I have wrong data I need to update. Therefore I am looking for spikes in my data and I am using OFFSET and LIMIT in a divide and conquer algorithm to find this spikes.

Now I have strange results, that prevent my code/ queries from beeing successful. I hope you can help me.

This query gives me the following results:
SELECT MAX(value) FROM (SELECT value FROM My_Measurement LIMIT 2 OFFSET 1882381)

| Time                 | max      |
| -------------------- | -------- |
| 2020-01-18 08:40:05  |   57.843 |

When I just execute the sub-query, it gives me totally different results which are several years later:
SELECT value FROM My_Measurement LIMIT 2 OFFSET 1882381

| Time                 | max      |
| -------------------- | -------- |
| 2023-05-06 05:25:05  |  541.173 |
| 2023-05-06 05:26:05  |  541.173 |

So how can this be? Whats my error?

Well, I would expect, that the select MAX (or MIN) on the subquery would give me the Max/Min of the values returned by the subquery.

I am using InfluxDB v2.7.0 (git: 85f725f8b9) build_date: 2023-04-05T15:32:38Z (upgraded from 1.7 to 2.0.3 to 2.7.0)

2

Hello @fuetti,
That is just wacky. What in the good gracious.

Do you have this error using 1.x?

Luckily v3 supports 1.x and 3.x an OSS build should be available by the end of this year.
I don’t want to point you to Flux because it won’t be moving forward into v3.
But if you need something to carry you over I wonder if this would work?

from(bucket: "your_bucket_name")
  |> range(start: -30d) // Example range of the last 30 days. Adjust as necessary.
  |> filter(fn: (r) => r._measurement == "My_Measurement")
  |> filter(fn: (r) => r._field == "value")
  |> offset(n: 1882381)
  |> limit(n: 2)
  |> max()
3

Hi @Anaisdg and thank you for your feedback.

So is this a bug? Can you reproduce or confirm it?
On the login screen I get this message with version info - does this help?
InfluxDB v2.7.0
Server: 85f725f
Frontend: 0c844dc

I have developed a Powershell script, that uses flux webservice and divide&conquer to find the spikes (data that is factor 10 time to high due to a bug in sensor). I am not very familiar with flux nor do I know how I would be able to query it from the powershell :frowning:

Just pasted you query in the data explorer editor in the web gui (adjusted with my bucket and measurement) but it throws an error:

error @5:6-5:12: undefined identifier offset