Different query results with single and double quotes in InfluxDB queries
I was getting different results for seemingly identical queries in
influx CLI and
in my Go code. The problem appeared to be a difference between single and double quote
handling in the InfluxDB query language.
The below query returns an error since
subscription without any quotes is
actually an InfluxDB keyword. Fair enough – I received an error!
SELECT * FROM azureCosts WHERE subscription = 'Datahub' AND costsReport = 'total' AND time = 1580860800000000000 LIMIT 1 ERR: error parsing query: found SUBSCRIPTION, expected identifier, string, number, bool at line 1, char 32
This one, single-quoting all tag names and string values returns an empty result set, but there’s no error:
SELECT * FROM azureCosts WHERE 'subscription' = 'Datahub' AND 'costsReport' = 'total' AND 'time' = 1580860800000000000 LIMIT 1
The next one, double-quoting
subscription and the string values also returns an
empty result set:
SELECT * FROM azureCosts WHERE "subscription" = "Datahub" AND costsReport = "total" AND time = 1580860800000000000 LIMIT 1
While this last one worked as I intended:
SELECT * FROM azureCosts WHERE "subscription" = 'main' AND costsReport = 'total' AND time = 1580860800000000000 LIMIT 1
The above query describes: get me the first row
subscription tag equals the string value
costReport tag equals
the string value
time is equal to the given timestamp, specified in nanoseconds.
After I finally figured it out, I knew what to search for. A blog post from InfluxData pointed me on to the InfluxDB v1.7 FAQ, stating the following:
Single quote string values (for example, tag values) but do not single quote identifiers (database names, retention policy names, user names, measurement names, tag keys, and field keys).
Double quote identifiers if they start with a digit, contain characters other than [A-z,0-9,_], or if they are an InfluxQL keyword. Double quotes are not required for identifiers if they don’t fall into one of those categories but we recommend double quoting them anyway.
You can see incoming queries in the
influxdb systemd unit logs as they come in
# export DB=my_database # journalctl -fu influxdb | grep --line-buffered $DB | grep -oP 'query=\K.+' "SELECT * FROM cloudcost_dev.autogen.azureCosts WHERE \"subscription\" = 'Datahub' AND costsReport = 'total'"