`null as zero` not working? Making sense of graphs with gaps (missing data points) in stacked graphs

Here’s a simplified postgres query of what kind of results we get. As you can see, the data has gaps, but that’s fine since each time period doesn’t have every event.

select *
from ( VALUES
	(timestamp '2020-01-01 00:01', 'a', 2),
	('2020-01-01 00:01', 'b', 1),
	('2020-01-01 00:02', 'a', 2),
	('2020-01-01 00:03', 'a', 2),
	('2020-01-01 00:03', 'b', 1),
	('2020-01-01 00:03', 'c', 1),
	('2020-01-01 00:04', 'a', 2)
) t("time", "metric", "value");

The problem comes in when we graph it using Grafana (stacked):

Some examples of the errors:

  • The point at :02 should be 2, since a is the only one in that time-group, but it’s being displayed as 3.

  • The hover legend for that point is showing a total of 4, which is also incorrect.

Changing Null value between null, null as zero, and connected doesn’t seem to do anything. They all seem to behave like connected. It even seems to backfill, showing c before it’s first value.

I would expect null as zero to act like a zerofill for the values. Am I wrong in that assumption?

Here’s what we would like it to look like (emulating zerofill using the DB, but this can be tricky depending on the query):

select *
from ( VALUES
	(timestamp '2020-01-01 00:01', 'a', 2),
	('2020-01-01 00:01', 'b', 1),
	('2020-01-01 00:01', 'c', 0),
	('2020-01-01 00:02', 'a', 2),
	('2020-01-01 00:02', 'b', 0),
	('2020-01-01 00:02', 'c', 0),
	('2020-01-01 00:03', 'a', 2),
	('2020-01-01 00:03', 'b', 1),
	('2020-01-01 00:03', 'c', 1),
	('2020-01-01 00:04', 'a', 2),
	('2020-01-01 00:04', 'b', 0),
	('2020-01-01 00:04', 'c', 0)
) s("time", "metric", "value");

Here’s a real-world example oh how it looks (most of those time-groups the values are 0 but they are incorrectly being connected as having a 1):

At time 00:49, for example, there is only one event, so the value should be 1 and not 9!

Changing it to bars makes even less sense with them floating (I realize this is to line them up with the lines, but when the lines are off they should “fall down”, no?):

Even when there is just one, stacking is turned off, and null as zero is selected, it still connects them:

Filling in the missing data with the previous value maybe a good workaround depending on what you are wanting to show from graphing the data. I you are using an SQL data source, you might want to look at doing something like this to fill in the gaps.

I’m also affected by this issue.
Here is my example, i parsed CSV Data and the Timestamp is a litte bit different.
This issue affects, if i upgrade from Grafana 7.3 to 7.4 or later.
On Grafana 7.3 i used a mySQL statement with “ORDER BY READING, TIMESTAMP” and on the newer Version i need to remove the “Reading” from Order method. because there is an error message :

failed to convert long to wide series when converting from dataframe: long series must be sorted ascending by time to be converted

Grafana 7.3 View:

Grafana 8.0 View:

And the DATA Image:

I testet a litte bit more and it looks like that somethings in the core is buggy. Because the Error is addicted to “Stack Series” and “Connected null Values”