[v0.9-rc30&31][prebuilt] No results when testing against a boolean field in WHERE clause

[renan-strauss]

Hi,

I'm quite a newcomer to InfluxDB, I mean I've been playing around with it and Grafana for the past couple of days. I started with v0.8.8 on Ubuntu 14.04 (64-bits) which worked just fine, but then I switched to 0.9-rc30 and encountered this strange behavior:

I have a serie holding data on each request made to the server, and it has a field called "exception". It's a boolean indicating if there was an error during the request (typically http 500's). I need this information so I tried to retrieve all the requests that resulted into an error (25% in my testing data) :

select * from log where exception = true;

But this query (through the web interface) returns field names, but no values (it's the same with false or <> operator).
Whereas this one works just fine :

select * from log where request_took > 1000;

(returns a dozen of points for my data)

I tried to use a string tag set to 'T' or 'F' and it worked but that's just not the right way of doing it...
I first noticed this behavior on 0.9-rc30 but it seems to be just the same in rc31.
Thanks for your time,

Renan

[beckettsean]

@renan- I don't believe boolean is a supported datatype. Most likely you are either storing the integer 1 or the string true depending on how you are writing the data.

Can you provide a sample of your writes?

What are the results of select * from log where exception = "true" or select * from log where exception = 1?

[beckettsean]

Turns out boolean is supported in the datastore but it looks like none of the operators handle them properly. So, although the underlying data has the right value for the boolean, you can't match those values with foo = 1 or foo = true.

To reproduce, post this point:

{
"database":"mydb",
"retentionPolicy":"default",
"points":[
     {
    "name":"bool",
    "fields":
         {"value":132.0,
        "boolean-true-nostring":true,
        "boolean-true-string":"true",
        "boolean-true-1":1,
        "boolean-false-0":0,
        "boolean-false-nostring":false,
        "boolean-false-string":"false"}
    }]
}

select * from bool gives

> select * from bool
{
    "results": [
        {
            "series": [
                {
                    "name": "bool",
                    "columns": [
                        "time",
                        "boolean-false-0",
                        "boolean-false-nostring",
                        "boolean-false-string",
                        "boolean-true-1",
                        "boolean-true-nostring",
                        "boolean-true-string",
                        "value"
                    ],
                    "values": [
                        [
                            "2015-05-22T22:07:49.683654443Z",
                            0,
                            false,
                            "false",
                            1,
                            true,
                            "true",
                            132
                        ]
                    ]
                }
            ]
        }
    ]
}

We can see that the nostring values are stored as booleans. However, there's no way to query the booleans. All of the following returned no points:

> select * from bool where "boolean-true-nostring" = true

> select * from bool where "boolean-true-nostring" = false

> select * from bool where "boolean-true-nostring" = 'true'

> select * from bool where "boolean-true-nostring" = 1

> select * from bool where "boolean-true-nostring" is true
ERR: error parsing query: found TRUE, expected SELECT, DELETE, SHOW, CREATE, DROP, GRANT, REVOKE, ALTER, SET at line 1, char 53

> select * from bool where "boolean-false-nostring" = 0

> select * from bool where "boolean-false-nostring" = false

> select * from bool where "boolean-false-nostring" = 'false'

> select * from bool where "boolean-false-nostring" <> true

[beckettsean]

@renan- thanks for the bug report, we've got a repro case now so it should get fixed fairly quickly.

[renan-strauss]

Thank you for the quick reply !
Glad I was able to help you out guys, keep up the good work ;)