Difficulty with < time WHERE clause

[duff]

I'm having some difficulty with a < query and I spoke with Edd Robinson in the #influxdb Slack channel who was very gracious and helpful. He asked me to file this report in the hopes that someone may be able to help. Thanks!

Here's my measurement:

> select time, amount, currency_code from transactions_1
name: transactions_1
--------------------
time				amount	currency_code
2016-08-09T11:28:07.551061524Z	13114	USD
2016-08-10T23:00:47.739801373Z	200	USD
2016-08-24T04:47:18.442277346Z	2999	USD
2016-09-03T12:29:53.984329376Z	2500	USD
2016-09-09T08:37:22.312705148Z	7115	USD
2016-09-11T11:06:12.934761643Z	8995	EUR
2016-09-13T13:32:11.094254625Z	25028	EUR
2016-09-14T17:02:20.758053347Z	23490	USD
2016-09-17T00:05:19.325800105Z	24030	USD
2016-09-18T11:34:55.552019607Z	4290	GBP
2016-09-21T23:32:44.735612336Z	10305	USD
2016-09-25T13:41:41.676837266Z	18428	MXN
2016-09-27T17:15:02.773343014Z	4900	USD
2016-09-30T14:29:41.847930497Z	5000	USD
2016-09-30T17:24:43.916382436Z	3300	USD
2016-10-05T11:47:53.813892702Z	20790	INR
2016-10-07T09:52:51.377802348Z	609	EUR
2016-10-10T05:24:42.395553557Z	5000	USD
2016-10-12T03:05:18.589752016Z	3200	MXN
2016-10-12T03:16:54.447338471Z	59700	USD
2016-10-16T12:27:12.193296921Z	1149	EUR
2016-10-20T00:05:43.414946815Z	7500	AUD
2016-10-20T11:26:15.992575663Z	5398	USD
2016-10-23T17:05:18.043246419Z	2921	USD
2016-10-25T15:15:26.944701172Z	3200	MXN
2016-10-30T14:06:17.43000273Z	2920	GBP
2016-10-31T08:37:31.406371138Z	412	EUR

Both > and < queries work great on it:

> select time, amount, currency_code from transactions_1 where time > '2016-10-01'
name: transactions_1
--------------------
time				amount	currency_code
2016-10-05T11:47:53.813892702Z	20790	INR
2016-10-07T09:52:51.377802348Z	609	EUR
2016-10-10T05:24:42.395553557Z	5000	USD
2016-10-12T03:05:18.589752016Z	3200	MXN
2016-10-12T03:16:54.447338471Z	59700	USD
2016-10-16T12:27:12.193296921Z	1149	EUR
2016-10-20T00:05:43.414946815Z	7500	AUD
2016-10-20T11:26:15.992575663Z	5398	USD
2016-10-23T17:05:18.043246419Z	2921	USD
2016-10-25T15:15:26.944701172Z	3200	MXN
2016-10-30T14:06:17.43000273Z	2920	GBP
2016-10-31T08:37:31.406371138Z	412	EUR

> select time, amount, currency_code from transactions_1 where time < '2016-10-01'
name: transactions_1
--------------------
time				amount	currency_code
2016-08-09T11:28:07.551061524Z	13114	USD
2016-08-10T23:00:47.739801373Z	200	USD
2016-08-24T04:47:18.442277346Z	2999	USD
2016-09-03T12:29:53.984329376Z	2500	USD
2016-09-09T08:37:22.312705148Z	7115	USD
2016-09-11T11:06:12.934761643Z	8995	EUR
2016-09-13T13:32:11.094254625Z	25028	EUR
2016-09-14T17:02:20.758053347Z	23490	USD
2016-09-17T00:05:19.325800105Z	24030	USD
2016-09-18T11:34:55.552019607Z	4290	GBP
2016-09-21T23:32:44.735612336Z	10305	USD
2016-09-25T13:41:41.676837266Z	18428	MXN
2016-09-27T17:15:02.773343014Z	4900	USD
2016-09-30T14:29:41.847930497Z	5000	USD
2016-09-30T17:24:43.916382436Z	3300	USD

When I create another measurement though (a basic downsampling) using this query:

select sum(amount) AS amount, sum(usd_amount) as usd_amount, count(amount) INTO "daily_transactions_1" FROM transactions_1 WHERE time >= '2015-01-01' GROUP BY time(1d), gateway_type, currency_code, refund

The result measurement daily_transactions_1 has trouble with < queries. Here are all of the points in daily_transactions_1:

