First Bucket in Histograms is Always 0 in 2.9.0

[sgarfinkel]

It looks like 2.9.0 introduced a pretty bad regression here

No matter the interval or granularity the first bucket is always 0. Explicitly passing dateRange: undefined in the chart config fixes this for reasons I don't fully understand.

[sgarfinkel]

I suspect the bug is here 991bd7e

[knudtty]

I feel like this has always been the case, much to my chagrin. Regardless, we should try and fix it

[sgarfinkel]

It’s really quite a mystery because Clickhouse returns the correct data per time slice. My guess is that there’s a disconnect between first and last bucket and the rendered chunk but I haven’t had a chance to confirm. I think the +- logic to set the time range correctly doesn’t work consistently and often times it will not properly get a bucket on either end due to rounding or something.

[pulpdrew]

Thanks for raising this @sgarfinkel. I think @knudtty is correct, and this is an existing issue related to #1331.

In metrics queries, the query aligns the data to the start of each time bucket (eg. start of each minute) but the first time bucket may start prior to the selected time range. So in your example, assuming 1 minute granularity, any data in the 8:28-8:29 bucket would be aligned to 8:28:00. This is prior to the 8:28:48 start of your time range, so the entire bucket is filtered out of the query results.

WITH Source AS (
  SELECT
    *,
    cityHash64(mapConcat(ScopeAttributes, ResourceAttributes, Attributes)) AS AttributesHash
  FROM otel_metrics_gauge
  WHERE (TimeUnix >= fromUnixTimestamp64Milli(1765536744000) AND TimeUnix <= fromUnixTimestamp64Milli(1765540344000)) AND ((MetricName = '...'))
),
Bucketed AS (
  SELECT
    toStartOfInterval(toDateTime(TimeUnix), INTERVAL 1 minute) AS `__hdx_time_bucket2`,
    AttributesHash,
    last_value(Value) AS LastValue,
    ...
  FROM Source
  GROUP BY AttributesHash, __hdx_time_bucket2
  ORDER BY AttributesHash, __hdx_time_bucket2
) 
SELECT count(),toStartOfInterval(toDateTime(__hdx_time_bucket2), INTERVAL 1 minute) AS `__hdx_time_bucket`
FROM Bucketed

-- Here is where we filter out the first bucket
WHERE (__hdx_time_bucket2 >= fromUnixTimestamp64Milli(1765536744000) AND __hdx_time_bucket2 <= fromUnixTimestamp64Milli(1765540344000))
GROUP BY toStartOfInterval(toDateTime(__hdx_time_bucket2), INTERVAL 1 minute) AS `__hdx_time_bucket`
ORDER BY toStartOfInterval(toDateTime(__hdx_time_bucket2), INTERVAL 1 minute) AS `__hdx_time_bucket`

If you align the start of your time range to the start of a time bucket (this depends on your granularity), you should typically see a data point for the first bucket, if there are metric data points available for that time bucket.

[sgarfinkel]

Why doesn’t the outer WHERE clause just use the same toStartOfInterval as the bucketing to address that? I’d personally prefer to show the previous slice as the incomplete bucket when possible.

[pulpdrew]

Why doesn’t the outer WHERE clause just use the same toStartOfInterval as the bucketing to address that? I’d personally prefer to show the previous slice as the incomplete bucket when possible.

I think that would be a good solution for metrics (this particular cause of the issue only applies to metrics)

[sgarfinkel]

@pulpdrew I think I stumbled on a solution here. The feature exists to power other metrics, but I assume it's safe to reuse includedDataInterval from the cteChartConfig.includedDataInterval? If that gets passed to the top-level chart config it replaces the explicit bounds with the interval-based bounds.

[sgarfinkel]

Should be added here: https://github.com/hyperdxio/hyperdx/blob/main/packages/common-utils/src/core/renderChartConfig.ts#L1308

The only downside is the bounds of the charts are now selected dateRange +/- one bucket of your interval but I feel like that's worth it? Unless clickhouse can interpolate between the buckets there's no alternative option really...

[sgarfinkel]

Actually definitely not, I think that adds an extra bucket and depending on aggregation that bucket could be massive (if it’s cumulative but needs to be delta).

[pulpdrew]

I think one solution here would be to apply the toStartOf bucketing function to the outer where clause's start date (for metric queries), so that the first bucket from the inner query is not filtered out (WHERE (__hdx_time_bucket2 >= toStartOfInterval(fromUnixTimestamp64Milli(1765536744000), 1 MINUTE) AND __hdx_time_bucket2 <= fromUnixTimestamp64Milli(1765540344000)).

I think this resolves this particular issue, but leaves the one mentioned in #1331 - the first bucket may still be empty or reduced because it is not a full bucket due to the inner query date range. But that could be resolved as suggested in #1331.