DELETE FROM does not work with the "WHERE time" clause

[kostasb]

Tested on v1.2.0

Description: DELETE FROM does not work well with the WHERE time clause.

Tested it with past, future and finite duration clauses in the following repro case.

In the docs it is implied that it works.

Repro steps:

> use my-db
Using database my-db
> insert device_data,device=ecc38a200186,sensor=2 value=1
> insert device_data,device=ecc38a200186,sensor=2 value=1 1513965600000000000

> select * from device_data
name: device_data
time                           device       sensor value
----                           ------       ------ -----
2017-02-14T17:25:33.361198355Z ecc38a200186 2      1
2017-12-22T18:00:00Z           ecc38a200186 2      1


> delete from device_data where device='ecc38a200186' and time() < "2017-02-14T17:31:00Z"
> select * from device_data
name: device_data
time                           device       sensor value
----                           ------       ------ -----
2017-02-14T17:25:33.361198355Z ecc38a200186 2      1
2017-12-22T18:00:00Z           ecc38a200186 2      1


> delete from device_data where device='ecc38a200186' and time() < "2017-12-29T00:00:00Z"

> select * from device_data
name: device_data
time                           device       sensor value
----                           ------       ------ -----
2017-02-14T17:25:33.361198355Z ecc38a200186 2      1
2017-12-22T18:00:00Z           ecc38a200186 2      1

> delete from device_data where device='ecc38a200186' and time() > "2017-01-01T00:00:00Z" and time() < "2018-12-29T00:00:00Z"

> select * from device_data
name: device_data
time                           device       sensor value
----                           ------       ------ -----
2017-02-14T17:25:33.361198355Z ecc38a200186 2      1
2017-12-22T18:00:00Z           ecc38a200186 2      1

Also using delete from with now() returns an error:

> delete from device_data where device='ecc38a200186' and time() > now()
ERR: invalid expression: time() > '2017-02-14T17:34:48.417769344Z'
> delete from device_data where device='ecc38a200186' and time() < now()
ERR: invalid expression: time() < '2017-02-14T17:40:14.225458287Z'

Without a time range delete from works up to now():

> delete from device_data where device='ecc38a200186'

> select * from device_data
name: device_data
time                 device       sensor value
----                 ------       ------ -----
2017-12-22T18:00:00Z ecc38a200186 2      1

> 

Show shards:

name: my-db
id database retention_policy shard_group start_time           end_time             expiry_time          owners
-- -------- ---------------- ----------- ----------           --------             -----------          ------
76 my-db    autogen          76          2017-02-13T00:00:00Z 2017-02-20T00:00:00Z 2017-02-20T00:00:00Z 
75 my-db    autogen          75          2017-12-18T00:00:00Z 2017-12-25T00:00:00Z 2017-12-25T00:00:00Z 

Probably related to:
#7706
#7798

[jwilder]

The time filter is not quite right:

Use time <= '2017-12-29T00:00:00Z'.

You have time() and also uses double-quotes.

[kostasb]

@jwilder double quotes is the only syntax that doesn't err in this statement, see:

> delete from device_data where "device" = 'ecc38a200186' and time() <= '2018-02-14T17:31:00Z'
ERR: invalid expression: time() < '2018-02-14T17:31:00Z'

> delete from device_data where "device" = 'ecc38a200186' and time() <= '1513965600000000000'
ERR: invalid expression: time() <= '1513965600000000000'

> delete from device_data where "device" = 'ecc38a200186' and time() <= "1513965600000000000"
>

[jwilder]

@kostasb Switch time() to time as well. No parens.

[kostasb]

@jwilder Sounds like I am having a bad day.

So, this works and it does delete points:

> delete from device_data where "device" = 'ecc38a200186' and time < '2018-02-14T17:31:00Z'
>