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)