Skip to content

[BUG] JSQLParser 5.4-SNAPSHOT : PostgreSQL : EXCLUDE TIES not supported in window function frame clause #2430

@tomershay

Description

@tomershay

Failing SQL Feature

PostgreSQL EXCLUDE TIES frame exclusion clause used inside a window function's frame specification.

PostgreSQL supports the SQL-standard frame_exclusion options on window function frames: EXCLUDE CURRENT ROW, EXCLUDE GROUP, EXCLUDE TIES, and EXCLUDE NO OTHERS. EXCLUDE TIES excludes any peers of the current row from the frame,
but keeps the current row itself. It is valid syntax in PostgreSQL and appears in analytical queries that need fine-grained control over window frame contents.

Docs: https://www.postgresql.org/docs/current/sql-expressions.html

SQL Example

  CREATE TABLE sales (
      id SERIAL PRIMARY KEY,
      salesperson TEXT,
      amount INT
  );

  INSERT INTO sales (salesperson, amount) VALUES
  ('Alice', 100),
  ('Bob',   100),
  ('Carol', 200),
  ('Dave',  300);

  SELECT
      id,
      salesperson,
      amount,
      SUM(amount) OVER (
          ORDER BY amount
          RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
          EXCLUDE TIES
      ) AS running_sum_exclude_ties
  FROM sales
  ORDER BY amount, id;

Parsing error

ParseException: Encountered: <K_EXCLUDE> / "EXCLUDE", at line 8, 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