{"action":"created","issue":{"url":"https://api.github.com/repos/influxdata/influxdb/issues/6751","repository_url":"https://api.github.com/repos/influxdata/influxdb","labels_url":"https://api.github.com/repos/influxdata/influxdb/issues/6751/labels{/name}","comments_url":"https://api.github.com/repos/influxdata/influxdb/issues/6751/comments","events_url":"https://api.github.com/repos/influxdata/influxdb/issues/6751/events","html_url":"https://github.com/influxdata/influxdb/issues/6751","id":157522652,"number":6751,"title":"GROUP BY SLIMIT and SOFFSET returns unpredictable results","user":{"login":"larrywalker","id":18051512,"avatar_url":"https://avatars.githubusercontent.com/u/18051512?v=3","gravatar_id":"","url":"https://api.github.com/users/larrywalker","html_url":"https://github.com/larrywalker","followers_url":"https://api.github.com/users/larrywalker/followers","following_url":"https://api.github.com/users/larrywalker/following{/other_user}","gists_url":"https://api.github.com/users/larrywalker/gists{/gist_id}","starred_url":"https://api.github.com/users/larrywalker/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/larrywalker/subscriptions","organizations_url":"https://api.github.com/users/larrywalker/orgs","repos_url":"https://api.github.com/users/larrywalker/repos","events_url":"https://api.github.com/users/larrywalker/events{/privacy}","received_events_url":"https://api.github.com/users/larrywalker/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/influxdata/influxdb/labels/category/queries","name":"category/queries","color":"5319e7"}],"state":"open","locked":false,"assignee":{"login":"jsternberg","id":1212129,"avatar_url":"https://avatars.githubusercontent.com/u/1212129?v=3","gravatar_id":"","url":"https://api.github.com/users/jsternberg","html_url":"https://github.com/jsternberg","followers_url":"https://api.github.com/users/jsternberg/followers","following_url":"https://api.github.com/users/jsternberg/following{/other_user}","gists_url":"https://api.github.com/users/jsternberg/gists{/gist_id}","starred_url":"https://api.github.com/users/jsternberg/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jsternberg/subscriptions","organizations_url":"https://api.github.com/users/jsternberg/orgs","repos_url":"https://api.github.com/users/jsternberg/repos","events_url":"https://api.github.com/users/jsternberg/events{/privacy}","received_events_url":"https://api.github.com/users/jsternberg/received_events","type":"User","site_admin":false},"milestone":null,"comments":15,"created_at":"2016-05-30T15:26:01Z","updated_at":"2016-06-06T16:50:36Z","closed_at":null,"body":"After working through a support case with Sean Beckett, he asked me to open up a case in github.  We have not been able to come up with a simple replication, my measurement isn't that complicated.  \r\nSeeing this problem with both 12.2 and 13.\r\n\r\nBasically we have a large amount of data being returned, we encountered the row limit via http of 10K rows.  So we started to use the SLIMIT and SOFFSET to page through the data.  The results when using SLIMT and SOFFSET are all over the place and are completely unpredictable.  The same behavior was noticed with the CLI client.\r\n\r\nWe are inserting via influx-java client with a non-default retention policy.\r\nI do not believe this is related to the \"insert into\" issue reported in https://github.com/influxdata/influxdb/issues/6711 as we are not using the \"into\".\r\n\r\nOK, here are the details...\r\nThe basic group by appears to work fine, it always returns data, but when you add the SLIMIT AND SOFFSET, the results are all over the place.\r\n\r\nI am seeing some really strange behavior when using the time > now() - 15m with the SLIMIT AND SOFFSET.\r\n\r\nWithout the SLIMIT and SOFFSET I get around 45 deveui values returned in this last 15 minutes.\r\n\r\n```\r\n> select seqno from evt_rp.evt where time > now() - 15m group by deveui\r\n\r\nname: evt\r\ntags: deveui=AABBCC0020002199\r\ntime seqno\r\n---- -----\r\n1464108400539165696 3290\r\nname: evt\r\ntags: deveui=AABBCC00200022F8\r\ntime seqno\r\n---- -----\r\n1464108614692312320 5665\r\nname: evt\r\ntags: deveui=AABBCC002000232B\r\ntime seqno\r\n---- -----\r\n1464108507834923520 5300\r\nname: evt\r\ntags: deveui=AABBCC0020003F54\r\ntime seqno\r\n---- -----\r\n1464108787483994880 490\r\nname: evt\r\ntags: deveui=AABBCC002000401B\r\ntime seqno\r\n---- -----\r\n1464108160006276608 4706\r\nname: evt\r\ntags: deveui=AABBCC00200042AE\r\ntime seqno\r\n---- -----\r\n1464108166697809408 4706\r\nname: evt\r\ntags: deveui=AABBCC00200045C8\r\ntime seqno\r\n---- -----\r\n1464108113667539712 3022\r\nname: evt\r\ntags: deveui=AABBCC00200048B8\r\ntime seqno\r\n---- -----\r\n1464108446000000000 2304\r\n```\r\n\r\n(truncated)\r\n\r\nOK good, got data, now run with a SLMIT and SOFFSET. Only the 3’rd query returns data.\r\nHow can I have data at SLIMIT 10 and SOFFSET 10, but not at SLIMIT 1 SOFFSET 1?????\r\nAnd why only 3 devices returned when I know there are ~45 in this time period???\r\n\r\n```\r\n> select seqno from evt_rp.evt where time > now() - 15m group by deveui SLIMIT 1 SOFFSET 1\r\n> select seqno from evt_rp.evt where time > now() - 15m group by deveui SLIMIT 10 SOFFSET 1\r\n> select seqno from evt_rp.evt where time > now() - 15m group by deveui SLIMIT 10 SOFFSET 10\r\n\r\nname: evt\r\ntags: deveui=AABBCC0020002199\r\ntime seqno\r\n---- -----\r\n1464108400539165696 3290\r\nname: evt\r\ntags: deveui=AABBCC00200022F8\r\ntime seqno\r\n---- -----\r\n1464108614692312320 5665\r\nname: evt\r\ntags: deveui=AABBCC002000232B\r\ntime seqno\r\n---- -----\r\n1464108507834923520 5300\r\nHere are a couple more oddities.\r\nI have an entry at position 2 (slimit 1 soffset 1), but NOT at position 1 (slimit 1 soffset 0)\r\n> select seqno from evt_rp.evt where time > now() - 15m group by deveui SLIMIT 1 SOFFSET 1\r\nname: evt\r\ntags: deveui=AABBCC0020001F7B\r\ntime seqno\r\n---- -----\r\n2016-05-24T17:27:04Z 3193\r\n> select seqno from evt_rp.evt where time > now() - 15m group by deveui SLIMIT 1 SOFFSET 0\r\n> select seqno from evt_rp.evt where time > now() - 15m group by deveui SLIMIT 1 SOFFSET 1\r\nname: evt\r\ntags: deveui=AABBCC0020001F7B\r\ntime seqno\r\n---- -----\r\n2016-05-24T17:27:04Z 3193\r\n```\r\n\r\nNothing with SLIMIT 1, but SLIMIT2 return a single entry. Seriously???\r\n\r\n```\r\n> select seqno from evt_rp.evt where time > now() - 15m group by deveui SLIMIT 1 SOFFSET 0\r\n> select seqno from evt_rp.evt where time > now() - 15m group by deveui SLIMIT 2 SOFFSET 0\r\nname: evt\r\ntags: deveui=AABBCC0020001F7B\r\ntime seqno\r\n---- -----\r\n2016-05-24T17:27:04Z 3193\r\n```\r\n\r\nAt this point I am not trusting any of the data that is returned because I cannot determine a pattern,\r\nso I changed the query around to be for a specific range (time > '2016-05-23' and time < '2016-05-24')\r\nbecause I didn’t want the possibility of a sliding time of now() to return different data\r\n\r\n```\r\n> select seqno from evt_rp.evt where time > '2016-05-23' and time < '2016-05-24' group by deveui SLIMIT 1 SOFFSET 0\r\nname: evt\r\ntags: deveui=AABBCC0020001F78\r\ntime seqno\r\n---- -----\r\n2016-05-23T20:38:13.928824832Z 1770\r\n2016-05-23T21:38:17.404920576Z 1771\r\n```\r\nOK GOOD, returned 1 device “1F78”\r\n\r\n```\r\n> select seqno from evt_rp.evt where time > '2016-05-23' and time < '2016-05-24' group by deveui SLIMIT 2 SOFFSET 0\r\nname: evt\r\ntags: deveui=AABBCC0020001F78\r\ntime seqno\r\n---- -----\r\n2016-05-23T20:38:13.928824832Z 1770\r\n2016-05-23T21:38:17.404920576Z 1771\r\nname: evt\r\ntags: deveui=AABBCC0020001F7B\r\ntime seqno\r\n---- -----\r\n2016-05-23T20:26:04.72924544Z 3172\r\n2016-05-23T21:26:08.415660032Z 3173\r\n2016-05-23T22:26:10.77421696Z 3174\r\n```\r\n\r\nOK GOOD, returned 2 devices “1F78”, and “1F7B”\r\n\r\n```\r\n> select seqno from evt_rp.evt where time > '2016-05-23' and time < '2016-05-24' group by deveui SLIMIT 2 SOFFSET 1\r\nname: evt\r\ntags: deveui=AABBCC0020001F7B\r\ntime seqno\r\n---- -----\r\n2016-05-23T20:26:04.72924544Z 3172\r\n2016-05-23T21:26:08.415660032Z 3173\r\n2016-05-23T22:26:10.77421696Z 3174\r\nname: evt\r\ntags: deveui=AABBCC0020001F9A\r\ntime seqno\r\n---- -----\r\n2016-05-23T20:09:31.134389248Z 2355\r\n2016-05-23T21:09:33.47566464Z 2356\r\n2016-05-23T22:09:27Z 2357\r\n```\r\n\r\nNice, returned 2 devices and used the soffset of 1 “1F7B”, and “1F9A” to give me the second device.\r\n\r\nHere is a what our schema looks like tags and fields.\r\n\r\n```\r\n> show tag keys from evt_rp.evt\r\nname: evt\r\n-------------\r\ntagKey\r\nbstneui\r\ndeveui\r\n\r\n> show field keys from evt_rp.evt\r\nname: evt\r\n-------------\r\nfieldKey\r\nbillable\r\nbstneui_f\r\nchnl\r\ndeveui_f\r\ndownpdusz\r\ndr\r\nfreq\r\njoinid\r\nmic\r\nmsg\r\npdu\r\nport\r\nrssi\r\nseqno\r\nsnr\r\ntype\r\nuppdusz\r\n```"},"comment":{"url":"https://api.github.com/repos/influxdata/influxdb/issues/comments/224017813","html_url":"https://github.com/influxdata/influxdb/issues/6751#issuecomment-224017813","issue_url":"https://api.github.com/repos/influxdata/influxdb/issues/6751","id":224017813,"user":{"login":"jsternberg","id":1212129,"avatar_url":"https://avatars.githubusercontent.com/u/1212129?v=3","gravatar_id":"","url":"https://api.github.com/users/jsternberg","html_url":"https://github.com/jsternberg","followers_url":"https://api.github.com/users/jsternberg/followers","following_url":"https://api.github.com/users/jsternberg/following{/other_user}","gists_url":"https://api.github.com/users/jsternberg/gists{/gist_id}","starred_url":"https://api.github.com/users/jsternberg/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jsternberg/subscriptions","organizations_url":"https://api.github.com/users/jsternberg/orgs","repos_url":"https://api.github.com/users/jsternberg/repos","events_url":"https://api.github.com/users/jsternberg/events{/privacy}","received_events_url":"https://api.github.com/users/jsternberg/received_events","type":"User","site_admin":false},"created_at":"2016-06-06T16:50:36Z","updated_at":"2016-06-06T16:50:36Z","body":"Can you confirm what data there is in that time period with doing this query?\r\n\r\n```\r\nSELECT * FROM senetdev.evtlora_rp.evtlora WHERE time > '2016-06-03T23:58:04.839075423Z' AND time <= '2016-06-04T23:58:04.839075423Z' GROUP BY *\r\n```"}}
{"actor":{"display_login":"jsternberg"}}