Repository navigation
sql: per-group top-k (LIMIT k BY cols) is rejected #818
Description
Problem
Per-group top-k written with ClickHouse's LIMIT k BY <cols> is rejected. The planner fails with SQL parse error: Failed to parse SQL query: ....
SELECT job, instance, SUM(value) AS v FROM data
WHERE time BETWEEN DATEADD(s, -300, NOW()) AND NOW()
GROUP BY job, instance
ORDER BY v DESC LIMIT 3 BY jobEquivalent PromQL: topk by (job) (3, sum_over_time(data[5m]))
Global top-k (ORDER BY v DESC LIMIT 3, no BY) is supported. Both sqlparser dialects in use (ClickHouseDialect in the planner, GenericDialect in the engine) parse LIMIT ... BY into limit_by, but SQLPatternParser::parse_limit_value returns None for any non-empty limit_by.
More examples:
| SQL | PromQL |
|---|---|
... GROUP BY job, instance ORDER BY c DESC LIMIT 3 BY job with COUNT(value) AS c |
topk by (job) (3, count_over_time(data[5m])) |
... ORDER BY v DESC LIMIT 3 BY job over a 1s window |
topk without (instance) (3, ordered_data) |
Reproduce
cargo test -p query_engine_rust --test sql_compliance -- --nocapture; see the topk-*-by-job* cases in aggregations.yaml and topk-ordering.yaml.
Activity