influxdata / influxdata/influxdb
where clause with or doesn't work when both a time and a non-time filter is used
- Dominant language
- Rust
- Stars
- 31.7k
- Forks
- 3.7k
- Avg merge
- 13h 37m
- Merged PRs (30d)
- 8
Description
__Steps to reproduce:__
List the minimal actions needed to reproduce the behavior.
1. `insert test,fooTag=0 value=0`
2. `insert test,fooTag=0 value=1`
3. `select * from test where time=1605046096208082180 or "value"=0` (replace 1605046096208082180 with the time of the second insertion)
__Expected behavior:__
I should get the same result as `select * from test where "value"=0`. It's a OR operator so if either filter is true then I should get results.
```
> select * from test where "value"=0
name: test
time fooTag value
---- ------ -----
1605046002738391427 0 0
```
__Actual behavior:__
No results
__Environment info:__
* System info: Ubuntu 18
* InfluxDB version: InfluxDB v1.8.3 (git: 1.8 563e6c3d1a7a2790763c6289501095dbec19244e)
__Config:__
Copy any non-default config values here or attach the full config as a gist or file.
```
[meta]
dir = "/var/lib/influxdb/meta"
[data]
dir = "/var/lib/influxdb/data"
wal-dir = "/var/lib/influxdb/wal"
[[collectd]]
enabled = true
bind-address = "0.0.0.0:25826"
```
Another person has ran into the same issue: https://stackoverflow.com/questions/62812878/influxdb-returns-no-results-when-adding-logical-or-condition
A interesting behavior I found is that if both or clauses are true then the query works correctly:
```
> select * from test where time=1605046002738391427 or "value"='0'
name: test
time fooTag value
---- ------ -----
1605046002738391427 0 0
```
The query fails only when the time clause doesn't return any results
```
> select * from test where time=1605046002738391428 or "value"='0'
```
Reversing the order doesn't help:
```
> select * from test where "value"='0' or time=1605046002738391428
```
Neither does using a tag:
```
select * from test where "fooTag"='0' or time=1605046002738391428
```
Contributor guide
Research direction
Start by reproducing the reported SELECT queries against InfluxDB v1.8.3, comparing an OR between a time predicate and a value or tag predicate with cases where both predicates match. No source file or test is named; done means the query returns rows when either predicate is true, including when the time predicate matches nothing.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Clearly specified
- Newbie friendliness
- 30/100