ORA-00979: not a GROUP BY expression

Problem:

-----------

ORA-00979: not a GROUP BY expression

We have upgraded our database from 11.2.0.4 to 12.2.0.1.

After upgrade one sql got "ORA-00979: not a GROUP BY expression" error.

Solution:

-----------

It was similar to Bug 18749211 ORA-979 FROM SELECT WITH COLUMN MASKING VPD AND VIEW MERGING VPD, but we don't use VPN or anything else, only we upgraded the database.

Also, /*+ materialize */ didn't work advice as a workaround in bug document.

Then I found another bug 27170305: ORA-00979 WHEN A CASE STATEMENT IS THERE IN THE "GROUP BY" EXPRESSION 

Workaround:

---------------------

/*+ optimizer_features_enable('11.1.0.6') */

But these hints caused performance problems, we need to apply the patch if it could be done :)

So, if this patch is not available for your version like us, you need to ask to oracle support.

Now, this patch is ready for our system in a one week:)