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语法问题：

01selectt.stat_date,02       t.species_name,03       t.species_id,04       0 jzcg_bid_price,05       (selectnvl(sum(a.bid_price), 0)06          fromvb_pr_stat27_2 a07         wherea.stock_mode_name = 'AAAAAAAA'08           anda.stat_date = t.stat_date09           anda.species_name = t.species_name10           anda.stock_mode_name = t.stock_mode_name11         groupbya.stat_date, a.species_name,a.site_id,a.org_id) gk_bid_price,12       (selectnvl(sum(a.bid_price), 0)13          fromvb_pr_stat27_2 a14         wherea.stock_mode_name = 'BBBBBBBB'15           anda.stat_date = t.stat_date16           anda.species_name = t.species_name17           anda.stock_mode_name = t.stock_mode_name18         groupbya.stat_date, a.species_name) yq_bid_price,19       (selectnvl(sum(a.bid_price), 0)20          fromvb_pr_stat27_2 a21         wherea.stock_mode_name = 'CCCCCCCC'22           anda.stat_date = t.stat_date23           anda.species_name = t.species_name24           anda.stock_mode_name = t.stock_mode_name25         groupbya.stat_date, a.species_name) jz_bid_price,26       (selectnvl(sum(a.bid_price), 0)27          fromvb_pr_stat27_2 a28         wherea.stock_mode_name = 'DDDDDDDD'29           anda.stat_date = t.stat_date30           anda.species_name = t.species_name31           anda.stock_mode_name = t.stock_mode_name32         groupbya.stat_date, a.species_name) xj_bid_price,33       (selectnvl(sum(a.bid_price), 0)34          fromvb_pr_stat27_2 a35         wherea.stock_mode_name = 'EEEEEEEE'36           anda.stat_date = t.stat_date37           anda.species_name = t.species_name38           anda.stock_mode_name = t.stock_mode_name39         groupbya.stat_date, a.species_name) dy_bid_price,40       (selectnvl(sum(a.bid_price), 0)41          fromvb_pr_stat27_2 a42         wherea.stock_mode_name = 'FFFF'43           anda.stat_date = t.stat_date44           anda.species_name = t.species_name45           anda.stock_mode_name = t.stock_mode_name46         groupbya.stat_date, a.species_name) qt_bid_price,47       t.site_id,48       t.agency_id,49       t.org_id,50       t.org_name51  fromvb_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，首先是建表语句，不用测试数据了：

01----02CREATE  TABLEpers_dinner03(04  "PER_ID"NUMBER(10) NOTNULL,05  "PERS_DINNER_COUNT"  NUMBER(3) NOTNULL,06  "PERS_DINNER_DATE"DATENOTNULL,07  "UPD_TS"DATEDEFAULT  SYSDATE NOTNULL,08  "UPD_UID"NUMBER(10),09  "PERS_DINNER_GROUP"  CHAR(1 byte) NOTNULL,10  "ID"NUMBER(10) NOTNULL,11  "STATUS"  NUMBER(1) 12    DEFAULT9 NOTNULL,13  "UCETNI_ROK"NUMBER(4) 14    DEFAULTto_number(to_char(sysdate,'YYYY')) NOTNULL,15  "UCETNI_MESIC"NUMBER(2) 16    DEFAULTto_number(to_char(sysdate,'MM')) NOTNULL,17  CONSTRAINT"PK_PERS_DINNER2"18    PRIMARYKEY("ID"),19  CONSTRAINT"UQ_PERS_DINNER2"20    UNIQUE("PER_ID", "PERS_DINNER_GROUP", "PERS_DINNER_DATE", "UCETNI_ROK")21)22LOGGING23MONITORING;

      然后一个比较复杂的查询：

view sourceprint?01select02  xx.ucetni_rok || xx.mesic asid,03  xx.ucetni_rok asrok,04  xx.mesic,05  (06    selectnvl(sum(d2.pers_dinner_count), 0) ascnt07     frompers_dinner d208    whered2.per_id = '27052'09      andd2.status in(0, 9)10      andd2.pers_dinner_group = 'U'11      andd2.ucetni_rok = xx.ucetni_rok12      andto_char(d2.pers_dinner_date, 'MM.YYYY') = xx.mesic13  ) as  suma_u14 from(15  select16    d.pers_dinner_group,17    d.ucetni_rok,18    to_char(d.pers_dinner_date, 'MM.YYYY') asmesic,19    sum(d.pers_dinner_count) ascnt20   frompers_dinner d21  whered.per_id = '112378'22    andd.status in(0,9)23  groupbyd.pers_dinner_group, d.ucetni_rok, to_char(d.pers_dinner_date, 'MM.YYYY') 24) xx;

      马上就会报： ORA-00979: not a GROUP BY expression 了。

      如果，将 _complex_view_merging 这个参数设定为 false 就可以马上得到结果。

1altersession set"_complex_view_merging"=false;

      在 10gR2 的第一个版本，也就是 10.2.0.1 没有这个问题，所以可以认为是 10.2.0.4 这个补丁包引入的BUG。

 

出处：http://www.cnblogs.com/killkill/archive/2010/08/02/1790381.html