由ORA-00979错误发现ORACLE一个BUG

今天测试TOM的9i&10g编程艺术中检查外键是否建立索引的SQL，竟然报错。我是在PL/SQL DEVELOPER工具上运行的。SQL如下：

 

SELECT TABLE_NAME, CONSTRAINT_NAME, CNAME1 || NVL2(CNAME2, ', ' || CNAME2, NULL) ||
NVL2(CNAME3, ', ' || CNAME3, NULL) || NVL2(CNAME4, ', ' || CNAME4, NULL) || NVL2(CNAME5, ', '
|| CNAME5, NULL) ||
NVL2(CNAME6, ', ' || CNAME6, NULL) || NVL2(CNAME7, ', ' || CNAME7, NULL) || NVL2(CNAME8, ', '
|| CNAME8, NULL) COLUMNS
FROM
(
SELECT B.TABLE_NAME, B.CONSTRAINT_NAME, MAX(DECODE(POSITION, 1, COLUMN_NAME, NULL)) CNAME1,
MAX(DECODE(POSITION, 2, COLUMN_NAME, NULL)) CNAME2, MAX(DECODE(POSITION, 3, COLUMN_NAME, NULL)) CNAME3,
MAX(DECODE(POSITION, 4, COLUMN_NAME, NULL)) CNAME4, MAX(DECODE(POSITION, 5, COLUMN_NAME, NULL)) CNAME5,
MAX(DECODE(POSITION, 6, COLUMN_NAME, NULL)) CNAME6, MAX(DECODE(POSITION, 7, COLUMN_NAME, NULL)) CNAME7,
MAX(DECODE(POSITION, 8, COLUMN_NAME, NULL)) CNAME8, COUNT(*) COL_CNT
FROM
(
SELECT SUBSTR(TABLE_NAME, 1, 30) TABLE_NAME, SUBSTR(CONSTRAINT_NAME, 1, 30) CONSTRAINT_NAME
,
SUBSTR(COLUMN_NAME, 1, 30) COLUMN_NAME, POSITION
FROM USER_CONS_COLUMNS
) A,
USER_CONSTRAINTS B
WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND B.CONSTRAINT_TYPE = 'R'
GROUP BY B.TABLE_NAME, B.CONSTRAINT_NAME
) CONS
WHERE COL_CNT > ALL
(
SELECT COUNT(*)
FROM USER_IND_COLUMNS I
WHERE I.TABLE_NAME = CONS.TABLE_NAME
AND I.COLUMN_NAME IN (CNAME1, CNAME2, CNAME3, CNAME4, CNAME5, CNAME6, CNAME7, CNAME8)
AND I.COLUMN_POSITION <= CONS.COL_CNT
GROUP BY I.INDEX_NAME
);

报错信息如下： ORA-00979 不是GROUP BY表达式。

 

oracle版本：

 

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production

TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

换了一个版本竟然是OK的，查询结果如下：

 

1 MVIEW$_ADV_GC MVIEW$_ADV_GC_FK FJGID#
2 MVIEW$_ADV_AJG MVIEW$_ADV_AJG_FK RUNID#
3 MVIEW$_ADV_FJG MVIEW$_ADV_FJG_FK AJGID#
4 MVIEW$_ADV_CLIQUE MVIEW$_ADV_CLIQUE_FK RUNID#
5 MVIEW$_ADV_ROLLUP MVIEW$_ADV_ROLLUP_PFK RUNID#, PLEVELID#
6 MVIEW$_ADV_ELIGIBLE MVIEW$_ADV_ELIGIBLE_FK RUNID#
7 MVIEW$_ADV_BASETABLE MVIEW$_ADV_BASETABLE_FK QUERYID#
8 REPCAT$_AUDIT_COLUMN REPCAT$_AUDIT_COLUMN_F1 ATTRIBUTE
9 REPCAT$_AUDIT_COLUMN REPCAT$_AUDIT_COLUMN_F2 BASE_SNAME, BASE_ONAME, BASE_CONFLICT_TYPE_ID, BASE_REFERENCE_NAME
10 REPCAT$_SITE_OBJECTS REPCAT$_SITE_OBJECTS_FK1 OBJECT_TYPE_ID
11 MVIEW$_ADV_EXCEPTIONS MVIEW$_ADV_EXCEPTION_FK RUNID#
12 REPCAT$_TEMPLATE_OBJECTS REPCAT$_TEMPLATE_OBJECTS_FK3 OBJECT_TYPE
13 MVIEW$_ADV_FILTERINSTANCE MVIEW$_ADV_FILTERINSTANCE_FK RUNID#
14 REPCAT$_REFRESH_TEMPLATES REPCAT$_REFRESH_TEMPLATES_FK1 TEMPLATE_TYPE_ID
15 REPCAT$_REFRESH_TEMPLATES REPCAT$_REFRESH_TEMPLATES_FK2 TEMPLATE_STATUS_ID

 

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production

TNS for IBM/AIX RISC System/6000: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

 

 

--看来不是SQL本身的问题，而是跟ORACLE版本有关，又或者是个BUG。查询metalink，发现果然是个bug。bug no：2623003  解决方法是设置一个隐含参数"_complex_view_merging"=false

 

SQL> alter system set "_complex_view_merging" = false scope = spfile;

System altered

 

重启数据库，再次运行SQL，没有报错信息了。查询结果如下：

 

1 REGISTRY$ REGISTRY_PARENT_FK PID
2 HS$_INST_DD HS$_INST_DD_FK2 DD_TABLE_ID
3 HS$_CLASS_DD HS$_CLASS_DD_FK2 DD_TABLE_ID
4 HS$_FDS_INST HS$_FDS_INST_FK1 FDS_CLASS_ID
5 HS$_INST_CAPS HS$_INST_CAPS_FK2 CAP_NUMBER
6 HS$_CLASS_CAPS HS$_CLASS_CAPS_FK2 CAP_NUMBER