SQL: partitioned topk (top-k per group via window functions) is unsupported

[milindsrivastava1997]

Summary

The SQL pattern for partitioned topk — top-k rows per group, expressed as a ROW_NUMBER() OVER (PARTITION BY ...) subquery — fails with an unsupported subquery error. There is currently no SQL path for this operation, even though the equivalent PromQL (topk by (label) (k, ...)) is partially supported.

Reproduction

-- S8 — top 5 per region by count
SELECT service, region, cnt FROM (
    SELECT service, region, COUNT(*) AS cnt,
           ROW_NUMBER() OVER (PARTITION BY region ORDER BY COUNT(*) DESC) AS rn
    FROM metrics GROUP BY service, region
) t WHERE rn <= 5

-- S9 — top 5 per region by avg latency
SELECT service, region, avg_lat FROM (
    SELECT service, region, AVG(latency) AS avg_lat,
           ROW_NUMBER() OVER (PARTITION BY region ORDER BY AVG(latency) DESC) AS rn
    FROM metrics GROUP BY service, region
) t WHERE rn <= 5

Error for both:

ERR: unsupported feature: subquery (inline view / derived table)

Context

The PromQL equivalent (topk by (region) (5, ...)) lowers to Limit { Sort { Partition { ... } } }. SQL would need subquery support plus recognition of the ROW_NUMBER() OVER (PARTITION BY ...) pattern.

Reproduce via example

cargo run -p asap-control-lower --example topk_ir

Labels: S8, S9