Error: fields not supported in WHERE clause during deletion

[nargiza-sarkulova]

Bug report

System info:
OS: Ubuntu 16.10
InfluxDB 1.3.1 & 1.3.2 (pre-built package)

Steps to reproduce:

1. curl -i http://localhost:8086/query -u root:root --data-urlencode "q=CREATE DATABASE mydb"
2. curl -i -XPOST 'http://localhost:8086/write?db=mydb' -u root:root --data-binary 'temperature,instrument_id=1 value=25 1422568543702900257'
3. DELETE FROM temperature WHERE instrument_id='1' AND time > '2015-01-29 20:00:00'

ERR: shard 3483: fields not supported in WHERE clause during deletion

Expected behavior:
Record is deleted.

Actual behavior:
The above error.

Additional info:
If I omit either tag constraint or time constraint it works. This exact query used to work before I upgraded InfluxDB to newer version.

[e-dard]

Hi @nargiza-sarkulova,

Can you double check your repro steps? I followed your steps with 1.3.2 but was unable to reproduce. The delete didn't complain at all.

[nargiza-sarkulova]

Hi @e-dard,

I tried again with the same result. I also tried to turn off new engine tsi1 as enabling auth and that are only changes I made to config file. I don't know what else could affect us having different outputs. Any suggestions?

[e-dard]

@nargiza-sarkulova that you were using tsi1 was the key piece of information. I have reproduced this. We'll look into this as a priority. Thanks for the report!

[e-dard]

@nargiza-sarkulova FYI - you can get around this issue by using the inmem index rather than tsi1.

[nargiza-sarkulova]

@e-dard Sorry for not being clear about using tsi1. I'll use inmem for now. Thanks for looking into it!

[derrley]

Can you guys confirm that you fixed this issue only for DELETE but not for DROP? Is it correct behavior that DROP SERIES WHERE tag=value should not work with tsi1?

[e-dard]

@derrley what release version are you using? It should work AFAIK. However in master there are some significant ongoing changes to how deletes/drops will work with tsi1.

[derrley]

@e-dard we got it working! query bug.

[evandhoffman]

I'm using influxdb to record sensors and every once in a while I get insane values, like that my house was using 1 Megawatt:

> SELECT value FROM "W" WHERE ("entity_id" = 'aeon_labs_dsb09104_home_energy_meter_power') AND time >= now() - 90d and value > 10000
name: W
time                value
----                -----
1599497993733863168 1784973.88
1599738202845696000 10190.08
1599889031122046976 270312.47
1600483882653166080 16199.12
1600617908573088000 125444
1600732342241799168 269368.59
1601211620259433984 10432.4
1601357917691514880 309650.59
1601370489087589120 327976.59
1601484772769915136 124249.6
1602111697960894976 8345656
>

These values throw off all my charts by scaling them insanely and also ruin max and avg reports. Since there's no delete from X where value > Y what's the recommended workaround for this? I'd be fine setting these to null, but I don't want to do them one by one.

[ahpigsy]

InfluxDB shell version: 1.8.10

[ahpigsy]

use WEATHER
Using database WEATHER
delete from tank where tank_litres = 114923.375 and time = '2023-02-17T14:38:05.660375186Z'
ERR: shard 413: fields not supported in WHERE clause during deletion

[ahpigsy]

What's wrong with my query?