Derivative of nulls is not null

[ScoreUnder]

System info:

influxdb 0.13.0

Steps to reproduce:

1. Use a measurement with some gaps in it (e.g. several hours of no data)
2. Run a derivative or non_negative_derivative over the mean/median/mode(/etc.) of those values, grouping by a short time period, filling with fill(null)

Expected behavior:

The derivative function should fill with nulls where all its inputs are null.

Actual behavior:

The derivative function behaves the same as if fill(none) had been used, and suppresses records when its inputs are all null.

> SELECT non_negative_derivative(mean("value")) FROM "disk_read" WHERE "host" = 'kirisame' AND "instance" = 'sda' AND time >= now() - 24h GROUP BY "instance", time(1h) fill(none)
name: disk_read
tags: instance=sda
time            non_negative_derivative
----            -----------------------
1471622400000000000 510468.42365956306
1471626000000000000 2.2326707869478226e+07
1471694400000000000 4.2967470870584756e+07
1471708800000000000 9.949401068170846e+06

> SELECT non_negative_derivative(mean("value")) FROM "disk_read" WHERE "host" = 'kirisame' AND "instance" = 'sda' AND time >= now() - 24h GROUP BY "instance", time(1h) fill(null)
name: disk_read
tags: instance=sda
time            non_negative_derivative
----            -----------------------
1471622400000000000 510468.42365956306
1471626000000000000 2.2326707869478226e+07
1471694400000000000 4.2967470870584756e+07
1471708800000000000 9.949401068170846e+06

Additional info:

Seems like it was previously bug reported in #780 but the fix missed these functions.

In practice, the impact of this is incorrect grafana graphs (network usage here):

[jsternberg]

I believe this is the intended behavior of derivative. The fill() for derivative is intended for the function inside of it so if you do:

SELECT derivative(mean(value)) FROM cpu WHERE ... GROUP BY time(5m) fill(null)

The values fed to derivative will have nulls. But derivative itself skips over the null values in the current behavior. Null values are considered not to exist so it finds the derivative between the two points to even out the resulting graph. If you want it to use some value for the derivative (like zero), you can use fill(0). What's the graph you think should be returned by a derivative with null values?

[britcey]

I've got the same issue; if both points fed to the derivative are null, I'd like it to output null (or even one point? less sure on that) - I want to see a gap in my graph for that period. Is that incorrect behavior for a derivative function? Do we need something else?

e.g., I'd rather see the gap like:

select mean("bytes_recv") FROM "net" where time > now() - 24h AND "host" = 'xxxx' GROUP BY time(2h)
name: net
---------
time            mean
2016-08-31T16:00:00Z    2.967001493183443e+11
2016-08-31T18:00:00Z    2.971375809658354e+11
2016-08-31T20:00:00Z    2.9758520667994586e+11
2016-08-31T22:00:00Z    2.979088292255e+11
2016-09-01T00:00:00Z    2.980935009536105e+11
2016-09-01T02:00:00Z
2016-09-01T04:00:00Z
2016-09-01T06:00:00Z
2016-09-01T08:00:00Z    2.9952670532007214e+11
2016-09-01T10:00:00Z    2.998899557329299e+11
2016-09-01T12:00:00Z    3.0023459804905347e+11
2016-09-01T14:00:00Z    3.006757222235958e+11
2016-09-01T16:00:00Z    3.009559445886804e+11

Instead of skipping over the null output:

> select non_negative_derivative(mean("bytes_recv")) FROM "net" where time > now() - 24h AND "host" = 'xxxx' GROUP BY time(2h)
name: net
---------
time            non_negative_derivative
2016-08-31T16:00:00Z    4.2512097606988525e+08
2016-08-31T18:00:00Z    4.64121579711792e+08
2016-08-31T20:00:00Z    4.4762571411047363e+08
2016-08-31T22:00:00Z    3.236225455541382e+08
2016-09-01T00:00:00Z    1.8467172811047363e+08
2016-09-01T08:00:00Z    3.583010916154175e+08
2016-09-01T10:00:00Z    3.6325041285772705e+08
2016-09-01T12:00:00Z    3.446423161235962e+08
2016-09-01T14:00:00Z    4.411241745423584e+08
2016-09-01T16:00:00Z    2.798738290499878e+08

[matak1]

Any workaround found for this? I am trying to monitor interface statistics as OP and running into this issue as well.

List of values with fill(null):

