[0.9.5] ORDER BY time desc doesn't work when used with LIMIT or GROUP BY tag

[nornagon]

Running influxdb 0.9.5.

Running SELECT <stuff> ORDER BY time ASC LIMIT 5 returns 5 results, but SELECT <stuff> ORDER BY time DESC LIMIT 5 returns 0 results. Specifically:

SELECT value
FROM temperature
WHERE deviceId =~ /tiso/ AND time > now() - 10m
ORDER BY time ASC
LIMIT 5

returns 5 results, while

SELECT value
FROM temperature
WHERE deviceId =~ /tiso/ AND time > now() - 10m
ORDER BY time DESC
LIMIT 5

returns 0 results.

Also, GROUP BY with ORDER BY time DESC has weird behaviour which I'm yet to be able to characterize sufficiently to explain. e.g.

SELECT value
FROM temperature
WHERE time > now() - 10m
GROUP BY deviceId
ORDER BY time DESC
LIMIT 1

Produces just 3 series results, whereas the ORDER BY time ASC version produces 24 on the same interval, and doesn't produce the latest data for those series, and doesn't even produce consistently bad data: the timestamp on the 3 series it does return appears to be a random timestamp within the 10m time range specified by the query, despite all 3 series having many datapoints (around 1 per second) in the time range. When I tried the query again some time later, it returned just a single (different) series.

{
  "results": [
    {
      "series": [
        {
          "name": "temperature",
          "tags": { "deviceId": "dev1" },
          "columns": ["time", "value"],
          "values": [
            ["2015-11-20T21:34:58.622Z", 28.4]
          ]
        },
        {
          "name": "temperature",
          "tags": { "deviceId": "dev2" },
          "columns": ["time", "value"],
          "values": [
            ["2015-11-20T21:42:03.279Z", 38.449999999999996]
          ]
        },
        {
          "name": "temperature",
          "tags": { "deviceId": "dev3" },
          "columns": ["time", "value"],
          "values": [
            ["2015-11-20T21:35:41.736Z", 37.90428924560547]
          ]
        }
      ]
    }
  ]
}

Even without GROUP BY there's weirdness going on.

SELECT * FROM temperature WHERE time > now() - 10m ORDER BY time DESC LIMIT 5

shows 5 data points from a single series, which is not the series with the most recent temperature data.

[beckettsean]

A recurrence of symptoms similar to #4235

[piotr-piatkowski]

I'm not sure if title was correctly updated, for sure this is not the case with 0.9.4.2, where I just had this issue:

>  select * from temperature where time > '2015-11-21T20:42:00Z' order by time asc
name: temperature
-----------------
time                lower   place   upper
2015-11-21T20:42:02.644884021Z  45.375  stove   46.25
2015-11-21T20:42:07.644832402Z  45.375  stove   46.25
2015-11-21T20:42:12.644900527Z  45.375  stove   46.25

>  select * from temperature where time > '2015-11-21T20:42:00Z' order by time desc
>  select * from temperature where time > '2015-11-21T20:42:00Z' order by time asc
name: temperature
-----------------
time                lower   place   upper
2015-11-21T20:42:02.644884021Z  45.375  stove   46.25
2015-11-21T20:42:07.644832402Z  45.375  stove   46.25
2015-11-21T20:42:12.644900527Z  45.375  stove   46.25
2015-11-21T20:42:17.644798026Z  45.375  stove   46.25
2015-11-21T20:42:22.644994788Z  45.375  stove   46.25

>  select * from temperature where time > '2015-11-21T20:42:00Z' order by time desc
> 

So it's without limit and without group by, but still not working (generally, with order by time desc some entries are missed, not all - in this case I was able to retrieve older records).

[beckettsean]

@piotr-piatkowski you are describing the symptoms in #4235. We don't know yet whether this is a different issue, as I haven't tried to repro this in 0.9.5.

[nickscript0]

Running InfluxDB 0.9.5 I'm seeing a similar issue to @nornagon: 0 results with ORDER BY time DESC, but when I remove the ORDER BY multiple results are returned (as expected).

This seems to be an intermittent issue, as restarting InfluxDB results in ORDER BY time DESC behaving correctly again.

[larsakeekstrand]

I'm using 0.9.6.1 and seeing a very similar behaviour. Running a query with ORDER BY time desc limit 1 will sometimes not return the latest value. In my use-case new data is written every 15 minutes but when this occurs, I get a several hours old result instead of the latest. A restart of influxdb seems to correct the fault for some time.

[captSL]

I think I'm facing the same issuel like @larsakeekstrand. A query like select value from Temperature_Outside where time < 1453485000s ORDER BY time DESC limit 1 InfluxDB 0.9.6.1 returns a line with the timestamp 2016-01-17T23:57:38.549Z. The series contains a lot more entries between this timestamp and 1453485000 (2016-01-22T17:50:00.000Z)

[jsternberg]

I can't reproduce this on the 0.11 release candidate so I think we fixed this issue as part of the refactor. Please try it out and reopen if the issue persists. Thanks!

Test case I used for verifying:

> create database mydb
> use mydb
Using database mydb
> insert cpu value=1
> insert cpu value=2
> insert cpu value=3
> insert cpu value=4
> insert cpu value=5
> insert cpu value=6
> insert cpu value=7
> insert cpu value=8
> select value from cpu order by time desc limit 2
name: cpu
---------
time                    value
1458142514565266229     8
1458142511573376955     7

[forzagreen]

In my InfluxDB v1.2.1 , it works when LIMIT is after ORDER BY.
This works:
SELECT * FROM http_response ORDER BY time DESC LIMIT 10
This does not work:
SELECT * FROM http_response LIMIT 10 ORDER BY time DESC
and returns the error: ERR: error parsing query: found ORDER, expected ; at line 1, char 38