Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
kostasb opened this Issue Feb 14, 2017 · 4 comments

Comments

Projects
None yet
2 participants
@kostasb
Copy link

kostasb commented Feb 14, 2017

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

This comment has been minimized.

Copy link
Contributor

jwilder commented Feb 14, 2017

The time filter is not quite right:

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

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

@kostasb

This comment has been minimized.

Copy link
Author

kostasb commented Feb 14, 2017

@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

This comment has been minimized.

Copy link
Contributor

jwilder commented Feb 14, 2017

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

@kostasb

This comment has been minimized.

Copy link
Author

kostasb commented Feb 14, 2017

@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'
>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.