[feature request] SHOW TAG VALUES should respect fields in the WHERE clause

[beckettsean]

Or it should throw a parser error, one of the two. Right now it appears to parse but has no effect on the output:

> insert foobar,key=val1 value=1
> insert foobar,key=val1 value=2
> insert foobar,key=val1 value=3
> insert foobar,key=val2 value=3
> insert foobar,key=val2 value=4
> insert foobar,key=val2 value=5
> select * from foobar
name: foobar
tags: key=val1
time                value
----                -----
2015-06-18T19:09:52.947299286Z  1
2015-06-18T19:10:01.619062526Z  2
2015-06-18T19:10:03.171388731Z  3

name: foobar
tags: key=val2
time                value
----                -----
2015-06-18T19:10:06.339544273Z  3
2015-06-18T19:10:07.611584869Z  4
2015-06-18T19:10:08.763866774Z  5

> show tag values from foobar with key = "key"
name: keyTagValues
------------------
key
val1
val2

Everything works as expected up to this point. Now add a WHERE clause to the SHOW TAG VALUES query:

> show tag values from foobar with key = "key" where value > 3
name: keyTagValues
------------------
key
val1
val2

It's returning both keys, even though value > 3 should exclude val1 if it is being respected. If the WHERE clause is not being respected, why does it parse?

[otoolep]

Probably one I should fix, as I rework the query engine.

[beckettsean]

@otoolep Are you planning to have the parser throw an error or try to make the WHERE clause actually function? The latter would require pulling in all points in the series, which seems very expensive for little gain, but this is also assumed to be an infrequent query. Maybe we require a time clause in the WHERE, like with an aggregation?

[beckettsean]

Short-term we should just throw a parser error on the WHERE clause with SHOW TAG VALUES and related queries.

[qunpp1]

I am experiencing the same issue. I would like to be able to filter tag values using time and other tags that share a measurement (if this is possible).

e.g. "SHOW TAG VALUES FROM measurement_name WHERE other_measurement_tag = x AND time < y"

If I am understanding the information noted in this issue above, we are not able to do either at this point. Is the intent to eventually allow for querying tag values with filtering based on related tags and/or time? Seems like it might be useful in certain instances...

[qunpp1]

Tested on 0.9.2. This issue appears to be resolved!

[beckettsean]

Confirmed fixed in 0.9.2, but only for WHERE clauses with tags. Doesn't work for WHERE clauses with fields (which makes sense, that necessitates a full scan), but there should be better error handling:

This all makes sense:

> select * from foobar
name: foobar
tags: bar=val1, foo=a
time                value
----                -----
2015-07-30T23:43:33.334538964Z  1
2015-07-30T23:43:33.338640423Z  2
2015-07-30T23:43:33.34152599Z   3

name: foobar
tags: bar=val2, foo=
time                value
----                -----
2015-07-30T23:43:33.343811278Z  3
2015-07-30T23:43:33.346062061Z  4
2015-07-30T23:43:34.178269307Z  5

> show tag values with key = bar
name: barTagValues
------------------
bar
val1
val2

> show tag values with key = bar where foo = 'a'
name: barTagValues
------------------
bar
val1

All good to this point, but WHERE with a field restriction fails, and without any errors:

> show tag values with key = bar where value > 3
name: barTagValues
------------------
bar
val1
val2

> show tag values with key = bar where value = 1
name: barTagValues
------------------
bar
val1
val2

> show tag values with key = bar where value = 12
name: barTagValues
------------------
bar
val1
val2

Even garbage passes the parser, returning no results, as long as there's something in the WHERE clause.

> show tag values with key = bar where 
ERR: error parsing query: found EOF, expected identifier, string, number, bool at line 1, char 38
> show tag values with key = bar where sakdjhfksajhdf
> show tag values with key = bar where sakdjhfksajhdf
> show tag values with key = bar where sakdjhfksajhdf = 1
> show tag values with key = bar where sakdjhfksajhdf = 'aksdhka'
> 

