influxdata / influxdata/influxdb
[Feature Request] COUNT(): return zeros instead of no results
- Dominant language
- Rust
- Stars
- 31.7k
- Forks
- 3.7k
- Avg merge
- 13h 37m
- Merged PRs (30d)
- 8
Description
### Feature Request
**Proposal:** Provide a way to return zeros for count() queries instead of no results.
**Current behavior:** (Note: this feature request is a followup to what I mistakenly thought was a bug. That github issue is here: https://github.com/influxdata/influxdb/issues/6400)
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)
>
```
**Desired behavior:** Instead of not returning any results I would like the second query to return zeros:
```
name: myMeasurement
------------------------
time count
1460757720000000000 0
1460757780000000000 0
```
**Possible Implementation:** @jsternberg mentiond in https://github.com/influxdata/influxdb/issues/6400 that, "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."
**Perhaps an optional windowing period could be passed to count() to generate the list of tag combinations that could then be filled if missing?**
**Use case:** This is rendering my Kapacitor alerts, which only sometimes fire due to inconsistent zero counts vs. no results, essentially useless. Kapacitor alerts that test for "count" == 0 cannot be relied upon to trigger when expected. This doesn't seem like it's Kapacitor's fault -- Influx is only returning zeros in the groups when there is a data point in the window.
Contributor guide
Research direction
Start by reproducing the two COUNT("myField") queries with GROUP BY TIME(1m) from the issue and compare their results. Investigate the COUNT and time-grouping query paths, including how tag combinations are determined when windows have no observations. Done means the second query can reliably return zero-valued groups without making unsupported assumptions about tags.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- rust
- Domain
- databases
- Issue type
- Feature
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 25/100