Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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                3600                                                                                                                              
audit_file_dest                   /u01/app/oracle/admin/oracle/adump                                                                                                
audit_trail                       NONE                                                                                                                              
compatible                        11.2.0.3                                                                                                                          
control_file_record_keep_time     31                                                                                                                                
control_files                     /home/oracle/cfile/controlfile.dat, +DATA/oracle/controlfile/current.915.730988607, +FRA/oracle/controlfile/current.970.730988607 
core_dump_dest                    /u01/app/oracle/admin/oracle/cdump                                                                                                
db_block_size                     4096                                                                                                                              
db_create_file_dest               +DATA                                                                                                                             
db_domain                                                                                                                                                           
db_file_multiblock_read_count     1                                                                                                                                 
db_name                           oracle                                                                                                                            
db_recovery_file_dest             +FRA                                                                                                                              
db_recovery_file_dest_size        375809638400                                                                                                                      
diagnostic_dest                   /u01/app/oracle                                                                                                                   
dispatchers                       (PROTOCOL=TCP) (SERVICE=oracleXDB)                                                                                                
event                                                                                                                                                               
filesystemio_options              setall                                                                                                                            
global_names                      TRUE                                                                                                                              
job_queue_processes               10                                                                                                                                
log_archive_dest_1                                                                                                                                                  
log_archive_dest_10               LOCATION=USE_DB_RECOVERY_FILE_DEST MANDATORY REOPEN=60                                                                            
log_checkpoint_timeout            30                                                                                                                                
memory_max_target                 36507222016                                                                                                                       
memory_target                     36507222016                                                                                                                       
nls_language                      ENGLISH                                                                                                                           
nls_length_semantics              BYTE                                                                                                                              
nls_territory                     UNITED KINGDOM                                                                                                                    
open_cursors                      300                                                                                                                               
pga_aggregate_target              0                                                                                                                                 
processes                         150                                                                                                                               
remote_login_passwordfile         EXCLUSIVE                                                                                                                         
sga_max_size                      32212254720                                                                                                                       
sga_target                        0                                                                                                                                 
shared_pool_size                  536870912                                                                                                                         
smtp_out_server                   mailout.redacted.com                                                                                                                   
streams_pool_size                 1073741824                                                                                                                        
undo_management                   AUTO                                                                                                                              
undo_retention                    900                                                                                                                               
undo_tablespace                   TS_UNDO                                                                                                                           

 41 rows selected
*/
share|improve this question
    
Can you reproduce this on a small dataset on sql fiddle? –  FreshPhilOfSO Apr 10 '13 at 22:18
    
I guess it's a bug in Oracle11 parser/optimizer (it's probably too smart and ignores product_id in inline view) . Both of them fail in Oracle10g ("not a single-group group function"). –  a1ex07 Apr 10 '13 at 22:44
    
@Jack What patch level are you on? –  FreshPhilOfSO Apr 10 '13 at 23:36
    
@Phil SQLFiddle gives the same odd result (it's 11.2.0.2 XE, I'm on 11.2.0.3 SE 64bit Linux) –  Jack Douglas Apr 11 '13 at 9:27
    
@a1ex07 I'm sure you are right it's an optimizer bug: select sum(units), avg(product_id) from (select product_id, sum(units) units from w); fails as expected. –  Jack Douglas Apr 11 '13 at 9:29
show 2 more comments

2 Answers

I suspect that the query optimiser is applying a transformation to your first query that eliminates product_id from the inline view, then merging the inline view into the main query to that it executes as:

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

Actually, it probably then transforms it further to:

select sum(units) from (select 10 units from dual union all select 5 from dual);
share|improve this answer
    
Your two examples share the same plan (see here and here) but my original has a different plan, and using the no_merge or materialize doesn't make any difference, which is why I think the CBO is eliminating product_id from the inline view but not merging the inline view into the main query. –  Jack Douglas Apr 12 '13 at 7:52
add comment

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.

share|improve this answer
    
Is your 11.2 64-bit Linux like mine? –  Jack Douglas Apr 11 '13 at 9:26
    
Yes, it's from the apex.oracle.com demo website. Exactly the same v$version info! –  Vincent Malgrat Apr 11 '13 at 9:40
    
Very odd. Any significant differences to my select name, value from v$parameter where isdefault = 'FALSE' order by name;? –  Jack Douglas Apr 11 '13 at 9:51
    
Unfortunately I don't have access to this view on that instance. –  Vincent Malgrat Apr 11 '13 at 9:55
    
btw the no_merge and materialize hints don't make any difference. I think technically it is eliminating the unused column rather than merging the queries but I guess the bug is that it doesn't throw a parse error first. –  Jack Douglas Apr 11 '13 at 9:59
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.