GROUPING SETS and COLLECT Don’t Get Along

I’ve recently got reminded that assuming something will work, just because it makes sense, doesn’t mean it will really work.

While reviewing some code a few days ago, I saw a query of the following form:

select 'X='||x, collect(z)
from t
group by x
union all
select 'Y='||y, collect(z)
from t
group by y;

I immediately recommended to convert it to use GROUPING SETS; like this:

select decode(grouping(x), 0, 'X='||x, 'Y='||y),
       collect(z)
from t
group by grouping sets (x,y);

The code will be shorter, more elegant, and probably more efficient. Great, isn’t it?
The only problem is that it doesn’t work 🙁

Let’s create a demo table:

SQL> create table t (
  2    x number,
  3    y number,
  4    z number
  5  );

Table created.

SQL> insert into t (x,y,z)
  2  select mod(rownum, 2),
  3         mod(rownum, 3),
  4         rownum
  5  from dual
  6  connect by level <= 10;

10 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user,'T')

PL/SQL procedure successfully completed.

GROUPING SETS works nicely with most of the aggregate functions…

SQL> select decode(grouping(x), 0, 'X='||x, 'Y='||y) grp_by,
  2         count(*) "CNT",
  3         count(distinct z) "CNT D",
  4         sum(z) "SUM",
  5         avg(z) "AVG",
  6         stddev(z) "STDDEV",
  7         min(z) "MIN",
  8         max(z) "MAX"
  9  from t
 10  group by grouping sets (x,y)
 11  order by 1;

GRP_BY      CNT CNT D  SUM  AVG STDDEV  MIN  MAX
---------- ---- ----- ---- ---- ------ ---- ----
X=0           5     5   30    6   3.16    2   10
X=1           5     5   25    5   3.16    1    9
Y=0           3     3   18    6      3    3    9
Y=1           4     4   22  5.5   3.87    1   10
Y=2           3     3   15    5      3    2    8

5 rows selected.

… but not with the COLLECT aggregate function.
In Oracle 11.2 and 12.1 we get ORA-604 and ORA-907:

SQL> select decode(grouping(x), 0, 'X='||x, 'Y='||y),
  2         collect(z)
  3  from t
  4  group by grouping sets (x,y);
group by grouping sets (x,y)
             *
ERROR at line 4:
ORA-00604: error occurred at recursive SQL level 1
ORA-00907: missing right parenthesis

Looks like a bug.
But in Oracle 12.2 something has changed. No, the bug was not fixed. Instead, it is now officially not supported:

SQL> select decode(grouping(x), 0, 'X='||x, 'Y='||y),
  2         collect(z)
  3  from t
  4  group by grouping sets (x,y);
       collect(z)
       *
ERROR at line 2:
ORA-03001: unimplemented feature

-- tested in 12.2, 18.3 and 19.3

So, at least for now, the original query should remain as is:

SQL> select 'X='||x grp_by, collect(z) coll
  2  from t
  3  group by x
  4  union all
  5  select 'Y='||y, collect(z)
  6  from t
  7  group by y;

GRP_BY     COLL
---------- --------------------------------------------------
X=0        ST00001HGTfH6lTUWkKMCXAmZAQg=(2, 10, 8, 6, 4)
X=1        ST00001HGTfH6lTUWkKMCXAmZAQg=(1, 9, 7, 5, 3)
Y=0        ST00001HGTfH6lTUWkKMCXAmZAQg=(3, 9, 6)
Y=1        ST00001HGTfH6lTUWkKMCXAmZAQg=(1, 10, 7, 4)
Y=2        ST00001HGTfH6lTUWkKMCXAmZAQg=(2, 8, 5)

2 thoughts on “GROUPING SETS and COLLECT Don’t Get Along”

  1. Hi Oren,

    Just as a curiosity, using ROLLUP, which is a particular case of GROUPING SETS, does work, at least in 19c (tested in LiveSQL):

    select decode(grouping_id(x,y), 0, 'X='||x|| ' Y='||y,
                                    1, 'X='||x,
                                       'ALL'),
           collect(z)
    from t
    group by rollup (x,y)
    /
    
    DECODE(GROUPING_ID(X,Y),0,'X='||X||'Y='||Y,1,'X='||X,'ALL')	COLLECT(Z)
    --------------------------------------------------------------------------
    X=0 Y=0	[unsupported data type]
    X=0 Y=1	[unsupported data type]
    X=0 Y=2	[unsupported data type]
    X=1 Y=0	[unsupported data type]
    X=1 Y=1	[unsupported data type]
    X=1 Y=2	[unsupported data type]
    X=0	[unsupported data type]
    X=1	[unsupported data type]
    ALL	[unsupported data type]
    
    9 rows selected.
    

    Using CUBE, instead, does produce the same ORA-03001 error 🙁

    So, it looks to me that we can expect this problem to be corrected,
    except if, maybe, Oracle is not aware of it (yet) ?!?

    Cheers & Best Regards,
    Iudith

  2. I had a Service Request opened two years ago and achieved that the following Bug was opened:

    Bug 26479946 – ORA-00907 IN SELECT CONTAINING COLLECT AGGREGATE FUNCTION AND GROUP BY CUBE

    It was an interesting problem
    (reproducible under Oracle 12.1.0.2 and 11.2.0.4):

    SELECT COLLECT(LANG_ID) AL FROM TEST GROUP BY CUBE(CTX, ITEM_TYPE); 
    

    raised error

     ORA-00604: error occurred at recursive SQL level 1 
     ORA-00907: missing right parenthesis 
    

    The actual recursive SQL which got the error looked as follows, and I’d say there could be double quotes missing around the system generated type SYSTPUtlfyvX/VL7gU9AFdAqDKg==:

     PARSE ERROR #140736332297232:len=236 dep=1 uid=0 oct=1 lid=0 tim=6206773413412 err=907 
     CREATE GLOBAL TEMPORARY T ... 
     ORA-00604: error occurred at recursive SQL level 1 
     ORA-00907: missing right parenthesis 
     CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D66F4_8255AA52" SHARING=NONE ("C0" NUMBER,"C1" NUMBER,"D0" NUMBER,"A0" SYSTPUtlfyvX/VL7gU9AFdAqDKg== ) IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT STORAGE (OBJNO 4254951156 ) NOPARALLEL 
    

    The table for this simple test case was created as follows:

    CREATE TABLE TEST AS SELECT level CTX, level ITEM_TYPE, level LANG_ID FROM DUAL CONNECT BY LEVEL <= 3;
    

Leave a Reply

Your email address will not be published. Required fields are marked *