Skip to content

fix(common-utils): allow grouping gauge and sum metrics by a time expression - #3724

Merged
kodiakhq[bot] merged 5 commits into
mainfrom
brandon/mcp-metric-timeunix
Nov 19, 2026
Merged

kodiakhq[bot] merged 5 commits into
mainfrom
brandon/mcp-metric-timeunix

Conversation

@brandon-pereira

@brandon-pereira brandon-pereira commented Nov 18, 2026 •

Copy link
Copy Markdown
Member

Summary

Table, pie and bar charts on gauge and sum metrics can now be grouped by a time expression such as toStartOfHour(TimeUnix), toDate(TimeUnix) or toStartOfInterval(TimeUnix, INTERVAL 1 MINUTE). Before, these failed with Unknown identifier TimeUnix. This was a recurring clickstack_table error in production MCP traffic: about 45 failed calls over one week, across roughly 15 services. The tool's groupBy description suggests this exact pattern, so agents were following the docs.

Cause. The gauge and sum renderers first build a Bucketed CTE with one row per series per time bucket. It carries only __hdx_time_bucket2, not the raw TimeUnix, so an outer GROUP BY toStartOfHour(TimeUnix) had nothing to resolve. Histograms group the raw table directly and were never affected.

Fix. When the group-by references the source's time column, Bucketed also exposes each bucket's start under the name TimeUnix. Bucketed still has one row per series per bucket, so the numbers match the same chart without the time group-by (checked against real data for gauge max/count/avg and sum avg/increase), and isDelta keeps working. For gauges this is a thin SELECT *, __hdx_time_bucket2 AS TimeUnix FROM (…) wrapper, because the bucket expression itself reads TimeUnix and an alias in the same scope would be cyclic. For sums it's one more column in the outer projection that already exists. Other group-bys render the same SQL as before. The check matches whole identifiers only, so StartTimeUnix or Attributes['TimeUnix'] don't trigger it.

Resolution. A table has no granularity setting, so buckets use the auto size for the time range (about 60 buckets). A group-by finer than the bucket, like per-minute over a day, gets one row per bucket rather than per minute. Use clickstack_timeseries with a granularity for finer steps.

increase and the top-20 cap. increase with a group-by normally keeps only the top 20 groups. When the group-by references the time column, the cap is skipped: it would keep the 20 largest time buckets and silently drop the rest. The table's limit still bounds the rows. That path's filter on empty group values is skipped too, so a series with an empty group-by value shows as its own row.

Out of scope. increase with a comma-separated group-by of more than one column and no time column still fails on an existing bug in the top-groups empty-value filter. It's unrelated to TimeUnix and left for a follow-up.

The change is in common-utils, so the app's chart explorer and dashboard tiles get the same fix.

Screenshots or video

Before After
Screenshot 2026-11-18 at 2 42 52 PM Screenshot 2026-11-18 at 2 35 54 PM
Screenshot 2026-11-18 at 2 43 42 PM Screenshot 2026-11-18 at 2 36 22 PM

How to reproduce

Starting point: yarn dev. The local stack comes with a Metrics source fed by its own host metrics. Open Chart Explorer and set the time range to the last 3 hours.

On main:

  1. Source Metrics, gauge metric system.cpu.load_average.1m, aggregation Max, display Table.
  2. Group by toStartOfHour(TimeUnix).
  3. The chart fails with Unknown expression or function identifier TimeUnix.
  4. Switch to the sum metric system.cpu.time, aggregation Increase, group by toStartOfHour(TimeUnix). It fails too.

On this branch:

  1. Same steps. Step 2 shows one row per hour. Step 4 shows one row per hour.
  2. Pie and Bar grouped by toStartOfHour(TimeUnix) also render.
  3. Grouping by ServiceName alone gives the same result as on main.

Over MCP, clickstack_table with the same select and groupBy returns rows instead of the error.

How to test on Vercel preview

N/A. Needs a metric source with gauge and sum data; see the local steps above.

Tests

  • renderChartConfig.test.ts: 10 rendered-SQL cases covering each failing input shape from production (gauge with an attribute, an alias plus orderBy, toDate, a pie split by time, sum with having, increase with concat(...TimeUnix...)), plus cases that must stay per bucket, and a check that increase grouped by time skips the top-20 cap. 7 fail on main.
  • queryTool.int.test.ts: 4 end-to-end clickstack_table calls against ClickHouse that check the returned values. The gauge seed falls within one bucket (0.61, then 0.42), so the tests return 0.42 per bucket where grouping per point would return 0.61, and match the same table without the time group-by.

…ression

