Re: group by behavior in 11gR2
- From: Kumar Madduri <ksmadduri@xxxxxxxxx>
- To: Dominic Brooks <dombrooks@xxxxxxxxxxx>
- Date: Tue, 1 Nov 2011 06:25:34 -0700
I logged a tar for this after patch 9824198 did not resolve our issue. Per
Oracle, it is expected behavior. What 984198 would fix is spurious cases of
bug 5442206 where by ORA-979 eventhough all the columns are included in
group by clause (oracle was not willing to share the sql for this because
it was related to customer test case). The problem is to identify all
custom sql that have this 'buggy' behavior and fix them. One way is to
enable the event 979 in init.ora and monitor the alert log.
*getting the ora-979 is an expected behavior, since all the columns are not
@ included in the group by clause.
@ .
@ .
@ bug5442206 also, ineffect states that the ora-979 should always be
reported
@ when all the columns are not included in the group by clause, which was
not
@ happening when view was merged. The same was fixed in bug5520732.
@ .
@ Hence with the fix of bug5520732 applied, the ora-979 is an expected
behavior
@ and disabling the same will give the previous buggy behavior.
@ .
@ In releases prior to 11.2, the expected ora-979 was not seen, which was
the
@ buggy behavior, which was fixed as part of bug5520732.
@ .
@ disabling bug5520732 will restore the old behavior.
It seems that when an inline view is used in the query and the group
function is used then all the columns required to be grouped which was not
happening in previous version and which was considered as bug and now it
is fixed in 11.2 version *
On Thu, Oct 27, 2011 at 1:22 AM, Dominic Brooks <dombrooks@xxxxxxxxxxx>wrote:
> It's not a deliberate change in behaviour, just a bug (9824198).
>
> Cheers,
> Dominic
>
> > Date: Wed, 26 Oct 2011 13:57:43 -0700
> > Subject: group by behavior in 11gR2
> > From: ksmadduri@xxxxxxxxx
> > To: Oracle-L@xxxxxxxxxxxxx
>
> >
> > Hi
> > I noticed this in 11gR2. The query here does not mean anything, but I
> just
> > ran it to show the difference.
> > select end_date, kumar, user_name, employee_id, max(end_date)
> > from (select end_date||user_name||employee_id as kumar, end_Date,
> > user_name, employee_id from fnd_user)
> > group by end_date, user_name, employee_id
> > /
> >
> > ERROR at line 1:
> > ORA-00979: not a GROUP BY expression
> >
> > But the same query would work in 11.1.0.7 for example.
> >
> >
> > To make it work in 11.2.0.2 , I have to group by kumar as well.
> >
> > I see some workarounds in the form of changing optimizer features enabled
> > and some other _ parameters can be set/unset.But my question is why is
> this
> > change in behavior. Looks like now the 'virtual columns' also need to be
> > included in the group by clause.
> >
> >
> > Thank you
> > Kumar
> >
> >
> > --
> > //www.freelists.org/webpage/oracle-l
> >
> >
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
