Why is a "non-single-group group function" allowed in a subselect but not on it's own?

Why does the first query not fail with the same error as the second:

with w as (select 1 product_id, 10 units from dual union all select 2, 5 from dual)
select sum(units) from (select product_id, sum(units) units from w);

/*
SUM(UNITS)
----------
        15 
*/

with w as (select 1 product_id, 10 units from dual union all select 2, 5 from dual)
select product_id, sum(units) units from w;

/*
Error starting at line 7 in command:
with w as (select 1 product_id, 10 units from dual union all select 2, 5 from dual)
select product_id, sum(units) units from w
Error at Command Line:8 Column:8
Error report:
SQL Error: ORA-00937: not a single-group group function
00937. 00000 -  "not a single-group group function"
*Cause:    
*Action:
*/

edit: version info added:

select * from v$version;
/*
BANNER                                                                         
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production                        
PL/SQL Release 11.2.0.3.0 - Production                                           
CORE    11.2.0.3.0  Production                                                         
TNS for Linux: Version 11.2.0.3.0 - Production                                   
NLSRTL Version 11.2.0.3.0 - Production                 
*/

edit: non-default params added:

```
select name, value from v$parameter where isdefault = 'FALSE' order by name;
/*
NAME VALUE
--------------------------------- ----------------------------------------------------------------------------------------------------------------------------------
aq_tm_processes 1
archive_lag_target

I would say it's a bug in your Oracle version.

-
In 11.1.0.7.0, 9.2.0.7.0 and 11.2.0.3.0:

SQL> with w as (
  2  SELECT 1 product_id, 10 units FROM dual
  3  UNION ALL
  4  SELECT 2, 5 FROM dual)
  5  SELECT SUM(units) FROM (SELECT product_id, SUM(units) units FROM w);

ORA-00937: not a single-group group function

The subquery is definitely not valid. There may be a simplification made by the optimizer in merging the two aggregate queries (as suggested by @a1ex07), but the merge shouldn't take place in any case since the query is not logically correct.

There are several bugs regarding incorrect GROUP BY behaviour logged in MOS, but I couldn't find one that looks exactly this this one. The closest I found is Bug 8945974 where an incorrect query with a GROUP BY was working in 10.2.0.3 and patching the database to 10.2.0.4 made it appropriately fail.

StackExchange Database Administrators Q#39674, answer score: 3