{"action":"created","issue":{"url":"https://api.github.com/repos/influxdata/influxdb/issues/6519","repository_url":"https://api.github.com/repos/influxdata/influxdb","labels_url":"https://api.github.com/repos/influxdata/influxdb/issues/6519/labels{/name}","comments_url":"https://api.github.com/repos/influxdata/influxdb/issues/6519/comments","events_url":"https://api.github.com/repos/influxdata/influxdb/issues/6519/events","html_url":"https://github.com/influxdata/influxdb/issues/6519","id":151966368,"number":6519,"title":"Select a tag key when the tag and field conflict","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},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":11,"created_at":"2016-04-29T20:51:27Z","updated_at":"2016-04-29T22:23:15Z","closed_at":null,"body":"### Problem\r\n\r\nThis is a single issue to discuss #4630.\r\n\r\nWhen points are written to the same measurement where a tag key and a field key conflict with each other, it is impossible to query the tag key with InfluxQL. The following examples (rendered to different measurements to demonstrate these are different examples) leaves the tag not queryable.\r\n\r\n#### Example 1\r\n```\r\nexample1,host=server01 value=2\r\nexample1 host='server02',value=3\r\n```\r\n\r\n#### Example 2\r\n```\r\nexample2,host=server01 host='server02',value=2\r\n```\r\n\r\nThe following queries become ambiguous and cannot be resolved correctly, so they default to selecting the field rather than the tag.\r\n\r\n```\r\nSELECT host, value FROM example1\r\nSELECT host, value FROM example2\r\n```\r\n\r\n### Background\r\n\r\nThere is a bit of a debate on exactly what a tag is. Before talking about what a tag is, here is a list of exactly what capabilities a tag has:\r\n\r\n* They are indexed.\r\n* They are always strings.\r\n* Points that have the same tag keys/values make up a series.\r\n* They are associated with the series, not the individual point.\r\n\r\nSince a tag is associated with the series and not the individual point, tags do _not_ have a time associated with them. This makes it impossible to query for only a tag in a measurement since a select works by finding the time associated with a field and returning the value at that time. Since tags are not associated with points, they also do not have a time and cannot be iterated over. To query a tag with a select, they are wholly dependent on being queried with a field in the same query.\r\n\r\nThere is also no way to differentiate between a field and a tag in InfluxQL at the moment. The search path for how to treat a variable reference is:\r\n\r\n* Check if the measurement (not series) has a field with the value of the key.\r\n* Check if the series has a tag with the value of the key.\r\n* Pretend the cursor is a tag with a blank value.\r\n\r\nBecause of the first step, it makes it impossible to reference a tag once any series in a measurement writes uses a key as a field even if there was previous data with that tag.\r\n\r\n### Possible Solutions\r\n\r\n#### Solution 1\r\n\r\nChange the prioritization so it will read a tag over a field (if present) and restrict points from being written with a conflicting field and tag key as described and implemented in #6410.\r\n\r\nThis solution makes it so example 1 is possible and works correctly when querying so that the first series will return the value of the tag and the second will return the value in the field. It makes example 2 into an error so it becomes impossible to get into this situation to begin with.\r\n\r\nPros:\r\n* Tags become equivalent to indexed fields that are always strings.\r\n* No additional complexity is added to the query language or query engine beyond a small fix to how it creates cursors.\r\n* Makes `SELECT value, host FROM cpu GROUP BY host` use a consistent value (currently it will use the tag for the `GROUP BY` and the field for the actual selection).\r\n\r\nCons:\r\n* Tags only sometimes act as fields. As described earlier, you cannot query a tag by itself. If you do `SELECT host FROM example1` you will get a weird result where it will only return the second point.\r\n* Old data written where the field and the tag were written as part of the same series will still be unreadable, but you won't be able to write any new data.\r\n* Breaking change.\r\n\r\n#### Solution 2\r\n\r\nIntroduce new syntax to specify whether you meant to grab a tag or a field to InfluxQL as described and implemented in #6509 or as proposed in #4823.\r\n\r\nThis solution makes it so both examples are queryable and doesn't require any change to existing data or the point writer, but does require users to use new syntax to reference the correct value.\r\n\r\nPros:\r\n* Backwards compatible.\r\n* Keeps tags and fields as completely separate entities rather than tags being a special type of field.\r\n* May allow us to detect when someone tries to select only a tag as we will be able to notify the user that they must select at least one field. This benefit is only if this new syntax is made required to access tags (not backwards compatible anymore) or if somebody actually uses it.\r\n\r\nCons:\r\n* The implementation is more complex and InfluxQL will have to be modified.\r\n* To maintain backwards compatibility, you still have to allow tags to be referenced with the same variable reference as fields. If you write `host` as a tag and then write it once as a field to one series in the measurement, all of your previous queries will break. It is much more optimal to require that the new syntax is always used for tags, but then we're not backwards compatible anymore.\r\n\r\n##### Proposed Syntax\r\n\r\n###### Solution 1\r\n\r\nPrepend the variable reference with `tag` or `field`.\r\n\r\n```\r\nSELECT tag.host, field.value FROM cpu\r\n```\r\n\r\nThe syntax for this looks pretty ugly in my personal opinion and I dislike that it adds more reserved keywords to a language that already has a lot of them. It becomes impossible to have a key with these names which makes this a backwards incompatible change. We then also might have to add code that strips the front of the variable reference from the ident as the query engine passes around auxiliary fields as a raw string. That means the identifier cannot just be stripped at parse time since we need to send it to the underlying engine which doesn't accept the AST structs as arguments. We can modify the iterator options struct to include references for the auxiliary fields, but this changes the wire protocol for RPC and might make things more complicated with protobuf.\r\n\r\n###### Solution 2\r\n\r\n```\r\nSELECT @host, value FROM cpu\r\n```\r\n\r\nPrepend the input with a special sigil to signify that a tag is being referenced. The syntax for this is common in programming languages like Perl and Ruby. Some programmers may be offput by the syntax and so it might not be desirable (Perl and Ruby's use of sigils is a holy war among programmers). The positive benefit is that it is one character (very easy to check and strip efficiently) and so it is easier to pass around as a string without involving complex AST structures. This is also a backwards compatible change since the `@` symbol is not used like this anywhere else.\r\n\r\nWhat sigil is used specifically is debatable. I would like to keep `$` reserved for future InfluxQL support for a Template node in the AST since I think this will make some things, like Chronograf or Influx Stress, easier to implement using the influxql package. I also avoided `#` since that's a comment character even though I was very tempted to use it because tag and hashtag both reference tags (even if its real name is the pound sign).\r\n\r\n###### Solution 3\r\n\r\n```\r\nSELECT host::tag, value::field FROM cpu\r\n```\r\n\r\nThis syntax alleviates one of the problems from solution 1 because it is less likely to conflict with a real measurement name, but it still may be difficult to strip off the end in an efficient manner when passing it through auxiliary fields. The syntax is supposed to correspond to the casting syntax from PostreSQL, but we're not technically doing a cast, but a selection. There is no equivalent in PostgreSQL to what we're doing because SQL doesn't have two separate types of values. It might be best to reserve this kind of syntax for actual casting (which we may need if fields from different shards have conflicting types and we want to give the users more control over which they select)."},"comment":{"url":"https://api.github.com/repos/influxdata/influxdb/issues/comments/215897168","html_url":"https://github.com/influxdata/influxdb/issues/6519#issuecomment-215897168","issue_url":"https://api.github.com/repos/influxdata/influxdb/issues/6519","id":215897168,"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-04-29T22:23:15Z","updated_at":"2016-04-29T22:23:15Z","body":"The sigil syntax is much easier to parse than the others so it can be embedded within the string without requiring any kind of special parser. The `::` syntax requires looking at the entire string rather than just the first character."}}