Aggregate functions doesnot work with Limit values in InfluxQL query

[kondubhSICKAG]

I am using influx 2.7 v2 version with influx query. I have a scenario where I need to get min, max values of last 10 records. Query that I was using
select min(*) from "testapis" order by time desc limit 10; Shown as below

here are my last 10 records
select * from "testapis" order by time desc limit 10

Do we have a way to address this ? or is there a work around ?

[philjb]

If I understand your problem statement, I believe you want

select min(britainpound), max(britainpound) from (
   select * from testapis order by time desc limit 10
)

its a subquery. I believe 2.7 should support it: https://docs.influxdata.com/influxdb/v2/query-data/influxql/explore-data/subqueries/

[kondubhSICKAG]

Hi,
I am not trying to run sub queries but I am trying to get min and max values from for latest10 records, I tried subqueries I still get wrong results.
First image has my latest 10 records

As you mentioned I ran query to get latest 10 records
select min(britainpound), max(britainpound) from (select * from testapis order by time desc limit 10) order by time desc
and here is the result

[philjb]

ah I see now. yes it appears that min, max over subqueries are not working as expected. It appears to be a bug. nice find.

We would welcome a pr from you to fix it or you can work around it by processing the results outside influxdb for example in python.

[kondubhSICKAG]

Please help me with process of creating a PR. For I handled it in a different for min, max aggregates in java. Once I know the process of creating a PR i will create and will wait for fix.

[philjb]

I think i steered you wrong. I tried again locally with 2.7 (build from main-2.x branch https://github.com/influxdata/influxdb/tree/main-2.x) and the results are what I would expect. I think you might need to add a where clause or you are running into an issue with data exactly at 1970-01-01. It is difficult without your exact dataset and query verbatim to know.

select time,cpu,usage_idle from influxql.autogen.cpu where cpu='cpu10' order by time desc limit 5

Name: cpu
┏━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━┳━━━━━━━━━━━━━━━┓
┃ index ┃              time              ┃  cpu  ┃  usage_idle   ┃
┣━━━━━━━╋━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╋━━━━━━━╋━━━━━━━━━━━━━━━┫
┃      1┃  1591894460000000000.0000000000┃cpu10  ┃  98.2000000000┃
┃      2┃  1591894450000000000.0000000000┃cpu10  ┃  98.6013986014┃
┃      3┃  1591894440000000000.0000000000┃cpu10  ┃  98.3983983984┃
┃      4┃  1591894430000000000.0000000000┃cpu10  ┃  98.7000000000┃
┃      5┃  1591894420000000000.0000000000┃cpu10  ┃  98.7000000000┃
┣━━━━━━━┻━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┻━━━━━━━┻━━━━━━━━━━━━━━━┫
┃                                     4 Columns, 5 Rows, Page 1/1┃
┃                                        Table 1/1, Statement 1/1┃
┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛

Then with select min(usage_idle), max(usage_idle) from (select time,cpu,usage_idle from influxql.autogen.cpu where cpu='cpu10' order by time desc limit 5) order by time desc;

┏━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┓
┃ index ┃     time     ┃      min      ┃      max      ┃
┣━━━━━━━╋━━━━━━━━━━━━━━╋━━━━━━━━━━━━━━━╋━━━━━━━━━━━━━━━┫
┃      1┃  0.0000000000┃  98.2000000000┃  98.7000000000┃
┣━━━━━━━┻━━━━━━━━━━━━━━┻━━━━━━━━━━━━━━━┻━━━━━━━━━━━━━━━┫
┃                           4 Columns, 1 Rows, Page 1/1┃
┃                              Table 1/1, Statement 1/1┃
┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛

You can see i'm getting the correct min and max values.

I'm using this line protocol as data: https://github.com/influxdata/influxdb/blob/main/test_fixtures/lineproto/metrics.lp

(the process for creating a pull request is to fork the repo, fix the bug, and then open a pull request in the original repo - you need to sign the contributors release too)

[kondubhSICKAG]

I have bitcoin api storing britainpound, eurodollar and I am trying to get min, max for latest 10 records.
Here is the query which I used
select min(britainpound), min(eurodollar) from (select britainpound, eurodollar from "testapis" order by time desc limit 10) order by time desc; This is the response but In the screenshot you see the different results
Name: testapis
┏━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━┓
┃ index ┃ time ┃ min ┃ min_1 ┃
┣━━━━━━━╋━━━━━━━━━━━━━━━━━━━━━━╋━━━━━━━━━━━━━━━━━━╋━━━━━━━━━━━━━━━━━━┫
┃ 1┃1970-01-01T00:00:00Z ┃ 39484.1709000000┃ 46427.9744000000┃
┣━━━━━━━┻━━━━━━━━━━━━━━━━━━━━━━┻━━━━━━━━━━━━━━━━━━┻━━━━━━━━━━━━━━━━━━┫
┃ 4 Columns, 1 Rows, Page 1/1┃
┃ Table 1/1, Statement 1/1┃
┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