influxdb

InfluxDB query with subquery not returning expected result

I'm using InfluxDB 1.8. I want to get the sum of the values in a column of the last 3 rows within a time range.

My query looks like this:

SELECT SUM("MyValue")
 FROM (
 SELECT "MyValue" FROM "oneYear"."MYTABLE"
 WHERE time < now() - 20s
 ORDER BY time desc
 LIMIT 3
)
 ORDER BY time desc

The result returned is 0.

When I execute only the subquery, the result is 1, 2 and 2. All aggregation-functions (MEAN, SUM, MAX, MIN) return 0, except for COUNT, which returns the correct count.

What am I doing wrong?

Solution

  • It seems this is a know bug in InfluxQL: It 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.

    https://github.com/influxdata/influxdb/issues/10253 https://github.com/influxdata/influxdb/issues/24089 https://github.com/influxdata/influxdb/issues/24622

    The issue does not occur when using Flux. Although Flux will be discontinued, it seems the only way to do subqueries with a LIMIT for now.