Influxdb: InfluxDB delete with predicate works for _measurement, but not for specific _field

[snenkov]

__Steps to reproduce:__
List the minimal actions needed to reproduce the behavior.

Here is the code I use (in python) to try and delete a specific _field:

def delete_data(data: str, org: str, bucket: str, token: str, db_url=INFLUX_URL) -> str:
    url = f'{db_url}/api/v2/delete?org={org}&bucket={bucket}'
    headers = {
        'Authorization': f'Token {token}',
        'Content-type': 'application/json'
    }
    response = requests.post(url, json=data, headers=headers)
    response.raise_for_status()
    return response

data = {
    "start": "2020-12-01T00:00:00Z",
    "stop": "2021-12-20T00:00:00Z",
    "predicate": "_field=\"my_field\""
}
r = delete_data(data=data, org=ORG, bucket='my_bucket', token=MY_TOKEN)

I also tried with curl - same story:

curl --request POST "<INFLUX_URL>/api/v2/delete/?org=<org>&bucket=<bucket>" -H 'Authorization: Token <MY_TOKEN>'   -H 'Content-Type: application/json'   --data '{
    "start": "2020-12-01T00:00:00Z",
    "stop": "2020-12-20T00:00:00Z",
    "predicate": "_field=\"my_field\""
  }'

This does not delete the _field.
The same code, works just fine with _measurement

__Expected behavior:__
I expect the data to be deleted.

__Actual behavior:__
Nothing.

__Environment info:__

• System info: Linux 4.15.0-128-generic x86_64
• InfluxDB version: I am running it through docker: quay.io/influxdb/influxdb:v2.0.3 (when I do influxd version it does not show the docker instance)
• Other relevant environment details: Container runtime is up 7 days.

[danxmoran]

@snenkov can you provide some extra info to help me set up a realistic reproduction? Ideally:

• Example line-protocol inputs that would be affected by your delete call (ideally with multiple field=value pairs in their field-sets)
• Expected output of querying the bucket after executing the delete on the inputs

[snenkov]

Here is some example data:
2020-12-22-21-42_chronograf_data.csv.zip

One the delete is executed, there should be no _field of close_price (so each row with this field should be gone)

[danxmoran]

(Disclaimer: not an InfluxQL expert)

I think delete-with-predicate might be running into trouble because the InfluxQL engine pivots _field values into column names, so a search for the _field tag finds nothing. i.e. you wouldn't write this query in V1:

select * from price_volume_1_min where _field = "close_price"

Instead, you'd probably write:

select "close_price" from price_volume_1_min

I'm not sure of the best way to extend support for deleting via _field. SQL has 2 separate APIs for these use-cases (DELTE WHERE vs. DROP COLUMN); we might want to take a simliar route, or else rearrange our query logic so the filters run before data is pivoted.

[snenkov]

Thanks @danxmoran. This confirms the bug then.
So, what are the options to delete it now? Is there work which needs to be done on the backend, or some loophole that we can use for now?

[danxmoran]

I'm honestly not sure. I'll raise it internally, but I'm not sure how much attention it'll get until after the holiday season since so many people are on vacation.

[snenkov]

Okay cool. Thanks. Yeah now is kind of a laid-back time. :)
It’s important to have it fixed at some point. Otherwise we can’t really delete data :/

[danxmoran]

6150 is the (ancient) parallel from the 1.x line

[snenkov]

I cannot believe this does not exist. It's quite crazy : / Thanks for linking to the #6150 . I hope it gets resolved soon.

[ski2day]

Thanks to Dan for pointing me here.

This is a major problem. Not only is a broken feature documented as something that works, some people (such as myself) are totally blocked from the most basic database functionality; deleting data points in a time series. I am blocked.

Since I picked up the 2.x version, I have not yet once successfully deleted any individual data points. This feature is long overdue for a released product.