Oracle dialect: KEEP (DENSE_RANK FIRST|LAST ORDER BY ...) on aggregate functions is unparsable and produces a spurious AL02 violation #8192

Search before asking

• I searched the issues and found no similar issues.

What Happened

Oracle's KEEP (DENSE_RANK FIRST|LAST ORDER BY ...) clause on aggregate functions (MIN, MAX, SUM, AVG, COUNT, VARIANCE, STDDEV) isn't recognized by the oracle dialect grammar. The parser falls back to treating the bare word KEEP as an implicit alias for the aggregate call, then treats everything from the following ( onward as an unparsable section; including any legitimate, already-explicit AS alias that appears later in that same clause. The knock-on effect: rule AL02 (require explicit column aliasing) fires on the phantom KEEP alias, even when the actual column alias is written correctly with AS.

oracle parser errors · Issue #7313 · sqlfluff/sqlfluff (unrelated/closed - confirms no existing umbrella issue covers this)

Expected Behaviour

The KEEP (DENSE_RANK FIRST|LAST ORDER BY ...) clause should parse as part of the aggregate function expression (see Oracle's docs below), and AL02 should not fire when an explicit AS is present.

FIRST/LAST Aggregate Functions — Oracle SQL Language Reference 19c

Observed Behaviour

$ sqlfluff parse --dialect oracle repro.sql

select_clause_element:
    function:
        function_name: MAX
        function_contents: (salary)
    alias_expression:
        naked_identifier: 'KEEP'          <-- phantom alias hallucinated from the KEEP keyword
unparsable:                                <-- everything else, including the real "AS best_salary", is dropped here
    (DENSE_RANK LAST ORDER BY commission_pct) AS best_salary

$ sqlfluff lint --dialect oracle --rules AL02 repro.sql
L: 2 | P: 20 | AL02 | Implicit/explicit aliasing of columns. [aliasing.column]

How to reproduce

SELECT department_id,
       MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct) AS best_salary
  FROM employees
  GROUP BY department_id;

(This is Oracle's own canonical example for the clause, per their SQL Language Reference — see below.)

sqlfluff lint --dialect oracle --rules AL02 repro.sql

FIRST/LAST Aggregate Functions — Oracle SQL Language Reference 19c

Dialect

oracle

Version

sqlfluff, version 4.0.4 (Python 3.12.3)

Configuration

[sqlfluff]
dialect = oracle
rules = AL02

Are you willing to work on and submit a PR to address the issue?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct