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)
Influx can't handle subqueries with a LIMIT. This issue is present in both Influxv1 and v2. It has been reported multiple times already (since 2018), but keeps getting ignored.