> select time, amount, currency_code from daily_transactions_1
name: daily_transactions_1
--------------------------
time			amount	currency_code
2016-08-09T00:00:00Z	13114	USD
2016-08-10T00:00:00Z	200	USD
2016-08-24T00:00:00Z	2999	USD
2016-09-03T00:00:00Z	2500	USD
2016-09-09T00:00:00Z	7115	USD
2016-09-11T00:00:00Z	8995	EUR
2016-09-13T00:00:00Z	25028	EUR
2016-09-14T00:00:00Z	23490	USD
2016-09-17T00:00:00Z	24030	USD
2016-09-18T00:00:00Z	4290	GBP
2016-09-21T00:00:00Z	10305	USD
2016-09-25T00:00:00Z	18428	MXN
2016-09-27T00:00:00Z	4900	USD
2016-09-30T00:00:00Z	8300	USD
2016-10-05T00:00:00Z	20790	INR
2016-10-07T00:00:00Z	609	EUR
2016-10-10T00:00:00Z	5000	USD
2016-10-12T00:00:00Z	3200	MXN
2016-10-12T00:00:00Z	59700	USD
2016-10-16T00:00:00Z	1149	EUR
2016-10-20T00:00:00Z	7500	AUD
2016-10-20T00:00:00Z	5398	USD
2016-10-23T00:00:00Z	2921	USD
2016-10-25T00:00:00Z	3200	MXN
2016-10-30T00:00:00Z	2920	GBP
2016-10-31T00:00:00Z	412	EUR

> works great:

> select time, amount, currency_code from daily_transactions_1 where time > '2016-10-01'
name: daily_transactions_1
--------------------------
time			amount	currency_code
2016-10-05T00:00:00Z	20790	INR
2016-10-07T00:00:00Z	609	EUR
2016-10-10T00:00:00Z	5000	USD
2016-10-12T00:00:00Z	3200	MXN
2016-10-12T00:00:00Z	59700	USD
2016-10-16T00:00:00Z	1149	EUR
2016-10-20T00:00:00Z	7500	AUD
2016-10-20T00:00:00Z	5398	USD
2016-10-23T00:00:00Z	2921	USD
2016-10-25T00:00:00Z	3200	MXN
2016-10-30T00:00:00Z	2920	GBP
2016-10-31T00:00:00Z	412	EUR

But < doesn't:

> select time, amount, currency_code from daily_transactions_1 where time < '2016-10-01'
>

Any idea what I might be doing wrong?

I'll also include this, in case it helps:

> show shards
name: _internal
---------------
id	database	retention_policy	shard_group	start_time		end_time		expiry_time		owners
44	_internal	monitor			44		2016-10-28T00:00:00Z	2016-10-29T00:00:00Z	2016-11-05T00:00:00Z
45	_internal	monitor			45		2016-10-29T00:00:00Z	2016-10-30T00:00:00Z	2016-11-06T00:00:00Z
46	_internal	monitor			46		2016-10-30T00:00:00Z	2016-10-31T00:00:00Z	2016-11-07T00:00:00Z
47	_internal	monitor			47		2016-10-31T00:00:00Z	2016-11-01T00:00:00Z	2016-11-08T00:00:00Z
49	_internal	monitor			49		2016-11-01T00:00:00Z	2016-11-02T00:00:00Z	2016-11-09T00:00:00Z
50	_internal	monitor			50		2016-11-02T00:00:00Z	2016-11-03T00:00:00Z	2016-11-10T00:00:00Z
51	_internal	monitor			51		2016-11-03T00:00:00Z	2016-11-04T00:00:00Z	2016-11-11T00:00:00Z
54	_internal	monitor			54		2016-11-04T00:00:00Z	2016-11-05T00:00:00Z	2016-11-12T00:00:00Z


name: gateway_flow
------------------
id	database	retention_policy	shard_group	start_time		end_time		expiry_time		owners
58	gateway_flow	autogen			58		2016-08-08T00:00:00Z	2016-08-15T00:00:00Z	2016-08-15T00:00:00Z
62	gateway_flow	autogen			62		2016-08-22T00:00:00Z	2016-08-29T00:00:00Z	2016-08-29T00:00:00Z
66	gateway_flow	autogen			66		2016-08-29T00:00:00Z	2016-09-05T00:00:00Z	2016-09-05T00:00:00Z
60	gateway_flow	autogen			60		2016-09-05T00:00:00Z	2016-09-12T00:00:00Z	2016-09-12T00:00:00Z
57	gateway_flow	autogen			57		2016-09-12T00:00:00Z	2016-09-19T00:00:00Z	2016-09-19T00:00:00Z
64	gateway_flow	autogen			64		2016-09-19T00:00:00Z	2016-09-26T00:00:00Z	2016-09-26T00:00:00Z
61	gateway_flow	autogen			61		2016-09-26T00:00:00Z	2016-10-03T00:00:00Z	2016-10-03T00:00:00Z
55	gateway_flow	autogen			55		2016-10-03T00:00:00Z	2016-10-10T00:00:00Z	2016-10-10T00:00:00Z
65	gateway_flow	autogen			65		2016-10-10T00:00:00Z	2016-10-17T00:00:00Z	2016-10-17T00:00:00Z
56	gateway_flow	autogen			56		2016-10-17T00:00:00Z	2016-10-24T00:00:00Z	2016-10-24T00:00:00Z
63	gateway_flow	autogen			63		2016-10-24T00:00:00Z	2016-10-31T00:00:00Z	2016-10-31T00:00:00Z
59	gateway_flow	autogen			59		2016-10-31T00:00:00Z	2016-11-07T00:00:00Z	2016-11-07T00:00:00Z

