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

[0.9.4.1] "ORDER BY DESC" doesn't properly order output #4235

Closed
abukaka opened this issue Sep 25, 2015 · 33 comments · Fixed by #4809
Closed

[0.9.4.1] "ORDER BY DESC" doesn't properly order output #4235

abukaka opened this issue Sep 25, 2015 · 33 comments · Fixed by #4809
Assignees
@corylanou
Labels
area/queries support
Milestone
0.9.5

Comments

@abukaka
Copy link

abukaka commented Sep 25, 2015

I'm trying to get the newest n points from a series

>  select * from "76603ea0-f7ea-40ce-819a-df51f16cb379" where time > now() - 1m
name: 76603ea0-f7ea-40ce-819a-df51f16cb379
------------------------------------------
time                            value
2015-09-25T21:54:51.074Z        3.49632
2015-09-25T21:55:21.084Z        3.51199

> select value from "76603ea0-f7ea-40ce-819a-df51f16cb379" order by desc limit 10
name: 76603ea0-f7ea-40ce-819a-df51f16cb379
------------------------------------------
time                            value
2015-09-25T21:38:50.69Z         3.34024
2015-09-25T21:38:20.673Z        3.33428
2015-09-25T21:37:50.663Z        3.33001
2015-09-25T21:37:20.653Z        3.32566
2015-09-25T21:36:50.636Z        3.32132
2015-09-25T21:36:20.626Z        3.31698
2015-09-25T21:35:50.615Z        3.31252
2015-09-25T21:35:20.605Z        3.30831
2015-09-25T21:34:50.595Z        3.30364
2015-09-25T21:34:20.585Z        3.29886

The first query correctly shows the latest point:

2015-09-25T21:55:21.084Z        3.51199

But the second query is showing 10 points from an earlier time.
The data is coming in every 30 seconds, but I keep getting the exact same resultset from the second query until I restart influxdb, and then it stays at a newer resultset.
@beckettsean
Copy link
Contributor

Might be a caching issue. @jwilder thoughts on this ORDER BY DESC bug?

@beckettsean
Copy link
Contributor

I can confirm the behavior. The following queries were run within 20 seconds of each other:

For each of the select * from cpu_busy order by desc limit 10 notice that the timestamps are identical but the series returned is non-deterministic. Not sure if that's a bug or not.

In any event, there is clearly more recent data than the 18:37:28 points, yet that's all the ORDER BY DESC queries return.

> select * from cpu_busy order by desc limit 10
name: cpu_busy
--------------
time                cpu host        value
2015-09-25T18:37:28.145778884Z  cpu5    catalyst    572.640625
2015-09-25T18:37:21.146185424Z  cpu5    catalyst    572.6328125
2015-09-25T18:37:14.14466783Z   cpu5    catalyst    572.625
2015-09-25T18:37:07.146164339Z  cpu5    catalyst    572.6171875
2015-09-25T18:37:00.138410476Z  cpu5    catalyst    572.609375
2015-09-25T18:36:53.145089554Z  cpu5    catalyst    572.6015625
2015-09-25T18:36:46.139973579Z  cpu5    catalyst    572.59375
2015-09-25T18:36:39.141992625Z  cpu5    catalyst    572.5859375
2015-09-25T18:36:32.147192457Z  cpu5    catalyst    572.578125
2015-09-25T18:36:25.143305949Z  cpu5    catalyst    572.578125

> select * from cpu_busy order by desc limit 10
name: cpu_busy
--------------
time                cpu host        value
2015-09-25T18:37:28.145778884Z  cpu1    catalyst    550.203125
2015-09-25T18:37:21.146185424Z  cpu1    catalyst    550.203125
2015-09-25T18:37:14.14466783Z   cpu1    catalyst    550.1875
2015-09-25T18:37:07.146164339Z  cpu1    catalyst    550.1875
2015-09-25T18:37:00.138410476Z  cpu1    catalyst    550.1796875
2015-09-25T18:36:53.145089554Z  cpu1    catalyst    550.171875
2015-09-25T18:36:46.139973579Z  cpu1    catalyst    550.1640625
2015-09-25T18:36:39.141992625Z  cpu1    catalyst    550.15625
2015-09-25T18:36:32.147192457Z  cpu1    catalyst    550.1484375
2015-09-25T18:36:25.143305949Z  cpu1    catalyst    550.1484375

