Re: group by behavior in 11gR2

From: Kumar Madduri <ksmadduri_at_gmail.com>
Date: Tue, 1 Nov 2011 06:25:34 -0700
Message-ID: <CAHDOOG4vBn0w5t7ZCq_BHZpqAgcpxVWRYqvk2vT-P0gsx97BfQ_at_mail.gmail.com>

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

_at_ included in the group by clause.
_at_ .
_at_ .
_at_ bug5442206 also, ineffect states that the ora-979 should always be

reported
_at_ when all the columns are not included in the group by clause, which was not

_at_ happening when view was merged. The same was fixed in bug5520732.
_at_ .
_at_ Hence with the fix of bug5520732 applied, the ora-979 is an expected

behavior

_at_ and disabling the same will give the previous buggy behavior.
_at_ .
_at_ In releases prior to 11.2, the expected ora-979 was not seen, which was

the

_at_ buggy behavior, which was fixed as part of bug5520732.
_at_ .
_at_ 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_at_hotmail.com>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_at_gmail.com
> > To: Oracle-L_at_freelists.org
>
> >
> > 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
> >
> >
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Nov 01 2011 - 08:25:34 CDT