ORA-00979 不是GROUP BY表达式在 11.2.0.1

以下文档用于描述 Oracle 11.2 版本中，和 ORA-979 相关的问题：
The purpose of this document is to have a complete list of ORA-979 bugs with workarounds and their current status on 11.2

QUESTIONS AND ANSWERS

1. BUG 9411496 - ORA-979 ON ‘UNION ALL PUSHED PREDICATE’ (Query Optimizer bug)
   — Symptoms —
   When the query has group by and order by, and they use the same attribute and the cursor_sharing is set to a value <> EXACT, the queries fail with ORA-979.

The call stack from the event 979 errorstack is:

… qcuErroer qcuErroep erroep qecgoc qecoby qecpqbcheck qecdrv kkqcttcalo kkqctdrvJPPD kkqjpdctr qksqbApplyToQbc kkqctdrvTD kkqjpddrv kkqdrv kkqctdrvIT apadrv opitca kksFullTypeCheck rpiswu2 kksSetBindType kksfbc …

— Changes —
Following the upgrade to 11.2.

— Cause —
Bug 9411496 - ORA-979 ON ‘UNION ALL PUSHED PREDICATE’

— Solution —
The workarounds are:

1. “_fix_control”=“5520732:OFF”

OR

2. optimizer_features_enable = ‘11.1.0.7’

OR

3."_optimizer_push_pred_cost_based" = false

OR

4."_optimizer_cost_based_transformation" = off

The bug is fixed and there are patches for some platforms. If the workarounds are not acceptable, a one-off patch for the bug should be requested from Oracle Support.

2. Bug 9478304 “LOOP FAILING WITH ORA-00979 NOT A GROUP BY EXPRESSION” (Query Optimizer bug)
   — Symptoms —

GROUP BY/ORDER BY in PL/SQL cursor loops fails with ORA-979.

The testcase is:

CREATE TABLE testing (AAA NUMBER, BBB NUMBER)
/
DECLARE
CURSOR curTesting(ciPPGroupID IN NUMBER) IS
SELECT DECODE(ciPPGroupID, AAA, 'PP', 'PP_GROUP') AS prisa_type,
COUNT(DISTINCT BBB) AS nof
FROM testing
GROUP BY DECODE(ciPPGroupID, AAA, 'PP', 'PP_GROUP')
ORDER BY prisa_type DESC;
复制

BEGIN
FOR row IN curTesting(0) LOOP
NULL;
END LOOP;
END;
/
.
insert into testing values(1,2);
insert into testing values(1,2);
.
DECLARE
CURSOR curTesting(ciPPGroupID IN NUMBER) IS
SELECT DECODE(ciPPGroupID, AAA, 'PP', 'PP_GROUP') AS prisa_type,
COUNT(DISTINCT BBB) AS nof
FROM testing
GROUP BY DECODE(ciPPGroupID, AAA, 'PP', 'PP_GROUP')
ORDER BY prisa_type DESC;
.
BEGIN
FOR row IN curTesting(0) LOOP
NULL;
END LOOP;
END;
/
复制

ERROR at line 1:
ORA-979: not a GROUP BY expression
ORA-6512: at line 3
ORA-6512: at line 10

The call stack from the event 979 errorstack is:

… qcuErroer qcuErroep erroep qecgoc qecoby qecpqbcheck qecdrv kkqcttcalo kkqctdrvIT apadrv opitca
kksFullTypeCheck rpiswu2 kksSetBindType kksfbc opiexe opipls opiodr PGOSF141_rpidrus skgmstack rpiswu2 rpidrv psddr0 psdnal pevm_EXECC pfrinstr_EXECC pfrrun_no_tool pfrrun plsql_run peicnt kkxexe …

— Changes —
Following the upgrade to 11.2.

— Cause —
Bug 9478304 “LOOP FAILING WITH ORA-00979 NOT A GROUP BY EXPRESSION”

— Solution —
The workarounds are:

1. optimizer_features_enable=‘11.1.0.7’

OR

2. optimizer_features_enable=‘10.2.0.4’

OR

3. _optimizer_distinct_agg_transform = false;

The bug is still being worked by development and a fix has not been provided yet.

