[BUG] JSQLParser 5.4-SNAPSHOT : ClickHouse : ORDER BY ... WITH FILL / INTERPOLATE not supported

[tomershay]

Failing SQL Feature

ClickHouse supports the WITH FILL modifier on ORDER BY expressions, which fills in missing rows in a sorted sequence (gap filling for time series). It accepts optional FROM, TO and STEP bounds, where STEP may be an INTERVAL literal, and an optional query-level INTERPOLATE clause that computes the values of non-sorted columns for the generated rows. JSQLParser fails to parse the WITH FILL modifier when used in ORDER BY.

See the ClickHouse docs:
https://clickhouse.com/docs/en/sql-reference/statements/select/order-by#order-by-expr-with-fill-modifier

SQL Example

CREATE TABLE t (d Date, v UInt32) ENGINE = MergeTree ORDER BY d;

INSERT INTO t VALUES ('2024-01-03', 10), ('2024-01-06', 20), ('2024-01-10', 30);

SELECT d, v FROM t ORDER BY d WITH FILL FROM toDate('2024-01-01') TO toDate('2024-02-01') STEP INTERVAL 1 DAY INTERPOLATE (v AS v + 1);

Parsing error

ParseException: Encountered: <K_WITH> / "WITH", at line 4, column 3, in lexical state DEFAULT.

Tested with Clickhouse 25.8.