influxdb: Influx >=1.4: non_negative_derivative and difference broken, CQ's stop working

Bug report

System info: InfluxDB 1.4.2 on both 16.04.3 LTS and latest Docker image

My continuous queries stopped worked after upgrading to 1.4.2, there seems to be some problem with a few aggregate functions: non_negative_derivative and difference tested

InfluxDB shell version: 1.4.2
> select topic, Total from telegraf.autogen.mqtt_consumer WHERE topic = 'tele/sonoffpowsplit/ENERGY' LIMIT 10
name: mqtt_consumer
time                topic                      Total
----                -----                      -----
1514560525743803802 tele/sonoffpowsplit/ENERGY 457.902
1514560555772347740 tele/sonoffpowsplit/ENERGY 457.91
1514560585849035726 tele/sonoffpowsplit/ENERGY 457.917
1514560615896216743 tele/sonoffpowsplit/ENERGY 457.925
1514560646493127648 tele/sonoffpowsplit/ENERGY 457.932
1514560677089769307 tele/sonoffpowsplit/ENERGY 457.939
1514560708040578480 tele/sonoffpowsplit/ENERGY 457.946
1514560783541412296 tele/sonoffpowsplit/ENERGY 457.963
1514560814705513205 tele/sonoffpowsplit/ENERGY 457.97
1514560848327978540 tele/sonoffpowsplit/ENERGY 457.977

> select difference(max("Total")) from telegraf.autogen.mqtt_consumer WHERE topic = 'tele/sonoffpowsplit/ENERGY' GROUP BY time(5m)

No results

> select non_negative_derivative(max(Total)) from telegraf.autogen.mqtt_consumer WHERE topic = 'tele/sonoffpowsplit/ENERGY' GROUP BY time(1m)

No results

> select sum(Total) from telegraf.autogen.mqtt_consumer WHERE topic = 'tele/sonoffpowsplit/ENERGY' GROUP BY time(1m) LIMIT 10
name: mqtt_consumer
time                sum
----                ---
1514560500000000000 915.812
1514560560000000000 915.842
1514560620000000000 915.8710000000001
1514560680000000000 457.946
1514560740000000000 457.963
1514560800000000000 915.947
1514560860000000000 915.9770000000001
1514560920000000000 916.011
1514560980000000000 458.019
1514561040000000000 916.067

Summing the values still works

Continuous query that worked fine for a year and stopped working:

cq_splitheatpump CREATE CONTINUOUS QUERY cq_splitheatpump ON home BEGIN SELECT non_negative_derivative(max(Total), 1m) AS value INTO home."1day".splitheatpump FROM telegraf.autogen.mqtt_consumer WHERE topic = 'tele/sonoffpowsplit/ENERGY' GROUP BY time(1m) END

Which filled this series:

> select * from home."1day".splitheatpump LIMIT 10
name: splitheatpump
time                value
----                -----
1485905760000000000 0.0005
1485905820000000000 0.0005
1485905880000000000 0.0005
1485905940000000000 0.0005
1485906000000000000 0.0005
1485906060000000000 0.0005
1485906120000000000 0
1485906180000000000 0.0005
1485906240000000000 0.0005
1485906300000000000 0.0005

About this issue

Original URL
State: closed
Created 7 years ago
Reactions: 4
Comments: 22 (2 by maintainers)

Most upvoted comments

Downgraded to 1.3.8 and it’s working again. This is one hell of a regression to go unnoticed for 2-3 versions.

sandervandegeijn on Jan 18, 2018

Still no attention for this?

sandervandegeijn on Nov 9, 2018

I just ran into a similar problem: I think non_negative_derivative doesn’t work with GROUP BY time if there is no WHERE time.

Reproduced with current master:

CREATE DATABASE test
USE test
INSERT test val=1i
Wait some seconds, then run INSERT test val=2i

SELECT non_negative_derivative("val", 1s) FROM test works
SELECT non_negative_derivative(first("val"), 1s) FROM test GROUP BY time(1s) doesn’t, but
SELECT non_negative_derivative(first("val"), 1s) FROM test WHERE time > NOW() - 1h GROUP BY time(1s) is fine.

We also have some CQs running which use non_negative_derivative and still produce the expected results. They use the Advanced Syntax, maybe that’s a workaround you can try?

hahnjo on Jun 21, 2018