Hi Team,

We are migrating from 11g to 12c.

We observed one behavioral change, as "group by" not mention in in-line query but 11g not thrown error.


Query characteristics
----------------------------------
1. Main query contains a inline view
2. Inline view has aggregate function like count, min, max, sum, etc
3. Inline view has one or more columns listed that are not used inside aggregate function
4. Inline view does not have GROUP BY clause
5. Main query refers to only aggregate columns from inline view

Sample query
Example 1:
select decode(cnt, 0, 'N', 'Y') chk,
trim(cnt),
CASE WHEN cnt > 0 THEN 'Y' END chk_case,
substr(cnt, 1, 2)
from (
select object_type, count(1) cnt
from dba_objects
--where object_type = 'TABLE'
);

Example 2:
select cnt,
decode(cnt, 0, 'N', 'Y') chk,
trim(cnt),
CASE WHEN cnt > 0 THEN 'Y' END chk_case,
substr(cnt, 1, 2),
analyzed_date,
u.*
from (
select owner, count(1) cnt, max(last_analyzed) analyzed_date
from dba_tables
--where owner = 'NPFDBO'
) t,
dba_users u
where analyzed_date >= u.created
;

Example 3:
with w as (
SELECT 1 assetid, 10 units FROM dual
UNION ALL
SELECT 2, 5 FROM dual)
SELECT SUM(units) FROM (SELECT assetid, SUM(units) units FROM w);

Observations
1. Query returns data in 11g
2. Query fails in 12c with following error message
ORA-00937: not a single-group group function
3. When inline view is executed by itself, it throws error in both 11g and 12c
4. Even though query fails in 12c, it does not throw compile-time error when used in procedures/functions. (I think, oracle does not
throw compile-time errors for such queries where GROUP BY clause is missing)
5. As there is no GROUP BY clause provided, Oracle 11g is ignoring column mentioned outside aggregate function and returning single
record for inline view.
6. It looks like it was a bug in 11g which Oracle has fixed in 12c.

SQL> select decode(cnt, 0, 'N', 'Y') chk,
  2  trim(cnt),
  3  CASE WHEN cnt > 0 THEN 'Y' END chk_case,
  4  substr(cnt, 1, 2)
  5  from (
  6  select object_type, count(1) cnt
  7  from t
  8  --where object_type = 'TABLE'
  9  );

C TRIM(CNT)                                C SUBSTR(C
- ---------------------------------------- - --------
Y 86845                                    Y 86

SQL>  alter session set optimizer_features_enable = '10.2.0.4';

Session altered.

SQL> select decode(cnt, 0, 'N', 'Y') chk,
  2  trim(cnt),
  3  CASE WHEN cnt > 0 THEN 'Y' END chk_case,
  4  substr(cnt, 1, 2)
  5  from (
  6  select object_type, count(1) cnt
  7  from t
  8  --where object_type = 'TABLE'
  9  );
select object_type, count(1) cnt
       *
ERROR at line 6:
ORA-00937: not a single-group group function