time                    mean
----                    ----
2017-01-15T21:44:00Z
2017-01-15T21:45:00Z
2017-01-15T21:46:00Z    1.46948564e+08
2017-01-15T21:47:00Z    1.46958132e+08
2017-01-15T21:48:00Z    1.4697539e+08
2017-01-15T21:49:00Z    1.46986751e+08
2017-01-15T21:50:00Z    1.4700753e+08
2017-01-15T21:51:00Z
2017-01-15T21:52:00Z
2017-01-15T21:53:00Z    1.47026106e+08
2017-01-15T21:54:00Z    1.47029611e+08
2017-01-15T21:55:00Z    1.47057513e+08
2017-01-15T21:56:00Z    1.47072717e+08
2017-01-15T21:57:00Z    1.47077828e+08
2017-01-15T21:58:00Z
2017-01-15T21:59:00Z
2017-01-15T22:00:00Z    1.47103466e+08
2017-01-15T22:01:00Z    1.47118698e+08
2017-01-15T22:02:00Z    1.47120911e+08
2017-01-15T22:03:00Z    1.47139794e+08
2017-01-15T22:04:00Z    1.47152878e+08
2017-01-15T22:05:00Z    1.47162111e+08
2017-01-15T22:06:00Z    1.47171321e+08
2017-01-15T22:07:00Z    1.47181758e+08
2017-01-15T22:08:00Z    1.4718567e+08
2017-01-15T22:09:00Z    1.47188846e+08
2017-01-15T22:10:00Z    1.47205286e+08
2017-01-15T22:11:00Z    1.47214065e+08
2017-01-15T22:12:00Z
2017-01-15T22:13:00Z
2017-01-15T22:14:00Z`

Derivative with fill(null) skips to next time immediately and shown as a continuous line in graphing applications such as Grafana:

time                    derivative
----                    ----------
2017-01-15T21:47:00Z    159.46666666666667
2017-01-15T21:48:00Z    287.6333333333333
2017-01-15T21:49:00Z    189.35
2017-01-15T21:50:00Z    346.31666666666666
2017-01-15T21:53:00Z    103.2
2017-01-15T21:54:00Z    58.416666666666664
2017-01-15T21:55:00Z    465.03333333333336
2017-01-15T21:56:00Z    253.4
2017-01-15T21:57:00Z    85.18333333333334
2017-01-15T22:00:00Z    142.43333333333334
2017-01-15T22:01:00Z    253.86666666666667
2017-01-15T22:02:00Z    36.88333333333333
2017-01-15T22:03:00Z    314.71666666666664
2017-01-15T22:04:00Z    218.06666666666666
2017-01-15T22:05:00Z    153.88333333333333
2017-01-15T22:06:00Z    153.5
2017-01-15T22:07:00Z    173.95
2017-01-15T22:08:00Z    65.2
2017-01-15T22:09:00Z    52.93333333333333
2017-01-15T22:10:00Z    274
2017-01-15T22:11:00Z    146.31666666666666
2017-01-15T22:14:00Z    108.98333333333333

Derivative with a fill(0) results in huge spikes on graph:

time                    derivative
----                    ----------
2017-01-15T21:44:00Z    0
2017-01-15T21:45:00Z    0
2017-01-15T21:46:00Z    2.4491427333333334e+06
2017-01-15T21:47:00Z    159.46666666666667
2017-01-15T21:48:00Z    287.6333333333333
2017-01-15T21:49:00Z    189.35
2017-01-15T21:50:00Z    346.31666666666666
2017-01-15T21:51:00Z    -2.4501255e+06
2017-01-15T21:52:00Z    0
2017-01-15T21:53:00Z    2.4504351e+06
2017-01-15T21:54:00Z    58.416666666666664
2017-01-15T21:55:00Z    465.03333333333336
2017-01-15T21:56:00Z    253.4
2017-01-15T21:57:00Z    85.18333333333334
2017-01-15T21:58:00Z    -2.4512971333333333e+06
2017-01-15T21:59:00Z    0
2017-01-15T22:00:00Z    2.451724433333333e+06
2017-01-15T22:01:00Z    253.86666666666667
2017-01-15T22:02:00Z    36.88333333333333
2017-01-15T22:03:00Z    314.71666666666664
2017-01-15T22:04:00Z    218.06666666666666
2017-01-15T22:05:00Z    153.88333333333333
2017-01-15T22:06:00Z    153.5
2017-01-15T22:07:00Z    173.95
2017-01-15T22:08:00Z    65.2
2017-01-15T22:09:00Z    52.93333333333333
2017-01-15T22:10:00Z    274
2017-01-15T22:11:00Z    146.31666666666666
2017-01-15T22:12:00Z    -2.45356775e+06
2017-01-15T22:13:00Z    0
2017-01-15T22:14:00Z    2.4538947e+06

Really like InfluxDb! Slowly migrating from RRDTool and this is really the only issue that I've run across.

Thanks!!!

[chooko]

I too am seeing this issue. Graphing network octets with a derivative function to get the bits/sec. If my device is down for a time, I'd prefer to see a gap in the graph, instead of a linear or previous value graphed.

Any ideas on how to achieve that?

[amit-meshbey]

@chooko Ran into the same issue, and the way we solved this was by multiplying by count()/count()

SELECT non_negative_derivative(min("bytes"), 1s) * count("bytes") / count("bytes") * 8 FROM "net" WHERE "id" = 'id' AND time > now() - 24h GROUP BY time(60s) fill(null)

[nixikanius]

@amit-meshbey, nice hack! It works for me.

Think InfluxDB team should be ashamed of having this workaround. Hope more pretty fix will appear sooner than they wanted.

[danron]

If I specify 1s interval for the derivative I want it to be 1s always. Not 60s if data happens to be missing for 60s. Then a gap in the derivative results is much less surprising.

I'd also really like to see this fixed.

[PnoT]

I'm seeing this same issue any "fix" yet?

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[stale]

This issue has been automatically closed because it has not had recent activity. Please reopen if this issue is still important to you. Thank you for your contributions.

[iridos]

I guess it depends on the type of gaps you have in the data…
You could provide an option that switches between the two types of behavior, if you think the old behavior is important for some type of data with gaps…

[iridos]

As I don't seem to be able to re-open someone elses issues:
I don't think this should be closed. You could pretty easily provide an option to the function and offer both behaviors.

[HyperDevil]

I also have this issue, data showing as incorrect and nulls are not really nulls, so please do not close this yet.

[ScoreUnder]

Bump?

Looks like I don't have power over stale bot

[f00b4r0]

I'm curious why this has been tagged "wontfix"? This is something that even the venerable rrdtool got right, so surely it shouldn't be that hard? It still affects 1.8.x.