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

Oracle 10g 10.2.0.4的group by BUG |ORA-00979 not a GROUP BY expression|