In 11.2.0.3, ORA-937 is not generated when expected (Doc ID 1589317.1)

Last updated on MARCH 10, 2025

Applies to:

Symptoms

An incorrect behaviour has been identified in 11g for the following syntax, which returns a result, although it should generated ORA-937:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option

SQL> select count(*)
 2 from (select tablespace_name,count(*) tables
 3 from all_tables)
 4 where tables<100
 5 /

 COUNT(*)
----------
  0

There's an incorrect syntax above in the in-line view but it still runs and produces an incorrect answer.

If you change the SELECT statement to:

 select *
 from (select tablespace_name,count(*) tables
 from all_tables)
* where tables<100
SQL> /
from (select tablespace_name,count(*) tables
  *
ERROR at line 2:
ORA-00937: not a single-group group function

Now it exhibits the sort of behaviour expected.

The following is the expected output if we put the GROUP BY in the in-line view:

 select count(*)
 from (select tablespace_name,count(*) tables
 from all_tables
 group by tablespace_name)
* where tables<100
SQL> /

 COUNT(*)
----------
  16

This is what happens in 10g and is what we would expect:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

SQL> select count(*)
 2 from (select tablespace_name,count(*) tables
 3 from all_tables)
 4 where tables<100
 5 /
from (select tablespace_name,count(*) tables
  *
ERROR at line 2:
ORA-00937: not a single-group group function

Changes

 Upgraded from 10g to 11g.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.