Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[Feature Request] COUNT(): return zeros instead of no results #6412

Open
gargooza opened this issue Apr 18, 2016 · 14 comments

Comments

@gargooza
Copy link

commented Apr 18, 2016

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: #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 #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.

@gargooza gargooza changed the title COUNT(): return zeros instead of no results [Feature Request] COUNT(): return zeros instead of no results Apr 18, 2016

@BrannonKing

This comment has been minimized.

Copy link

commented Apr 19, 2016

+1. I also thought it was weird to run a count and get no results.

@altenschmidt

This comment has been minimized.

Copy link

commented May 31, 2016

+1.

Another implementation proposal considering jsternberg's comment:
Use all actually existing tag combinations, similar to what 'show series' puts out. That eliminates the necessity for a windowing period as proposed by gargooza.

@beckettsean

This comment has been minimized.

Copy link
Contributor

commented Jul 6, 2016

@gargooza does https://docs.influxdata.com/kapacitor/v0.13/nodes/join_node/#fill meet your needs? It's a fill() option implemented directly in Kapacitor

@nirpodo

This comment has been minimized.

Copy link

commented Jul 17, 2016

+1

@schmurfy

This comment has been minimized.

Copy link
Contributor

commented Nov 7, 2016

is there any chance this is getting somewhere ? any update on the issue ?
I really wonder what was the logic behind this decision, if anyone know I am really curious.
The expectation that a function called COUNT can only returns an integer seems reasonnable and this is what I expected too.

@MasterDDT

This comment has been minimized.

Copy link

commented Feb 23, 2017

+1, fill workaround doesn't help either

@yellowred

This comment has been minimized.

Copy link

commented Mar 16, 2017

+1

6 similar comments
@mariomann

This comment has been minimized.

Copy link

commented Mar 16, 2017

+1

@oberschlauberger

This comment has been minimized.

Copy link

commented Mar 16, 2017

+1

@tangerstein

This comment has been minimized.

Copy link

commented Mar 17, 2017

+1

@birdy81

This comment has been minimized.

Copy link

commented Mar 17, 2017

+1

@stefansiegl

This comment has been minimized.

Copy link

commented Mar 17, 2017

+1

@matthias-huber

This comment has been minimized.

Copy link

commented Mar 17, 2017

+1

@jsternberg

This comment has been minimized.

Copy link
Contributor

commented Mar 17, 2017

I'm locking this due to excessive 👍 . We'll take a look at this in the future.

@influxdata influxdata locked and limited conversation to collaborators Mar 17, 2017

@dgnorton dgnorton added the 1.x label Jan 7, 2019

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Projects
None yet
You can’t perform that action at this time.