search

influxdata / influxdb

Scalable datastore for metrics, events, and real-time analytics
https://influxdata.com
Apache License 2.0
28.25k stars 3.51k forks source link

[Bug] Empty results when query includes specific point in time #16817

Open karimcitoh opened 4 years ago

karimcitoh commented 4 years ago

Steps to reproduce: (influx shell)

  1. Select database

    > use my-db

  2. Make a query that includes any time between timestamps '2020-01-05T23:59:59.999Z' and 2020-01-06T00:00:00.001Z

  3. Result is empty (this is the bug).

    > select value from "measurements" where time >= '2020-01-05T23:59:59.999Z' and time <  '2020-01-09T00:00:00.001Z'   and type =  'power' and sensorId = 'xxxxx' tz('Europe/Amsterdam')
>

  4. When querying from 6th of Jan instead, result data is quite large (this is OK).

    > select value from "measurements" where time >= '2020-01-06T00:00:00.001Z' and time <  '2020-01-09T00:00:00.001Z'   and type =  'power' and sensorId = 'xxxxx' tz('Europe/Amsterdam')
name: measurements
time                value
----                -----
1578487578755000000 19514.163836741332
1578487600700000000 20236.169733411425
1578487618936000000 20586.021932776734
1578487649191000000 20644.526820862357
1578487679442000000 20052.218646311576
etc
...

Expected behavior: Result should not be empty when query time range includes 5th of January. Or error should be shown.

Actual behavior: Empty result with status code 200.

Debugging with explain:

(working query - large result)

> explain select value from "measurements" where time >= '2020-01-06T00:00:00.001Z' and time <  '2020-01-09T00:00:00.001Z'   and type =  'power' and sensorId = 'xxxxx' tz('Europe/Amsterdam')
QUERY PLAN
----------
EXPRESSION: <nil>
AUXILIARY FIELDS: value::float
NUMBER OF SHARDS: 1
NUMBER OF SERIES: 1
CACHED VALUES: 0
NUMBER OF FILES: 1
NUMBER OF BLOCKS: 3
SIZE OF BLOCKS: 30383

(not working query - empty result)

> explain select value from "measurements" where time >= '2020-01-05T23:59:59.999Z' and time <  '2020-01-09T00:00:00.001Z'   and type =  'power' and sensorId = 'xxxxx' tz('Europe/Amsterdam')
QUERY PLAN
----------
EXPRESSION: <nil>
AUXILIARY FIELDS: value::float
NUMBER OF SHARDS: 2
NUMBER OF SERIES: 12007
CACHED VALUES: 0
NUMBER OF FILES: 12006
NUMBER OF BLOCKS: 28902
SIZE OF BLOCKS: 283665889

(querying the problematic timestamp)

explain select value from "measurements" where time >= '2020-01-05T23:59:59.999Z' and time <  '2020-01-06T00:00:00.001Z'   and type =  'power' and sensorId = 'xxxxx' tz('Europe/Amsterdam')
QUERY PLAN
----------
EXPRESSION: <nil>
AUXILIARY FIELDS: value::float
NUMBER OF SHARDS: 2
NUMBER OF SERIES: 12007
CACHED VALUES: 0
NUMBER OF FILES: 0
NUMBER OF BLOCKS: 0
SIZE OF BLOCKS: 0

Environment info:

# uname -srm
Linux 4.4.0-1094-aws x86_64

Config:

# cat /etc/influxdb/influxdb.conf
bind-address = ":8088"

[meta]
  dir = "/opt/influxdb/meta"
  logging-enabled = true

[data]
  dir = "/opt/influxdb/data"
  wal-dir = "/opt/influxdb/wal"
  data-logging-enabled = true
  wal-logging-enabled = true
  query-log-enabled = true
  trace-logging-enable = true

[http]
  log-enabled = true

Logs:

Feb 11 16:58:23 ip-10-5-1-91 influxd[14144]: ts=2020-02-11T15:58:23.024084Z lvl=info msg="Executing query" log_id=0KuBNmzl000 service=query query="SELECT value FROM \"my-db\".autogen.\"measurements\" WHERE time >= '2020-01-05T23:59:59.999Z' AND time < '2020-01-09T00:00:00.001Z' AND type = 'power' AND sensorId = 'xxxxx' TZ('Europe/Amsterdam')"
Feb 11 16:58:59 ip-10-5-1-91 influxd[14144]: [httpd] 127.0.0.1 - - [11/Feb/2020:16:58:23 +0100] "POST /query?chunked=true&db=sf-measurement&epoch=ns&q=select+value+from+%22measurements%22+where+time+%3E%3D+%272020-01-05T23%3A59%3A59.999Z%27+and+time+%3C++%272020-01-09T00%3A00%3A00.001Z%27+++and+type+%3D++%27power%27+and+sensorId+%3D+%27xxxxx%27+tz%28%27Europe%2FAmsterdam%27%29 HTTP/1.1" 200 57 "-" "InfluxDBShell/1.7.8" 54b09a43-4ce7-11ea-8010-022a75c3fd0e 36324648

Performance: iostat.txt profiles.tar.gz vars.txt

russorat commented 4 years ago

@karimcitoh thanks for the issue. Have you tried time = 2020-01-05T23:59:59.999Z in your query out of curiosity?

karimcitoh commented 4 years ago

@russorat Yes! Mostly curiosity, I was just trying to get closer to the issue.