Unable to parse ORACLE LISTAGG-Function in combination with OVER-Clause

[andghe]

Hi

The parser (version 4.5) reports

Exception in thread "main" net.sf.jsqlparser.JSQLParserException: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "BY" "BY"
    at line 10, column 88.

Was expecting one of:

    ")"
    ","
    "BINARY"
    "BIT"
    "CHAR"
    "CHARACTER"
    "DOUBLE"
    "INTERVAL"
    "JSON"
    "SET"
    "SIGNED"
    "UNSIGNED"
    "XML"
    <DT_ZONE>
    <K_DATETIMELITERAL>
    <K_DATE_LITERAL>
    <S_IDENTIFIER>
    <S_QUOTED_IDENTIFIER>

while parsing the following statement (reduced to the minimum with sample data included):

-- not parseable
WITH CTE_DUMMY_DATA(COL_TO_AGG, PART_COL) AS (
    SELECT 'Foo', 1 FROM DUAL
    UNION
    SELECT 'Bar', 2 FROM DUAL
    UNION
    SELECT 'Baz', 1 FROM DUAL
)
SELECT
    LISTAGG (d.COL_TO_AGG, ' / ') WITHIN GROUP (ORDER BY d.COL_TO_AGG) OVER (PARTITION BY d.PART_COL) AS MY_LISTAGG
FROM cte_dummy_data d;

This is fine ORACLE-Sql (see https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030) and returns something like

I was able to reduce the Problem to the OVER-Clause, as the following (semantically different) snippet is parseable:

-- parseable
WITH CTE_DUMMY_DATA(COL_TO_AGG, PART_COL) AS (
    SELECT 'Foo', 1 FROM DUAL
    UNION
    SELECT 'Bar', 2 FROM DUAL
    UNION
    SELECT 'Baz', 1 FROM DUAL
)
SELECT
    LISTAGG (d.COL_TO_AGG, ' / ') WITHIN GROUP (ORDER BY d.COL_TO_AGG) AS MY_LISTAGG
FROM cte_dummy_data d;

Fix/Workaround would be appreciated.

[wumpz]

within group and over are not supported at the same time. Why not use the order by for window functions? I am not quite sure if I understand the use of within group here. This should work:

WITH CTE_DUMMY_DATA(COL_TO_AGG, PART_COL) AS (
    SELECT 'Foo', 1 FROM DUAL
    UNION
    SELECT 'Bar', 2 FROM DUAL
    UNION
    SELECT 'Baz', 1 FROM DUAL
)
SELECT
    LISTAGG (d.COL_TO_AGG, ' / ') OVER (PARTITION BY d.PART_COL ORDER BY d.COL_TO_AGG) AS MY_LISTAGG
FROM cte_dummy_data d;

[andghe]

Hi @wumpz

Thank you for your reply. Unfortunately your suggestion is not feasible. According to Oracle's documentation (https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030) the WITHIN GROUP clause is not optional when using LISTAGG. The execution results in an ORA-02000 when the WITHING GROUP clause is omitted.

[manticore-projects]

Greetings,

thank your for reporting.
I do use LISTAGG on Oracle myself and thus will look into this issue soonest.

[wumpz]

@andghe unfortunately you are right: http://sqlfiddle.com/#!4/c6069c/4/0. Sorry I missed that.