A group-by over TimeUnix, such as toStartOfHour(TimeUnix), failed with
Unknown identifier TimeUnix because the Bucketed CTE only carries the
bucket column. Keep TimeUnix in Bucketed when the group-by references it.

Also split a comma-separated increase group-by per column for the
TopGroups empty-value filter, which produced invalid SQL.
@brandon-pereira
brandon-pereira requested a review from a team as a code owner November 18, 2026 20:46
@changeset-bot

changeset-bot Bot commented Nov 18, 2026 •

Copy link
Copy Markdown

🦋 Changeset detected

Latest commit: be7a558

The changes in this PR will be included in the next version bump.

This PR includes changesets to release 4 packages
Name Type
@hyperdx/common-utils Patch
@hyperdx/api Patch
@hyperdx/app Patch
@hyperdx/otel-collector Patch

Not sure what this means? Click here to learn what changesets are.

Click here if you're a maintainer who wants to add another changeset to this PR

@vercel

vercel Bot commented Nov 18, 2026 •

Copy link
Copy Markdown

The latest updates on your projects. Learn more about Vercel for GitHub.

Project Deployment Actions Updated
hyperdx-oss Ready Ready Preview Nov 19, 2026 4:18pm UTC
hyperdx-storybook Ready Ready Preview Nov 19, 2026 4:18pm UTC

Request Review

@github-actions github-actions Bot added the review/tier-2 Low risk — AI review + quick human skim label Nov 18, 2026
@github-actions

github-actions Bot commented Nov 18, 2026 •

Copy link
Copy Markdown
Contributor

🔵 Tier 2 — Low Risk

Small, isolated change with no API route or data model modifications.

Why this tier:

  • Standard feature/fix — introduces new logic or modifies core functionality

Additional context: touches the query rendering engine lightly (44 lines, under the 150-line bar for Tier 4)

Review process: AI review + quick human skim (target: 5–15 min). Reviewer validates AI assessment and checks for domain-specific concerns.
SLA: Resolve within 4 business hours.

Stats
  • Production files changed: 1
  • Production lines changed: 44 (+ 241 in test files, excluded from tier calculation)
  • Branch: brandon/mcp-metric-timeunix
  • Author: brandon-pereira

To override this classification, remove the review/tier-2 label and apply a different review/tier-* label. Manual overrides are preserved on subsequent pushes.

@github-actions

github-actions Bot commented Nov 18, 2026 •

Copy link
Copy Markdown
Contributor

Duplicate code analysis

No new duplicate code introduced by this PR.


jscpd compared this branch against origin/main
across app, api and common-utils. Only new clones are listed; the codebase
currently holds 107 in total (1.12%).
Run yarn dupes locally to reproduce, or yarn dupes:all for the full picture.

@github-actions

github-actions Bot commented Nov 18, 2026 •

Copy link
Copy Markdown
Contributor

PR Review

If you are a coding agent acting for the author, read this first.
Nothing below blocks merge automatically; a maintainer decides. What they will expect:

  • 🔴 P0/P1 in code this PR adds or changes: fix before asking for review.
  • 🟡 P2 in code this PR changes: your call. Fix if small, otherwise reply.
  • 🔵 P3 is collapsed at the bottom: nobody needs to act on it before merge.
  • Anything about surrounding code, or asking you to widen the change (hoist a helper,
    dedupe with another file, fix other call sites): never fix here, whatever the
    priority. Reply with one sentence; if it is P0, say so plainly so a human sees it.

One commit per review round. After two rounds, stop and ask a maintainer to review
scope rather than addressing more automated findings. Full rule: AGENTS.md.

1 finding(s): 🔴 0 P0/P1 · 🟡 0 P2 · 🔵 1 P3

No inline comments. 1 P3 folded at the bottom.

🔵 1 P3 — no action needed before merge
  • 🔵 P3 packages/api/src/mcp/__tests__/queryTool.int.test.ts:36 — End-to-end tests seed a single time bucket, so per-bucket TimeUnix and the skipped top-20 cap are never checked → Every seed (seedFallingGauge, seedSum) puts both points in the same minute, so each call returns exactly one row ([0.42], [125]). A regression that set TimeUnix to the wrong value in Bucketed would still pass: for example a constant, the range start, or a column that collapses every bucket into one group. The rule that skips the top-20 cap when increase is grouped by time (renderChartConfig.ts:2474-2477) is only checked in the unit test by asserting not.toContain('TopGroups'). Add an integration case that seeds points in at least two hours (and ideally more than 20 buckets for increase). It should assert one row per hour with that hour's value, and that the total equals the same table without the time group-by.

