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

      乍看 ORA-00979 not a GROUP BY expression 這個(gè)提示估計(jì)很快能將其定位為SQL語句寫得有問題，實(shí)際上有可能你遇到了一個(gè)Oracle的BUG，這個(gè)BUG常見于10.2.0.4這個(gè)版本（10g最后一個(gè)補(bǔ)丁版本是10.2.0.5）。

      前幾天和同事做一個(gè)應(yīng)用系統(tǒng)升級(jí)的時(shí)候遇到了這個(gè)問題，首先是一張視圖無法創(chuàng)建，仔細(xì)分析構(gòu)成視圖的查詢也沒有發(fā)現(xiàn)明顯的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;

      就是死活報(bào) ORA-00979 ，由于這個(gè)查詢涉及其他視圖，其他視圖又涉及多張表，一時(shí)沒有辦法拿到其他版本的數(shù)據(jù)庫中測(cè)試，并沒有意識(shí)到這個(gè)BUG。

      后來我同事在會(huì)話級(jí)別設(shè)定參數(shù) _complex_view_merging 為 false 之后，就沒有再報(bào) ORA-00979 了。查閱了一些相關(guān)資料，在這位仁兄的blog中找到了對(duì)這個(gè)BUG的描述，據(jù)說10.2.0.5的Fixed Bug List中能找到這個(gè)BUG，但是一直搞不到這份List。

      以下是基本上就是摘錄這位仁兄的內(nèi)容了，讓我們重現(xiàn)一下這個(gè)BUG，首先是建表語句，不用測(cè)試數(shù)據(jù)了：

----
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;

      然后一個(gè)比較復(fù)雜的查詢：

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;

      馬上就會(huì)報(bào)： ORA-00979: not a GROUP BY expression 了。

      如果，將 _complex_view_merging 這個(gè)參數(shù)設(shè)定為 false 就可以馬上得到結(jié)果。

alter session set "_complex_view_merging"=false ;

      在 10gR2 的第一個(gè)版本，也就是 10.2.0.1 沒有這個(gè)問題，所以可以認(rèn)為是 10.2.0.4 這個(gè)補(bǔ)丁包引入的BUG。