yashmayya opened a new pull request, #19007: URL: https://github.com/apache/pinot/pull/19007
Add support for value-based RANGE window frames with offset `PRECEDING` / `FOLLOWING` bounds (e.g. `RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING`) in the multi-stage query engine. Previously only `ROWS` offset frames and `RANGE` frames bounded solely by `UNBOUNDED` / `CURRENT ROW` were supported; `RANGE` offset frames were rejected both at planning and execution. ## Newly supported `RANGE` frames with numeric offset bounds in any valid combination — `n PRECEDING`, `n FOLLOWING`, `CURRENT ROW`, `UNBOUNDED PRECEDING/FOLLOWING`: ```sql ... OVER (ORDER BY k RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING) ... OVER (ORDER BY k RANGE BETWEEN 10 PRECEDING AND CURRENT ROW) ... OVER (ORDER BY k RANGE BETWEEN CURRENT ROW AND 3 FOLLOWING) ... OVER (ORDER BY k RANGE BETWEEN UNBOUNDED PRECEDING AND 7 FOLLOWING) ... OVER (ORDER BY k RANGE BETWEEN 3 PRECEDING AND UNBOUNDED FOLLOWING) ... OVER (ORDER BY k DESC RANGE BETWEEN 1 PRECEDING AND 3 FOLLOWING) -- ASC and DESC ``` - Works for every frame-respecting window function: `SUM`, `COUNT`, `AVG`, `MIN`, `MAX`, `BOOL_AND`, `BOOL_OR`, `FIRST_VALUE`, `LAST_VALUE` (including `IGNORE NULLS`). - Standard SQL / PostgreSQL semantics: the frame is a value range over the single `ORDER BY` key (not a row count); peers (equal order-key values) share a frame; `NULL` order keys form their own peer group (honoring `NULLS FIRST/LAST`); leading/trailing `NULL`s are included only for `UNBOUNDED` bounds; empty frames yield `NULL` (`SUM`/`AVG`/`MIN`/`MAX`) or `0` (`COUNT`). - Both the legacy (v1) and physical-optimizer (v2) planning paths. ## Example use cases - Windowed aggregates over a *value* neighborhood rather than a row count — e.g. "sum of order amounts within ±100 of the current order value", "count of events whose score is within 5 of the current row", "min/max over a numeric band". - Coarse time-series aggregation over a numeric time key (e.g. epoch seconds) — e.g. "average over the preceding and following 3600 seconds". ## Design & performance - Frame bounds are computed with an `O(n)` two-pointer sweep over the (already sorted) partition, and computed **once per partition and shared across all window functions** in the same window (`RangeWindowFrameBounds`). - Incremental aggregation keeps each function at its optimal complexity: `SUM`/`COUNT`/`AVG` `O(1)` per slide, `MIN`/`MAX` via the existing monotonic deque (amortized `O(1)`), `BOOL_AND`/`BOOL_OR` via counts `O(1)`; `FIRST_VALUE`/`LAST_VALUE` `O(1)` per row (`O(n)` prefix arrays for `IGNORE NULLS`). Net: `O(n)` per partition (the sort is already done upstream by the sort exchange). - Type-appropriate arithmetic: a primitive `long` fast path with overflow-saturating arithmetic for `INT`/`LONG` order keys, exact `BigDecimal` for `BIG_DECIMAL` (and for non-integral offsets on integer keys), and `double` for `FLOAT`/`DOUBLE`. ## Wire / rolling-upgrade compatibility - The value offset is carried in two new additive, message-typed protobuf fields (`WindowNode.lowerBoundOffset` / `upperBoundOffset`) — backward compatible; absence is distinguishable from a real value. - A new broker sending a `RANGE` offset query to a server that predates this change fails fast with a clear error rather than producing wrong results. **Upgrade servers before brokers.** ## Limitations / future work - Supported only for **numeric** `ORDER BY` keys (`INT`/`LONG`/`FLOAT`/`DOUBLE`/`BIG_DECIMAL`). Temporal keys with `INTERVAL` offsets (e.g. `RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW`) are accepted by Calcite but rejected at planning with a clear message — a natural follow-up. - Frame `EXCLUDE` clauses remain unsupported (pre-existing). ## Testing - `RangeWindowFrameBoundsTest`: the two-pointer calculator across ASC/DESC, symmetric/asymmetric offsets, `CURRENT ROW`, `UNBOUNDED`, empty frames, `NULLS FIRST/LAST`, `INT`/`LONG`/`FLOAT`/`DOUBLE`/`BIG_DECIMAL`, non-integral-offset fallback, and `LONG` overflow saturation. - `WindowAggregateOperatorTest`: operator-level `SUM`/`MAX`/`BOOL_AND` and `FIRST_VALUE`/`LAST_VALUE` (respect + `IGNORE NULLS`) over RANGE offset frames. - `WindowFunctions.json` (H2-oracle end-to-end, both optimizer paths): aggregates + value functions, ASC/DESC, empty/unbounded/current-row combos, `DOUBLE` keys, nullable values, and nullable order keys. - Plan-shape (`WindowFunctionPlans.json`), plan serde round-trip incl. backward-compat (`PlanNodeSerDeTest`), planner validation (`QueryCompilationTest`, numeric-only rejection), and a multi-stage integration smoke test. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
