influxdata / influxdata/influxdb

Inside a subquery, time range of main query is used instead

Open
#10,028 14 comments 3 reactions 1 assignee Claimed by @dgnorton View on GitHub
1.x area/queries area/subqueries
Dominant language
Rust
Stars
31.7k
Forks
3.7k
Avg merge
13h 37m
Merged PRs (30d)
8

Description

__System info:__
InfluxDB versions tested: 1.3.5, 1.5.3, 1.6.0
Shell version 1.7.0~n201806140800
Ubuntu 16.04
Installed influx from nightly builds.

__Steps to reproduce:__

1. Insert this data in a database:
```
[
{
"measurement": "test_measurement",
"time": "2018-06-12T17:00:00Z",
"fields": {
"numeric_value": 9
}
},
{
"measurement": "test_measurement",
"time": "2018-06-13T16:20:00Z",
"fields": {
"numeric_value": 12
}
},
{
"measurement": "test_measurement",
"time": "2018-06-13T18:45:00Z",
"fields": {
"numeric_value": 10
}
}
]
```
2. Execute this query (workaround for time-weighted average):
```
SELECT mean(numeric_value) as numeric_value FROM (
SELECT mean(numeric_value) AS numeric_value
FROM test_measurement
WHERE time >= '2018-06-13T00:00:00Z' - 24h AND time <= '2018-06-14T00:00:00Z'
GROUP BY time(1s) FILL(previous))
WHERE time >= '2018-06-13T00:00:00Z' - 24h AND time <= '2018-06-14T00:00:00Z'
GROUP BY time(1h) ORDER BY time ASC
```
Results, as expected (before the first value in the db, null is returned for each hour):
```
time numeric_value
---- -------------
2018-06-12T00:00:00Z
2018-06-12T01:00:00Z
2018-06-12T02:00:00Z
2018-06-12T03:00:00Z
2018-06-12T04:00:00Z
2018-06-12T05:00:00Z
2018-06-12T06:00:00Z
2018-06-12T07:00:00Z
2018-06-12T08:00:00Z
2018-06-12T09:00:00Z
2018-06-12T10:00:00Z
2018-06-12T11:00:00Z
2018-06-12T12:00:00Z
2018-06-12T13:00:00Z
2018-06-12T14:00:00Z
2018-06-12T15:00:00Z
2018-06-12T16:00:00Z
2018-06-12T17:00:00Z 9
2018-06-12T18:00:00Z 9
2018-06-12T19:00:00Z 9
2018-06-12T20:00:00Z 9
2018-06-12T21:00:00Z 9
2018-06-12T22:00:00Z 9
2018-06-12T23:00:00Z 9
2018-06-13T00:00:00Z 9
2018-06-13T01:00:00Z 9
2018-06-13T02:00:00Z 9
2018-06-13T03:00:00Z 9
2018-06-13T04:00:00Z 9
2018-06-13T05:00:00Z 9
2018-06-13T06:00:00Z 9
2018-06-13T07:00:00Z 9
2018-06-13T08:00:00Z 9
2018-06-13T09:00:00Z 9
2018-06-13T10:00:00Z 9
2018-06-13T11:00:00Z 9
2018-06-13T12:00:00Z 9
2018-06-13T13:00:00Z 9
2018-06-13T14:00:00Z 9
2018-06-13T15:00:00Z 9
2018-06-13T16:00:00Z 11
2018-06-13T17:00:00Z 12
2018-06-13T18:00:00Z 11.5
2018-06-13T19:00:00Z 10
2018-06-13T20:00:00Z 10
2018-06-13T21:00:00Z 10
2018-06-13T22:00:00Z 10
2018-06-13T23:00:00Z 10
2018-06-14T00:00:00Z 10
```

3. Now execute this query, reducing the range of the outer query:
```
SELECT mean(numeric_value) as numeric_value FROM (
SELECT mean(numeric_value) AS numeric_value
FROM test_measurement
WHERE time >= '2018-06-13T00:00:00Z' - 24h AND time <= '2018-06-14T00:00:00Z'
GROUP BY time(1s) FILL(previous))
WHERE time >= '2018-06-13T00:00:00Z' AND time <= '2018-06-14T00:00:00Z'
GROUP BY time(1h) ORDER BY time ASC
```

__Expected behavior:__
Only the part within the outer query range is returned, but taken from the inner query so there are no nulls:
```
2018-06-13T00:00:00Z 9
2018-06-13T01:00:00Z 9
2018-06-13T02:00:00Z 9
2018-06-13T03:00:00Z 9
2018-06-13T04:00:00Z 9
2018-06-13T05:00:00Z 9
2018-06-13T06:00:00Z 9
2018-06-13T07:00:00Z 9
2018-06-13T08:00:00Z 9
2018-06-13T09:00:00Z 9
2018-06-13T10:00:00Z 9
2018-06-13T11:00:00Z 9
2018-06-13T12:00:00Z 9
2018-06-13T13:00:00Z 9
2018-06-13T14:00:00Z 9
2018-06-13T15:00:00Z 9
2018-06-13T16:00:00Z 11
2018-06-13T17:00:00Z 12
2018-06-13T18:00:00Z 11.5
2018-06-13T19:00:00Z 10
2018-06-13T20:00:00Z 10
2018-06-13T21:00:00Z 10
2018-06-13T22:00:00Z 10
2018-06-13T23:00:00Z 10
2018-06-14T00:00:00Z 10
```

__Actual behavior:__
The range of the outer query is used for the inner query, so we have nulls until the first value in the outer query's range.
```
time numeric_value
---- -------------
2018-06-13T00:00:00Z
2018-06-13T01:00:00Z
2018-06-13T02:00:00Z
2018-06-13T03:00:00Z
2018-06-13T04:00:00Z
2018-06-13T05:00:00Z
2018-06-13T06:00:00Z
2018-06-13T07:00:00Z
2018-06-13T08:00:00Z
2018-06-13T09:00:00Z
2018-06-13T10:00:00Z
2018-06-13T11:00:00Z
2018-06-13T12:00:00Z
2018-06-13T13:00:00Z
2018-06-13T14:00:00Z
2018-06-13T15:00:00Z
2018-06-13T16:00:00Z 12
2018-06-13T17:00:00Z 12
2018-06-13T18:00:00Z 11.5
2018-06-13T19:00:00Z 10
2018-06-13T20:00:00Z 10
2018-06-13T21:00:00Z 10
2018-06-13T22:00:00Z 10
2018-06-13T23:00:00Z 10
2018-06-14T00:00:00Z 10
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.