Log and trace charts read the whole attribute map for GROUP BY and aggregates, even when a materialized column exists #3539

Summary

When a log or trace table has a materialized column for a map key, e.g. __otel_materialized_k8s.pod.name MATERIALIZED ResourceAttributes['k8s.pod.name'], HyperDX rewrites a WHERE on ResourceAttributes['k8s.pod.name'] to use the column. The same expression in groupBy, or inside an aggregate such as count_distinct, still reads the whole ResourceAttributes map.

Grouping logs or spans by a k8s attribute (pod, namespace, deployment, cluster) is one of the most common chart, search and MCP queries, so it pays the map cost every time. On one hour of logs, grouping by the map read 5.5× more bytes and took 10× longer than grouping by the materialized column.

Environment

• HyperDX main at d08b2fa (api 2.43.1, common-utils 0.33.0)
• ClickHouse 26.10.3.7; otel_logs / otel_traces with __otel_materialized_k8s.* columns

Steps to reproduce

reproMaterializedGroupBy.test.ts (below; copy to packages/common-utils/src/__tests__/) mocks a lookup ResourceAttributes['k8s.pod.name'] → __otel_materialized_k8s.pod.name and renders a logs chart with count_distinct of the pod, a where on the pod, and a group-by on the pod:

SELECT COUNT(DISTINCT ResourceAttributes['k8s.pod.name']), ResourceAttributes['k8s.pod.name']
FROM otel.otel_logs
WHERE (...) AND (__otel_materialized_k8s.pod.name LIKE 'api-%')        -- rewritten
GROUP BY ResourceAttributes['k8s.pod.name']                            -- not rewritten

Code: packages/common-utils/src/core/renderChartConfig.ts

• line 725: renderSelectList returns a string list (the usual form of groupBy) as raw SQL before the materialized lookup at line 735, so a string groupBy is never rewritten (renderGroupBy, line 1518);
• lines 280–339: fastifySQL handles column_ref, binary_expr and function nodes, but node-sql-parser parses count, sum, min, max, avg as aggr_func, which falls to default. So those select items are not rewritten, while uniq(...) (parsed as function) is.

Expected vs actual

Expected: GROUP BY ResourceAttributes['k8s.pod.name'] and count_distinct(ResourceAttributes['k8s.pod.name']) use __otel_materialized_k8s.pod.name, as the WHERE does.

Measured on one hour of logs:

Proposed fix

1. Apply the same materialized-column rewrite to groupBy (string and array forms) and to orderBy.
2. Extend the fastifySQL traversal to aggr_func nodes, so aggregate select items are rewritten too.

Happy to send a PR for both.

Also affects SQL metric charts (secondary)

Metric charts skip the lookup entirely: at line 738, configs with subquery CTEs (every metric chart, through the Source/Bucketed CTEs) don't get the rewrite, so a metric chart grouped by ResourceAttributes['k8s.pod.name'] keeps the map in every branch. The Bucketed CTE already projects the materialized columns (#3191), so the outer query could use them. This is a separate code path and could be its own change.

Related

• API keeps stale table columns until restart, so new MATERIALIZED columns are ignored (metadata cache has no TTL) #3475 / fix(common-utils): expire cached table columns after 5 minutes #3481 (stale column cache, fixed)
• fix: allow grouping gauge and sum metric charts by materialized columns #3191 (grouping metric charts by materialized columns)
• feat(common-utils): apply direct_read KV items optimization to SQL filters #2405 (KV items optimization for SQL filters)

// Copy to packages/common-utils/src/__tests__/ and run: npx jest src/__tests__/reproMaterializedGroupBy.test.ts
import { parameterizedQueryToSql } from '@/clickhouse';
import { Metadata } from '@/core/metadata';
import { renderChartConfig } from '@/core/renderChartConfig';
import { ChartConfigWithOptDateRange } from '@/types';

const MAT = new Map([["ResourceAttributes['k8s.pod.name']", '__otel_materialized_k8s.pod.name']]);

const metadata = {
  getColumns: jest.fn().mockResolvedValue([]),
  getMaterializedColumnsLookupTable: jest.fn().mockResolvedValue(MAT),
  getColumn: jest.fn().mockImplementation(async ({ column }) =>
    ['Timestamp', 'ServiceName', 'Body', 'ResourceAttributes', 'TimeUnix', 'Value', 'MetricName'].includes(column)
      ? { name: column, type: column === 'ResourceAttributes' ? 'Map(LowCardinality(String), String)' : 'String' }
      : undefined,
  ),
  getTableMetadata: jest.fn().mockResolvedValue({ primary_key: 'Timestamp' }),
  getSkipIndices: jest.fn().mockResolvedValue([]),
  getSetting: jest.fn().mockResolvedValue(undefined),
  isClickHouseCloud: jest.fn().mockResolvedValue(false),
} as unknown as Metadata;

const range: [Date, Date] = [new Date('2026-11-04T00:00:00Z'), new Date('2026-11-04T06:00:00Z')];

it('logs: WHERE uses the materialized column, GROUP BY and SELECT read the map', async () => {
  const config: ChartConfigWithOptDateRange = {
    connection: 'c',
    from: { databaseName: 'otel', tableName: 'otel_logs' },
    select: [{ aggFn: 'count_distinct', valueExpression: "ResourceAttributes['k8s.pod.name']" }],
    where: "ResourceAttributes['k8s.pod.name'] LIKE 'api-%'",
    whereLanguage: 'sql',
    groupBy: "ResourceAttributes['k8s.pod.name']",
    timestampValueExpression: 'Timestamp',
    dateRange: range,
  };
  const sql = parameterizedQueryToSql(await renderChartConfig(config, metadata, undefined));
  console.log('LOGS:\n' + sql);
  expect(sql).toContain("__otel_materialized_k8s.pod.name LIKE 'api-%'");
  expect(sql).toMatch(/GROUP BY ResourceAttributes\['k8s.pod.name'\]/);
});