> select * from cpu_busy order by desc limit 10
name: cpu_busy
--------------
time                cpu host        value
2015-09-25T18:37:28.145778884Z  cpu2    catalyst    5758.6796875
2015-09-25T18:37:21.146185424Z  cpu2    catalyst    5758.6484375
2015-09-25T18:37:14.14466783Z   cpu2    catalyst    5758.4453125
2015-09-25T18:37:07.146164339Z  cpu2    catalyst    5758.3671875
2015-09-25T18:37:00.138410476Z  cpu2    catalyst    5758.3203125
2015-09-25T18:36:53.145089554Z  cpu2    catalyst    5758.2109375
2015-09-25T18:36:46.139973579Z  cpu2    catalyst    5758.1484375
2015-09-25T18:36:39.141992625Z  cpu2    catalyst    5758.1015625
2015-09-25T18:36:32.147192457Z  cpu2    catalyst    5758.0390625
2015-09-25T18:36:25.143305949Z  cpu2    catalyst    5757.9921875

> select * from cpu_busy where time > now() - 1m
name: cpu_busy
--------------
time                cpu     host        value
2015-09-25T22:46:26.822606851Z  cpu3        catalyst    654.40625
2015-09-25T22:46:26.822606851Z  cpu1        catalyst    627.3125
2015-09-25T22:46:26.822606851Z  cpu7        catalyst    650.8203125
2015-09-25T22:46:26.822606851Z  cpu5        catalyst    649.5703125
2015-09-25T22:46:26.822606851Z  cpu4        catalyst    6984.0859375
2015-09-25T22:46:26.822606851Z  cpu6        catalyst    6977.765625
2015-09-25T22:46:26.822606851Z  cpu2        catalyst    6988.0703125
2015-09-25T22:46:26.822606851Z  cpu0        catalyst    10862.1640625
2015-09-25T22:46:26.822606851Z  cpu-total   catalyst    34394.1953125
2015-09-25T22:46:33.823754066Z  cpu0        catalyst    10862.828125
2015-09-25T22:46:33.823754066Z  cpu-total   catalyst    34395.5
2015-09-25T22:46:33.823754066Z  cpu7        catalyst    650.828125
2015-09-25T22:46:33.823754066Z  cpu3        catalyst    654.4140625
2015-09-25T22:46:33.823754066Z  cpu1        catalyst    627.3203125
2015-09-25T22:46:33.823754066Z  cpu2        catalyst    6988.25
2015-09-25T22:46:33.823754066Z  cpu5        catalyst    649.5703125
2015-09-25T22:46:33.823754066Z  cpu6        catalyst    6977.875
2015-09-25T22:46:33.823754066Z  cpu4        catalyst    6984.4140625
2015-09-25T22:46:49.032374681Z  cpu6        catalyst    6978.34375
2015-09-25T22:46:49.032374681Z  cpu4        catalyst    6984.9765625
2015-09-25T22:46:49.032374681Z  cpu7        catalyst    650.84375
2015-09-25T22:46:49.032374681Z  cpu0        catalyst    10864.359375
2015-09-25T22:46:49.032374681Z  cpu-total   catalyst    34398.5703125
2015-09-25T22:46:49.032374681Z  cpu5        catalyst    649.578125
2015-09-25T22:46:49.032374681Z  cpu3        catalyst    654.421875
2015-09-25T22:46:49.032374681Z  cpu2        catalyst    6988.7109375
2015-09-25T22:46:49.032374681Z  cpu1        catalyst    627.3359375
2015-09-25T22:46:56.035665737Z  cpu2        catalyst    6988.96875
2015-09-25T22:46:56.035665737Z  cpu1        catalyst    627.3359375
2015-09-25T22:46:56.035665737Z  cpu7        catalyst    650.84375
2015-09-25T22:46:56.035665737Z  cpu6        catalyst    6978.640625
2015-09-25T22:46:56.035665737Z  cpu4        catalyst    6985.234375
2015-09-25T22:46:56.035665737Z  cpu3        catalyst    654.4375
2015-09-25T22:46:56.035665737Z  cpu0        catalyst    10865.1015625
2015-09-25T22:46:56.035665737Z  cpu5        catalyst    649.59375
2015-09-25T22:46:56.035665737Z  cpu-total   catalyst    34400.1640625
2015-09-25T22:47:03.035745541Z  cpu5        catalyst    649.6015625
2015-09-25T22:47:03.035745541Z  cpu-total   catalyst    34402.1328125
2015-09-25T22:47:03.035745541Z  cpu7        catalyst    650.8515625
2015-09-25T22:47:03.035745541Z  cpu2        catalyst    6989.484375
2015-09-25T22:47:03.035745541Z  cpu1        catalyst    627.34375
2015-09-25T22:47:03.035745541Z  cpu0        catalyst    10865.9375
2015-09-25T22:47:03.035745541Z  cpu6        catalyst    6978.9375
2015-09-25T22:47:03.035745541Z  cpu3        catalyst    654.4375
2015-09-25T22:47:03.035745541Z  cpu4        catalyst    6985.5390625
2015-09-25T22:47:10.036339336Z  cpu4        catalyst    6985.734375
2015-09-25T22:47:10.036339336Z  cpu7        catalyst    650.8515625
2015-09-25T22:47:10.036339336Z  cpu5        catalyst    649.6015625
2015-09-25T22:47:10.036339336Z  cpu-total   catalyst    34403.53125
2015-09-25T22:47:10.036339336Z  cpu3        catalyst    654.4453125
2015-09-25T22:47:10.036339336Z  cpu6        catalyst    6979.171875
2015-09-25T22:47:10.036339336Z  cpu2        catalyst    6989.7109375
2015-09-25T22:47:10.036339336Z  cpu0        catalyst    10866.6640625
2015-09-25T22:47:10.036339336Z  cpu1        catalyst    627.3515625
2015-09-25T22:47:17.039300539Z  cpu4        catalyst    6986.1640625
2015-09-25T22:47:17.039300539Z  cpu0        catalyst    10867.4921875
2015-09-25T22:47:17.039300539Z  cpu1        catalyst    627.421875
2015-09-25T22:47:17.039300539Z  cpu7        catalyst    650.9296875
2015-09-25T22:47:17.039300539Z  cpu3        catalyst    654.5234375
2015-09-25T22:47:17.039300539Z  cpu2        catalyst    6990
2015-09-25T22:47:17.039300539Z  cpu5        catalyst    649.6875
2015-09-25T22:47:17.039300539Z  cpu6        catalyst    6979.5625
2015-09-25T22:47:17.039300539Z  cpu-total   catalyst    34405.78125
2015-09-25T22:47:24.039074098Z  cpu6        catalyst    6979.765625
2015-09-25T22:47:24.039074098Z  cpu-total   catalyst    34407.1640625
2015-09-25T22:47:24.039074098Z  cpu4        catalyst    6986.390625
2015-09-25T22:47:24.039074098Z  cpu0        catalyst    10868.2109375
2015-09-25T22:47:24.039074098Z  cpu1        catalyst    627.4375
2015-09-25T22:47:24.039074098Z  cpu3        catalyst    654.53125
2015-09-25T22:47:24.039074098Z  cpu2        catalyst    6990.1875
2015-09-25T22:47:24.039074098Z  cpu7        catalyst    650.9453125
2015-09-25T22:47:24.039074098Z  cpu5        catalyst    649.6953125

