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

At first glance, this hint of ORA-00979 not a GROUP by expression is expected to quickly locate it as a SQL statement with a problem, in fact, you may encounter an Oracle bug, which is common in 10.2.0.4 version (10g The last patch version is 10.2.0.5).

A few days ago and colleagues to do an application system upgrade encountered this problem, first of all, a view can not be created, carefully analyze the composition of the view of the query did not find obvious SQL syntax problems:

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

Is the death of the newspaper ORA-00979, because this query involves other views, other views involved more than one table, in the moment there is no way to get the other version of the database test, and did not realize the bug.

Later, when my colleague set the parameter _complex_view_merging to false at the session level, no more ORA-00979 was reported. Read some of the relevant information, in this man's blog to find the description of the bug, it is said that 10.2.0.5 fixed bug list can find this bug, but has not been able to get this list.

Here is basically an excerpt of this guy's content, let us reproduce the bug, the first is to build a table statement, do not test the data:

----02CREATE tablepers_dinner03 (per_id) notnull,05 "Pers_dinner_count" number (3) notnull,06 "Pers_din Ner_date "datenotnull,07" Upd_ts "Datedefault sysdate notnull,08" upd_uid "number (Ten)," Pers_dinner_group "CHAR (1 byte ) notnull,10 "ID" number (notnull,11 "STATUS" number (1) DEFAULT9 notnull,13 "Ucetni_rok" number (4) defaul Tto_number (To_char (sysdate, ' YYYY ')) notnull,15 "Ucetni_mesic" number (2) defaultto_number (To_char (sysdate, ' MM ')) notnull,17 CONSTRAINT "Pk_pers_dinner2" PRIMARYKEY ("ID"), CONSTRAINT "Uq_pers_dinner2" ("per_id", "Pers_") Dinner_group "," Pers_dinner_date "," Ucetni_rok ") 22logging23monitoring;

Then a more complex query:

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 ') xx;

It will be reported immediately: Ora-00979:not a GROUP by expression.

If you set the _complex_view_merging parameter to False, you can get the result immediately.

1altersession set "_complex_view_merging" =FALSE;

In the first version of 10GR2, that is, 10.2.0.1 does not have this problem, so can be considered as 10.2.0.4 this patch package introduced bug.

Source: 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|