Oracle 10g 10.2.0.4的group by BUG |ORA-00979 not a GROUP BY expression|
乍看 ORA-00979 not a GROUP BY expression 这个提示估计很快能将其定位为SQL语句写得有问题，实际上有可能你遇到了一个Oracle的BUG，这个BUG常见于10.2.0.4这个版本（10g最后一个补丁版本是10.2.0.5）。
前几天和同事做一个应用系统升级的时候遇到了这个问题，首先是一张视图无法创建，仔细分析构成视图的查询也没有发现明显的SQL语法问题：
select t.stat_date,
t.species_name,
t.species_id,
0 jzcg_bid_price,
(select nvl(sum(a.bid_price), 0)
from vb_pr_stat27_2 a
where a.stock_mode_name = 'AAAAAAAA'
and a.stat_date = t.stat_date
and a.species_name = t.species_name
and a.stock_mode_name = t.stock_mode_name
group by a.stat_date, a.species_name,a.site_id,a.org_id) gk_bid_price,
(select nvl(sum(a.bid_price), 0)
from vb_pr_stat27_2 a
where a.stock_mode_name = 'BBBBBBBB'
and a.stat_date = t.stat_date
and a.species_name = t.species_name
and a.stock_mode_name = t.stock_mode_name
group by a.stat_date, a.species_name) yq_bid_price,
(select nvl(sum(a.bid_price), 0)
from vb_pr_stat27_2 a
where a.stock_mode_name = 'CCCCCCCC'
and a.stat_date = t.stat_date
and a.species_name = t.species_name
and a.stock_mode_name = t.stock_mode_name
group by a.stat_date, a.species_name) jz_bid_price,
(select nvl(sum(a.bid_price), 0)
from vb_pr_stat27_2 a
where a.stock_mode_name = 'DDDDDDDD'
and a.stat_date = t.stat_date
and a.species_name = t.species_name
and a.stock_mode_name = t.stock_mode_name
group by a.stat_date, a.species_name) xj_bid_price,
(select nvl(sum(a.bid_price), 0)
from vb_pr_stat27_2 a
where a.stock_mode_name = 'EEEEEEEE'
and a.stat_date = t.stat_date
and a.species_name = t.species_name
and a.stock_mode_name = t.stock_mode_name
group by a.stat_date, a.species_name) dy_bid_price,
(select nvl(sum(a.bid_price), 0)
from vb_pr_stat27_2 a
where a.stock_mode_name = 'FFFF'
and a.stat_date = t.stat_date
and a.species_name = t.species_name
and a.stock_mode_name = t.stock_mode_name
group by a.stat_date, a.species_name) qt_bid_price,
t.site_id,
t.agency_id,
t.org_id,
t.org_name
from vb_pr_stat27_2 t;
就是死活报 ORA-00979 ，由于这个查询涉及其他视图，其他视图又涉及多张表，一时没有办法拿到其他版本的数据库中测试，并没有意识到这个BUG。
后来我同事在会话级别设定参数 _complex_view_merging 为 false 之后，就没有再报 ORA-00979 了。查阅了一些相关资料，在这位仁兄的blog中找到了对这个BUG的描述，据说10.2.0.5的Fixed Bug List中能找到这个BUG，但是一直搞不到这份List。
以下是基本上就是摘录这位仁兄的内容了，让我们重现一下这个BUG，首先是建表语句，不用测试数据了：
----
CREATE TABLE pers_dinner
(
"PER_ID" NUMBER(10) NOT NULL,
"PERS_DINNER_COUNT" NUMBER(3) NOT NULL,
"PERS_DINNER_DATE" DATE NOT NULL,
"UPD_TS" DATE DEFAULT SYSDATE NOT NULL,
"UPD_UID" NUMBER(10),
"PERS_DINNER_GROUP" CHAR(1 byte) NOT NULL,
"ID" NUMBER(10) NOT NULL,
"STATUS" NUMBER(1)
DEFAULT 9 NOT NULL,
"UCETNI_ROK" NUMBER(4)
DEFAULT to_number(to_char(sysdate,'YYYY')) NOT NULL,
"UCETNI_MESIC" NUMBER(2)
DEFAULT to_number(to_char(sysdate,'MM')) NOT NULL,
CONSTRAINT "PK_PERS_DINNER2"
PRIMARY KEY("ID"),
CONSTRAINT "UQ_PERS_DINNER2"
UNIQUE("PER_ID", "PERS_DINNER_GROUP", "PERS_DINNER_DATE", "UCETNI_ROK")
)
LOGGING
MONITORING;
然后一个比较复杂的查询：
select
xx.ucetni_rok || xx.mesic as id,
xx.ucetni_rok as rok,
xx.mesic,
(
select nvl(sum(d2.pers_dinner_count), 0) as cnt
from pers_dinner d2
where d2.per_id = '27052'
and d2.status in (0, 9)
and d2.pers_dinner_group = 'U'
and d2.ucetni_rok = xx.ucetni_rok
and to_char(d2.pers_dinner_date, 'MM.YYYY') = xx.mesic
) as suma_u
from (
select
d.pers_dinner_group,
d.ucetni_rok,
to_char(d.pers_dinner_date, 'MM.YYYY') as mesic,
sum(d.pers_dinner_count) as cnt
from pers_dinner d
where d.per_id = '112378'
and d.status in (0,9)
group by d.pers_dinner_group, d.ucetni_rok, to_char(d.pers_dinner_date, 'MM.YYYY')
) xx;
马上就会报： ORA-00979: not a GROUP BY expression 了。
如果，将 _complex_view_merging 这个参数设定为 false 就可以马上得到结果。
alter session set "_complex_view_merging"=false ;
在 10gR2 的第一个版本，也就是 10.2.0.1 没有这个问题，所以可以认为是 10.2.0.4 这个补丁包引入的BUG。