> select * from cpu_busy order by desc limit 10
name: cpu_busy
--------------
time                cpu     host        value
2015-09-25T18:37:28.145778884Z  cpu-total   catalyst    29100.0859375
2015-09-25T18:37:21.146185424Z  cpu-total   catalyst    29099.8359375
2015-09-25T18:37:14.14466783Z   cpu-total   catalyst    29098.6875
2015-09-25T18:37:07.146164339Z  cpu-total   catalyst    29098.3125
2015-09-25T18:37:00.138410476Z  cpu-total   catalyst    29097.9765625
2015-09-25T18:36:53.145089554Z  cpu-total   catalyst    29097.5546875
2015-09-25T18:36:46.139973579Z  cpu-total   catalyst    29097.3125
2015-09-25T18:36:39.141992625Z  cpu-total   catalyst    29097.0546875
2015-09-25T18:36:32.147192457Z  cpu-total   catalyst    29096.6640625
2015-09-25T18:36:25.143305949Z  cpu-total   catalyst    29096.4296875

>

@beckettsean
Copy link
Contributor

It's not caching, or at least changing the measurement to one I haven't queried ever before still returns the older points:

> select * from cpu_guest where time > now() - 20s
name: cpu_guest
---------------
time                cpu     host        value
2015-09-25T22:50:33.041239173Z  cpu7        catalyst    0
2015-09-25T22:50:33.041239173Z  cpu2        catalyst    0
2015-09-25T22:50:33.041239173Z  cpu3        catalyst    0
2015-09-25T22:50:33.041239173Z  cpu1        catalyst    0
2015-09-25T22:50:33.041239173Z  cpu-total   catalyst    0
2015-09-25T22:50:33.041239173Z  cpu0        catalyst    0
2015-09-25T22:50:33.041239173Z  cpu5        catalyst    0
2015-09-25T22:50:33.041239173Z  cpu4        catalyst    0
2015-09-25T22:50:33.041239173Z  cpu6        catalyst    0
2015-09-25T22:50:40.039990577Z  cpu7        catalyst    0
2015-09-25T22:50:40.039990577Z  cpu4        catalyst    0
2015-09-25T22:50:40.039990577Z  cpu6        catalyst    0
2015-09-25T22:50:40.039990577Z  cpu2        catalyst    0
2015-09-25T22:50:40.039990577Z  cpu-total   catalyst    0
2015-09-25T22:50:40.039990577Z  cpu5        catalyst    0
2015-09-25T22:50:40.039990577Z  cpu3        catalyst    0
2015-09-25T22:50:40.039990577Z  cpu0        catalyst    0
2015-09-25T22:50:40.039990577Z  cpu1        catalyst    0
2015-09-25T22:50:47.039657797Z  cpu0        catalyst    0
2015-09-25T22:50:47.039657797Z  cpu1        catalyst    0
2015-09-25T22:50:47.039657797Z  cpu7        catalyst    0
2015-09-25T22:50:47.039657797Z  cpu4        catalyst    0
2015-09-25T22:50:47.039657797Z  cpu6        catalyst    0
2015-09-25T22:50:47.039657797Z  cpu-total   catalyst    0
2015-09-25T22:50:47.039657797Z  cpu5        catalyst    0
2015-09-25T22:50:47.039657797Z  cpu2        catalyst    0
2015-09-25T22:50:47.039657797Z  cpu3        catalyst    0