I can't seem to replicate the issue when I'm using InfluxCloud DB. When I hit the hosted database, the < query is working great. For my local db though, the < doesn't give the results I'd expect.

I even dropped the DB locally and re-created it, trying to ensure I was at a clean state.

Thanks for any help!

[kostasb]

There have been two bugfixes since 1.0.2 which seem related and may have resolved this:

#7482
#7564

Can you check if this is still an issue with the nightly builds?

https://dl.influxdata.com/influxdb/nightlies/influxdb_nightly_amd64.deb.

[duff]

I'm on a Mac and I used homebrew to install. I'm not sure how to use the nightly build. I see this:

on https://www.influxdata.com/downloads/ but I don't think that works for the homebrew part (since that gets 1.0.2. When I switch back to 1.0.0 locally, the < query fails using the existing data. If I use 1.0.0, then clear our the measurement and repeat adding it all back, the < query works great. This seems to correlate with what you said @kostasb, but I'm not certain if the nightly build will fix it. My testing does seem to indicate that there's an issue with 1.0.2 though.

Is there an good way to switch to a nightly build using homebrew? Or another way on the mac?

Thanks so much!!

[rossmcdonald]

@duff You can download the latest nightly for Mac in tarball form here:

https://dl.influxdata.com/influxdb/nightlies/influxdb-nightly_darwin_amd64.tar.gz

You can extract and manually run the influxd binary (or overwrite the existing influxd binary installed by brew, and restart the service) in order to verify the latest changes.

[duff]

@rossmcdonald Excellent! I just did that. When running this:

✦  ./influxd

 8888888           .d888 888                   8888888b.  888888b.
   888            d88P"  888                   888  "Y88b 888  "88b
   888            888    888                   888    888 888  .88P
   888   88888b.  888888 888 888  888 888  888 888    888 8888888K.
   888   888 "88b 888    888 888  888  Y8bd8P' 888    888 888  "Y88b
   888   888  888 888    888 888  888   X88K   888    888 888    888
   888   888  888 888    888 Y88b 888 .d8""8b. 888  .d88P 888   d88P
 8888888 888  888 888    888  "Y88888 888  888 8888888P"  8888888P"

[run] 2016/11/07 11:23:35 InfluxDB starting, version 1.2.0~n201611070800, branch master, commit 4b85371a71d4382a5e53902d2aac997a0b0733bf
[run] 2016/11/07 11:23:35 Go version go1.7.3, GOMAXPROCS set to 4
[run] 2016/11/07 11:23:35 no configuration provided, using default settings
[store] 2016/11/07 11:23:35 Using data dir: /Users/duff/.influxdb/data
[subscriber] 2016/11/07 11:23:35 opened service
[monitor] 2016/11/07 11:23:35 Starting monitor system
[monitor] 2016/11/07 11:23:35 'build' registered for diagnostics monitoring
[monitor] 2016/11/07 11:23:35 'runtime' registered for diagnostics monitoring
[monitor] 2016/11/07 11:23:35 'network' registered for diagnostics monitoring
[monitor] 2016/11/07 11:23:35 'system' registered for diagnostics monitoring
[shard-precreation] 2016/11/07 11:23:35 Starting precreation service with check interval of 10m0s, advance period of 30m0s

The < query worked great (I would think this means that the points were being placed in the correct shard.)

Thanks so much for your help! Sounds like 1.0.3 would include the fix (whenever it gets released).

[rossmcdonald]

@duff Great! The next release will be v1.1.0, and is due out in the next couple of weeks. It will be available on homebrew when it's released, but, for the meantime, I'd recommend using the nightly.

[duff]

I switched back to 1.0.0 for now since it'll automatically get started by homebrew, etc.

I'm curious what InfluxCloud is using. :)

[jwilder]

Sounds like this is resolved in 1.1rc1. Closing.