Non_negative_difference() in subquery being forced to have an aggregate

I don’t know if the error message I am getting is expected behaviour.

Here’s my query, attempting to show accumulated rainfall starting from zero, for a particular period, grouped by some time buckets

SELECT 
    cumulative_sum(sum(nnd))
FROM (SELECT 
          non_negative_difference(rain) as nnd 
      FROM 
          weather
      WHERE
          $time_query)
GROUP BY
    time(1h)

(I use non_negative_difference() to handle counter resets.)

But this gives the error:

ERR: aggregate function required inside the call to non_negative_difference

The subquery on its own shows the correct increments, but wrapped in an outer query it fails.

I cannot find an aggregate/group-by combination for the subquery that doesn’t compromise the output, apart from forcing the subquery aggregation into buckets smaller than my sensor frequency.

SELECT 
    cumulative_sum(sum(nnd)) 
FROM 
    (SELECT 
        non_negative_difference(sum(rain)) as nnd 
     FROM 
        weather  
     WHERE 
        $time_query
     GROUP BY 
        time(1s)) 
GROUP BY 
    time(1h)

I’m just checking that this is expected behaviour and not a compiler bug.

Thanks

Rob

Background:

Here is my base query:

select 
    non_negative_difference(rain) as nnd 
FROM 
    weather  
WHERE 
    $time_query

… which yields the increment per raw data point, for example:

2018-06-01T14:21:00.926Z 0
2018-06-01T14:22:02.959Z 0.30000000000000426
2018-06-01T14:23:04.992Z 0.3999999999999986
2018-06-01T14:24:07.024Z 0.10000000000000142
2018-06-01T14:25:09.059Z 0.19999999999999574
2018-06-01T14:26:11.094Z 0
2018-06-01T14:27:13.127Z 0.10000000000000142
2018-06-01T14:28:15.158Z 0.20000000000000284
2018-06-01T14:29:20.027Z 0.09999999999999432
2018-06-01T14:30:22.476Z 0.10000000000000142
2018-06-01T14:30:53.918Z 0.6000000000000014
2018-06-01T14:31:55.968Z 0.5
2018-06-01T14:32:58.007Z 0.5
2018-06-01T14:34:00.046Z 0.20000000000000284
2018-06-01T14:35:02.075Z 0.3999999999999986
2018-06-01T14:36:04.102Z 0.3999999999999986
2018-06-01T14:37:06.136Z 0.20000000000000284
2018-06-01T14:38:08.201Z 0

So far so good.

I’m now trying to stitch these readings back to cumulative total.

I can use cumulative_sum() for this, for example:

SELECT 
    cumulative_sum(nnd) 
FROM 
    (SELECT 
        non_negative_difference(rain) as nnd 
     FROM 
        weather
     WHERE 
        $time_query )

…which yields:

2018-06-01T14:21:00.926Z 0
2018-06-01T14:22:02.959Z 0.30000000000000426
2018-06-01T14:23:04.992Z 0.7000000000000028
2018-06-01T14:24:07.024Z 0.8000000000000043
2018-06-01T14:25:09.059Z 1
2018-06-01T14:26:11.094Z 1
2018-06-01T14:27:13.127Z 1.1000000000000014
2018-06-01T14:28:15.158Z 1.3000000000000043
2018-06-01T14:29:20.027Z 1.3999999999999986
2018-06-01T14:30:22.476Z 1.5
2018-06-01T14:30:53.918Z 2.1000000000000014
2018-06-01T14:31:55.968Z 2.6000000000000014
2018-06-01T14:32:58.007Z 3.1000000000000014
2018-06-01T14:34:00.046Z 3.3000000000000043
2018-06-01T14:35:02.075Z 3.700000000000003
2018-06-01T14:36:04.102Z 4.100000000000001
2018-06-01T14:37:06.136Z 4.300000000000004
2018-06-01T14:38:08.201Z 4.300000000000004

Looking good!

But, as above when I add a Aggregate+GroupBy on the outer query, it forces the same on the subquery.