Oracle - RETURNING combined with aggregate functions

Question

Oracle supports RETURNING clause which could be very useful.

For example for data:

CREATE TABLE t(Id INT, Val varchar2(50));

INSERT INTO t(Id, Val)
SELECT 10,'a' FROM dual
UNION ALL SELECT 20,'b' FROM dual
UNION ALL SELECT 30,'a' FROM dual
UNION ALL SELECT 40,'b' FROM dual;

Query:

DECLARE
   l_cnt INT;
BEGIN
   DELETE FROM t RETURNING COUNT(*) INTO l_cnt;
   DBMS_OUTPUT.put_line('l_cnt: ' || l_cnt);
END;

l_cnt: 4

It supports MIN/MAX/AVG/SUM/LISTAGG:

DECLARE
   l_max INT;
   l_min INT;
   l_str VARCHAR2(100);
BEGIN
   DELETE FROM t 
   RETURNING MAX(id), MIN(id), LISTAGG(id, ',') WITHIN GROUP(ORDER BY id) 
   INTO l_max, l_min, l_str;
   DBMS_OUTPUT.put_line('l_max:'||l_max||' l_min:'||l_min||' l_str:'|| l_str);
END;

l_max:40 l_min:10 l_str:10,20,30,40

Unfortunately when combined with DISTINCT keyword I get an error:

DECLARE
   l_distinct_cnt INT;
BEGIN
   DELETE FROM t 
   RETURNING COUNT(DISTINCT val) INTO l_distinct_cnt ;
   DBMS_OUTPUT.put_line('l_distinct_cnt:' || l_distinct_cnt );
END;

ORA-00934: group function is not allowed here

db<>fiddle demo

The question is why aggregate functions with DISTINCT are not allowed? I am looking for an answer drawing from official sources.

---

EDIT:

Please note that COUNT(DISTINCT ...) was only an example. Same behavior is for SUM(col)/SUM(DISTINCT col) and any aggregate function that supports DISTINCT keyword.

SUM(val) vs SUM(DISTINCT val)

Chrysostom

No answers, no comments. I don't have anything official either, but here's what I think: RETURNING is, actually, SQL%ROWCOUNT in disguise. So, when you insert, update, or - as in your case - delete some rows, SQL%ROWCOUNT knows how many rows you deleted in total. It doesn't (can't?) count distinct values. As I've said: that's just what I think and it may be very wrong. – Beatify

@Beatify Actually RETURNING is much more than SQL%ROWCOUNT, it could return single/multiple values/do aggregations/return collections/.... My case is why one agg function like COUNT(val) is working fine, but COUNT(DISTINCT val) is treated differently db<>fiddle demo – Chrysostom

Yes, I agree. Perhaps I should have said that my impression is that returning count(*) acts like sql%rowcount. – Beatify

@Beatify That is why I introduced examples withMIN/MAX/LISTAGG. Anyway SUM(id) will work but SUM(DISTINCT id) won't work. So the root of the problem is DISTINCT. – Chrysostom

The reason could be that count(distinct ...) is T = O (n log n ) whereas the rest of the potential aggregation functions is T = O (n ). In other words, you are forbidden to ask for an additional sort of the data being returned from your query. In another words, the PL/SQL engine does not want to store the query results anywhere in memory, it only wants to loop through them in a pipelined fashion and calculate your aggregate. However, I also don't know of any official sources for this specific behaviour. – Romanticism

@nop77svk I could almost agree with such explanation, but I could write Returning (SELECT COUNT(DISTINCT id) FROM t) INTO ..... So returning with subquery will allow to run arbitrary aggregate but unfortunately the data is taken from snapshot just before operation DELETE. – Chrysostom

Answer 1

First of all, documentation and actual functionality is a bit out of sync so "official sources" will not shed a light on the details.

Syntactic diagram for 10g R2 (https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/returninginto_clause.htm) is below

In 11g (https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/returninginto_clause.htm) this was split into two: static_returning_clause (for insert, update, delete) and dynamic_returning_clause (for execute immediate). We are interested in the one for DML.

So for 10g there was a single row expression which according to documentation is Expression that returns a single row of a table. It's a subtle question whether DML statement must affect a single row or single row can be derived after execution of the statement (say, by using aggregate functions). I assume the idea was to use this syntax when DML operation affects single row (as opposed to bulk collect into); not using aggregate functions which return single row for affected rows.

So aggregate functions in returning into clause are not documented clearly. Moreover, for 11g just a column name may appear after returning keyword, so even expression like abs(column_name) is not allowed not to mention aggregate_function(column_name), even though in reality it works.

So, strictly speaking, this functionality with aggregate functions is not documented, especially for 11g, 12c, 18c and you cannot rely on it.

Instead you can use "bulk collect into" (and set operator to get distinct set of the elements)

SQL> create type str_tab as table of varchar2(4000)
  2  /

Type created.

SQL> set serveroutput on
SQL> declare
  2    i int;
  3    a str_tab;
  4  begin
  5    delete from t returning val bulk collect into a;
  6    dbms_output.put_line('cnt all ' || a.count || ' cnt distinct ' || set(a).count);
  7    rollback;
  8  end;
  9  /
cnt all 4 cnt distinct 2

PL/SQL procedure successfully completed.

Pay also attention to error message. It clearly says

ORA-00934: group function is not allowed here

Not just "distinct is not allowed" like in this example

SQL> select listagg(distinct val) within group (order by val) str from t;
select listagg(distinct val) within group (order by val) str from t
       *
ERROR at line 1:
ORA-30482: DISTINCT option not allowed for this function

Stereo

Answer 2

The primary reason is that SQL is not composable. C. J. Date showed, at least in the 2009 class I attended in North Texas, that SQL is not composable. Because it's not composable, certain things don't come free. And by free, I mean without having even a thought. But the folks in Server Technologies are pretty smart and I'm sure the ones who managed the "add returning feature" project consciously determined where to draw the line. They apparently decided they would not "fully" write the parser. I suspect it's because they knew that if they took the position of 100% supportability, then as soon as some other part of SQL is enhanced, then they'd have to also spend time enhancing other parts of the language too.

I do admire ST for how fast their SQL parser executes and how rare it produces incorrect results. But I wonder, but not as fervently as Mr. Date, how much better the world might be if the dominant query language were at least composable.

Bucko

Do you have link to materials about part that SQL is not composable? I am sure it will be interesting lecture. – Chrysostom