Oracle result without group by

Question

I'm running below query on Oracle Exadata.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options

select sum (t.sum_edw_trx_cnt) 
  from ( 
         select max(x.edw_trx_cnt)sum_edw_trx_cnt, 
                x.prctr_cell_nbr
           from p_prctr_smpl_pf_sp3 x
          where  mdld_prctr_flg = 'Y'
       )t;

I was expecting oracle to return an error since - As you can see there's no group by clause in the inner query “t” and I was expecting this query to fail.

There are millions of records and for each prctr_cell_nbr I want max count and then outer query should sum up max counts for every prctr_cell. It's a simple query. However, query runs and returns the output of 112 which is max count from the inner query.

I'm puzzled by this behavior since this is not the correct result returned by query. I don't think this is a known behavior, has anyone seen this recently ?

Thanks

Answer 1

What you see is the effect of applying "select list pruning" by the optimizer. In this case it is considered to be a bug - if an in-line view contains an aggregate function, unreferenced column(s) in the main query, and there is no group by clause, optimizer decides to simply get rid of those unreferenced columns(SLP - select list pruning):

Environment: Windows x64; Oracle 12.1.0.1.0

-- test-table 
create table t1 as
  select level as col1
       , level as col2
    from dual
  connect by level <= 7;

-- gather statistic on t1 table. 
exec dbms_stats.gather_table_stats('', 'T1');

Now let's execute that buggy query with 10053 trace enabled and see what will happen under the cover:

alter session set tracefile_identifier='no_group_by';

alter session set events '10053 trace name context forever';

select /*+ qb_name(outer) */ col1
  from (
         select /*+ qb_name(inner) */ max(col1) as col1
              , col2
           from t1
       );


      COL1
----------
         7 

alter session set events '10053 trace name context off';

There was no expected ORA-00937 error. Everything went smoothly. Now the trace file:

OPTIMIZER INFORMATION

******************************************
----- Current SQL Statement for this session (sql_id=d14y7zuxvvfbw) -----
select /*+ qb_name(outer)  */ col1
  from (
         select /*+ qb_name(inner)*/max(col1) as col1
              , col2
           from t1
       )
*******************************************
.....

Query transformations (QT)
**************************
....
SVM: SVM bypassed: Single grp set fct (aggr) without group by.

/* That's where we lose our COL2 */

SLP: Removed select list item COL2 from query block INNER
query block OUTER (#0) unchanged

....

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ QB_NAME ("OUTER") */ "from$_subquery$_001"."COL1" "COL1" 
  FROM  (SELECT /*+ QB_NAME ("INNER") */ MAX("T1"."COL1") "COL1" 
           FROM "HR"."T1" "T1") "from$_subquery$_001"

As a workaround the _query_rewrite_vop_cleanup parameter can be set to false. But of course consult with oracle support if this parameter needs to be set in a production environment.

alter session set "_query_rewrite_vop_cleanup"=false;
session altered

select /*+ qb_name(outer)  */ col1
  from (
         select /*+ qb_name(inner) */ max(col1) as col1
              , col2
           from t1
       );

Result:

Error report -
SQL Error: ORA-00937: not a single-group group function
00937. 00000 -  "not a single-group group function"

And when we add group by clause, the query works as expected.

select /*+ qb_name(outer) */ col1
  from (
         select /*+ qb_name(inner) */ max(col1) as col1
              , col2
           from t1
          group by col2
       );

Result:

COL1
----------
         1 
         6 
         2 
         4 
         5 
         3 
         7 

If you have access to MOS, take a look at 1589317.1, 16989676.8(Bug 16989676 - should be fixed in 12.1.0.2 release), Bug 8945586 notes.