group by behavior in 11gR2

From: Kumar Madduri <>
Date: Wed, 26 Oct 2011 13:57:43 -0700
Message-ID: <>

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 for example.

To make it work in , 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

Received on Wed Oct 26 2011 - 15:57:43 CDT

Original text of this message