> select * from cpu_guest order by desc limit 10
name: cpu_guest
---------------
time                cpu     host        value
2015-09-25T18:37:28.145778884Z  cpu-total   catalyst    0
2015-09-25T18:37:21.146185424Z  cpu-total   catalyst    0
2015-09-25T18:37:14.14466783Z   cpu-total   catalyst    0
2015-09-25T18:37:07.146164339Z  cpu-total   catalyst    0
2015-09-25T18:37:00.138410476Z  cpu-total   catalyst    0
2015-09-25T18:36:53.145089554Z  cpu-total   catalyst    0
2015-09-25T18:36:46.139973579Z  cpu-total   catalyst    0
2015-09-25T18:36:39.141992625Z  cpu-total   catalyst    0
2015-09-25T18:36:32.147192457Z  cpu-total   catalyst    0
2015-09-25T18:36:25.143305949Z  cpu-total   catalyst    0

@beckettsean
Copy link
Contributor

It's interesting that the timestamp doesn't move forward. Some similar symptoms to #4209 although no ORDER BY in that issue and in this issue, the normal WHERE time queries return the expected results.

@markusr
Copy link

markusr commented Sep 29, 2015

I see the same behaviour.

@beckettsean
Copy link
Contributor

possibly related to #4232

@melson-jao
Copy link

I have the same problem. It looks like when using DESC, it cannot have the latest data in X minutes but ASC works fine.

Here is my test:
select * from "2.bs.status" where time > now() - 5m order by time desc
=> no record
select * from "2.bs.status" where time > now() - 5m order by time asc
=> 51 records

@theromis
Copy link

theromis commented Oct 7, 2015

same issue for me

