sql - Oracle result without group by -

i'm running below query on oracle exadata.

oracle database 11g enterprise edition release 11.2.0.4.0 - 64bit production partitioning, real application clusters, automatic storage management, olap, data mining , real application testing options

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

i expecting oracle return error since - can see there's no group by clause in inner query “t” , expecting query fail.

there millions of records , each prctr_cell_nbr want max count , outer query should sum max counts every prctr_cell. it's simple query. however, query runs , returns output of 112 max count inner query.

i'm puzzled behavior since not correct result returned query. don't think known behavior, has seen ?

thanks

what see effect of applying "select list pruning" optimizer. in case considered bug - if in-line view contains aggregate function, unreferenced column(s) in main query, , there no group by clause, optimizer decides rid of unreferenced columns(slp - select list pruning):

environment: windows x64; oracle 12.1.0.1.0

-- test-table  create table t1   select level col1        , level col2     dual   connect level <= 7;  -- gather statistic on t1 table.  exec dbms_stats.gather_table_stats('', 't1'); 

now let's execute buggy query 10053 trace enabled , see happen under cover:

alter session set tracefile_identifier='no_group_by';  alter session set events '10053 trace name context forever';  select /*+ qb_name(outer) */ col1   (          select /*+ qb_name(inner) */ max(col1) col1               , col2            t1        );         col1 ----------          7   alter session set events '10053 trace name context off'; 

there no expected ora-00937 error. went smoothly. trace file:

optimizer information  ****************************************** ----- current sql statement session (sql_id=d14y7zuxvvfbw) ----- select /*+ qb_name(outer)  */ col1   (          select /*+ qb_name(inner)*/max(col1) col1               , col2            t1        ) ******************************************* .....  query transformations (qt) ************************** .... svm: svm bypassed: single grp set fct (aggr) without group by.  /* that's lose our col2 */  slp: removed select list item col2 query block inner query block outer (#0) unchanged  ....  final query after transformations:******* unparsed query ******* select /*+ qb_name ("outer") */ "from$_subquery$_001"."col1" "col1"     (select /*+ qb_name ("inner") */ max("t1"."col1") "col1"             "hr"."t1" "t1") "from$_subquery$_001" 

as workaround _query_rewrite_vop_cleanup parameter can set false. of course consult oracle support if parameter needs set in production environment.

alter session set "_query_rewrite_vop_cleanup"=false; session altered  select /*+ qb_name(outer)  */ col1   (          select /*+ qb_name(inner) */ max(col1) col1               , col2            t1        ); 

result:

error report - sql error: ora-00937: not single-group group function 00937. 00000 -  "not single-group group function" 

and when add group by clause, query works expected.

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

result:

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

if have access mos, take @ 1589317.1, 16989676.8(bug 16989676 - should fixed in 12.1.0.2 release), bug 8945586 notes.