[0.9.3-rc1-ish] Aggregate function 'mean' don't return all available points

[papylhomme]

I'm experiencing a strange behaviour when querying the data:

• simple query select * from users_value where host='danska' and time > now() - 2h returns first result at 18h00 and points every 10 seconds till 19:30:00 (request results: https://gist.github.com/papylhomme/5c4afb2004142f158587#file-res_select-json)
• query select mean(value) from users_value where host='danska' and time > now() - 2h group by time(10s) returns nothing for 17:XX:XX, 1 value at 18:00:00 and nothing for 18:XX:XX, and a value for each 10 seconds in 19:XX:XX (request results: https://gist.github.com/papylhomme/5c4afb2004142f158587#file-res_mean-json)
• query select derivative(value) from users_value where host='danska' and time > now() - 2h group by time(10s) works as expected with a point every 10 seconds. (request results: https://gist.github.com/papylhomme/5c4afb2004142f158587#file-res_derivative-json)

Please note this behaviour appeared after a fix (#3702) to ensure shard groups of 1 hour (see logs below).

août 22 19:38:22 danska influxd[15923]: [retention] 2015/08/22 19:38:22 shard '69', start time '2015-08-22 16:00:00 +0000 UTC', end time '2015-08-22 17:00:00 +0000 UTC', deleted at '0001-01-01
août 22 19:38:22 danska influxd[15923]: [retention] 2015/08/22 19:38:22 shard '70', start time '2015-08-22 17:00:00 +0000 UTC', end time '2015-08-22 18:00:00 +0000 UTC', deleted at '0001-01-01
août 22 19:38:22 danska influxd[15923]: [retention] 2015/08/22 19:38:22 shard '71', start time '2015-08-22 18:00:00 +0000 UTC', end time '2015-08-22 19:00:00 +0000 UTC', deleted at '0001-01-01

[otoolep]

At first pass, I don't see how #3702 could affect the data that is queried, since it only kicks in when retention policies are altered, and only affects new shards.

Is there any chance you are seeing #3781 ?

[beckettsean]

@papylhomme from the first bullet point it sounds like the timestamps are all exactly 10 seconds apart, since you get results every 10 seconds without a GROUP BY time(10s).

Given that, perhaps there's a strange edge case behavior when grouping by a time interval that's exactly equal to the point spacing. Do you get meaningful results from select mean(value) from users_value where host='danska' and time > now() - 2h group by time(14s)

Also, what version of InfluxDB are you running?

[papylhomme]

Yes my points are 10 seconds apart, they're coming from a collectd daemon with polling set to 10s. The query with group by time(14s) exhibit the same behaviour, with slight shift in the resulting timestamp (the first 0 is at 17:59:56 instead of 18:00:00).

Regarding the version, it was compiled some days ago against master, head is 4753346

[beckettsean]

@papylhomme I have a theory but I'm not sure it completely holds up.

From #3702 we know you have a short retention policy duration. It's entirely possible that shards have a time range of one hour. Data types are consistent per shard, but it is possible to have the same field with different data types in two different shards. Since the behavior changes exactly on the 1800 and 1900 boundaries, that seems consistent.

Somehow the 17xx data and the 19xx data are the correct data type (int64 or float64) but the 18xx data are something invalid for MEAN, like boolean or string.

I can't explain why DERIVATIVE still returns consistent results for 18xx points, but it is a recently implemented function and it's possible it is automatically and perhaps incorrectly filling 0 for null results.

@jwilder what would DERIVATIVE return if run on a string or boolean data type?

[papylhomme]

Seeing new commits related to the mappers I tried with a version build against 3f525ff, behaviour for select mean(value) is the same, but now there is a gap in my values: select * and select derivative(value) are missing values for the whole 13h hour. Values for 12h and 14h exists.

I'll wait for the data to accumulate for some hours and make another test

[beckettsean]

@papylhomme any updates on this issue?

[papylhomme]

Unfortunately no, I'm currently reinstalling the system after a disk crash. I'll test with the latest version and let you know asap.

[papylhomme]

Running the latest HEAD with a fresh database everything is ok, the results from mean are coherent with the content of the database. Problem solved as far as I'm concerned

[beckettsean]

Great, thanks for the follow-up @papylhomme. I wish we had found the cause, but as long as the behavior is gone that's enough.