[JIRA] (HHH-14045) Unable to group by "to_char" function
by Jacek Ślimok (JIRA)
Jacek Ślimok ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *created* an issue
Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiZDYxMmEzMDk4... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-14045?atlOrigin=eyJpIjoiZDYxMm... ) HHH-14045 ( https://hibernate.atlassian.net/browse/HHH-14045?atlOrigin=eyJpIjoiZDYxMm... ) Unable to group by "to_char" function ( https://hibernate.atlassian.net/browse/HHH-14045?atlOrigin=eyJpIjoiZDYxMm... )
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%... )
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-usin...
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=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-14045#add-comment?atlOrigin=ey... )
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.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100127- sha1:33b9894 )
5 years, 5 months
[JIRA] (HHH-14043) Two concurrent element shifts in a list damage database integrity.
by Björn Zurmaar (JIRA)
Björn Zurmaar ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5ecd86c... ) *updated* an issue
Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiNjQyNGZiOTk4... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-14043?atlOrigin=eyJpIjoiNjQyNG... ) HHH-14043 ( https://hibernate.atlassian.net/browse/HHH-14043?atlOrigin=eyJpIjoiNjQyNG... ) Two concurrent element shifts in a list damage database integrity. ( https://hibernate.atlassian.net/browse/HHH-14043?atlOrigin=eyJpIjoiNjQyNG... )
Change By: Björn Zurmaar ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5ecd86c... )
*Problem description:* Assume you have an entity Foo having a list of Bars. It's a unidirectional OneToMany mapping with a @JoinColumn annotation. When shifting element in the lists from within two different transactions starting concurrently the database gets inconsistent and elements disappear when the range of affected elements overlaps.
*Example:* when you have a list with three elements and shift element 0 to index 1 in one and element 2 to 1 in another transaction then you'll end up with a table with two indices 1 and one index 2. Reloading the collection in hibernate in a new transaction the element at index 0 is then 0. More generally speaking arbitrary list elements may vanish in this scenario when working with bigger lists and other elements to be shifted. There is also no way to reinsert the vanished element into the collection unless you have it cached somewhere else. Here is the relevant code from the attached reproducer.
{code:java}// Step 1: We create a Foo containing three bars and persist it.
EntityManager entityManager = entityManagerFactory.createEntityManager();
entityManager.getTransaction().begin();
Foo foo = new Foo();
foo.bars.add(new Bar("bar1"));
foo.bars.add(new Bar("bar2"));
foo.bars.add(new Bar("bar3"));
entityManager.persist(foo);
entityManager.getTransaction().commit();
entityManager.close();
// Step 2: We start two transactions reading the Foo created in step 1.
EntityManager e1 = entityManagerFactory.createEntityManager();
e1.getTransaction().begin();
Foo foo1 = e1.find(Foo.class,"foo");
EntityManager e2 = entityManagerFactory.createEntityManager();
e2.getTransaction().begin();
Foo foo2 = e2.find(Foo.class,"foo");
// In transaction 1 we move element 0 to index 1 and commit.
Bar bar1 = foo1.bars.remove(0);
foo1.bars.add(1,bar1);
e1.getTransaction().commit();
e1.close();
// In transaction 2 we move element 2 to index 1 and commit.
Bar bar2 = foo2.bars.remove(2);
foo2.bars.add(1,bar2);
e2.getTransaction().commit();
e2.close();
// Now we load Foo again and log its content.
EntityManager e3 = entityManagerFactory.createEntityManager();
e3.getTransaction().begin();
Foo foo3 = e3.find(Foo.class,"foo");
Logger.getLogger(getClass()).info(foo3);
// We also log the indices found in the table.
for(Object row : e3.createNativeQuery("SELECT bars_ORDER FROM bar;").getResultList()) {
Logger.getLogger(getClass()).info(row);
}
e3.getTransaction().commit();
e3.close(); {code}
The output of the logging statements I inserted is
{noformat}2020-05-27 12:37:43 INFO JPAUnitTestCase:68 - [null, bar3, bar2]
2020-05-27 12:37:51 INFO JPAUnitTestCase:72 - 1
2020-05-27 12:37:52 INFO JPAUnitTestCase:72 - 2
2020-05-27 12:37:53 INFO JPAUnitTestCase:72 - 1{noformat}
*Analysis:* This behavior is caused by the way hibernate rewrites the list's indices. Only affected indices will be updated. When the shift affects n list elements only the indices of the n affected elements will be updated. Hibernate generates 2*n UPDATE statements for this. The first n to set all the affected indices to null, the second n statements to write the new index to the rows. This is a perfectly fine and clever optimization in a single transactioned / threaded environment but fails when multiple transactions are involved. That is because the transaction manager does not know about lists and applies both changes consecutively. It does not have a chance to detect the resulting conflict because the changes are incomplete from a transactional point of view.
*Suggestion:* Hibernate should update _all_ list indices when it detects that elements of a collection were moved. Regardless of the transaction interleaving this would guarantee that database remains in a consistent state. The transaction that is closed last would then define the state without damaging database consistency.
I have a attached a unit test exposing the undesired behavior. Sorry for the two attachments. Despite the name they are the same.
( https://hibernate.atlassian.net/browse/HHH-14043#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-14043#add-comment?atlOrigin=ey... )
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.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100127- sha1:33b9894 )
5 years, 5 months