influxdata / influxdata/influxdb

Issue with combination of nested queries, zero filling and grouping by tag.

Open
#23,359 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
Rust
Stars
31.7k
Forks
3.7k
Avg merge
13h 37m
Merged PRs (30d)
8

Description

__Steps to reproduce:__

In the influx shell (InfluxDB 1.8.5), here are the steps to reproduce:

Creating test database and measurement:
```
> create database "bugreport"
> use bugreport
Using database bugreport
> INSERT mess,mytag=TCP value=110903335 1643792400000000000
> INSERT mess,mytag=TCP value=94600509 1643793000000000000
> INSERT mess,mytag=TCP value=117927321 1643793600000000000
> INSERT mess,mytag=TCP value=104537794 1643794200000000000
> INSERT mess,mytag=TCP value=94579986 1643794800000000000
> INSERT mess,mytag=ESP value=41860 1643795400000000000
> INSERT mess,mytag=TCP value=90617644 1643795400000000000
```

Now querying the data:
```
select * from mess
name: mess
time mytag value
---- ----- -----
1643792400000000000 TCP 110903335
1643793000000000000 TCP 94600509
1643793600000000000 TCP 117927321
1643794200000000000 TCP 104537794
1643794800000000000 TCP 94579986
1643795400000000000 ESP 41860
1643795400000000000 TCP 90617644
```

So far so good, now lets get more fancy:

```
> SELECT "value", "mytag" FROM (SELECT SUM("value") as "value" FROM "mess" WHERE time >= 1643792400s AND time < 1643796000s GROUP BY time(10m), "mytag" )
name: mess
time value mytag
---- ----- -----
1643792400000000000 110903335 TCP
1643793000000000000 94600509 TCP
1643793600000000000 117927321 TCP
1643794200000000000 104537794 TCP
1643794800000000000 94579986 TCP
1643795400000000000 41860 ESP
1643795400000000000 90617644 TCP
```

Still good, now we want to add zero filling to get those ESP lines for each timestamp as well:

```
> SELECT "value", "mytag" FROM (SELECT SUM("value") as "value" FROM "mess" WHERE time >= 1643792400s AND time < 1643796000s GROUP BY time(10m), "mytag" fill(0))
name: mess
time value mytag
---- ----- -----
1643792400000000000 110903335 TCP
1643793000000000000 94600509 TCP
1643793600000000000 117927321 TCP
1643794200000000000 104537794 TCP
1643794800000000000 94579986 TCP
1643795400000000000 0 TCP
1643792400000000000 0 ESP
1643793000000000000 0 ESP
1643793600000000000 0 ESP
1643794200000000000 0 ESP
1643794800000000000 0 ESP
1643795400000000000 41860 ESP
1643792400000000000 0 TCP
1643793000000000000 0 TCP
1643793600000000000 0 TCP
1643794200000000000 0 TCP
1643794800000000000 0 TCP
1643795400000000000 90617644 TCP
```

**Now we get duplicate entries for TCP, with 0 values, that are identical in time and mytag to already existing lines with data!**

The inner zero fill query alone seems to do what it is supposed to do (first without zero filling):

```
> SELECT SUM("value") as "value" FROM "mess" WHERE time >= 1643792400s AND time < 1643796000s GROUP BY time(10m), "mytag"
name: mess
tags: mytag=ESP
time value
---- -----
1643792400000000000
1643793000000000000
1643793600000000000
1643794200000000000
1643794800000000000
1643795400000000000 41860

name: mess
tags: mytag=TCP
time value
---- -----
1643792400000000000 110903335
1643793000000000000 94600509
1643793600000000000 117927321
1643794200000000000 104537794
1643794800000000000 94579986
1643795400000000000 90617644
```

Now with zero filling:

```
> SELECT SUM("value") as "value" FROM "mess" WHERE time >= 1643792400s AND time < 1643796000s GROUP BY time(10m), "mytag" fill(0)
name: mess
tags: mytag=ESP
time value
---- -----
1643792400000000000 0
1643793000000000000 0
1643793600000000000 0
1643794200000000000 0
1643794800000000000 0
1643795400000000000 41860

name: mess
tags: mytag=TCP
time value
---- -----
1643792400000000000 110903335
1643793000000000000 94600509
1643793600000000000 117927321
1643794200000000000 104537794
1643794800000000000 94579986
1643795400000000000 90617644
```

To reproduce, copy these commands:

```
create database "bugreport"
use bugreport
INSERT mess,mytag=TCP value=110903335 1643792400000000000
INSERT mess,mytag=TCP value=94600509 1643793000000000000
INSERT mess,mytag=TCP value=117927321 1643793600000000000
INSERT mess,mytag=TCP value=104537794 1643794200000000000
INSERT mess,mytag=TCP value=94579986 1643794800000000000
INSERT mess,mytag=ESP value=41860 1643795400000000000
INSERT mess,mytag=TCP value=90617644 1643795400000000000
SELECT "value", "mytag" FROM (SELECT SUM("value") as "value" FROM "mess" WHERE time >= 1643792400s AND time < 1643796000s GROUP BY time(10m), "mytag" fill(0))
```

__Expected behavior:__
The query should not have multiple entries for time + tag combinations, where all but one have zero values.
We have examples where there are even more zero entries generated.

__Actual behavior:__
The query returns multiple entries for the same time + tag combination, where all but one have zero values.

__Environment info:__

* System info: Linux 4.9.0-15-amd64 x86_64
* InfluxDB version: InfluxDB v1.8.5 (git: 1.8 3d16c6318cf5)
* Other relevant environment details: none

__Config:__
```
index-version = "tsi1"
max-series-per-database = 10000000
max-values-per-tag = 2000000
```
plus some cache settings, write timeout, write cold durations, etc.

Contributor guide

Open the contributing guide

Research direction

Reproduce the issue in InfluxDB 1.8.5 using the provided database, inserts, and nested query, then compare the inner grouped query with and without fill(0). Investigate the query behavior that produces duplicate time and mytag combinations in the nested form. Done means the nested query returns one result per time and tag combination without the extra zero-valued rows.

Written by the indexing model from the issue text.

Assessment

Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.