Skip to Main Content
  • Questions
  • LISTAGG .. WITHIN GROUP (ORDER BY ..) is ignored in UPDATE .. RETURNING lause

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Lukas.

Asked: September 26, 2018 - 12:12 pm UTC

Last updated: September 27, 2018 - 4:13 am UTC

Version: 18.0.0.0.0

Viewed 1000+ times

You Asked

Consider this script (which I've also put on Live SQL: https://livesql.oracle.com/apex/livesql/s/hbfb7x0c47ubwjpygs3klr3wj )

CREATE TABLE t ( 
  id NUMBER(10) GENERATED ALWAYS AS IDENTITY NOT NULL PRIMARY KEY, 
  category NUMBER(10) NOT NULL, 
  counter NUMBER(10), 
  text VARCHAR2(10) NOT NULL 
);

INSERT INTO t (category, text) 
SELECT dbms_random.value(1, 10), dbms_random.string('a', 10) 
FROM dual 
CONNECT BY level <= 100;

DECLARE 
  v_text VARCHAR2(2000); 
  v_updated PLS_INTEGER := 0; 
BEGIN 
  UPDATE t 
  SET counter = nvl(counter, 0) + 1 
  WHERE category = 1 
  RETURNING 
    listagg (text, ', ') WITHIN GROUP (ORDER BY text DESC), 
    count(*) 
  INTO 
    v_text, 
    v_updated; 
     
  COMMIT; 
  dbms_output.put_line('Rows updated: ' || v_updated); 
  dbms_output.put_line('Returned:     ' || v_text); 
END; 


When I run it, I get something like this:

Rows updated: 8
Returned: EHjMWhyYYB, NeXOeOUOtl, eCKQeVNgii, vdfoaOEDbS, WnfPUiTNcZ, ciBYUHUgEe, ccZVEgaVAm, sPyXHepzae


No matter what I put in the WITHIN GROUP (ORDER BY ..) clause, the ordering is ignored and seems random. Is this a bug or a known restriction? Is there a workaround?

and Connor said...

My guess is a bug (and I'll log it).

Pretty much as long as its been implemented, RETURNING has always been about simple expressions or "simple" aggregates (where I'm using the term 'simple' to be nothing that implies ordering), eg, we're not allowed to DISTINCT in the aggregation etc.

So I'd be willing to hypothesize that we have never implemented an *ordering* operation in the RETURNING clause without realising that LISTAGG is the first such aggregate that needs both aggregation AND order.

I modified your demo a bit - looks like its simply aggregated as encountered in the table, eg

SQL> CREATE TABLE t (
  2    id NUMBER(10) GENERATED ALWAYS AS IDENTITY NOT NULL PRIMARY KEY,
  3    category NUMBER(10) NOT NULL,
  4    counter NUMBER(10),
  5    text VARCHAR2(10) NOT NULL
  6  );

Table created.

SQL>
SQL> INSERT INTO t (category, text)
  2  SELECT mod(rownum,3), chr(64+rownum)
  3  FROM dual
  4  CONNECT BY level <= 10;

10 rows created.

SQL>
SQL> select * from t;

        ID   CATEGORY    COUNTER TEXT
---------- ---------- ---------- ----------
         1          1            A
         2          2            B
         3          0            C
         4          1            D
         5          2            E
         6          0            F
         7          1            G
         8          2            H
         9          0            I
        10          1            J

10 rows selected.

SQL>
SQL> set serverout on
SQL> DECLARE
  2    v_text VARCHAR2(2000);
  3    v_updated PLS_INTEGER := 0;
  4  BEGIN
  5    UPDATE t
  6    SET counter = nvl(counter, 0) + 1
  7    WHERE category = 1
  8    RETURNING
  9      listagg (text, ', ') WITHIN GROUP (ORDER BY text ),
 10      count(*)
 11    INTO
 12      v_text,
 13      v_updated;
 14
 15    COMMIT;
 16    dbms_output.put_line('Rows updated: ' || v_updated);
 17    dbms_output.put_line('Returned:     ' || v_text);
 18  END;
 19  /
Rows updated: 4
Returned:     A, D, G, J

PL/SQL procedure successfully completed.

SQL>
SQL> delete t;

10 rows deleted.

SQL> INSERT INTO t (category, text)
  2  SELECT mod(rownum,3), chr(64+rownum)
  3  FROM dual
  4  CONNECT BY level <= 10
  5  order by 2 desc;              <====== changed order

10 rows created.

SQL>
SQL> select * from t;

        ID   CATEGORY    COUNTER TEXT
---------- ---------- ---------- ----------
        11          1            J
        12          0            I
        13          2            H
        14          1            G
        15          0            F
        16          2            E
        17          1            D
        18          0            C
        19          2            B
        20          1            A

10 rows selected.

SQL>
SQL> set serverout on
SQL> DECLARE
  2    v_text VARCHAR2(2000);
  3    v_updated PLS_INTEGER := 0;
  4  BEGIN
  5    UPDATE t
  6    SET counter = nvl(counter, 0) + 1
  7    WHERE category = 1
  8    RETURNING
  9      listagg (text, ', ') WITHIN GROUP (ORDER BY text ),
 10      count(*)
 11    INTO
 12      v_text,
 13      v_updated;
 14
 15    COMMIT;
 16    dbms_output.put_line('Rows updated: ' || v_updated);
 17    dbms_output.put_line('Returned:     ' || v_text);
 18  END;
 19  /
Rows updated: 4
Returned:     J, G, D, A

PL/SQL procedure successfully completed.


Workaround is a bit clunky because the responsibility to sort the rows falls onto you, eg

SQL> delete t;

10 rows deleted.

SQL> INSERT INTO t (category, text)
  2  SELECT mod(rownum,3), chr(64+rownum)
  3  FROM dual
  4  CONNECT BY level <= 10
  5  order by 2 desc;

10 rows created.

SQL>
SQL> select * from t;

        ID   CATEGORY    COUNTER TEXT
---------- ---------- ---------- ----------
        31          1            J
        32          0            I
        33          2            H
        34          1            G
        35          0            F
        36          2            E
        37          1            D
        38          0            C
        39          2            B
        40          1            A

10 rows selected.

SQL>
SQL> set serverout on
SQL> DECLARE
  2    v_text VARCHAR2(2000);
  3    v_updated PLS_INTEGER := 0;
  4
  5    l_list sys.odcivarchar2list := sys.odcivarchar2list();
  6  BEGIN
  7    UPDATE t
  8    SET counter = nvl(counter, 0) + 1
  9    WHERE category = 1
 10    RETURNING text bulk collect into l_list;
 11
 12    select listagg(column_value,',') within group ( order by column_value)
 13    into   v_text
 14    from   table(l_list);
 15
 16    COMMIT;
 17    dbms_output.put_line('Rows updated: ' || l_list.count);
 18    dbms_output.put_line('Returned:     ' || v_text);
 19  END;
 20  /
Rows updated: 4
Returned:     A,D,G,J

PL/SQL procedure successfully completed.




Is this answer out of date? If it is, please let us know via a Comment

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.