Bug: Accelerated queries using `quantilesState`

[Defman]

Generates the following accelerated query, however it uses quantilesMerge instead of quantileMerge which returns an array with a single element instead of a scalar. Using quantilesMerge might be preferred such to accelerate multiple overlapping series.

SELECT
  quantilesMerge (0.99) (quantiles_duration),
  SpanName,
  toStartOfInterval(toDateTime(Timestamp), INTERVAL 1 minute) AS `__hdx_time_bucket`
FROM
  otel_staging.traces_aggregated
WHERE
  (
    Timestamp >= fromUnixTimestamp64Milli(1776928440000)
    AND Timestamp < fromUnixTimestamp64Milli(1776932100000)
  )
GROUP BY
  SpanName,
  toStartOfInterval(toDateTime(Timestamp), INTERVAL 1 minute) AS `__hdx_time_bucket`
ORDER BY
  toStartOfInterval(toDateTime(Timestamp), INTERVAL 1 minute) AS `__hdx_time_bucket`
LIMIT
  100000

Which results in the following error

Error loading chart, please check your query or try again later.

Error Message:

No value columns found in result column metadata. Make sure a numeric column exists in the result set.

Result column metadata: [{"name":"quantilesMerge(0.99)(quantiles_duration)","type":"Array(Float64)"},{"name":"SpanName","type":"LowCardinality(String)"},{"name":"__hdx_time_bucket","type":"DateTime"}]

[pulpdrew]

Hey @Defman thanks for the report! I believe the issue is probably that your MV is using quantiles instead of quantile, and HyperDX is inferring that it should use quantiles based on the schema. Could you share your MV and table schema to confirm?

If that's the case, we can update HyperDX to use quantileMerge instead of quantilesMerge.

As a workaround for now: if you define your MV with quantileState instead of quantilesState, that should work. A single quantileState(n) column in the MV can support queries with quantileMerge(0.5), quantileState(0.9), etc

Example schema:

CREATE TABLE otel_traces_1m
(
    `Timestamp` DateTime,
    `ServiceName` LowCardinality(String),
    `StatusCode` LowCardinality(String),
    `count` SimpleAggregateFunction(sum, UInt64),
    `avg__Duration` AggregateFunction(avg, UInt64),
    `max__Duration` SimpleAggregateFunction(max, Int64),
    `quantile__Duration` AggregateFunction(quantile(0.95), UInt64)
)
ENGINE = AggregatingMergeTree
ORDER BY (Timestamp, ServiceName, StatusCode);

CREATE MATERIALIZED VIEW otel_traces_1m_mv TO otel_traces_1m
AS
SELECT
    toStartOfMinute(Timestamp) AS Timestamp,
    ServiceName,
    StatusCode,
    count() AS count,
    avgState(Duration) AS avg__Duration,
    maxSimpleState(Duration) AS max__Duration,
    quantileState(0.95)(Duration) AS quantile__Duration
FROM otel_traces
GROUP BY
    Timestamp,
    ServiceName,
    StatusCode;

[Defman]

I had the exact same thought and tested it - it does work. So I initially assumed quantilesState simply wasn't supported, but then I read the documentation which actually uses quantilesState.

For reference, here's the aggregated table I'm using:

CREATE TABLE IF NOT EXISTS `{database}`.`traces_aggregated` (
    `Timestamp`             DateTime                                                            CODEC(Delta, ZSTD(1)),
    `ServiceName`           LowCardinality(String)                                              CODEC(ZSTD(1)),
    `SpanName`              LowCardinality(String)                                              CODEC(ZSTD(1)),
    `StatusCode`            LowCardinality(String)                                              CODEC(ZSTD(1)),
    `quantiles_duration`    AggregateFunction(quantiles(0.75, 0.9, 0.95, 0.99, 0.999), UInt64),
    `traces`                AggregateFunction(count)
) ENGINE = AggregatingMergeTree
PARTITION BY toDate(Timestamp)
ORDER BY (ServiceName, SpanName, StatusCode, Timestamp)
TTL toDate(Timestamp) + {ttl}
SETTINGS index_granularity = 8192