3. Bug 9004697 “ORA-979 or ORA-937 in query with a correlated set subquery containing aggregates” (Query Optimizer bug)
   — Symptoms —
   ORA-979 or ORA-937 in query with a correlated set subquery containing aggregates.
   select empno from scott.emp d where d.deptno =10 and
   exists ( – Subquery
   select count(1) from scott.dept b where d.deptno= b.deptno – Correlated
   UNION – The SET operand
   select count(1) from scott.dept c where d.deptno = c.deptno – Correlated
   )
   The call stack from the event 979 errorstack is:
   …qcuErroer qcuErroep erroep qecgoc qechav qechav qechav qecpqbcheck qecdrv
   qecdrv qecdrv qecdrv kkqcttcalo kkqctdrvSU kkqutruns kkqudrv kkqctdrvTD
   kkqdrv kkqctdrvIT apadrv opitca kksLoadChild kxsGetRuntimeLock kksfbc
   kkspsc0 kksParseCursor…
   — Changes —
   Following the upgrade to 11.2.
   — Cause —
   Unpublished Bug 9004697 - “ORA-979 or ORA-937 in query with a correlated set subquery containing aggregates”
   — Solution —
   The workaround is:
   _optimizer_unnest_corr_set_subq=false
   The bug is fixed and there are patches for some platforms. If the workarounds are not acceptable and there is no patch for a particular environment, the patch should be requested from Oracle Support.
4. Bug 9071471 “STAS ORA-979 WHILE RUNNING SELECT SUBSTRB(COL)… GROUP BY COL” (RDBMS bug)
   — Symptoms —
   ORA-979 if select list contains an expression and group by is on base column of expression.
   The call stack from the event 979 errorstack is:
   … qcuErroer <- qcuErroep <- erroep <- qecgoc <- qecsel <- qecpqbcheck
   <- qecdrv <- kkqcttcalo <- kkqctInterleaveCVM <- kkqctdrvSU <- kkqutruns
   <- kkqudrv <- kkqctdrvTD <- kkqdrv <- kkqctdrvIT <- apadrv
   <- opitca <- kksFullTypeCheck <- rpiswu2 <- kksLoadChild <- kxsGetRuntimeLock
   <- kksfbc <- kkspsc0 …
   — Changes —
   Following the upgrade to 11.2.
   — Cause —
   Unpublished Bug 9071471 “STAS ORA-979 WHILE RUNNING SELECT SUBSTRB(COL)… GROUP BY COL”
   — Solution —
   The workaround is:
   _replace_virtual_columns = false
   The bug is fixed and there are patches for some platforms. If the workarounds are not acceptable and there is no patch for a particular environment, the patch should be requested from Oracle Support.
5. Bug 9800061 “ORA-00979: NOT A GROUP BY EXPRESSION” (Query Optimizer bug)
   — Symptoms —
   ORA-979 when the query contains ORDER BY expression.
   The call stack from the event 979 errorstack is:
   … qcuErroep <- erroep <- qecgoc <- qecoby <- qecpqbcheck <- qecdrv <- kkqcttcalo
   <- kkqctInterleaveCVM <- kkqctdrvSU <- kkqutruns <- kkqudrv
   <- kkqctdrvTD <- kkqdrv <- kkqctdrvIT <- apadrv <- opitca
   <- kksFullTypeCheck <- rpiswu2 <- kksLoadChild <- kxsGetRuntimeLock
   <- kksfbc <- kkspsc0 <- kksParseCursor …
   The testcase is:
   create table t1 ( NR_RELA number, CD_ROL varchar2(2));
   create table t2(NR_CLNT number);
   create table t3(NR_CLNT number,DAT_STRT date);
   alter session set “_complex_view_merging”=TRUE;
   select 1 from
   t1 rol,t2 clv
   where
   DECODE(rol.cd_rol, ‘VN’,1,2) = ( SELECT MIN(DECODE(cd_rol, ‘VN’,1,2))
   from t1 where nr_rela = rol.nr_rela)
   order by decode( ( select max(dat_strt) from t3 cvn
   where cvn.nr_clnt = clv.nr_clnt), NULL, 2, 1);
   — Changes —
   Following the upgrade to 11.2.
   — Cause —
   Bug 9800061 “ORA-00979: NOT A GROUP BY EXPRESSION”
   — Solution —
   The workarounds are:
   _complex_view_merging=false
   OR
   _optimizer_cost_based_transformation=off
   OR
   optimizer_features_enable=‘10.2.0.4’
   OR
   optimizer_features_enable=‘11.1.0.7’
   The bug is fixed in version 12.1. If the workarounds are not acceptable, the patch should be requested from Oracle Support