Oracle - RETURNING combined with aggregate functio

问题:

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)

回答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

回答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.