Skip to content

[BUG] JSQLParser 5.4-SNAPSHOT : PostgreSQL : GROUPS not supported in window function frame clause #2431

@tomershay

Description

@tomershay

Failing SQL Feature

PostgreSQL GROUPS frame mode used inside a window function's frame specification.

PostgreSQL supports three frame_mode options on window function frames: RANGE, ROWS, and GROUPS. The GROUPS mode (added in PostgreSQL 11) defines the frame in terms of peer groups — sets of rows that are equivalent according to
the window's ORDER BY clause — rather than individual rows or value ranges. It is valid SQL-standard syntax in PostgreSQL and appears in analytical queries that need to aggregate over a fixed number of distinct ordered groups.

See the PostgreSQL docs for details: https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS

SQL Example

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    user_id INT,
    ts INT,
    value INT
);

INSERT INTO events (user_id, ts, value) VALUES
(1, 1, 10),
(1, 1, 20),
(1, 2, 30),
(1, 3, 40),
(1, 3, 50),
(1, 4, 60);

SELECT
    id,
    ts,
    value,
    SUM(value) OVER (
        ORDER BY ts
        GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
    ) AS sum_last_2_groups
FROM events
ORDER BY ts, id;

Parsing error

ParseException: Encountered: <S_IDENTIFIER> / "GROUPS", at line 7, column 9, in lexical state DEFAULT.

Metadata

Metadata

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions