Influxdb: COUNT() returning no results instead of zeros

[gargooza]

Bug report

InfluxDB v0.12.1 (git: 0.12 e094138084855d444195b252314dfee9eae34cab)

Check out these two simple queries. The first query returns zeros for all of the grouped minutes for which there are no observations to count. That is the expected behavior -- return zero when there are no observations to count. The second query returns no results at all. That seems incorrect. The two queries only differ in the time part of the WHERE clause, but there is time overlap between them.

> SELECT COUNT("myField") FROM "myMeasurement" WHERE time > 1460757540000000000 AND time < 1460757840000000000 GROUP BY TIME(1m)
name: myMeasurement
------------------------
time                count
1460757540000000000 0
1460757600000000000 0
1460757660000000000 19
1460757720000000000 0
1460757780000000000 0

> SELECT COUNT("myField") FROM "myMeasurement" WHERE time > 1460757720000000000 AND time < 1460757840000000000 GROUP BY TIME(1m)
>

Why does the second query not return any results? COUNT() should be returning zeros. Instead of not returning any results I would expect the second query to return:

name: myMeasurement
------------------------
time                count
1460757720000000000 0
1460757780000000000 0

This bug is killing my Kapacitor alerts which only sometimes fire, rendering them inconsistent and essentially useless. Kapacitor alerts that test for "count" == 0 can't be relied upon to fire when expected. But this doesn't seem like it's Kapacitor's fault -- shouIdn't Influx be returning zeros when there is no data to count?

[e-dard]

/cc @jsternberg

[jsternberg]

@gargooza this is expected. In order to know what series need to be filled in, there needs to be at least one point with that name/tag combination. Since tags can be any arbitrary combination, it's impossible for us to know which combinations to fill without having some basis. If there's a name/tag combination such as cpu,host=server01 we can't assume that we should fill in values for cpu,host=server02 as zero values since we don't know if that's a valid name/tag combination.

The FILL(0) option is just to fill in transient missing data and shouldn't be relied on as a substitute for reporting an actual zero value.

Does this explanation help?

[gargooza]

Thank you, that explanation does help. I can see why you can't assume what values you can/should fill in for all of the tags. The exponential number of possible tag value combinations alone would stop a query dead in its tracks.

I'll have to think about this problem I am having some more. My first thought was to do some kind of outer join on tag values with my count() query. Then I could fill null count values with zeros. But it seems that any join that isn't strictly time based is not supported by Influx.

Since the issue I am having is manifesting itself in Kapacitor, I'll go back there to see what options I might have to deal with missing counts.

[jsternberg]

@gargooza can you open a feature request about this? While technically it's needed, it is a bit weird and it's something that we'll try to keep in mind. If you think of anything that works, you can also include it on the feature request.

At the moment, you might need to have a third party aggregate the counts and report them separately as integers to influxd.

@jwilder thoughts on this?

[gargooza]

@jsternberg sure thing re: feature request.

[iridos]

Actually that explanation doesn't help me one bit. You don't need to know "what series to fill in". It's a bug in the count() function, which should return a count of 0 if there is no data. It shouldn't be undefined. The count function doesn't need to know what series had a count zero, it just needs to return a zero.
Yes, the host tags and all other tags on that query would be empty. But why should that stop COUNT() to return a 0 instead of nothing? Yes, obviously if the count is 0 and it's grouped by host or something, then host is undefined or emtpy, but not the count.

@jsternberg : I think your answer assumes that the bug is in the fill(0) functionality… it's not. It's in the COUNT() function that returns "no result" instead of 0 (as the bug title already clearly states)

[mertbakir]

I am not happy write in an old issue but I wonder why is this not fixed? Is it not considered as a bug or is it hard to fix because of some internal design?

I've read about flux and if statements, yet using an if statement for this is not intuitive. In my opinion, count() should always return a non-negative integer and 0 if there is no value. Using join | .fill( ) in kapacitor worked for me for a certain case but dashboarding is painful when count in INFLUXQL returns null. Dashboards become meaningless and ugly.

Regards,

[ceds]

Would also love to see a fix for this

[shrv]

+1
Otherwise, dashboards become useless. For example, in the Grafana, visulisation replace the return null from the count() to zero.
And as just a suggestion for improvement to Chronograf.
In Grafana in tables have mapping, which is convenient for a number of data obtained from the SNMP. you can change the returned data as numbers to clear text.