2 fill() problems

[Dieterbe]

I noticed two things with fill():

1. filling without aggregation function

using influxdb-cli:

graphite> select time, value from stats.gauges.dfvimeo.last_good_epoch group by time(60s) fill(null) where time > 1406230367s and time < 1406231207s
┌───────────────┬─────────────────┬──────────┐
│ time          │ sequence_number │ value    │
├───────────────┼─────────────────┼──────────┤
│ 1406231207000 │ 1               │ 10885813 │
│ 1406231147000 │ 1               │ 10885813 │
│ 1406231087000 │ 1               │ 10885813 │
│ 1406231027000 │ 1               │ 10885813 │
│ 1406230967000 │ 1               │ 10885813 │
│ 1406230367000 │ 1               │ 10885813 │
└───────────────┴─────────────────┴──────────┘
Query took  2 ms

there is a 600 second gap between the last and second-to-last point, so the gap is within the data range. Using where time > 1406230368s to exclude 406230367000 creates the empty range at the end, which is nothing new given #426 , so this a different case, also evidenced by the fact that selecting max(value) activates the filling (see below).
So consider this a tentative feature request to make the fill work without aggregation function. there is some ambiguity about what should happen when the input dataset has too many points per given group-interval. In that case I would assume that its the users' responsability to assure there's a datapoint per group-by-interval, and if the user has too many, we can just use the first or last. Of course, all of this might be too much of a hassle, in which case I'm fine with not executing on this feature request and just using whatever is the most lightweight aggregation function (first() ?), even when no aggregation should strictly be needed.

2. bad values returned
   continuing.... let's add max() around the value:

graphite> select time, max(value) from stats.gauges.dfvimeo.last_good_epoch group by time(60s) fill(null) where time > 1406230367s and time < 1406231207s
┌───────────────┬──────────────────────────┐
│ time          │ max                      │
├───────────────┼──────────────────────────┤
│ 1406231160000 │ 10885813                 │
│ 1406231100000 │ 10885813                 │
│ 1406231040000 │ 10885813                 │
│ 1406230980000 │ 10885813                 │
│ 1406230920000 │ 10885813                 │
│ 1406230860000 │ -1.7976931348623157e+308 │
│ 1406230800000 │ -1.7976931348623157e+308 │
│ 1406230740000 │ -1.7976931348623157e+308 │
│ 1406230680000 │ -1.7976931348623157e+308 │
│ 1406230620000 │ -1.7976931348623157e+308 │
│ 1406230560000 │ -1.7976931348623157e+308 │
│ 1406230500000 │ -1.7976931348623157e+308 │
│ 1406230440000 │ -1.7976931348623157e+308 │
│ 1406230380000 │ -1.7976931348623157e+308 │
│ 1406230320000 │ 10885813                 │
└───────────────┴──────────────────────────┘
Query took  3 ms

now the filling activates, but we get bogus values?

confirm with different fill value:

graphite> select time, max(value) from stats.gauges.dfvimeo.last_good_epoch group by time(60s) fill(10) where time > 1406230367s and time < 1406231207s
┌───────────────┬──────────────────────────┐
│ time          │ max                      │
├───────────────┼──────────────────────────┤
│ 1406231160000 │ 10885813                 │
│ 1406231100000 │ 10885813                 │
│ 1406231040000 │ 10885813                 │
│ 1406230980000 │ 10885813                 │
│ 1406230920000 │ 10885813                 │
│ 1406230860000 │ -1.7976931348623157e+308 │
│ 1406230800000 │ -1.7976931348623157e+308 │
│ 1406230740000 │ -1.7976931348623157e+308 │
│ 1406230680000 │ -1.7976931348623157e+308 │
│ 1406230620000 │ -1.7976931348623157e+308 │
│ 1406230560000 │ -1.7976931348623157e+308 │
│ 1406230500000 │ -1.7976931348623157e+308 │
│ 1406230440000 │ -1.7976931348623157e+308 │
│ 1406230380000 │ -1.7976931348623157e+308 │
│ 1406230320000 │ 10885813                 │
└───────────────┴──────────────────────────┘
Query took  3 ms
graphite> 

just to make sure influxdb-cli is not doing something funky, here's the http sniff to influxdb, which confirms influx returns the values.

