[hibernate-issues] [JIRA] (HHH-14045) Unable to group by "to_char" function

[Jacek Ślimok (JIRA)]

Jacek Ślimok ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%3A92c0759e-ac5e-45c8-9d57-2ec9e9936e57 ) *created* an issue

Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiZDYxMmEzMDk4MDc4NDg1NThmNjhjOTFjYjQxODEzNTkiLCJwIjoiaiJ9 ) / Bug ( https://hibernate.atlassian.net/browse/HHH-14045?atlOrigin=eyJpIjoiZDYxMmEzMDk4MDc4NDg1NThmNjhjOTFjYjQxODEzNTkiLCJwIjoiaiJ9 ) HHH-14045 ( https://hibernate.atlassian.net/browse/HHH-14045?atlOrigin=eyJpIjoiZDYxMmEzMDk4MDc4NDg1NThmNjhjOTFjYjQxODEzNTkiLCJwIjoiaiJ9 ) Unable to group by "to_char" function ( https://hibernate.atlassian.net/browse/HHH-14045?atlOrigin=eyJpIjoiZDYxMmEzMDk4MDc4NDg1NThmNjhjOTFjYjQxODEzNTkiLCJwIjoiaiJ9 )

Issue Type: Bug Affects Versions: 5.3.9 Assignee: Unassigned Components: hibernate-core Created: 28/May/2020 05:00 AM Environment: PostgreSQL, Spring Boot Priority: Major Reporter: Jacek Ślimok ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%3A92c0759e-ac5e-45c8-9d57-2ec9e9936e57 )

My final goal is to group entities by a date field with any date interval (such as year, month or hours). This has to be done using Criteria API due to a large amount of surrounding code in the project already utilizing this way of querying for entities. I've already described my case in more detail in the following StackOverflow question: https://stackoverflow.com/questions/61984604/group-by-date-intervals-using-jpas-criteria-api

Because as in example of grouping by month, I need groups like "2019-05" and "2020-05" (same month) to be separate, a simple "MONTH" function is not sufficient. Therefore I'm trying to achieve this using "to_char", hoping to get a query that would look like this:

SELECT TO_CHAR(sell_date, 'YYYY-MM' ) AS alias1 FROM receipts GROUP BY alias1;

My code looks like this:

public void test() {
       CriteriaBuilder cb = em.getCriteriaBuilder();
       CriteriaQuery< Object []> query = cb.createQuery( Object [].class);
       Root<?> root = query.from(DbReceipt.class);
       Expression<?> expr = cb.function( "to_char" , String.class, root.get( "sellDate" ), cb.literal( "YYYY-MM" ));

       query.groupBy(expr);
       query.multiselect(expr);

       TypedQuery< Object []> typedQuery = em.createQuery(query);
       List< Object []> resultList = typedQuery.getResultList();
}

This code results in the following query and exception:

Hibernate: select to_char(dbreceipt0_.sell_date, ?) as col_0_0_ from receipts dbreceipt0_ group by to_char(dbreceipt0_.sell_date, ?)
24-05-2020 12:16:30.071 [http-nio-1234-exec-5] WARN  o.h.e.jdbc.spi.SqlExceptionHelper.logExceptions - SQL Error: 0, SQLState: 42803
24-05-2020 12:16:30.071 [http-nio-1234-exec-5] ERROR o.h.e.jdbc.spi.SqlExceptionHelper.logExceptions - ERROR: column "dbreceipt0_.sell_date" must appear in the GROUP BY clause or be used in an aggregate function

which to me seems to happen due to the fact that the whole expression has been put into both "select" and "group by" sections of the query. Is there a way to force alias to be used instead using the "groupBy" method? Is there a way around the issue?

( https://hibernate.atlassian.net/browse/HHH-14045#add-comment?atlOrigin=eyJpIjoiZDYxMmEzMDk4MDc4NDg1NThmNjhjOTFjYjQxODEzNTkiLCJwIjoiaiJ9 ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-14045#add-comment?atlOrigin=eyJpIjoiZDYxMmEzMDk4MDc4NDg1NThmNjhjOTFjYjQxODEzNTkiLCJwIjoiaiJ9 )

Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.core&referrer=utm_source%3DNotificationLink%26utm_medium%3DEmail ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailNotificationLink&mt=8 ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100127- sha1:33b9894 )
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jboss.org/pipermail/hibernate-issues/attachments/20200528/407e0e23/attachment-0001.html