<style>
/* Changing the layout to use less space for mobiles */
@media screen and (max-device-width: 480px), screen and (-webkit-min-device-pixel-ratio: 2) {
#email-body { min-width: 30em !important; }
#email-page { padding: 8px !important; }
#email-banner { padding: 8px 8px 0 8px !important; }
#email-avatar { margin: 1px 8px 8px 0 !important; padding: 0 !important; }
#email-fields { padding: 0 8px 8px 8px !important; }
#email-gutter { width: 0 !important; }
}
</style>
<div id="email-body">
<table id="email-wrap" align="center" border="0" cellpadding="0" cellspacing="0" style="background-color:#f0f0f0;color:#000000;width:100%;">
<tr valign="top">
<td id="email-page" style="padding:16px !important;">
<table align="center" border="0" cellpadding="0" cellspacing="0" style="background-color:#ffffff;border:1px solid #bbbbbb;color:#000000;width:100%;">
<tr valign="top">
<td bgcolor="#3e4c4e" style="background-color:#3e4c4e;color:#ffffff;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;line-height:1;"><img src="https://www.jboss.org/dms/hibernate/images/jira/jiraheader_hibernate.png" alt="" style="vertical-align:top;" /></td>
</tr><tr valign="top">
<td id="email-banner" style="padding:32px 32px 0 32px;">
<table align="left" border="0" cellpadding="0" cellspacing="0" width="100%" style="width:100%;">
<tr valign="top">
<td style="color:#505050;font-family:Arial,FreeSans,Helvetica,sans-serif;padding:0;">
<img id="email-avatar" src="https://hibernate.onjira.com/secure/useravatar?avatarId=10162" alt="" height="48" width="48" border="0" align="left" style="padding:0;margin: 0 16px 16px 0;" />
<div id="email-action" style="padding: 0 0 8px 0;font-size:12px;line-height:18px;">
<a class="user-hover" rel="davide" id="email_davide" href="https://hibernate.onjira.com/secure/ViewProfile.jspa?name=davide" style="color:#6c797f;">David Eitel</a>
created <img src="https://hibernate.onjira.com/images/icons/bug.gif" height="16" width="16" border="0" align="absmiddle" alt="Bug"> <a style='color:#6c797f;text-decoration:none;' href='https://hibernate.onjira.com/browse/HHH-7397'>HHH-7397</a>
</div>
<div id="email-summary" style="font-size:16px;line-height:20px;padding:2px 0 16px 0;">
<a style='color:#6c797f;text-decoration:none;' href='https://hibernate.onjira.com/browse/HHH-7397'><strong>Incorrect SQL generated when removing element from a collection when there is a ManyToAny mapping</strong></a>
</div>
</td>
</tr>
</table>
</td>
</tr>
<tr valign="top">
<td id="email-fields" style="padding:0 32px 32px 32px;">
<table border="0" cellpadding="0" cellspacing="0" style="padding:0;text-align:left;width:100%;" width="100%">
<tr valign="top">
<td id="email-gutter" style="width:64px;white-space:nowrap;"></td>
<td>
<table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr valign="top">
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 10px 10px 0;white-space:nowrap;">
<strong style="font-weight:normal;color:#505050;">Issue Type:</strong>
</td>
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 0 10px 0;width:100%;">
<img src="https://hibernate.onjira.com/images/icons/bug.gif" height="16" width="16" border="0" align="absmiddle" alt="Bug"> Bug
</td>
</tr> <tr valign="top">
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 10px 10px 0;white-space:nowrap;">
<strong style="font-weight:normal;color:#505050;">Affects Versions:</strong>
</td>
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 0 10px 0;width:100%;">
3.6.0 </td>
</tr>
<tr valign="top">
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 10px 10px 0;white-space:nowrap;">
<strong style="font-weight:normal;color:#505050;">Assignee:</strong>
</td>
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 0 10px 0;width:100%;">
Unassigned </td>
</tr> <tr valign="top">
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 10px 10px 0;white-space:nowrap;">
<strong style="font-weight:normal;color:#505050;">Attachments:</strong>
</td>
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 0 10px 0;width:100%;">
hibernate_test_case.zip </td>
</tr>
<tr valign="top">
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 10px 10px 0;white-space:nowrap;">
<strong style="font-weight:normal;color:#505050;">Components:</strong>
</td>
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 0 10px 0;width:100%;">
core </td>
</tr>
<tr valign="top">
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 10px 10px 0;white-space:nowrap;">
<strong style="font-weight:normal;color:#505050;">Created:</strong>
</td>
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 0 10px 0;width:100%;">
18/Jun/12 4:45 PM
</td>
</tr> <tr valign="top">
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 10px 10px 0;white-space:nowrap;">
<strong style="font-weight:normal;color:#505050;">Description:</strong>
</td>
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 0 10px 0;width:100%;">
<p style='margin-top:0;margin-bottom:10px;'>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.</p>
<p style='margin-top:0;margin-bottom:10px;'>Consider the following mapping from the class "ColorGroup" for a set of "GroupMember"s:</p>
<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<pre class="code-java">@AnyMetaDef(
name = <span class="code-quote">"GroupMember"</span>,
idType = <span class="code-quote">"<span class="code-object">long</span>"</span>,
metaType = <span class="code-quote">"string"</span>,
metaValues = {
@MetaValue(value = <span class="code-quote">"BrassMember"</span>, targetEntity = BrassMember.class),
@MetaValue(value = <span class="code-quote">"SilverMember"</span>, targetEntity = SilverMember.class)})
@ManyToAny(metaDef = <span class="code-quote">"GroupMember"</span>, metaColumn = @Column(name = <span class="code-quote">"member_type"</span>), fetch = FetchType.LAZY)
@JoinTable(name = <span class="code-quote">"GroupMembership"</span>,
joinColumns = @JoinColumn(name = <span class="code-quote">"group_id"</span>, nullable = <span class="code-keyword">false</span>),
inverseJoinColumns = @JoinColumn(name = <span class="code-quote">"member_id"</span>, nullable = <span class="code-keyword">false</span>)
)
<span class="code-keyword">private</span> Set<GroupMember members;</pre>
</div></div>
<p style='margin-top:0;margin-bottom:10px;'>There is also a GroupMembership table that looks like this:</p>
<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<pre class="code-sql">CREATE TABLE GroupMembership (
group_id bigint(20) NOT NULL,
member_id bigint(20) NOT NULL,
member_type varchar(100) NOT NULL,
CONSTRAINT GroupMembership_PK PRIMARY KEY (group_id, member_id, member_type),
CONSTRAINT GroupMembership_member_id_FK FOREIGN KEY (member_id) REFERENCES GroupMember (groupMember_id),
CONSTRAINT GroupMembership_group_id_FK FOREIGN KEY (group_id) REFERENCES ColorGroup (id)
);</pre>
</div></div>
<p style='margin-top:0;margin-bottom:10px;'>group_id is foreign key'ed to the ColorGroup table and member_id is foreign key'ed to a GroupMember table.</p>
<p style='margin-top:0;margin-bottom:10px;'>If multiple items (GroupMembers) are added to the "members" set in "ColorGroup", and then one is removed, the following error is encountered:</p>
<blockquote>
<p style='margin-top:0;margin-bottom:10px;'>2012-06-18 16:39:30,685 DEBUG <span class="error">[main]</span> <span class="error">[org.hibernate.SQL]</span> delete from GroupMembership where group_id=? and member_id=?<br/>
Hibernate: delete from GroupMembership where group_id=? and member_id=?<br/>
2012-06-18 16:39:30,695 WARN <span class="error">[main]</span> <span class="error">[o.h.u.JDBCExceptionReporter]</span> SQL Error: 0, SQLState: S1009<br/>
2012-06-18 16:39:30,695 ERROR <span class="error">[main]</span> <span class="error">[o.h.u.JDBCExceptionReporter]</span> Parameter index out of range (3 > number of parameters, which is 2).<br/>
2012-06-18 16:39:30,706 ERROR <span class="error">[main]</span> <span class="error">[o.h.e.d.AbstractFlushingEventListener]</span> Could not synchronize database state with session<br/>
org.hibernate.exception.GenericJDBCException: could not delete collection rows: <a href="#1">scratch.hibernate.ColorGroup.members#1</a><br/>
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:140) ~<span class="error">[hibernate-core-3.6.0.jar:3.6.0.Final]</span><br/>
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:128) ~<span class="error">[hibernate-core-3.6.0.jar:3.6.0.Final]</span><br/>
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) ~<span class="error">[hibernate-core-3.6.0.jar:3.6.0.Final]</span><br/>
at org.hibernate.persister.collection.AbstractCollectionPersister.deleteRows(AbstractCollectionPersister.java:1352) ~<span class="error">[hibernate-core-3.6.0.jar:3.6.0.Final]</span><br/>
at org.hibernate.action.CollectionUpdateAction.execute(CollectionUpdateAction.java:84) ~<span class="error">[hibernate-core-3.6.0.jar:3.6.0.Final]</span><br/>
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:273) ~<span class="error">[hibernate-core-3.6.0.jar:3.6.0.Final]</span><br/>
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:265) ~<span class="error">[hibernate-core-3.6.0.jar:3.6.0.Final]</span><br/>
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:187) ~<span class="error">[hibernate-core-3.6.0.jar:3.6.0.Final]</span><br/>
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321) ~<span class="error">[hibernate-core-3.6.0.jar:3.6.0.Final]</span><br/>
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51) <span class="error">[hibernate-core-3.6.0.jar:3.6.0.Final]</span><br/>
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1216) <span class="error">[hibernate-core-3.6.0.jar:3.6.0.Final]</span><br/>
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:383) <span class="error">[hibernate-core-3.6.0.jar:3.6.0.Final]</span><br/>
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:133) <span class="error">[hibernate-core-3.6.0.jar:3.6.0.Final]</span><br/>
at scratch.hibernate.MappingTest.testBasicUsage(MappingTest.java:77) <span class="error">[bin/:na]</span><br/>
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~<span class="error">[na:1.6.0_17]</span><br/>
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) ~<span class="error">[na:1.6.0_17]</span><br/>
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) ~<span class="error">[na:1.6.0_17]</span><br/>
at java.lang.reflect.Method.invoke(Method.java:597) ~<span class="error">[na:1.6.0_17]</span><br/>
at junit.framework.TestCase.runTest(TestCase.java:168) <span class="error">[junit-4.6.jar:na]</span><br/>
at junit.framework.TestCase.runBare(TestCase.java:134) <span class="error">[junit-4.6.jar:na]</span><br/>
at junit.framework.TestResult$1.protect(TestResult.java:110) <span class="error">[junit-4.6.jar:na]</span><br/>
at junit.framework.TestResult.runProtected(TestResult.java:128) <span class="error">[junit-4.6.jar:na]</span><br/>
at junit.framework.TestResult.run(TestResult.java:113) <span class="error">[junit-4.6.jar:na]</span><br/>
at junit.framework.TestCase.run(TestCase.java:124) <span class="error">[junit-4.6.jar:na]</span><br/>
at junit.framework.TestSuite.runTest(TestSuite.java:232) <span class="error">[junit-4.6.jar:na]</span><br/>
at junit.framework.TestSuite.run(TestSuite.java:227) <span class="error">[junit-4.6.jar:na]</span><br/>
at org.junit.internal.runners.JUnit38ClassRunner.run(JUnit38ClassRunner.java:91) <span class="error">[junit-4.6.jar:na]</span><br/>
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:49) <span class="error">[.cp/:na]</span><br/>
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38) <span class="error">[.cp/:na]</span><br/>
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467) <span class="error">[.cp/:na]</span><br/>
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683) <span class="error">[.cp/:na]</span><br/>
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390) <span class="error">[.cp/:na]</span><br/>
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197) <span class="error">[.cp/:na]</span><br/>
Caused by: java.sql.SQLException: Parameter index out of range (3 > number of parameters, which is 2).<br/>
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073) ~<span class="error">[mysql-connector-java-5.1.20.jar:na]</span><br/>
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987) ~<span class="error">[mysql-connector-java-5.1.20.jar:na]</span><br/>
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982) ~<span class="error">[mysql-connector-java-5.1.20.jar:na]</span><br/>
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927) ~<span class="error">[mysql-connector-java-5.1.20.jar:na]</span><br/>
at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3754) ~<span class="error">[mysql-connector-java-5.1.20.jar:na]</span><br/>
at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3738) ~<span class="error">[mysql-connector-java-5.1.20.jar:na]</span><br/>
at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3780) ~<span class="error">[mysql-connector-java-5.1.20.jar:na]</span><br/>
at com.mysql.jdbc.PreparedStatement.setLong(PreparedStatement.java:3796) ~<span class="error">[mysql-connector-java-5.1.20.jar:na]</span><br/>
at org.hibernate.type.descriptor.sql.BigIntTypeDescriptor$1.doBind(BigIntTypeDescriptor.java:52) ~<span class="error">[hibernate-core-3.6.0.jar:3.6.0.Final]</span><br/>
at org.hibernate.type.descriptor.sql.BasicBinder.bind(BasicBinder.java:89) ~<span class="error">[hibernate-core-3.6.0.jar:3.6.0.Final]</span><br/>
at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:282) ~<span class="error">[hibernate-core-3.6.0.jar:3.6.0.Final]</span><br/>
at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:277) ~<span class="error">[hibernate-core-3.6.0.jar:3.6.0.Final]</span><br/>
at org.hibernate.type.AbstractSingleColumnStandardBasicType.nullSafeSet(AbstractSingleColumnStandardBasicType.java:85) ~<span class="error">[hibernate-core-3.6.0.jar:3.6.0.Final]</span><br/>
at org.hibernate.type.AnyType.nullSafeSet(AnyType.java:162) ~<span class="error">[hibernate-core-3.6.0.jar:3.6.0.Final]</span><br/>
at org.hibernate.persister.collection.AbstractCollectionPersister.writeElementToWhere(AbstractCollectionPersister.java:844) ~<span class="error">[hibernate-core-3.6.0.jar:3.6.0.Final]</span><br/>
at org.hibernate.persister.collection.AbstractCollectionPersister.deleteRows(AbstractCollectionPersister.java:1316) ~<span class="error">[hibernate-core-3.6.0.jar:3.6.0.Final]</span><br/>
... 29 common frames omitted</p></blockquote>
<p style='margin-top:0;margin-bottom:10px;'>It seems that the correct delete sql is generated: <em>delete from GroupMembership where group_id=? and member_id=?</em></p>
<p style='margin-top:0;margin-bottom:10px;'>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:</p>
<blockquote>
<p style='margin-top:0;margin-bottom:10px;'><span class="error">[org.hibernate.type.descriptor.sql.BasicBinder]</span> binding parameter<br/>
<span class="error">[1]</span> as <span class="error">[BIGINT]</span> - 15 <span class="error">[o.h.type.descriptor.sql.BasicBinder]</span> binding<br/>
parameter <span class="error">[3]</span> as <span class="error">[BIGINT]</span> - 20 <span class="error">[o.h.util.JDBCExceptionReporter]</span><br/>
Parameter index out of range (3 number of parameters, which is 2).</p></blockquote>
<p style='margin-top:0;margin-bottom:10px;'>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.</p>
<p style='margin-top:0;margin-bottom:10px;'>I've included a zip file that contains a test case (readme, java code, hibernate config) to generate this error.</p>
</td>
</tr>
<tr valign="top">
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 10px 10px 0;white-space:nowrap;">
<strong style="font-weight:normal;color:#505050;">Environment:</strong>
</td>
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 0 10px 0;width:100%;">
hibernate-core: 3.6.0
<br/>
hibernate-infinispan: 3.6.0
<br/>
hibernate-validator: 4.1.0
<br/>
<br/>
MySQL 5.5.11
<br/>
</td>
</tr>
<tr valign="top">
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 10px 10px 0;white-space:nowrap;">
<strong style="font-weight:normal;color:#505050;">Project:</strong>
</td>
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 0 10px 0;width:100%;">
<a style="color:#6c797f;" href="https://hibernate.onjira.com/browse/HHH">Hibernate ORM</a>
</td>
</tr> <tr valign="top">
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 10px 10px 0;white-space:nowrap;">
<strong style="font-weight:normal;color:#505050;">Labels:</strong>
</td>
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 0 10px 0;width:100%;">
hibernate
core
</td>
</tr>
<tr valign="top">
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 10px 10px 0;white-space:nowrap;">
<strong style="font-weight:normal;color:#505050;">Priority:</strong>
</td>
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 0 10px 0;width:100%;">
<img src="https://hibernate.onjira.com/images/icons/priority_major.gif" height="16" width="16" border="0" align="absmiddle" alt="Major"> Major
</td>
</tr> <tr valign="top">
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 10px 10px 0;white-space:nowrap;">
<strong style="font-weight:normal;color:#505050;">Reporter:</strong>
</td>
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 0 10px 0;width:100%;">
<a class="user-hover" rel="davide" id="email_davide" href="https://hibernate.onjira.com/secure/ViewProfile.jspa?name=davide" style="color:#6c797f;">David Eitel</a>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td><!-- End #email-page -->
</tr>
<tr valign="top">
<td style="color:#505050;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:10px;line-height:14px;padding: 0 16px 16px 16px;text-align:center;">
This message is automatically generated by JIRA.<br />
If you think it was sent incorrectly, please contact your <a style='color:#6c797f;' href='https://hibernate.onjira.com/secure/ContactAdministrators!default.jspa'>JIRA administrators</a>.<br />
For more information on JIRA, see: <a style='color:#6c797f;' href='http://www.atlassian.com/software/jira'>http://www.atlassian.com/software/jira</a>
</td>
</tr>
</table><!-- End #email-wrap -->
</div><!-- End #email-body -->