Incorrect SQL generated when removing element from a collection when there is a ManyToAny mapping

Description

I've been experiencing an interesting Hibernate issue in my application relating to removing an entity from a collection where there is a ManyToAny mapping from the entity that contains the collection to the interface that defines the members in the collection.

Consider the following mapping from the class "ColorGroup" for a set of "GroupMember"s:

There is also a GroupMembership table that looks like this:

group_id is foreign key'ed to the ColorGroup table and member_id is foreign key'ed to a GroupMember table.

If multiple items (GroupMembers) are added to the "members" set in "ColorGroup", and then one is removed, the following error is encountered:

2012-06-18 16:39:30,685 DEBUG [main] [org.hibernate.SQL] delete from GroupMembership where group_id=? and member_id=?
Hibernate: delete from GroupMembership where group_id=? and member_id=?
2012-06-18 16:39:30,695 WARN [main] [o.h.u.JDBCExceptionReporter] SQL Error: 0, SQLState: S1009
2012-06-18 16:39:30,695 ERROR [main] [o.h.u.JDBCExceptionReporter] Parameter index out of range (3 > number of parameters, which is 2).
2012-06-18 16:39:30,706 ERROR [main] [o.h.e.d.AbstractFlushingEventListener] Could not synchronize database state with session
org.hibernate.exception.GenericJDBCException: could not delete collection rows: [scratch.hibernate.ColorGroup.members#1]
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:140) ~[hibernate-core-3.6.0.jar:3.6.0.Final]
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:128) ~[hibernate-core-3.6.0.jar:3.6.0.Final]
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) ~[hibernate-core-3.6.0.jar:3.6.0.Final]
at org.hibernate.persister.collection.AbstractCollectionPersister.deleteRows(AbstractCollectionPersister.java:1352) ~[hibernate-core-3.6.0.jar:3.6.0.Final]
at org.hibernate.action.CollectionUpdateAction.execute(CollectionUpdateAction.java:84) ~[hibernate-core-3.6.0.jar:3.6.0.Final]
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:273) ~[hibernate-core-3.6.0.jar:3.6.0.Final]
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:265) ~[hibernate-core-3.6.0.jar:3.6.0.Final]
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:187) ~[hibernate-core-3.6.0.jar:3.6.0.Final]
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321) ~[hibernate-core-3.6.0.jar:3.6.0.Final]
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51) [hibernate-core-3.6.0.jar:3.6.0.Final]
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1216) [hibernate-core-3.6.0.jar:3.6.0.Final]
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:383) [hibernate-core-3.6.0.jar:3.6.0.Final]
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:133) [hibernate-core-3.6.0.jar:3.6.0.Final]
at scratch.hibernate.MappingTest.testBasicUsage(MappingTest.java:77) [bin/:na]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.6.0_17]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) ~[na:1.6.0_17]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) ~[na:1.6.0_17]
at java.lang.reflect.Method.invoke(Method.java:597) ~[na:1.6.0_17]
at junit.framework.TestCase.runTest(TestCase.java:168) [junit-4.6.jar:na]
at junit.framework.TestCase.runBare(TestCase.java:134) [junit-4.6.jar:na]
at junit.framework.TestResult$1.protect(TestResult.java:110) [junit-4.6.jar:na]
at junit.framework.TestResult.runProtected(TestResult.java:128) [junit-4.6.jar:na]
at junit.framework.TestResult.run(TestResult.java:113) [junit-4.6.jar:na]
at junit.framework.TestCase.run(TestCase.java:124) [junit-4.6.jar:na]
at junit.framework.TestSuite.runTest(TestSuite.java:232) [junit-4.6.jar:na]
at junit.framework.TestSuite.run(TestSuite.java:227) [junit-4.6.jar:na]
at org.junit.internal.runners.JUnit38ClassRunner.run(JUnit38ClassRunner.java:91) [junit-4.6.jar:na]
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:49) [.cp/:na]
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38) [.cp/:na]
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467) [.cp/:na]
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683) [.cp/:na]
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390) [.cp/:na]
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197) [.cp/:na]
Caused by: java.sql.SQLException: Parameter index out of range (3 > number of parameters, which is 2).
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073) ~[mysql-connector-java-5.1.20.jar:na]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987) ~[mysql-connector-java-5.1.20.jar:na]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982) ~[mysql-connector-java-5.1.20.jar:na]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927) ~[mysql-connector-java-5.1.20.jar:na]
at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3754) ~[mysql-connector-java-5.1.20.jar:na]
at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3738) ~[mysql-connector-java-5.1.20.jar:na]
at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3780) ~[mysql-connector-java-5.1.20.jar:na]
at com.mysql.jdbc.PreparedStatement.setLong(PreparedStatement.java:3796) ~[mysql-connector-java-5.1.20.jar:na]
at org.hibernate.type.descriptor.sql.BigIntTypeDescriptor$1.doBind(BigIntTypeDescriptor.java:52) ~[hibernate-core-3.6.0.jar:3.6.0.Final]
at org.hibernate.type.descriptor.sql.BasicBinder.bind(BasicBinder.java:89) ~[hibernate-core-3.6.0.jar:3.6.0.Final]
at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:282) ~[hibernate-core-3.6.0.jar:3.6.0.Final]
at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:277) ~[hibernate-core-3.6.0.jar:3.6.0.Final]
at org.hibernate.type.AbstractSingleColumnStandardBasicType.nullSafeSet(AbstractSingleColumnStandardBasicType.java:85) ~[hibernate-core-3.6.0.jar:3.6.0.Final]
at org.hibernate.type.AnyType.nullSafeSet(AnyType.java:162) ~[hibernate-core-3.6.0.jar:3.6.0.Final]
at org.hibernate.persister.collection.AbstractCollectionPersister.writeElementToWhere(AbstractCollectionPersister.java:844) ~[hibernate-core-3.6.0.jar:3.6.0.Final]
at org.hibernate.persister.collection.AbstractCollectionPersister.deleteRows(AbstractCollectionPersister.java:1316) ~[hibernate-core-3.6.0.jar:3.6.0.Final]
... 29 common frames omitted

