Eclipselink group by clause with trunc function

Question

I have a query which works in sqldeveloper, but not in jpql (eclipselink 2.6).

select trunc(SYS_CR_DT, 'mm'), count(trunc(SYS_CR_DT, 'mm')) from A
group by trunc(SYS_CR_DT, 'mm')
order by trunc(SYS_CR_DT, 'mm') DESC;

This works in sqldeveloper, but this is not(in jpql), saying it is not a group by expression :

"SELECT FUNCTION('TRUNC', lh.sysCreateDate, 'mm'), COUNT(FUNCTION('TRUNC', lh.sysCreateDate, 'mm')) "
        + " FROM A lh"
        + " GROUP BY FUNCTION('TRUNC', lh.sysCreateDate, 'mm')"
        + " ORDER BY FUNCTION('TRUNC', lh.sysCreateDate, 'mm') DESC";

This also works, so grouping with that function is working :

"SELECT FUNCTION('TRUNC', lh.sysCreateDate), COUNT(FUNCTION('TRUNC', lh.sysCreateDate)) "
        + " FROM A lh"
        + " GROUP BY FUNCTION('TRUNC', lh.sysCreateDate)"
        + " ORDER BY FUNCTION('TRUNC', lh.sysCreateDate) DESC";

This works also, so it is not a problem with the second parameter, but with the grouping:

"SELECT FUNCTION('TRUNC', lh.sysCreateDate, 'mm'), COUNT(FUNCTION('TRUNC', lh.sysCreateDate, 'mm')) "
        + " FROM A lh"

Long story short : When I add the second parameter, it is still a valid function call, but not working inside group by.

Any thoughts will be appreciated.

Answer 1

So I could not resolve the problem, seems like a bug for me. Anyway, I made it work. Not a nice solution, but what can I do.

CREATE OR REPLACE FUNCTION TRUNCBYMONTH(
    dateparam DATE
)
RETURN DATE
AS
BEGIN

    RETURN TRUNC(dateparam, 'mm');

END;

Calling this function instead of the original TRUNC with 'mm' extra parameter works correctly.