####
T 127.0.0.1:51606 -> 127.0.0.1:8086 [AP]
GET /db/graphite/series?u=root&p=root&q=select%20time%2C%20max(value)%20from%20stats.gauges.dfvimeo.last_good_epoch%20group%20by%20time(60s)%20fill(10)%20where%20time%20%3E%201406230367s%20and%20time%20%3C%201406231207s&time_precision=m&chunked=false HTTP/1.1.
host: dfvimeographite1:8086.
accept: application/json.
Connection: keep-alive.
.

##
T 127.0.0.1:8086 -> 127.0.0.1:51606 [AP]
HTTP/1.1 200 OK.
Access-Control-Allow-Headers: Origin, X-Requested-With, Content-Type, Accept.
Access-Control-Allow-Methods: GET, POST, PUT, DELETE.
Access-Control-Allow-Origin: *.
Access-Control-Max-Age: 2592000.
Content-Type: application/json.
X-Influxdb-Version: InfluxDB vdev (git: 78b682f) (leveldb: 1.15).
Date: Thu, 24 Jul 2014 20:51:36 GMT.
Content-Length: 638.
.
[{"name":"stats.gauges.dfvimeo.last_good_epoch","columns":["time","max"],"points":[[1406231160000,1.0885813e+07],[1406231100000,1.0885813e+07],[1406231040000,1.0885813e+07],[1406230980000,1.0885813e+07],[1406230920000,1.0885813e+07],[1406230860000,-1.7976931348623157e+308],[1406230800000,-1.7976931348623157e+308],[1406230740000,-1.7976931348623157e+308],[1406230680000,-1.7976931348623157e+308],[1406230620000,-1.7976931348623157e+308],[1406230560000,-1.7976931348623157e+308],[1406230500000,-1.7976931348623157e+308],[1406230440000,-1.7976931348623157e+308],[1406230380000,-1.7976931348623157e+308],[1406230320000,1.0885813e+07]]}]

[otoolep]

When I worked on support for null as a fill() option, I only tested using mean as an aggregator. It seemed to work fine (as the unit tests show). Perhaps this is specifically max?

[Dieterbe]

@otoolep aha, you're right that mean() works. I decided to try all currently documented aggregation functions (with otherwise the same query as 2) above). looks like i opened pandora's box...

here's every aggregation function and the result

mean fill value
count fill value
min 1.7976931348623157e+308
max -1.7976931348623157e+308
mode Internal Error: interface conversion: interface is nil, not *engine.ModeAggregatorState
median fill value
distinct Error: Internal Error: runtime error: invalid memory address or nil pointer dereference
count(distinct) fill value
percentile(value,10) fill value
histogram Internal Error: interface conversion: interface is nil, not engine.HistogramAggregatorState
derivative no records?
sum 0
stddev no filling happens, only original rows (stddev 0)
first Internal Error: interface conversion: interface is nil, not engine.FirstOrLastAggregatorState
last Internal Error: interface conversion: interface is nil, not engine.FirstOrLastAggregatorState
difference no records returned.
top(value,1): fill value
bottom(value,1): fill value

[Dieterbe]

you can easily reproduce a lot of these problems like so:
(I use influx-cli here which simplifies the process)
put this in file influx-issue-780.sql

