[0.9.5-nightly] COUNT() not working with field in WHERE clause #4701

ivanscattergood · 2015-11-07T03:11:44Z

Hi,

I have been using the influxdb-0.9.5_nightly_9633410-1.x86_64 for the last week to record data and the count aggregate function is not working as expected. I have just upgraded to 0.9.5-nightly-ef84e33 and the problem remains. I have one measurement metric with one field value.

I have several tags (e.g. Type & "Managed Entity"). If I do a query to show the values added in the last day for value I get a list. (limited to 1 for brevity here):

> Select  value from metric WHERE Type = 'Event'  AND TIME > now() - 1d limit 1
name: metric
------------
time                    value
1446806868301000000     101

HOWEVER if I do a count then I get null:

 > Select  count(value) from metric WHERE Type = 'Event'  AND TIME > now() - 1d
name: metric
------------
time                    count
1446779243868660021

The text was updated successfully, but these errors were encountered:

beckettsean · 2015-11-09T19:19:24Z

@benbjohnson related to the QE refactor?

benbjohnson · 2015-11-09T19:43:51Z

@beckettsean The QE refactor is still in a branch so it won't affect anything coming out of master.

otoolep · 2015-11-09T22:57:55Z

@ivanscattergood -- do you see this issue with 0.9.4.2?

ivanscattergood · 2015-11-10T07:11:55Z

It works fine in 0.9.4.2

---- otoolep wrote ----

@ivanscattergoodhttps://github.com/ivanscattergood -- do you see this issue with 0.9.4.2?

Reply to this email directly or view it on GitHubhttps://github.com//issues/4701#issuecomment-155225753.

xv-tom-l · 2015-11-12T01:44:00Z

I upgraded to 0.9.5 nightly yesterday, and I got exactly same error.

corylanou · 2015-11-12T17:23:34Z

I am unable to replicate this.

> create database mydb
> insert metric,Type=Event value=101
> Select  value from metric WHERE Type = 'Event'  AND TIME > now() - 1d limit 1
name: metric
------------
time                    value
1447348895184066731     101

>  Select  count(value) from metric WHERE Type = 'Event'  AND TIME > now() - 1d
name: metric
------------
time                    count
1447262513434782993     1

Can you show me a select * from metric so I can see all fields/tags? Fields and tags are case sensitive, is that causing the problem?

beckettsean · 2015-11-12T19:04:26Z

Using populated database (_internal), cannot repro on 0.9.5-nightly-905437c

> select autoFlush from wal where hostname='sean-0.9.5' and nodeID='1' and time > now() - 1d limit 1
name: wal
---------
time            autoFlush
1447268610000000000 159274

> select count(autoFlush) from wal where hostname='sean-0.9.5' and nodeID='1' and time > now() - 1d
name: wal
---------
time            count
1447268577578500563 58697

beckettsean · 2015-11-12T19:07:29Z

Also cannot repro with latest

InfluxDB shell 0.9.5-nightly-ebb9172
> use _internal
Using database _internal
> select autoFlush from wal where hostname='sean-0.9.5' and nodeID='1' and time > now() - 1d limit 1
name: wal
---------
time            autoFlush
1447268770000000000 156511

> select count(autoFlush) from wal where hostname='sean-0.9.5' and nodeID='1' and time > now() - 1d
name: wal
---------
time            count
1447268764714990027 58674

@ivanscattergood can you give SHOW TAG KEYS FROM metric and SHOW FIELD KEYS FROM metric?

@xvtom can you give any output to show the issue happening for you, too? It would really help to have more than one repro case to attempt.

ivanscattergood · 2015-11-12T22:41:10Z

Here’s the output requested:


> SHOW TAG KEYS FROM metric
name: metric
------------
tagKey
BU
Category
Column name
Component
Data Unit Type
Data View
Datacenter
Environment
Gateway
HARole
Location
Managed Entity
Probe
Row name
Sampler
Subcomponent
Variable name


name: metric
------------
fieldKey
Error count
Ok count
Severity id
Type
Warning count
value

beckettsean · 2015-11-12T23:56:52Z