> select host_platform_hostname, ip from device where uuid='xxx' order by time desc limit 20;
name: device
------------
time                host_platform_hostname  ip
2015-10-07T20:07:15.413421077Z  xxx
2015-10-07T16:06:58.455773567Z  xxx
2015-10-07T12:06:41.936796682Zxxx
2015-10-07T08:06:28.2645801Z    xxx
2015-10-07T04:06:26.473557076Z  xxx
2015-10-07T00:06:23.686127241Z  xxx
2015-10-06T20:06:12.676490453Z  xxx
2015-10-06T16:06:08.18404945Z   xxx
2015-10-06T12:06:00.728460079Z  xxx
2015-10-06T08:05:51.025483794Zxxx
2015-10-06T04:05:37.363029562Z  xxx
2015-10-06T00:05:28.033830294Z  xxx
2015-10-05T20:05:17.565873246Zxxx
2015-10-05T16:05:02.900082772Z  xxx
2015-10-05T12:04:50.210404264Zxxx
2015-10-05T08:04:34.163433683Z  xxx
2015-10-05T04:04:21.854884971Zxxx
2015-10-07T21:50:28.74730167Z   xxx     a.b.c.d
2015-10-05T00:04:13.617422854Zxxx
2015-10-04T20:04:06.976163638Zxxx

I assume a.b.c.d ip should be on the top

@jwilder
Copy link
Contributor

jwilder commented Oct 7, 2015

Pretty sure this is an issue w/ the bz1 engine and WAL cursors. Have you tried the tsm1 engine?

@theromis
Copy link

theromis commented Oct 7, 2015

How can I try it? Sorry I'm not familiar with influxdb internals.
But I really like this db and believe in it.
BTW: if this is an issue, how I can convert my current storage to tsm1?

@jwilder
Copy link
Contributor

jwilder commented Oct 7, 2015

@theromis More info here: https://influxdb.com/blog/2015/10/07/the_new_influxdb_storage_engine_a_time_structured_merge_tree.html

@theromis
Copy link

theromis commented Oct 7, 2015

@jwilder Thank you, I saw this info today, but how I can convert my current data into this new format?

@jwilder
Copy link
Contributor

jwilder commented Oct 7, 2015

@theromis This is no convertor right now. If you enable the new engine, new data will be written using the the new engine. Existing data will still use the older engines.

@theromis
Copy link

theromis commented Oct 7, 2015

@jwilder Can I switch storage type to tsm1 in 0.9.4.1 just changing type in conf file?
Cannot find any entries in the influxdb.conf

edit:
can see in 0.9.4.2, will try it now

@jwilder
Copy link
Contributor

jwilder commented Oct 7, 2015

@theromis No. tsm1 is not available in 0.9.4 releases. You need to install the latest nightly build or wait until 0.9.5 and change your config file according the the instructions linked from the blog post. The latest nightly can be downloaded here: https://influxdb.com/download/index.html

@theromis
Copy link

theromis commented Oct 7, 2015

@jwilder updated to 0.9.5 nightly build, but it still showing same result. Should I create new empty database?

@jwilder
Copy link
Contributor

jwilder commented Oct 7, 2015

@theromis You're old data will still still show the wrong sorts since it's still stored in the older engine format. New data written to new shards created by the tsm1 engine should (hopefully) sort correctly. Creating a new database will make that happen faster though.

@theromis
Copy link

theromis commented Oct 7, 2015

@jwilder Thank you, will try it now.

@johnjelinek johnjelinek mentioned this issue Oct 9, 2015
Closed
@beckettsean beckettsean mentioned this issue Oct 13, 2015
Closed
@peekeri
Copy link
Contributor

peekeri commented Oct 15, 2015

I observed the same behaviour, it seems as if limit is applied before ordering, so in my case:

select sum(value) FROM net_bytes_recv where time > now() - 1h group by time(30s) order by time desc limit 5;

returns five oldest points ordered by time in descending order.

@markcwill
Copy link

I can reproduce same behavior on 0.9.4.2

Using the nightly build of 0.9.5, with the tsm1 engine and a fresh db, ORDER BY DESC seems to work correctly.

@plopp
Copy link

plopp commented Oct 28, 2015

Using nightly 0.9.5 with tsm1, fresh install, fresh db and ORDER BY DESC is still broken:

Version, commit, built info:

