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

[Bug]Query issue with GROUP BY + fill + ORDER BY #8148

Closed
lemonli opened this issue Mar 16, 2017 · 4 comments
Closed

[Bug]Query issue with GROUP BY + fill + ORDER BY #8148

lemonli opened this issue Mar 16, 2017 · 4 comments
Assignees
@jsternberg
Labels
area/queries kind/bug

Comments

@lemonli
Copy link

lemonli commented Mar 16, 2017

Bug report

System info: [InfluxDB 1.2.1, CentOS 7]

Steps to reproduce:

  1. Sample data "NOAA_water_database" on influxdb doc(https://docs.influxdata.com/influxdb/v1.2/query_language/data_download/)

  2. 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 "location=coyote_creek" and "fill(linear)" is correct.

>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

And all are correct if I remove the [ORDER_BY_clause].

> 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

Thanks
Lemon Li
@jsternberg jsternberg self-assigned this Mar 16, 2017
@jsternberg
Copy link
Contributor

Just to clarify, it is only fill(linear) when using ORDER BY time DESC that fails?

It looks like it's trying to interpolate from the number at the start of the second series. I'll look at it.

@lemonli
Copy link
Author

lemonli commented Mar 17, 2017 •
edited
Loading

It seems only fill(linear), but both ORDER BY time DESC/ASC that fails.
An example with ORDER BY time ASC:

> SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >= '2015-09-18T16:00:00Z' AND time <= '2015-09-18T23:00:00Z' GROUP BY *,time(6m) fill(linear) ORDER BY time ASC
name: h2o_feet
tags: location=coyote_creek
time                 mean
----                 ----
2015-09-18T16:00:00Z 3.599
2015-09-18T16:06:00Z 3.497
2015-09-18T16:12:00Z 3.402
2015-09-18T16:18:00Z 3.314
2015-09-18T16:24:00Z 3.235
2015-09-18T16:30:00Z 3.0767499999999997
2015-09-18T16:36:00Z 2.9185
2015-09-18T16:42:00Z 2.76025
2015-09-18T16:48:00Z 2.602
2015-09-18T16:54:00Z 2.4437499999999996
2015-09-18T17:00:00Z 2.2855
2015-09-18T17:06:00Z 2.12725
2015-09-18T17:12:00Z 1.9689999999999999
2015-09-18T17:18:00Z 1.8107499999999999
2015-09-18T17:24:00Z 1.6524999999999999
2015-09-18T17:30:00Z 1.4942499999999999
2015-09-18T17:36:00Z 1.3359999999999999
2015-09-18T17:42:00Z 1.17775
2015-09-18T17:48:00Z 1.0194999999999999
2015-09-18T17:54:00Z 0.8612499999999996
2015-09-18T18:00:00Z 0.7029999999999998
2015-09-18T18:06:00Z 0.5447500000000001
2015-09-18T18:12:00Z 0.38649999999999984
2015-09-18T18:18:00Z 0.22824999999999962
2015-09-18T18:24:00Z 0.06999999999999984
2015-09-18T18:30:00Z -0.08824999999999994
2015-09-18T18:36:00Z -0.24650000000000016
2015-09-18T18:42:00Z -0.4047500000000004
2015-09-18T18:48:00Z -0.5630000000000002
2015-09-18T18:54:00Z -0.72125
2015-09-18T19:00:00Z -0.8794999999999997
2015-09-18T19:06:00Z -1.0377500000000004
2015-09-18T19:12:00Z -1.1960000000000002
2015-09-18T19:18:00Z -1.35425
2015-09-18T19:24:00Z -1.5125000000000006
2015-09-18T19:30:00Z -1.6707500000000004
2015-09-18T19:36:00Z -1.8290000000000002
2015-09-18T19:42:00Z -1.98725
2015-09-18T19:48:00Z -2.1454999999999997
2015-09-18T19:54:00Z -2.3037500000000004
2015-09-18T20:00:00Z -2.462
2015-09-18T20:06:00Z -2.62025
2015-09-18T20:12:00Z -2.7785000000000006
2015-09-18T20:18:00Z -2.9367500000000004
2015-09-18T20:24:00Z -3.095
2015-09-18T20:30:00Z -3.25325
2015-09-18T20:36:00Z -3.4114999999999998
2015-09-18T20:42:00Z -3.5697500000000004
2015-09-18T20:48:00Z -3.728
2015-09-18T20:54:00Z -3.88625
2015-09-18T21:00:00Z -4.044500000000001
2015-09-18T21:06:00Z -4.20275
2015-09-18T21:12:00Z -4.361000000000001
2015-09-18T21:18:00Z -4.5192499999999995
2015-09-18T21:24:00Z -4.6775
2015-09-18T21:30:00Z -4.835750000000001
2015-09-18T21:36:00Z -4.994
2015-09-18T21:42:00Z -5.15225
2015-09-18T21:48:00Z -5.310500000000001
2015-09-18T21:54:00Z -5.46875
2015-09-18T22:00:00Z -5.627000000000001
2015-09-18T22:06:00Z -5.785250000000001
2015-09-18T22:12:00Z -5.9435
2015-09-18T22:18:00Z -6.101750000000001
2015-09-18T22:24:00Z -6.260000000000002
2015-09-18T22:30:00Z -6.4182500000000005
2015-09-18T22:36:00Z -6.576500000000001
2015-09-18T22:42:00Z -6.73475
2015-09-18T22:48:00Z -6.893000000000001
2015-09-18T22:54:00Z -7.051250000000001
2015-09-18T23:00:00Z -7.2095

name: h2o_feet
tags: location=santa_monica
time                 mean
----                 ----
2015-09-18T16:00:00Z 3.868
2015-09-18T16:06:00Z 3.917
2015-09-18T16:12:00Z 4.124
2015-09-18T16:18:00Z 4.065
2015-09-18T16:24:00Z 4.57
2015-09-18T16:30:00Z 4.547
2015-09-18T16:36:00Z 4.377
2015-09-18T16:42:00Z 4.508
2015-09-18T16:48:00Z 4.298
2015-09-18T16:54:00Z 4.6
2015-09-18T17:00:00Z 4.81
2015-09-18T17:06:00Z 4.885
2015-09-18T17:12:00Z 4.744
2015-09-18T17:18:00Z 4.8
2015-09-18T17:24:00Z 4.911
2015-09-18T17:30:00Z 4.82
2015-09-18T17:36:00Z 5.233
2015-09-18T17:42:00Z 5.367
2015-09-18T17:48:00Z 5.043
2015-09-18T17:54:00Z 5.322
2015-09-18T18:00:00Z 5.089
2015-09-18T18:06:00Z 5.2
2015-09-18T18:12:00Z 5.407
2015-09-18T18:18:00Z 5.564
2015-09-18T18:24:00Z 5.482
2015-09-18T18:30:00Z 5.505
2015-09-18T18:36:00Z 5.489
2015-09-18T18:42:00Z 5.266
2015-09-18T18:48:00Z 5.486
2015-09-18T18:54:00Z 5.522
2015-09-18T19:00:00Z 5.614
2015-09-18T19:06:00Z 5.748
2015-09-18T19:12:00Z 5.509
2015-09-18T19:18:00Z 5.663
2015-09-18T19:24:00Z 5.702
2015-09-18T19:30:00Z 5.633
2015-09-18T19:36:00Z 5.653
2015-09-18T19:42:00Z 5.492
2015-09-18T19:48:00Z 5.499
2015-09-18T19:54:00Z 5.522
2015-09-18T20:00:00Z 5.627
2015-09-18T20:06:00Z 5.62
2015-09-18T20:12:00Z 5.459
2015-09-18T20:18:00Z 5.551
2015-09-18T20:24:00Z 5.502
2015-09-18T20:30:00Z 5.604
2015-09-18T20:36:00Z 5.62
2015-09-18T20:42:00Z 5.302
2015-09-18T20:48:00Z 5.24
2015-09-18T20:54:00Z 5.322
2015-09-18T21:00:00Z 5.338
2015-09-18T21:06:00Z 5.341
2015-09-18T21:12:00Z 5.213
2015-09-18T21:18:00Z 5.072
2015-09-18T21:24:00Z 5.013
2015-09-18T21:30:00Z 5.01
2015-09-18T21:36:00Z 5.066
2015-09-18T21:42:00Z 4.938
2015-09-18T21:48:00Z
2015-09-18T21:54:00Z
2015-09-18T22:00:00Z
2015-09-18T22:06:00Z
2015-09-18T22:12:00Z
2015-09-18T22:18:00Z
2015-09-18T22:24:00Z
2015-09-18T22:30:00Z
2015-09-18T22:36:00Z
2015-09-18T22:42:00Z
2015-09-18T22:48:00Z
2015-09-18T22:54:00Z
2015-09-18T23:00:00Z

Raw data without fill(near):

> SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >= '2015-09-18T16:00:00Z' AND time <= '2015-09-18T23:00:00Z' GROUP BY *,time(6m) ORDER BY time ASC
name: h2o_feet
tags: location=coyote_creek
time                 mean
----                 ----
2015-09-18T16:00:00Z 3.599
2015-09-18T16:06:00Z 3.497
2015-09-18T16:12:00Z 3.402
2015-09-18T16:18:00Z 3.314
2015-09-18T16:24:00Z 3.235
2015-09-18T16:30:00Z
2015-09-18T16:36:00Z
2015-09-18T16:42:00Z
2015-09-18T16:48:00Z
2015-09-18T16:54:00Z
2015-09-18T17:00:00Z
2015-09-18T17:06:00Z
2015-09-18T17:12:00Z
2015-09-18T17:18:00Z
2015-09-18T17:24:00Z
2015-09-18T17:30:00Z
2015-09-18T17:36:00Z
2015-09-18T17:42:00Z
2015-09-18T17:48:00Z
2015-09-18T17:54:00Z
2015-09-18T18:00:00Z
2015-09-18T18:06:00Z
2015-09-18T18:12:00Z
2015-09-18T18:18:00Z
2015-09-18T18:24:00Z
2015-09-18T18:30:00Z
2015-09-18T18:36:00Z
2015-09-18T18:42:00Z
2015-09-18T18:48:00Z
2015-09-18T18:54:00Z
2015-09-18T19:00:00Z
2015-09-18T19:06:00Z
2015-09-18T19:12:00Z
2015-09-18T19:18:00Z
2015-09-18T19:24:00Z
2015-09-18T19:30:00Z
2015-09-18T19:36:00Z
2015-09-18T19:42:00Z
2015-09-18T19:48:00Z
2015-09-18T19:54:00Z
2015-09-18T20:00:00Z
2015-09-18T20:06:00Z
2015-09-18T20:12:00Z
2015-09-18T20:18:00Z
2015-09-18T20:24:00Z
2015-09-18T20:30:00Z
2015-09-18T20:36:00Z
2015-09-18T20:42:00Z
2015-09-18T20:48:00Z
2015-09-18T20:54:00Z
2015-09-18T21:00:00Z
2015-09-18T21:06:00Z
2015-09-18T21:12:00Z
2015-09-18T21:18:00Z
2015-09-18T21:24:00Z
2015-09-18T21:30:00Z
2015-09-18T21:36:00Z
2015-09-18T21:42:00Z
2015-09-18T21:48:00Z
2015-09-18T21:54:00Z
2015-09-18T22:00:00Z
2015-09-18T22:06:00Z
2015-09-18T22:12:00Z
2015-09-18T22:18:00Z
2015-09-18T22:24:00Z
2015-09-18T22:30:00Z
2015-09-18T22:36:00Z
2015-09-18T22:42:00Z
2015-09-18T22:48:00Z
2015-09-18T22:54:00Z
2015-09-18T23:00:00Z

name: h2o_feet
tags: location=santa_monica
time                 mean
----                 ----
2015-09-18T16:00:00Z 3.868
2015-09-18T16:06:00Z 3.917
2015-09-18T16:12:00Z 4.124
2015-09-18T16:18:00Z 4.065
2015-09-18T16:24:00Z 4.57
2015-09-18T16:30:00Z 4.547
2015-09-18T16:36:00Z 4.377
2015-09-18T16:42:00Z 4.508
2015-09-18T16:48:00Z 4.298
2015-09-18T16:54:00Z 4.6
2015-09-18T17:00:00Z 4.81
2015-09-18T17:06:00Z 4.885
2015-09-18T17:12:00Z 4.744
2015-09-18T17:18:00Z 4.8
2015-09-18T17:24:00Z 4.911
2015-09-18T17:30:00Z 4.82
2015-09-18T17:36:00Z 5.233
2015-09-18T17:42:00Z 5.367
2015-09-18T17:48:00Z 5.043
2015-09-18T17:54:00Z 5.322
2015-09-18T18:00:00Z 5.089
2015-09-18T18:06:00Z 5.2
2015-09-18T18:12:00Z 5.407
2015-09-18T18:18:00Z 5.564
2015-09-18T18:24:00Z 5.482
2015-09-18T18:30:00Z 5.505
2015-09-18T18:36:00Z 5.489
2015-09-18T18:42:00Z 5.266
2015-09-18T18:48:00Z 5.486
2015-09-18T18:54:00Z 5.522
2015-09-18T19:00:00Z 5.614
2015-09-18T19:06:00Z 5.748
2015-09-18T19:12:00Z 5.509
2015-09-18T19:18:00Z 5.663
2015-09-18T19:24:00Z 5.702
2015-09-18T19:30:00Z 5.633
2015-09-18T19:36:00Z 5.653
2015-09-18T19:42:00Z 5.492
2015-09-18T19:48:00Z 5.499
2015-09-18T19:54:00Z 5.522
2015-09-18T20:00:00Z 5.627
2015-09-18T20:06:00Z 5.62
2015-09-18T20:12:00Z 5.459
2015-09-18T20:18:00Z 5.551
2015-09-18T20:24:00Z 5.502
2015-09-18T20:30:00Z 5.604
2015-09-18T20:36:00Z 5.62
2015-09-18T20:42:00Z 5.302
2015-09-18T20:48:00Z 5.24
2015-09-18T20:54:00Z 5.322
2015-09-18T21:00:00Z 5.338
2015-09-18T21:06:00Z 5.341
2015-09-18T21:12:00Z 5.213
2015-09-18T21:18:00Z 5.072
2015-09-18T21:24:00Z 5.013
2015-09-18T21:30:00Z 5.01
2015-09-18T21:36:00Z 5.066
2015-09-18T21:42:00Z 4.938
2015-09-18T21:48:00Z
2015-09-18T21:54:00Z
2015-09-18T22:00:00Z
2015-09-18T22:06:00Z
2015-09-18T22:12:00Z
2015-09-18T22:18:00Z
2015-09-18T22:24:00Z
2015-09-18T22:30:00Z
2015-09-18T22:36:00Z
2015-09-18T22:42:00Z
2015-09-18T22:48:00Z
2015-09-18T22:54:00Z
2015-09-18T23:00:00Z

@lemonli
Copy link
Author

lemonli commented Mar 17, 2017

This is my community topic: https://community.influxdata.com/t/fill-issue-wrong-syntax-or-bug/227

@jsternberg jsternberg mentioned this issue Mar 17, 2017
Merged
3 tasks
@jsternberg
Copy link
Contributor

Fixed via #8149.

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

@jsternberg jsternberg

Labels
area/queries kind/bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@jsternberg @lemonli