queries with strings that look like dates end up with date types, not string types

[beckettsean]

Bug report

System info: [Include InfluxDB version, operating system name, and other relevant details]

InfluxDB 0.13.0 from package on Ubuntu 14.04

The query engine interprets date-like strings in the WHERE clause into dates, even when they should stay strings. This renders string comparison operators non-functional and leads to confusing query returns.

Test data

> insert strdate date="abcd"
> insert strdate date="1999-12-31"
> insert strdate date="1437-12-31"
> insert strdate date="4999-12-31"
> select * from strdate where date = 'abcd'
name: strdate
-------------
time            date
1464208209720484384 abcd

> select * from strdate where date = 'abcde'
> select * from strdate where date = 'abc'

So far, everything is behaving as expected, but when we use a string that looks like a date, we end up with spurious matches we didn't expect:

> select * from strdate where date = '2016-05-24'
name: strdate
-------------
time            date
1464208209720484384 abcd
1464208219668422100 1999-12-31
1464208225444538777 1437-12-31
1464208232317831217 4999-12-31

Submitting a date-like string that won't parse as a date shows the error very clearly:

> select * from strdate where date = '2016-05-241'
ERR: error parsing query: unable to parse datetime at line 1, char 35

Even if the actual field values don't look like dates, if the query filter string does, it shows the behavior:

> insert strdate str="stringystringystring"
> insert strdate str="2222stringystringystring"
> insert strdate str="qwertyuiop"
> select * from strdate where str = '1999-01-01'
name: strdate
-------------
time            date        str
1464208209720484384 abcd        
1464208219668422100 1999-12-31  
1464208225444538777 1437-12-31  
1464208232317831217 4999-12-31  
1464208338499313827         stringystringystring
1464208342804155057         2222stringystringystring
1464208352756546883         qwertyuiop

> 

This is not a CLI bug, it's in the query engine:

# curl -i 'http://localhost:8086/query?db=mydb&pretty=true' --data-urlencode "q=select * from strdate where date <> '1687-01-02'"
HTTP/1.1 200 OK
Connection: close
Content-Type: application/json
Request-Id: 4d16890b-22b7-11e6-afa0-000000000000
X-Influxdb-Version: 0.13.0
Date: Wed, 25 May 2016 20:28:54 GMT
Content-Length: 966

{
    "results": [
        {
            "series": [
                {
                    "name": "strdate",
                    "columns": [
                        "time",
                        "date",
                        "datetime"
                    ],
                    "values": [
                        [
                            "2016-05-25T20:24:51.549732921Z",
                            "2015-01-01",
                            "2013-02-02 01:23:45"
                        ],
                        [
                            "2016-05-25T20:25:03.896266879Z",
                            "2012-01-01",
                            "2013-02-02 01:23:45"
                        ],
                        [
                            "2016-05-25T20:25:22.874580532Z",
                            "abc",
                            "defg"
                        ]
                    ]
                }
            ]
        }
    ]
}


# curl -i 'http://localhost:8086/query?db=mydb&pretty=true' --data-urlencode "q=select * from strdate where date <> '1687-01-0221'"
HTTP/1.1 400 Bad Request
Content-Type: application/json
Request-Id: 4ff52213-22b7-11e6-afa1-000000000000
X-Influxdb-Version: 0.13.0
Date: Wed, 25 May 2016 20:28:59 GMT
Content-Length: 83

{
    "error": "error parsing query: unable to parse datetime at line 1, char 36"
}

[beckettsean]

From https://groups.google.com/d/msgid/influxdb/1c0fbaeb-4c73-4e0a-af04-6bf96f581739%40googlegroups.com

[beckettsean]

@jsternberg @benbjohnson Another probable query bug. This one I'm pretty sure isn't operator error, but if so I welcome the education!

[vaibhavdobriyal]

Thanks for support @beckettsean
Yes, this is not just for CLI; we were also able to replicate it via API.

Another observation: I tried appending date like fields with additional information like "1999-01-01" was appended with "1999-01-01a" still the behaviour is erroneous. This also happens for long format like "1999-01-01 12:34:00".

[joelegasse]

This is a result of our over-eager parser trying to assign a date/time type before it has the context of where that value is in the query...

I hacked a quick delayed-evaluation that allows strings to be used, and then parsed as timestamps only if there are compared against time:

> select * from test where time > '2016-01-01'
name: test
----------
time                    x
1464274406239861557     abcd
1464274413208741313     2015-01-01
1464274415222743659     2015-01-02
1464274416884363729     2015-01-03

> select * from test where x='2015-01-01'
name: test
----------
time                    x
1464274413208741313     2015-01-01

> select * from test where x<>'2015-01-01'
name: test
----------
time                    x
1464274406239861557     abcd
1464274415222743659     2015-01-02
1464274416884363729     2015-01-03

It probably broke another part of the system, but I'll try to get it cleaned up for a PR