influxdb icon indicating copy to clipboard operation
influxdb copied to clipboard

Published 20 hours ago • verified publisher icon influxdata

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

Open dlazaroi opened this issue 7 years ago • 14 comments

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
            }            
        }
]
  1. 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
  1. 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

dlazaroi avatar Jul 03 '18 09:07 dlazaroi

@dlazaroi can you upgrade to the latest version and confirm you have the same behaviour?

e-dard avatar Jul 09 '18 11:07 e-dard

@e-dard just tried with the latest versions (1.5.3, 1.6.0) and the result is still the same. I updated the bug description to reflect all the versions tested.

dlazaroi avatar Jul 09 '18 13:07 dlazaroi

This still happens with 1.6.4

ISO-B avatar Nov 09 '18 06:11 ISO-B

This is still an issue with 1.7.3. At least on the windows version

luke101308 avatar Feb 27 '19 16:02 luke101308

Still an issue in 1.7.7 on Windows.

PhilippS93 avatar Jul 03 '19 17:07 PhilippS93

same with 1.7.8 on linux.

duriantang avatar Sep 30 '19 05:09 duriantang

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

stale[bot] avatar Dec 29 '19 06:12 stale[bot]

This issue has been automatically closed because it has not had recent activity. Please reopen if this issue is still important to you. Thank you for your contributions.

stale[bot] avatar Jan 05 '20 07:01 stale[bot]

This is still an issue, please reopen.

garbagemule avatar Mar 29 '20 07:03 garbagemule

Any update on this? As the issue is still present in version 1.8.1

hellivan avatar Aug 25 '20 06:08 hellivan

Can you please update on this issue? @dgnorton

siddu108 avatar Sep 24 '20 07:09 siddu108

Do you know of any workaround for this? @siddu108 @hellivan

theamarchandan avatar Mar 14 '22 08:03 theamarchandan

No....@amarr1143l

siddu108 avatar Mar 14 '22 10:03 siddu108

Still present in 1.8.6 :(

aggo avatar Apr 28 '24 14:04 aggo