[lexh]

Should this respect a WHERE clause involving time? I'm a bit confused by the semantics of SHOW vs. SELECT. Intuitively, I want to fetch all of the tag values from a measurement subject to some constraints (i.e. WHERE clauses). But I want one of those restraints to be time. E.g. something like: SHOW TAG VALUES FROM anomaly_detection WITH key=type WHERE metric='some_metric' AND anomalous='True' AND time > now() - 6h. Alas, this query gives me the same results as if I had not supplied the final WHERE clause, that is to say, it gives me all results irrespective of time. This query does exactly what I expect it to: SHOW TAG VALUES FROM anomaly_detection WITH key=type WHERE metric='some_metric' AND anomalous='True'. Is there a way to also filter that result set by time? I am running InfluxDB v0.9.3.

[beckettsean]

@lexh You can mostly get what you want with an actual SELECT query:

SELECT value, type FROM anomaly_detection WHERE metric='some_metric' AND anomalous='True' AND time > now() - 6h

You must include at least one field in the SELECT clause, but you can ignore that in the return. You will get all values for type including duplicates. DISTINCT doesn't operate on tags yet (https://github.com/influxdb/influxdb/issues/3880).

If you do want the SHOW queries to respect time boundaries in the WHERE clause, please open a new ticket for that. I've updated the title on this one to be clearer.

[lexh]

Ah, I see. Thanks for the suggestion, that will work for me for the time being. I will ruminate on this and possibly open a new issue once I've organized my thoughts.

[aheusingfeld]

Is there any status update of this? I'd like to use something like SHOW TAG VALUES FROM "heap.usage" WITH KEY IN ("instance") where env = "prelive" and time > now() - 1m in a Grafana template to have a dropdown list of the instances - without the where clause I get quite a long list. ;)

[beckettsean]

@aheusingfeld this feature request is currently unscheduled.

[aheusingfeld]

@beckettsean hm, that's strange because this query SHOW TAG VALUES FROM "heap.usage" WITH KEY IN ("instance") WHERE time > now() - 1m and profile = '$profile' seems to work great with my production influxdb 0.9.2.1 (modified version of https://registry.hub.docker.com/u/tutum/influxdb) and grafana 2.1.0! I tested multiple things and it only works with single quotes and only if 'time' is the first argument in the where clause.

[beckettsean]

@aheusingfeld are you sure the results are scoped by time? There are some SHOW queries that still parse and return when given a WHERE time clause, but they were ignoring the WHERE time clause and returning results as if it were not there at all.

Also, this feature request is about using fields in the WHERE clause, not tags. If profile is a tag it is expected that your query would work, although again, the time portion of the WHERE clause is being ignored.

[pasancario]

I'm also a Grafana user, and i want to create a Server Resource Dashboard, to monitor also Processes from a given Server. I want to template it as much as possible and working with a query like that: SHOW TAG VALUES FROM "procstat_memory_vms" WITH KEY = "name" where host = "web-server-01"

will be usefull to have all processes being monitored for a given host. Then, it can be templated to expand a graph for each process.

Do you have some deadline for this feature?

[alfredocambera]

This feature would make dashboards even more powerful on grafana

[toddiuszho]

This actually worked in v0.10, but broke in v0.11.1. The WHERE clause on SHOW TAG VALUES still works with =~ operator, but fails with !~ operator.

[jsternberg]

I'm closing this since I don't think it's feasible for us to do the original request with allowing comparison against fields for SHOW TAG VALUES. SHOW TAG VALUES can only perform operations on tags because it never actually touches the database, just the schema.

But, you can use WHERE on tag values which is also mentioned in this issue. The != and !~ operators were broken and fixed by #6607.

[timhallinflux]

Dup of: https://github.com/influxdata/influxdb/issues/5668 https://github.com/influxdata/influxdb/issues/4029