[BUG] clickhouse: getTimeSeriesData drops dimension columns, collapsing long-format results into one series #843

Summary

ClickHouseTimeSeriesQuery.getTimeSeriesData() treats every column except time as a numeric metric column. Any string/dimension column is coerced with Number(), becomes null, and is then removed by the "drop all-null series" filter. For long-format (a.k.a. tall) SQL — the shape documented for the Grafana ClickHouse datasource this plugin is a migration target for — the dimension is silently destroyed and every series collapses into a single anonymous series.

Repro

SELECT toStartOfInterval(now(), INTERVAL 300 second) AS time,
       machine                             AS series,
       count()                             AS value
FROM   system.query_log
GROUP BY time, series
ORDER BY time, series

Returned rows (abridged):

Expected: 2 series — node-a and node-b — each with 2 points.

Actual: 1 series named value with 4 points and duplicated timestamps (a zig-zag), because both machine and the per-node separation are gone.

Cause

clickhouse/src/queries/click-house-time-series-query/get-click-house-data.ts:26-48

const metricNames = Object.keys(data[0] ?? {}).filter((key) => key !== 'time');   // line 32

return metricNames
  .map((metricName) => {
    const values: Array<[number, number | null]> = data.map((row) => {
      const timestamp = new Date(row.time).getTime();
      const value = toTimeSeriesValue(row[metricName]);                            // Number('node-a') -> NaN -> null
      return [timestamp, value];
    });
    return { name: metricName, values };
  })
  .filter((series) => series.values.some(([, value]) => value !== null));          // line 47: series dropped

There is no pivot step: the plugin can only express one series per column, never one series per (dimension, column).

Knock-on effect:

1. labels is never populated, so legend/labels-based features (and Table) have nothing to render. Note the log query does set labels (get-click-house-log-data.ts:62), so the contract is already understood elsewhere in the plugin, and the GreptimeDB plugin in this repo already pivots non-value columns into labels (greptimedb/src/queries/greptimedb-time-series-query/get-greptimedb-data.ts:124-143).

(inferStepMs() is not affected: it sorts timestamps and skips non-positive deltas, so [t0, t0, t1, t1] still yields the correct step. Correcting an earlier claim of mine in this issue.)

Scope

We migrated 215 Grafana dashboards (Grafana + grafana-clickhouse-datasource → Perses) and replayed every query through the plugin's real transport:

Per panel kind: TimeSeriesChart 73/141 broken, Table 30/30, GaugeChart 3/3, BarChart/PieChart/LogsTable 4/4, StatChart 1/37.

Real examples of the collapsed dimension:

Compatibility note

grafana-clickhouse-datasource documents this exact shape — README, "Multi-line time series": "field 1: datetime field with an alias of time; field 2: value to group by; field 3+: the metric values" — and Grafana's own Time series dimensions describes building labels from the string columns of a tall result. $__columns(timeColumn, key, value) in the Grafana plugin expands to precisely this shape ("one series per key value").

Proposed behaviour

Detect dimension columns instead of assuming "everything except time is numeric":

1. Partition the result columns into time (a column named time — the alias the Grafana plugin itself requires to recognise a time series), numeric (every value is null | '' | finite number) and dimension (the rest).
2. No dimension columns → keep the current behaviour exactly (wide results keep working; this is the 26 clean queries).
3. ≥1 dimension column → pivot: group rows by the stringified dimension columns and emit one series per group, following the naming already used by the GreptimeDB plugin in this repo (buildSeriesName: key=value,key=value):
   • labels = { <dimColumn>: <value>, ... };
   • one numeric column → name = key=value,...;
   • several numeric columns → one series per (dimension, numeric column), name = key=value,... <valueColumn> and the value column also recorded in labels so row-oriented consumers (Table) can tell the rows apart.
4. Results with no time column at all are handled in [BUG] clickhouse: queries without a time column render as a single row in Table panels #841.

time-less results (pure table queries, e.g. SELECT app, team, count() FROM ... GROUP BY app, team) currently hit new Date(undefined).getTime() → NaN for every point and end up as a single series holding only the last row — that is filed separately in #841.

Environment

• @perses-dev/clickhouse-plugin 0.6.0 and 0.7.0-beta.6 — behaviour is identical in both.
• Perses 0.54.0.