Fill() issue: wrong syntax or bug

1

Can I use the syntax below? Or is it a bug?
I use the sample data NOAA_water_database from the influxdb doc.
I found that data(“location=santa_monica” , “fill(linear)”, order by time desc) is not correct. Data with time range from ‘2015-08-17T23:00:00Z’ to ‘2015-08-18T00:00:00Z’ should be null because the raw data with the same time range is null.
But data with with “location=coyote_creek” and “fill(linear)” is correct.
And all are correct if I remove the [ORDER_BY_clause].
Influxdb version 1.2

SELECT_clause [INTO_clause] FROM_clause [WHERE_clause] GROUP BY *[,time(<time_interval>)] fill(xxx) [ORDER_BY_clause] SLIMIT N

> SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >= '2015-08-17T23:00:00Z' AND time <= '2015-08-18T00:42:00Z' GROUP BY *,time(6m) ORDER BY time DESC  SLIMIT 2

name: h2o_feet
tags: location=santa_monica
time                 mean
----                 ----
2015-08-18T00:42:00Z 2.057
2015-08-18T00:36:00Z 2.067
2015-08-18T00:30:00Z 2.051
2015-08-18T00:24:00Z 2.041
2015-08-18T00:18:00Z 2.126
2015-08-18T00:12:00Z 2.028
2015-08-18T00:06:00Z 2.116
2015-08-18T00:00:00Z 2.064
2015-08-17T23:54:00Z
2015-08-17T23:48:00Z
2015-08-17T23:42:00Z
2015-08-17T23:36:00Z
2015-08-17T23:30:00Z
2015-08-17T23:24:00Z
2015-08-17T23:18:00Z
2015-08-17T23:12:00Z
2015-08-17T23:06:00Z
2015-08-17T23:00:00Z

name: h2o_feet
tags: location=coyote_creek
time                 mean
----                 ----
2015-08-18T00:42:00Z 7.234
2015-08-18T00:36:00Z 7.372
2015-08-18T00:30:00Z 7.5
2015-08-18T00:24:00Z 7.635
2015-08-18T00:18:00Z 7.762
2015-08-18T00:12:00Z 7.887
2015-08-18T00:06:00Z 8.005
2015-08-18T00:00:00Z 8.12
2015-08-17T23:54:00Z
2015-08-17T23:48:00Z
2015-08-17T23:42:00Z
2015-08-17T23:36:00Z
2015-08-17T23:30:00Z
2015-08-17T23:24:00Z
2015-08-17T23:18:00Z
2015-08-17T23:12:00Z
2015-08-17T23:06:00Z
2015-08-17T23:00:00Z 

>SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >= '2015-08-17T23:00:00Z' AND time <= '2015-08-18T00:42:00Z' GROUP BY *,time(6m) fill(linear) ORDER BY time DESC SLIMIT 2

name: h2o_feet
tags: location=santa_monica
time                 mean
----                 ----
2015-08-18T00:42:00Z 2.057
2015-08-18T00:36:00Z 2.067
2015-08-18T00:30:00Z 2.051
2015-08-18T00:24:00Z 2.041
2015-08-18T00:18:00Z 2.126
2015-08-18T00:12:00Z 2.028
2015-08-18T00:06:00Z 2.116
2015-08-18T00:00:00Z 2.064
2015-08-17T23:54:00Z 1.3254285714285716
2015-08-17T23:48:00Z 0.586857142857143
2015-08-17T23:42:00Z -0.1517142857142857
2015-08-17T23:36:00Z -0.8902857142857141
2015-08-17T23:30:00Z -1.6288571428571426
2015-08-17T23:24:00Z -2.3674285714285714
2015-08-17T23:18:00Z -3.106
2015-08-17T23:12:00Z -3.8445714285714283
2015-08-17T23:06:00Z -4.583142857142857
2015-08-17T23:00:00Z -5.321714285714285

name: h2o_feet
tags: location=coyote_creek
time                 mean
----                 ----
2015-08-18T00:42:00Z 7.234
2015-08-18T00:36:00Z 7.372
2015-08-18T00:30:00Z 7.5
2015-08-18T00:24:00Z 7.635
2015-08-18T00:18:00Z 7.762
2015-08-18T00:12:00Z 7.887
2015-08-18T00:06:00Z 8.005
2015-08-18T00:00:00Z 8.12
2015-08-17T23:54:00Z
2015-08-17T23:48:00Z
2015-08-17T23:42:00Z
2015-08-17T23:36:00Z
2015-08-17T23:30:00Z
2015-08-17T23:24:00Z
2015-08-17T23:18:00Z
2015-08-17T23:12:00Z
2015-08-17T23:06:00Z
2015-08-17T23:00:00Z

> SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >= '2015-08-17T23:00:00Z' AND time <= '2015-08-18T00:42:00Z' GROUP BY *,time(6m) fill(linear)  SLIMIT 2

name: h2o_feet
tags: location=coyote_creek
time                 mean
----                 ----
2015-08-17T23:00:00Z
2015-08-17T23:06:00Z
2015-08-17T23:12:00Z
2015-08-17T23:18:00Z
2015-08-17T23:24:00Z
2015-08-17T23:30:00Z
2015-08-17T23:36:00Z
2015-08-17T23:42:00Z
2015-08-17T23:48:00Z
2015-08-17T23:54:00Z
2015-08-18T00:00:00Z 8.12
2015-08-18T00:06:00Z 8.005
2015-08-18T00:12:00Z 7.887
2015-08-18T00:18:00Z 7.762
2015-08-18T00:24:00Z 7.635
2015-08-18T00:30:00Z 7.5
2015-08-18T00:36:00Z 7.372
2015-08-18T00:42:00Z 7.234

name: h2o_feet
tags: location=santa_monica
time                 mean
----                 ----
2015-08-17T23:00:00Z
2015-08-17T23:06:00Z
2015-08-17T23:12:00Z
2015-08-17T23:18:00Z
2015-08-17T23:24:00Z
2015-08-17T23:30:00Z
2015-08-17T23:36:00Z
2015-08-17T23:42:00Z
2015-08-17T23:48:00Z
2015-08-17T23:54:00Z
2015-08-18T00:00:00Z 2.064
2015-08-18T00:06:00Z 2.116
2015-08-18T00:12:00Z 2.028
2015-08-18T00:18:00Z 2.126
2015-08-18T00:24:00Z 2.041
2015-08-18T00:30:00Z 2.051
2015-08-18T00:36:00Z 2.067
2015-08-18T00:42:00Z 2.057
2

It appears that you’ve discovered a bug.

Running the same query, but specifying location = 'santa_monica' yields the expected result.

> SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >= '2015-08-17T23:00:00Z' AND time <= '2015-08-18T00:42:00Z' AND location = 'santa_monica' GROUP BY *,time(6m) fill(linear) ORDER BY time DESC SLIMIT 2
name: h2o_feet
tags: location=santa_monica
time                mean
----                ----
1439858520000000000 2.057
1439858160000000000 2.067
1439857800000000000 2.051
1439857440000000000 2.041
1439857080000000000 2.126
1439856720000000000 2.028
1439856360000000000 2.116
1439856000000000000 2.064
1439855640000000000
1439855280000000000
1439854920000000000
1439854560000000000
1439854200000000000
1439853840000000000
1439853480000000000
1439853120000000000
1439852760000000000
1439852400000000000

Would you please open a bug report.

3

Might this be related to this?

4

OK, I have opend [Bug]Query issue with GROUP BY + fill + ORDER BY · Issue #8148 · influxdata/influxdb · GitHub

1 Like