Thanks for the output, @ivanscattergood. I notice there's a field in the WHERE clause of your examples, I was testing with tags in the WHERE clause.

beckettsean · 2015-11-13T00:02:17Z

Yep, it's the field in the WHERE clause that's causing the issue:

InfluxDB shell 0.9.5-nightly-ebb9172
> use _internal
Using database _internal

# COUNT without field works fine:

> select count(memSize) from wal where time > now() - 10m
name: wal
---------
time            count
1447372252444579785 124

# COUNT with field returns nothing:

> select count(memSize) from wal where metaFlush = 19 and time > now() - 10m
name: wal
---------
time            count
1447372256395823973 

# Data matches the selection criteria from the broken COUNT query:

> select memSize from wal where metaFlush = 19 and time > now() - 10m
name: wal
---------
time            memSize
1447372506000000000 0
1447372516000000000 0
1447372526000000000 5556
1447372536000000000 0
1447372546000000000 0
1447372556000000000 5556
1447372566000000000 0
1447372576000000000 0
1447372586000000000 5556
1447372596000000000 0
1447372606000000000 0
1447372616000000000 5556
1447372626000000000 0
1447372636000000000 0
1447372646000000000 5556
1447372656000000000 0
1447372666000000000 0
1447372676000000000 5556
1447372686000000000 0
1447372696000000000 0
1447372706000000000 5556
1447372716000000000 0
1447372726000000000 0
1447372736000000000 5556
1447372746000000000 0
1447372756000000000 0
1447372766000000000 5556
1447372776000000000 2778
1447372786000000000 0
1447372796000000000 5556
1447372806000000000 2778
1447372816000000000 0
1447372826000000000 0
1447372836000000000 2778
1447372846000000000 0
1447372856000000000 0
1447372866000000000 2778

beckettsean · 2015-11-13T00:06:57Z

Does not exist in 0.9.4.2:

# 0.9.5 output

> insert four701 value=1,foo=2
> insert four701 value=1,foo=3
> insert four701 value=1,foo=2
> insert four701 value=1,foo=3
> insert four701 value=1,foo=2
> insert four701 value=1,foo=3
> select count(value) from four701
name: four701
-------------
time    count
0   6

> select count(value) from four701 where foo=2
name: four701
-------------
time    count
0   


# 0.9.4 output:

> insert four701 value=1,foo=2
> insert four701 value=1,foo=3
> insert four701 value=1,foo=2
> insert four701 value=1,foo=3
> insert four701 value=1,foo=2
> insert four701 value=1,foo=3
> select count(value) from four701
name: four701
-------------
time            count
1970-01-01T00:00:00Z    6

> select count(value) from four701 where foo=2
name: four701
-------------
time            count
1970-01-01T00:00:00Z    3

otoolep · 2015-11-13T02:49:14Z

@ivanscattergood -- does adding fill fix your issue? Like so:

select value from metric WHERE Type = 'Event' AND TIME > now() - 1d fill(0) limit 1

Fix issue #4701.

otoolep · 2015-11-13T03:14:55Z

We believe this issue is now fixed in master and will be in 0.9.5-rc2.

Fix issue #4701.

beckettsean · 2015-11-13T18:21:25Z

Not fixed, still incorrectly reports zero when there are matching points:

> select count(value) from four701
name: four701
-------------
time    count
0   6

> select * from four701 where foo = 3
name: four701
-------------
time            foo value
1447438449694435399 3   1
1447438454838871141 3   1
1447438455382336680 3   1

> select value from four701 where foo = 3
name: four701
-------------
time            value
1447438449694435399 1
1447438454838871141 1
1447438455382336680 1

> select count(value) from four701 where foo = 3
name: four701
-------------
time    count
0   0

>

The last value should be 3, not zero. It's incorrectly reporting no matching points, but the previous query shows there are matching points, as long as there's no COUNT() function applied.

otoolep · 2015-11-13T18:24:15Z

OK, looks like I might have fixed the wrong bug. Taking a look now.

otoolep · 2015-11-13T22:42:40Z

I believe this is fixed by #4789

