Reordering items in List throws a constraint violation

Description

I want Hibernate to manage the order of a list. The time the list is generated, everything seems to work fine. The tables look like I would expect them to look, the items are retrieved in the order they have been saved.
The problem occurs when reordering the item list (removing item and inserting it at another place in the same list). I get the following error (first two lines are test output from my program):
{{MOVE CHILD DATA
One / Two / Three / Four / Five /
Hibernate: delete from ParentData_ChildData where ParentData_id=? and listOrder=?
Hibernate: update ParentData_ChildData set children_id=? where ParentData_id=? and listOrder=?
Mär 12, 2017 11:13:26 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: -104, SQLState: 23505
Mär 12, 2017 11:13:26 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: integrity constraint violation: unique constraint or index violation; UK_9TWN3CBE7SO94343AOP2QKCTC table: PARENTDATA_CHILDDATA
Mär 12, 2017 11:13:26 PM org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl release
INFO: HHH000010: On release of batch it still contained JDBC statements
javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute statement
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:147)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:155)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:162)
at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1413)
at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1393)
at net.upf.hibtest.HibTest.main(HibTest.java:56)}}

The third line indicates that hibernate tries to set a new children_id for an already existing listOrder position before removing this children_id at the previous listOrder postion and thus duplicationg this id.
Tried two databases (MariaDB on server and local HSQLDB), both gave the same results.

I set up a very basic testcase that can reproduce the behaviour. It consits of three files: parent-entity, child-entity and main()-method. I put my testcase in the attached zip, also containing the hibernate.cfg.xml. Hope this helps to reproduce the problem or see where I got something wrong.
Additional info: I always want to go from parent to child, so I didn't put a reference to the parent into the child (which should be OK, according to some documentation I found) and I understand, that this might not be the fastest solution on earth - but it should at least work somehow.

I kept this short and hope this bug report has all necessary information - for more explanation you also find my question here: https://forum.hibernate.org/viewtopic.php?f=1&t=1044101

I mark this as a blocker to me, since it simply blocks me from getting the task done.

PS: Sorry for any confusion - I uploaded a second src.zip which should make it easier to use the hibernate.cfg.xml file. A also was testing around with the name of the OrderColumn and left the wrong name there in the first version. I can't delete it, so please just ignore the first src.zip

Environment

Java 1.8.0_65, Hibernate, MariaDB or HSQLDB

Assignee

Andrea Boriero

Reporter

Uwe

Fix versions

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Blocker
Configure