2015/10/28 16:10:00 InfluxDB starting, version 0.9.5-nightly-7278ec9, branch master, commit 7278ec96a9decb403b2dc45576617fbca4a748ab, built '2015-10-28T04:00:40+0000'
2015/10/28 16:10:00 Go version go1.5.1, GOMAXPROCS set to 4
2015/10/28 16:10:00 Using configuration at: /etc/opt/influxdb/influxdb.conf

Config file in /etc/opt/influxdb/influxdb.conf:

# Controls the engine type for new shards. Options are b1, bz1, or tsm1.
# tsm1 is the 0.9.5 engine
  engine ="tsm1"

CLI-output with erroneous sorting:

Visit https://enterprise.influxdata.com to register for updates, InfluxDB server management, and monitoring.
Connected to http://localhost:8086 version 0.9.5-nightly-7278ec9
InfluxDB shell 0.9.5-nightly-7278ec9
> select * from power order by time desc
name: power
-----------
time            presence    room        value
1446045851359000000 false       testroom    25.3
1446045850815000000 false       testroom    25.3
1446045850287000000 false       testroom    25.3
1446045849775000000 false       testroom    25.3
1446045849247000000 false       testroom    25.3
1446045848705000000 false       testroom    25.3
1446045848159000000 false       testroom    25.3
1446045847615000000 false       testroom    25.3
1446045847086000000 false       testroom    25.3
1446045846560000000 false       testroom    25.3
1446045846040000000 false       testroom    25.3
1446045845511000000 false       testroom    25.3
1446045844921000000 false       testroom    25.3
1446045844199000000 false       testroom    25.3
1446045843680000000 false       testroom    25.3
1446045843152000000 false       testroom    25.3
1446045842632000000 false       testroom    25.3
### WRONG SORTING STARTS HERE ###
1446045857944000000 true        testroom    25.3
1446045857455000000 true        testroom    25.3
1446045856964000000 true        testroom    25.3
1446045856520000000 true        testroom    25.3
1446045856049000000 true        testroom    25.3
1446045855543000000 true        testroom    25.3
1446045855039000000 true        testroom    25.3
1446045854558000000 true        testroom    25.3
1446045853791000000 true        testroom    25.3
### ENDS HERE ###
1446045842063000000 false       testroom    25.3

@beckettsean beckettsean mentioned this issue Oct 28, 2015
Closed
@richterger
Copy link