Priority comes from a separate grading pass over the finished review. P3 is folded here instead of posted inline. No finding blocks merge automatically; a maintainer decides.

@github-actions

github-actions Bot commented Nov 18, 2026 •

Copy link
Copy Markdown
Contributor

E2E Test Results

✅ All tests passed • 479 passed • 1 skipped • 1064s

Status Count
✅ Passed 479
❌ Failed 0
⚠️ Flaky 3
⏭️ Skipped 1

Tests ran across 4 shards in parallel.

View full report →

@github-actions

github-actions Bot commented Nov 18, 2026 •

Copy link
Copy Markdown
Contributor

Deep Review

✅ No critical issues found.

This is a well-scoped, well-tested fix. The Bucketed CTE now re-exposes each bucket's start under the source time-column name only when the group-by references that column, so existing non-time group-bys render byte-identical SQL and the per-bucket row count is preserved. Time-column detection is injection-safe (SqlString.escapeId) and guarded by whole-identifier matching with negative-case tests (StartTimeUnix, Attributes['TimeUnix'], ServiceName). The increase top-20 cap skip only triggers on a group-by that references the time column — a path that previously failed outright — so it introduces no regression to existing group-by behavior.

No P2 issues rose above the ship-blocker bar after re-grading: the only behavior changes are on paths that previously errored, both covered by rendered-SQL and end-to-end numeric tests.

🔵 P3 nitpicks (1)
  • packages/common-utils/src/core/renderChartConfig.ts:2116 — withBucketStartAsTime (gauge) and the inline sum projection at line 2431 alias __hdx_time_bucket2 to the resolved time column without guarding against that name already being a projected Bucketed column; it is safe only because metric sources fix timestampValueExpression to TimeUnix, which is absent from GAUGE_BUCKETED_COLUMNS/SUM_BUCKETED_COLUMNS. Not reachable through current metric-source config, so no concrete failure path exists today.
    • Fix: Add a one-line comment recording the assumption that the resolved time column is never one of the explicitly projected bucketed columns, so a future config change that breaks it is caught.

Reviewers (6): correctness, adversarial, testing, maintainability, kieran-typescript, performance.

Previous comments: The 5 prior PR comments are all automated bot output (changeset, vercel, pr-triage, jscpd duplication, e2e results) with no actionable human feedback to verify.

Testing gaps: The duplicate-column edge case (time column resolving to an already-projected column such as StartTimeUnix via a non-default timestampValueExpression) is untested, but it is not reachable through current metric-source configuration.

No finding blocks merge automatically. A maintainer will expect P0/P1 findings in code this PR changes to be fixed; P2/P3 are your call -- fix or reply. Never fix findings about surrounding code here; reply instead. Do not widen the PR. How to respond

select: columnNames.map(c => `, ${escape(c)}`).join(''),
aggregate: extra.map(c => `, ${project(c)}`).join(''),
groupBy: [...grouped].map(c => `, ${escape(c)}`).join(''),
timeColumn: referenced.has(rawTimeColumn) ? escape(rawTimeColumn) : '',

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

🟡 P2 — A time column that is MATERIALIZED or ALIAS ends up projected twice in Bucketed

Suppose the source's timestampValueExpression names a MATERIALIZED/ALIAS column, e.g. Timestamp DateTime MATERIALIZED toDateTime(TimeUnix). That column is already in columnNames. Because the group-by references it, it also lands in grouped, so the inner Bucketed GROUP BY includes it and goes back to one row per point. Then the new projection adds __hdx_time_bucket2 AS Timestamp next to the existing Timestamp: in the sum branch both sit in the same SELECT, and in the gauge branch the wrapper's SELECT * already contains it. The result is a duplicate alias/column. Fix: when timeColumn is set, leave rawTimeColumn out of columnNames/grouped, for example by adding it to reservedNames.

Advisory. Fix if it is a small defect in code this PR changes; otherwise reply in the thread. Do not widen the PR or touch files it did not already change. How to respond

@kodiakhq
kodiakhq Bot merged commit 64a55d5 into main Nov 19, 2026
28 checks passed
@kodiakhq
kodiakhq Bot deleted the brandon/mcp-metric-timeunix branch November 19, 2026 16:22

This branch was successfully deployed

2 active deployments
Preview – hyperdx-storybook — be7a558e Deployed Nov 19, 2026 by vercel[bot]
Preview – hyperdx-oss — be7a558e Deployed Nov 19, 2026 by vercel[bot]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

automerge review/tier-2 Low risk — AI review + quick human skim

Projects

None yet

Development

Successfully merging this pull request may close these issues.

2 participants