Description

andghe

opened on Oct 25, 2022

Issue body actions

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.

Activity

1. wumpz commented on Oct 28, 2022

   

   wumpz

   on Oct 28, 2022

   Member

   More actions

   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;
   
2. andghe commented on Oct 29, 2022

   

   andghe

   on Oct 29, 2022

   Author

   More actions

   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.
3. manticore-projects

   self-assigned this

   on Oct 29, 2022
4. manticore-projects

   added

   bug

   on Oct 29, 2022
5. manticore-projects commented on Oct 29, 2022

   

   manticore-projects

   on Oct 29, 2022

   Contributor

   More actions

   Greetings,

   thank your for reporting.
   I do use LISTAGG on Oracle myself and thus will look into this issue soonest.
6. wumpz commented on Nov 2, 2022

   

   wumpz

   on Nov 2, 2022

   Member

   More actions

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

   added a commit that references this issue on Nov 14, 2022

   feat: LISTAGG() with OVER() clause

   3ae6d1c
8. wumpz

   closed this as completedin #1666on Nov 20, 2022
9. wumpz

   added a commit that references this issue on Nov 20, 2022

   Assorted fixes (#1666)

   bff268a

Issue actions