From what I saw, it works ok, unless new tags gets introduced (see #4077 for details) in a measurement

@theoweiss theoweiss mentioned this issue Nov 4, 2015
Closed
@ryan-williams
Copy link

Just ran in to this on 0.9.4.1, trying to switch to nightly 0.9.5.

One thing I noticed: the link to the line in the config file from this post points to master and no longer appears to be pointing to the right part of the file.

@ryan-williams
Copy link

It seems like there are nightly builds that can run on OSX; is that right?

@otoolep
Copy link
Contributor

otoolep commented Nov 9, 2015

We do not have nightly builds for OSX.

@ryan-williams
Copy link

Thanks, is there an easy way for me to build from source?

@otoolep
Copy link
Contributor

otoolep commented Nov 9, 2015

Please read the docs: https://github.com/influxdb/influxdb/blob/master/CONTRIBUTING.md

@ryan-williams
Copy link

Thanks, I built from HEAD and set engine to tsm1 and still see this issue:

> select * from task order by time desc limit 10
name: task
----------
time                    app                             id      idx     reason  start   success value
1445817842801000000     application_1444948191538_0465  59866   18041.0         true            1
1445817842800000000     application_1444948191538_0465  59865   18040.0         true            1
1445817842799000000     application_1444948191538_0465  59864   18039.0         true            1
1445817842798000000     application_1444948191538_0465  59863   18038.0         true            1
1445817842600000000     application_1444948191538_0465  57634   15539.0         false   true    1
1445817842600000000     application_1444948191538_0465  59862   18036.0         true            1
1445817842592000000     application_1444948191538_0465  57616   12833.3         false   true    1
1445817842592000000     application_1444948191538_0465  59861   18035.0         true            1
1445817842591000000     application_1444948191538_0465  57523   15400.0         false   true    1
1445817842590000000     application_1444948191538_0465  59860   18034.0         true            1

> select count(value) from task where time > 1445817842801000000
name: task
----------
time                    count
1445817842800999937     133773

So in this thread we have one report (@markcwill here) that tsm1 fixes this and two reports (@plopp above and myself) that it does not. Perplexing! Anyone know what is going on?

@beckettsean beckettsean changed the title [0.9.4.1] "ORDER BY DESC LIMIT 10" doesn't return the newest 10 points [0.9.4.1] "ORDER BY DESC" doesn't properly order output Nov 12, 2015
@beckettsean beckettsean mentioned this issue Nov 12, 2015
Closed
@oshoemaker
Copy link

I am also experiencing the same issue using tsm1.

root@db1:/app# influxd version
InfluxDB v0.9.5-nightly-2fe5e6b (git: master 2fe5e6b, built '2015-10-27T04:00:42+0000')

[meta]
dir = "/data/influxdb/meta"
hostname = "localhost"
bind-address = ":8088"
retention-autocreate = true
election-timeout = "1s"
heartbeat-timeout = "1s"
leader-lease-timeout = "500ms"
commit-timeout = "50ms"
cluster-tracing = false

[data]
dir = "/data/influxdb/data"
engine = "tsm1"
max-wal-size = 104857600
wal-flush-interval = "10m0s"
wal-partition-flush-delay = "2s"
wal-dir = "/data/influxdb/wal"
wal-logging-enabled = true
wal-ready-series-size = 30720
wal-compaction-threshold = 0.5
wal-max-series-size = 1048576
wal-flush-cold-interval = "5s"
wal-partition-size-threshold = 52428800
wal-flush-memory-size-threshold = 5242880
wal-max-memory-size-threshold = 104857600
index-compaction-age = 60000000000
index-min-compaction-interval = 60000000000
index-compaction-min-file-count = 5
index-compaction-full-age = 300000000000
query-log-enabled = true

select * from alarm 
name: alarm
-----------
time            alarm   baseId  bsid            locationId
2015-11-12T20:10:25Z    on  209 d0:63:be:ef:de:ad   2
2015-11-12T20:14:23Z    off 209 d0:63:be:ef:de:ad   2
2015-11-12T20:23:02Z    on  209 d0:63:be:ef:de:ad   2
2015-11-12T20:24:43Z    off 209 d0:63:be:ef:de:ad   2
2015-11-12T20:42:09Z    on  209 d0:63:be:ef:de:ad   2
2015-11-12T20:42:38Z    off 209 d0:63:be:ef:de:ad   2
2015-11-12T20:51:15Z    on  209 d0:63:be:ef:de:ad   2
2015-11-12T20:51:58Z    off 209 d0:63:be:ef:de:ad   2

select * from alarm ORDER by time DESC
name: alarm
-----------
time            alarm   baseId  bsid            locationId
2015-11-12T20:51:15Z    on  209 d0:63:be:ef:de:ad   2
2015-11-12T20:42:09Z    on  209 d0:63:be:ef:de:ad   2
2015-11-12T20:23:02Z    on  209 d0:63:be:ef:de:ad   2
2015-11-12T20:51:58Z    off 209 d0:63:be:ef:de:ad   2
2015-11-12T20:42:38Z    off 209 d0:63:be:ef:de:ad   2
2015-11-12T20:24:43Z    off 209 d0:63:be:ef:de:ad   2
2015-11-12T20:14:23Z    off 209 d0:63:be:ef:de:ad   2
2015-11-12T20:10:25Z    on  209 d0:63:be:ef:de:ad   2

@corylanou corylanou added this to the 0.9.5 milestone Nov 16, 2015
@corylanou
Copy link
Contributor

So it appears to do with when it's a select * and there are tags involved. Using identical timestamps and queries, one with tags, one without, you can see the results:

> select * from power
name: power
-----------
time                    value
1446045842063000000     1
1446045842632000000     2
1446045843152000000     3
1446045843680000000     4
1446045844199000000     5
1446045844921000000     6
1446045845511000000     7
1446045846040000000     8
1446045846560000000     9
1446045847086000000     10
1446045847615000000     11
1446045848159000000     12
1446045848705000000     13
1446045849247000000     14
1446045849775000000     15
1446045850287000000     16
1446045850815000000     17
1446045851359000000     18
1446045853791000000     19
1446045854558000000     20
1446045855039000000     21
1446045855543000000     22
1446045856049000000     23
1446045856520000000     24
1446045856964000000     25
1446045857455000000     26
1446045857944000000     27

> select * from power order by time desc
name: power
-----------
time                    value
1446045857944000000     27
1446045857455000000     26
1446045856964000000     25
1446045856520000000     24
1446045856049000000     23
1446045855543000000     22
1446045855039000000     21
1446045854558000000     20
1446045853791000000     19
1446045851359000000     18
1446045850815000000     17
1446045850287000000     16
1446045849775000000     15
1446045849247000000     14
1446045848705000000     13
1446045848159000000     12
1446045847615000000     11
1446045847086000000     10
1446045846560000000     9
1446045846040000000     8
1446045845511000000     7
1446045844921000000     6
1446045844199000000     5
1446045843680000000     4
1446045843152000000     3
1446045842632000000     2
1446045842063000000     1

> select * from power
name: power
-----------
time                    presence        room            value
1446045842063000000     false           testroom        1
1446045842632000000     false           testroom        2
1446045843152000000     false           testroom        3
1446045843680000000     false           testroom        4
1446045844199000000     false           testroom        5
1446045844921000000     false           testroom        6
1446045845511000000     false           testroom        7
1446045846040000000     false           testroom        8
1446045846560000000     false           testroom        9
1446045847086000000     false           testroom        10
1446045847615000000     false           testroom        11
1446045848159000000     false           testroom        12
1446045848705000000     false           testroom        13
1446045849247000000     false           testroom        14
1446045849775000000     false           testroom        15
1446045850287000000     false           testroom        16
1446045850815000000     false           testroom        17
1446045851359000000     true            testroom        18
1446045853791000000     true            testroom        19
1446045854558000000     true            testroom        20
1446045855039000000     true            testroom        21
1446045855543000000     true            testroom        22
1446045856049000000     true            testroom        23
1446045856520000000     true            testroom        24
1446045856964000000     true            testroom        25
1446045857455000000     true            testroom        26
1446045857944000000     false           testroom        27

> select * from power order by time desc
name: power
-----------
time                    presence        room            value
1446045857455000000     true            testroom        26
1446045856964000000     true            testroom        25
1446045856520000000     true            testroom        24
1446045856049000000     true            testroom        23
1446045855543000000     true            testroom        22
1446045855039000000     true            testroom        21
1446045854558000000     true            testroom        20
1446045853791000000     true            testroom        19
1446045857944000000     false           testroom        27
1446045851359000000     true            testroom        18
1446045850815000000     false           testroom        17
1446045850287000000     false           testroom        16
1446045849775000000     false           testroom        15
1446045849247000000     false           testroom        14
1446045848705000000     false           testroom        13
1446045848159000000     false           testroom        12
1446045847615000000     false           testroom        11
1446045847086000000     false           testroom        10
1446045846560000000     false           testroom        9
1446045846040000000     false           testroom        8
1446045845511000000     false           testroom        7
1446045844921000000     false           testroom        6
1446045844199000000     false           testroom        5
1446045843680000000     false           testroom        4
1446045843152000000     false           testroom        3
1446045842632000000     false           testroom        2
1446045842063000000     false           testroom        1

@jwilder jwilder removed their assignment Nov 16, 2015
@corylanou corylanou mentioned this issue Nov 16, 2015
Merged
@corylanou corylanou self-assigned this Nov 16, 2015
@beckettsean beckettsean mentioned this issue Nov 20, 2015
Closed
@manishjain002
Copy link

Even without limit and group, the issue is still there and running query with order by is very unreliable.
I have seen the results vary if time is set like "time < now() - 20h" to "time < 'epoch seconds for same time'.
When I am running this on 0.9.5
Select kvahT4 from PWR_00000555 where time < 1448821801s order by time desc

@beckettsean
Copy link
Contributor

@manishjain002 please open a new issue for what you describe, as that is a new, more specific bug.

@beckettsean beckettsean mentioned this issue Dec 1, 2015
Closed
@beckettsean
Copy link
Contributor

@manishjain002 looks like #4235 is for the 0.9.5 ORDER BY behavior.

@beckettsean beckettsean mentioned this issue Dec 30, 2015
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@corylanou corylanou

Labels
area/queries support
Projects
None yet
Milestone
0.9.5
Development

Successfully merging a pull request may close this issue.

Fix Order By Desc influxdata/influxdb