Weird behavior in SELECT UDAF(AGGR()) HAVING

user_6834680 0
Viewed 58

There are two select statements: 

select       max(min(str)) from (select 0 id, 'a' str from dual) group by id having min(str) = 'a';
select strconcat(min(str)) from (select 0 id, 'a' str from dual) group by id having min(str) = 'a';

The only difference is the outer-level aggregate function: max() vs. strconcat().
You can replace strconcat() with any UDAF you have.

The former statement works as expected: it returns string 'a'.
The latter statement:
- (on Oracle 10g) gives wrong result (null instead of string 'a')
- (on Oracle 11g) raises ORA-00979: not a GROUP BY expression

I don't understand this error message.
Could you please explain this behavior?
Is it an Oracle bug?

oracle11g oracle10g oracle sql
1 Answers

10g:

It seems that WM_CONCAT available to me (yes, undocumented, but doesn't matter in this case) or STRCONCAT you use (or perhaps some other functions) need to be put a level "up"; see this example:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> select       max(min(str)) result
  2  from (select 0 id, 'a' str from dual)
  3  group by id having min(str) = 'a';

R
-
a

SQL> -- returns NULL, just as you've said
SQL> select wm_concat(min(str)) result
  2  from (select 0 id, 'a' str from dual)
  3  group by id having min(str) = 'a';

RESULT
---------------------------------------------------------------------


SQL> -- but, if we put it a level "up", the result is OK
SQL> select wm_concat(minstr) result
  2  from (select min(str) minstr
  3        from (select 0 id, 'a' str from dual)
  4        group by id having min(str) = 'a'
  5       );

RESULT
---------------------------------------------------------------------
a

SQL>

11g:

OK, both queries (I'm using listagg here):

SQL> select       max(min(str)) result
  2  from (select 0 id, 'a' str from dual)
  3  group by id having min(str) = 'a';

R
-
a

SQL>
SQL> select listagg(min(str), ',') within group (order by null) result
  2  from (select 0 id, 'a' str from dual)
  3  group by id having min(str) = 'a';

RESULT
-----------------------------------------------------------------------
a

SQL>

So, is it a bug? I wouldn't know.

user_90979060
Related