Following query (I have simplified the query for the demonstration purpose) was running happily within Oracle 10gR2:
SQL>
SQL> select * from ( select TRUNC(dt,'MM')
2 from test
3 group by TRUNC(dt,'mm'));
TRUNC(DT,
---------
01-JAN-13
SQL>
However, the same query started to throws an error (ORA-00979) when executed in Oracle 11gR2 (11.2.0.2):
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL>
SQL> select * from ( select TRUNC(dt,'MM')
from test
group by TRUNC(dt,'mm'));
2 3 select * from ( select TRUNC(dt,'MM')
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
SQL>
At this point I normally do a search on My Oracle Support (MOS) to see if I get some hits pertaining to this problem and found the following bug information:
Bug 11067251 - False ORA-979 with TRUNC(date, format) or ROUND(date, format) - superceded [ID 11067251.8]
Apparently, you hit the bug when you use either TRUNC or ROUND functions in an inline view. Executing the same query with little modification (removing inline view) in 11.2.0.2 was however successful.
SQL> select TRUNC(dt,'MM')
from test
group by TRUNC(dt,'mm');
2 3
TRUNC(DT,
---------
01-JAN-13
SQL>
The above bug confirms that 11.2.0.2 is affected and proposes following two workarounds:
1) Use NO_MERGE hint or
2) Disable view merging "_simple_view_merging=false"
As it was not possible to rewrite the application at this point, so we disabled view merging at the system level. Well, disabling view merging at the system level might appear as a bad choice but I think it is a right decision at this time. We will soon be upgrading this database to 11.2.0.3. This will kill two birds with one stone, a) bug fix and b) upgrading to the latest patch level (who knows what new bugs are waiting for us ???).
References:
- My Oracle Support: Bug 11067251 - False ORA-979 with TRUNC(date, format) or ROUND(date, format) - superceded [ID 11067251.8]