It seems that the correct delete sql is generated: delete from GroupMembership where group_id=? and member_id=?

However, the binding does not appear to be functioning properly because Hibernate is trying to bind a parameter in the third parameter position despite there only being positions for two parameters. Here is an excerpt from a log when more detailed sql logging is enabled:

[org.hibernate.type.descriptor.sql.BasicBinder] binding parameter
[1] as [BIGINT] - 15 [o.h.type.descriptor.sql.BasicBinder] binding
parameter [3] as [BIGINT] - 20 [o.h.util.JDBCExceptionReporter]
Parameter index out of range (3 number of parameters, which is 2).

Tracing through the hibernate code, I noticed in AbstractCollectionPersister.deleteRows() that initially the key ("group_id" in this case) is written to the prepared statement for the deletion in writeKey(). Subsequently, the location of the next parameter to write to the prepared statement is incremented, which is correct. Next, Hibernate goes to write the member_id element to the prepared statement in writeElementToWhere(). Interestingly, the counter is incremented again before the element is written to the prepared statement, thus causing the error.

I've included a zip file that contains a test case (readme, java code, hibernate config) to generate this error.

Environment

hibernate-core: 3.6.0
hibernate-infinispan: 3.6.0
hibernate-validator: 4.1.0

MySQL 5.5.11

Activity

Show:
Brett Meyer
April 7, 2014, 5:48 PM

In an effort to clean up, in bulk, tickets that are most likely out of date, we're transitioning all ORM 3 tickets to an "Awaiting Test Case" state. Please see http://in.relation.to/Bloggers/HibernateORMJIRAPoliciesAndCleanUpTactics for more information.

If this is still a legitimate bug in ORM 4, please provide either a test case that reproduces it or enough detail (entities, mappings, snippets, etc.) to show that it still fails on 4. If nothing is received within 3 months or so, we'll be automatically closing them.

Thank you!

Brett Meyer
July 8, 2014, 3:11 PM

Bulk rejecting stale issues. If this is still a legitimate issue on ORM 4, feel free to comment and attach a test case. I'll address responses case-by-case. Thanks!

Heron Berton
July 27, 2020, 8:11 PM

I'm having the same problem (hibernate-core 5.2.7.Final), follows test case from the previous post for validation, was any correction made in future versions?

Assignee

Unassigned

Reporter

David Eitel

Fix versions

None

Labels

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Major
Configure