This change ensures that if there are any fields in the WHERE clause of an aggregate that are different from the fields in the SELECT clause, that the cursors also decode those fields. Otherwise WHERE clauses of the form 'SELECT f(w) FROM x WHERE y=z' will return incorrect results Fixes issue #4701.

This change ensures that if there are any fields in the WHERE clause of an aggregate that are different from the fields in the SELECT clause, that the cursors also decode those fields. Otherwise WHERE clauses of the form 'SELECT f(w) FROM x WHERE y=z' will return incorrect results Fixes issue #4701. Conflicts: CHANGELOG.md

otoolep · 2015-11-14T02:08:16Z

Fixed and cherry-picked to 0.9.5.

beckettsean changed the title ~~Count not working in 0.9.5-nightly~~ [0.9.5-nightly] COUNT() not working Nov 9, 2015

beckettsean added the area/queries label Nov 9, 2015

beckettsean added the support label Nov 9, 2015

ivanscattergood mentioned this issue Nov 10, 2015

Query: disable the persistence of the WAL #4734

Closed

beckettsean changed the title ~~[0.9.5-nightly] COUNT() not working~~ [0.9.5-nightly] COUNT() not working with field in WHERE clause Nov 13, 2015

otoolep added a commit that referenced this issue Nov 13, 2015

If no points to count, count is 0

f889ac1

Fix issue #4701.

otoolep mentioned this issue Nov 13, 2015

If no points to count, count is 0 #4778

Merged

otoolep added a commit that referenced this issue Nov 13, 2015

If no points to count, count is 0

d9b54e9

Fix issue #4701.

otoolep closed this as completed Nov 13, 2015

ivanscattergood mentioned this issue Nov 13, 2015

[0.9.5-nightly] installs influxdb-0.9.6_nightly_664b20e-1.x86_64 #4782

Closed

beckettsean reopened this Nov 13, 2015

otoolep mentioned this issue Nov 13, 2015

WHERE fields must be decoded during aggregates #4789

Merged

otoolep closed this as completed Nov 14, 2015

corylanou added this to the 0.9.5 milestone Nov 16, 2015

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

[0.9.5-nightly] COUNT() not working with field in WHERE clause #4701

[0.9.5-nightly] COUNT() not working with field in WHERE clause #4701

ivanscattergood commented Nov 7, 2015

beckettsean commented Nov 9, 2015

benbjohnson commented Nov 9, 2015

otoolep commented Nov 9, 2015

ivanscattergood commented Nov 10, 2015

xv-tom-l commented Nov 12, 2015

corylanou commented Nov 12, 2015

beckettsean commented Nov 12, 2015

beckettsean commented Nov 12, 2015

ivanscattergood commented Nov 12, 2015

beckettsean commented Nov 12, 2015

beckettsean commented Nov 13, 2015

beckettsean commented Nov 13, 2015

otoolep commented Nov 13, 2015

otoolep commented Nov 13, 2015

beckettsean commented Nov 13, 2015

otoolep commented Nov 13, 2015

otoolep commented Nov 13, 2015

otoolep commented Nov 14, 2015

[0.9.5-nightly] COUNT() not working with field in WHERE clause #4701

[0.9.5-nightly] COUNT() not working with field in WHERE clause #4701

Comments

ivanscattergood commented Nov 7, 2015

beckettsean commented Nov 9, 2015

benbjohnson commented Nov 9, 2015

otoolep commented Nov 9, 2015

ivanscattergood commented Nov 10, 2015

xv-tom-l commented Nov 12, 2015

corylanou commented Nov 12, 2015

beckettsean commented Nov 12, 2015

beckettsean commented Nov 12, 2015

ivanscattergood commented Nov 12, 2015

beckettsean commented Nov 12, 2015

beckettsean commented Nov 13, 2015

beckettsean commented Nov 13, 2015

otoolep commented Nov 13, 2015

otoolep commented Nov 13, 2015

beckettsean commented Nov 13, 2015

otoolep commented Nov 13, 2015

otoolep commented Nov 13, 2015

otoolep commented Nov 14, 2015