Feature: symmetric first-row-per-partition query for super tables, e.g. FIRST_ROW(*)

[Gerrit1999]

Summary

TDengine provides a highly optimized LAST_ROW(*) query per subtable via super table partitioning, but there is no symmetric high-performance way to retrieve the earliest row per subtable. This creates an API asymmetry that affects common IoT use cases such as device onboarding-time inspection, first-event debugging, and lifecycle analysis.

Current behavior

• LAST_ROW(*) FROM super_table PARTITION BY tbname is a first-class query with dedicated RocksDB cache (cache.rdb) and can return the latest row for hundreds of thousands of subtables in milliseconds.
• Getting the earliest row per subtable requires one of the following workarounds, and each has a fundamental drawback:
  1. FIRST(*) FROM super_table PARTITION BY tbname — FIRST is column-level, so the returned values can come from different rows.
  2. ROW_NUMBER() OVER (PARTITION BY tbname ORDER BY ts) with WHERE rn = 1 — supported from v3.4.2.0, but requires full table scan and has no row-cache optimization.
  3. SELECT tbname, FIRST(ts), FIRST(voltage), FIRST(current) ... — verbose, still column-level semantics, and easy to get wrong when schema evolves.

Desired behavior

A row-level, partition-aware earliest-row function with performance characteristics similar to LAST_ROW(*). For example:

SELECT FIRST_ROW(*) FROM super_table PARTITION BY tbname;

or an equivalent syntax/option that returns the complete earliest row per subtable with cache or index acceleration.

Use case / impact

• Device first-online state lookup across many subtables.
• First anomaly event inspection during device lifecycle analysis.
• Any scenario needing "first state per device" at scale without full-table-scan penalties.

Notes

• Asymmetric time-direction caching is understandable for append-only TSDB workloads, but the missing symmetric API makes some first-event analytics unnecessarily expensive.
• A cache-backed first-row path would complement the existing cache.rdb design rather than replace it.

[qiang436]

Thanks for you feedback.