create db issue-780
\db issue-780
bind
insert into demo values (60000, 0, 10)
insert into demo values (120000, 0, 10)
insert into demo values (300000, 0, 10)
select * from demo
select time, mean(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
select time, count(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
select time, min(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
select time, max(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
select time, mode(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
select time, median(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
select time, distinct(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
delete db issue-780

and then just:

cat influx-issue-780.sql | influx-cli

result (using 0.8 rc5)

$ cat influx-issue-780.sql | influx-cli
influx> create db issue-780
influx> \db issue-780
influx> bind
influx> insert into demo values (60000, 0, 10)
influx> insert into demo values (120000, 0, 10)
influx> insert into demo values (300000, 0, 10)
influx> select * from demo
## demo
                time sequence_number               value
       300000.000000        0.000000           10.000000
       120000.000000        0.000000           10.000000
        60000.000000        0.000000           10.000000
influx> select time, mean(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
## demo
                time                mean
       300000.000000                  10
       240000.000000               <nil>
       180000.000000               <nil>
       120000.000000                  10
        60000.000000                  10
influx> select time, count(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
## demo
                time               count
       300000.000000                   1
       240000.000000               <nil>
       180000.000000               <nil>
       120000.000000                   1
        60000.000000                   1
influx> select time, min(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
## demo
                time                 min
       300000.000000                  10
       240000.0000001.7976931348623157e+308
       180000.0000001.7976931348623157e+308
       120000.000000                  10
        60000.000000                  10
influx> select time, max(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
## demo
                time                 max
       300000.000000                  10
       240000.000000-1.7976931348623157e+308
       180000.000000-1.7976931348623157e+308
       120000.000000                  10
        60000.000000                  10
influx> select time, mode(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
Server returned (400): Internal Error: interface conversion: interface is nil, not *engine.ModeAggregatorState
influx> select time, median(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
## demo
                time              median
       300000.000000                  10
       240000.000000               <nil>
       180000.000000               <nil>
       120000.000000                  10
        60000.000000                  10
influx> select time, distinct(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
Server returned (400): Internal Error: runtime error: invalid memory address or nil pointer dereference
influx> delete db issue-780
influx> 

[Dieterbe]

more complete versions that checks all functions, and for some even a few different arguments

create db issue-780
\db issue-780
bind
insert into demo values (60000, 0, 10)
insert into demo values (120000, 0, 10)
insert into demo values (300000, 0, 10)
select * from demo
select time, count(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
select time, mean(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
select time, min(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
select time, max(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
select time, mode(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
select time, median(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
select time, distinct(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
select time, count(distinct(value)) from demo group by time(60s) fill(null) where time > 60s and time < 300s
select time, percentile(value, 10) from demo group by time(60s) fill(null) where time > 60s and time < 300s
select time, histogram(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
select time, histogram(value, 10.0) from demo group by time(60s) fill(null) where time > 60s and time < 300s
select time, derivative(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
select time, sum(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
select time, stddev(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
select time, first(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
select time, last(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
select time, difference(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
select time, top(value,0) from demo group by time(60s) fill(null) where time > 60s and time < 300s
select time, top(value,1) from demo group by time(60s) fill(null) where time > 60s and time < 300s
select time, top(value,2) from demo group by time(60s) fill(null) where time > 60s and time < 300s
select time, top(value,10) from demo group by time(60s) fill(null) where time > 60s and time < 300s
select time, bottom(value,0) from demo group by time(60s) fill(null) where time > 60s and time < 300s
select time, bottom(value,1) from demo group by time(60s) fill(null) where time > 60s and time < 300s
select time, bottom(value,2) from demo group by time(60s) fill(null) where time > 60s and time < 300s
select time, bottom(value,10) from demo group by time(60s) fill(null) where time > 60s and time < 300s
delete db issue-780

$ cat influx-issue-780.sql | influx-cli
influx> create db issue-780
influx> \db issue-780
influx> bind
influx> insert into demo values (60000, 0, 10)
influx> insert into demo values (120000, 0, 10)
influx> insert into demo values (300000, 0, 10)
influx> select * from demo
## demo
                time sequence_number               value
       300000.000000        0.000000           10.000000
       120000.000000        0.000000           10.000000
        60000.000000        0.000000           10.000000
influx> select time, count(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
## demo
                time               count
       300000.000000                   1
       240000.000000               <nil>
       180000.000000               <nil>
       120000.000000                   1
        60000.000000                   1
influx> select time, mean(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
## demo
                time                mean
       300000.000000                  10
       240000.000000               <nil>
       180000.000000               <nil>
       120000.000000                  10
        60000.000000                  10
influx> select time, min(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
## demo
                time                 min
       300000.000000                  10
       240000.0000001.7976931348623157e+308
       180000.0000001.7976931348623157e+308
       120000.000000                  10
        60000.000000                  10
influx> select time, max(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
## demo
                time                 max
       300000.000000                  10
       240000.000000-1.7976931348623157e+308
       180000.000000-1.7976931348623157e+308
       120000.000000                  10
        60000.000000                  10
influx> select time, mode(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
Server returned (400): Internal Error: interface conversion: interface is nil, not *engine.ModeAggregatorState
influx> select time, median(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
## demo
                time              median
       300000.000000                  10
       240000.000000               <nil>
       180000.000000               <nil>
       120000.000000                  10
        60000.000000                  10
influx> select time, distinct(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
Server returned (400): Internal Error: runtime error: invalid memory address or nil pointer dereference
influx> select time, count(distinct(value)) from demo group by time(60s) fill(null) where time > 60s and time < 300s
## demo
                time               count
       300000.000000                   1
       240000.000000               <nil>
       180000.000000               <nil>
       120000.000000                   1
        60000.000000                   1
influx> select time, percentile(value, 10) from demo group by time(60s) fill(null) where time > 60s and time < 300s
## demo
                time          percentile
       300000.000000                   0
       240000.000000               <nil>
       180000.000000               <nil>
       120000.000000                   0
        60000.000000                   0
influx> select time, histogram(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
Server returned (400): Internal Error: interface conversion: interface is nil, not engine.HistogramAggregatorState
influx> select time, histogram(value, 10.0) from demo group by time(60s) fill(null) where time > 60s and time < 300s
Server returned (400): Internal Error: interface conversion: interface is nil, not engine.HistogramAggregatorState
influx> select time, derivative(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
influx> select time, sum(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
## demo
                time                 sum
       300000.000000                  10
       240000.000000                   0
       180000.000000                   0
       120000.000000                  10
        60000.000000                  10
influx> select time, stddev(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
## demo
                time              stddev
       300000.000000                   0
       120000.000000                   0
        60000.000000                   0
influx> select time, first(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
Server returned (400): Internal Error: interface conversion: interface is nil, not engine.FirstOrLastAggregatorState
influx> select time, last(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
Server returned (400): Internal Error: interface conversion: interface is nil, not engine.FirstOrLastAggregatorState
influx> select time, difference(value) from demo group by time(60s) fill(null) where time > 60s and time < 300s
influx> select time, top(value,0) from demo group by time(60s) fill(null) where time > 60s and time < 300s
Server returned (400): Internal Error: runtime error: index out of range
influx> select time, top(value,1) from demo group by time(60s) fill(null) where time > 60s and time < 300s
## demo
                time                 top
       300000.000000                  10
       240000.000000               <nil>
       180000.000000               <nil>
       120000.000000                  10
        60000.000000                  10
influx> select time, top(value,2) from demo group by time(60s) fill(null) where time > 60s and time < 300s
## demo
                time                 top
       300000.000000                  10
       240000.000000               <nil>
       180000.000000               <nil>
       120000.000000                  10
        60000.000000                  10
influx> select time, top(value,10) from demo group by time(60s) fill(null) where time > 60s and time < 300s
## demo
                time                 top
       300000.000000                  10
       240000.000000               <nil>
       180000.000000               <nil>
       120000.000000                  10
        60000.000000                  10
influx> select time, bottom(value,0) from demo group by time(60s) fill(null) where time > 60s and time < 300s
Server returned (400): Internal Error: runtime error: index out of range
influx> select time, bottom(value,1) from demo group by time(60s) fill(null) where time > 60s and time < 300s
## demo
                time              bottom
       300000.000000                  10
       240000.000000               <nil>
       180000.000000               <nil>
       120000.000000                  10
        60000.000000                  10
influx> select time, bottom(value,2) from demo group by time(60s) fill(null) where time > 60s and time < 300s
## demo
                time              bottom
       300000.000000                  10
       240000.000000               <nil>
       180000.000000               <nil>
       120000.000000                  10
        60000.000000                  10
influx> select time, bottom(value,10) from demo group by time(60s) fill(null) where time > 60s and time < 300s
## demo
                time              bottom
       300000.000000                  10
       240000.000000               <nil>
       180000.000000               <nil>
       120000.000000                  10
        60000.000000                  10
influx> delete db issue-780
influx> 

[nicolai86]

Just a heads up: @jvshahid told me that @dgnorton seems to be working on this. I couldn't find a matching PR thought. Any hint on how to see progress/ maybe assist him in tackling this?

[dgnorton]

@nicolai86 , Working on it in the "fill" branch (bad name for it)... https://github.com/influxdb/influxdb/tree/fill . PR coming soon.

[nicolai86]

@dgnorton thank you ! I tried to fix this locally myself but didn't find the origin of the behaviour so I'm really interested in the fix to help myself fiddle around with influxdb better.