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.

Sign up for GitHub

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

Jump to bottom

[0.9.4.2] derivative is broken with order by desc #4675

Closed
poelzi opened this issue Nov 5, 2015 · 7 comments
Closed

[0.9.4.2] derivative is broken with order by desc #4675

poelzi opened this issue Nov 5, 2015 · 7 comments
Assignees
@jsternberg
Labels
area/queries
Milestone
0.13.0

Comments

@poelzi
Copy link

poelzi commented Nov 5, 2015

When using order by, the derivative function gives strange results:

SELECT DERIVATIVE(value) from net_bytes_recv where host='influxdb' and interface='eth0'  order by desc

2015-11-05T15:47:20Z    0
2015-11-05T15:47:10Z    0
2015-11-05T15:47:00Z    0
2015-11-05T15:46:50Z    0
2015-11-05T15:46:40Z    0
2015-11-05T15:46:30Z    0
2015-11-05T15:38:20Z    0

vs

SELECT DERIVATIVE(value) from net_bytes_recv where host='influxdb' and interface='eth0'  order by asc

ame: net_bytes_recv
--------------------
time                    derivative
2015-11-05T15:38:30Z    146904.2
2015-11-05T15:38:40Z    151736.9
2015-11-05T15:38:50Z    127525.5
2015-11-05T15:39:00Z    124495.7
2015-11-05T15:39:10Z    143187.3
2015-11-05T15:39:20Z    151087
[...]
@jackzampolin
Copy link
Contributor

@poelzi What Influx version are you running?

@poelzi
Copy link
Author

poelzi commented Nov 6, 2015

Connected to http://localhost:8086 version 0.9.4.2
InfluxDB shell 0.9.4.2

@beckettsean beckettsean changed the title derivative is broken with order by desc [0.9.4.2] derivative is broken with order by desc Nov 6, 2015
@jackzampolin
Copy link
Contributor

@poelzi derivative( field_key, [unit] ) returns the rate of change for the values in a single field in a series. InfluxDB calculates the difference between chronological non-NAN field values and converts those results into the rate of change per unit. The unit argument is optional and, if not specified, defaults to one second (1s).

What does the the data look like between 2015-11-05T15:47:20Z and 2015-11-05T15:38:20Z?

@poelzi
Copy link
Author

poelzi commented Nov 9, 2015

It seems that it calculates the change after sorting which messes up the values. I would expect the results just in opposite direction so limit can be used for the last n values and I think thats the user expected behaviour.

@jackzampolin
Copy link
Contributor

@poelzi We have documentation coming out to explain the derivative behavior soon. While this is expected behavior, it is not what most users expect.

@beckettsean
Copy link
Contributor

@jackzampolin I tend to agree with @poelzi on this. ORDER BY should operate on the outputs, not the inputs. With or without documentation it is a bug if ORDER BY applies to the inputs.

@beckettsean beckettsean reopened this Nov 10, 2015
@jsternberg jsternberg self-assigned this Apr 7, 2016
@jsternberg
Copy link
Contributor

I think this should just take the derivative in reverse and that's what I would expect if I were using it. Does this look like what others would expect?

> select derivative(value) from cpu
name: cpu
---------
time                            derivative
2016-04-07T15:50:26.595722704Z  0.970136522680435
2016-04-07T15:51:17.199127238Z  0.039523032460320275

> select derivative(value) from cpu order by desc
name: cpu
---------
time                            derivative
2016-04-07T15:50:26.595722704Z  -0.039523032460320275
2016-04-07T15:50:24.534157189Z  -0.970136522680435

@jsternberg jsternberg added this to the 0.13.0 milestone Apr 7, 2016
jsternberg added a commit that referenced this issue Apr 8, 2016
@jsternberg
Allow derivative() function to be used with ORDER BY desc
a5c5da4 
The derivative function had an arbitrary limitation that would cause it
to set the value to zero if the previous value was after the next value.
This caused all `ORDER BY desc` queries with `derivative()` to always
return zero values.

Fixes #4675.
jsternberg added a commit that referenced this issue Apr 18, 2016
@jsternberg
Allow derivative() function to be used with ORDER BY desc
4d57175 
The derivative function had an arbitrary limitation that would cause it
to set the value to zero if the previous value was after the next value.
This caused all `ORDER BY desc` queries with `derivative()` to always
return zero values.

Fixes #4675.
@jsternberg jsternberg mentioned this issue Apr 18, 2016
Merged
jsternberg added a commit that referenced this issue Apr 19, 2016
@jsternberg
Allow derivative() function to be used with ORDER BY desc
299f4bf 
The derivative function had an arbitrary limitation that would cause it
to set the value to zero if the previous value was after the next value.
This caused all `ORDER BY desc` queries with `derivative()` to always
return zero values.

Fixes #4675.
jsternberg added a commit that referenced this issue Apr 20, 2016
@jsternberg
Allow derivative() function to be used with ORDER BY desc
095de6a 
The derivative function had an arbitrary limitation that would cause it
to set the value to zero if the previous value was after the next value.
This caused all `ORDER BY desc` queries with `derivative()` to always
return zero values.

Fixes #4675.
@toni-moreno toni-moreno mentioned this issue Apr 20, 2016
Closed
jsternberg added a commit that referenced this issue Apr 22, 2016
@jsternberg
Allow derivative() function to be used with ORDER BY desc
9b6d5df 
The derivative function had an arbitrary limitation that would cause it
to set the value to zero if the previous value was after the next value.
This caused all `ORDER BY desc` queries with `derivative()` to always
return zero values.

Fixes #4675.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@jsternberg jsternberg

Labels
area/queries
Projects
None yet
Milestone
0.13.0
Development

No branches or pull requests
4 participants
@poelzi @jsternberg @beckettsean @jackzampolin