[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-4065) Incorrect SQL is used for HQL if the number of values for a filter collection parameter is changed

Incorrect SQL is used for HQL if the number of values for a filter collection parameter is changed -------------------------------------------------------------------------------------------------- Key: HHH-4065 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-4065 Project: Hibernate Core Issue Type: Bug Components: query-hql Affects Versions: 3.3.2 Environment: Hibernate 3.3.2 Reporter: Nicklas Nordborg Assignee: Steve Ebersole Fix For: 3.3.x, 3.5 Attachments: org.hibernate.test.filter.DynamicFilterTest-output.txt, org.hibernate.test.filter.DynamicFilterTest-patch.txt, org.hibernate.test.filter.DynamicFilterTest.txt, trace.txt I think that maybe the fix for HHH-530 has introduced a problem with filters that has a collection-type parameter. If the number of parameters in that collection changes during the lifetime of a SessionFactory the SQL that is used becomes incorrect and either has too many or too few parameter placeholders (eg. '?') in the SQL. There was no problem in Hibernate 3.3.1. A typical error message is: java.sql.SQLException: Parameter index out of bounds. 2 is not between valid values of 1 and 1 Below is some pseudo-code that shows what happens when the number of values in a collection is increased from 1 to 2. The 'memberOf' is a simple filter on the `id` column: `id` IN (:items) String hql = "select n from NewsData n"; List idList = new ArrayList(); idList.add(1); Session s = .... // create new session s.enableFilter("memberOf").setParameterList("items", idList); s.createQuery(hql).list(); // SQL: select .... `News` newsdata0_ where newsdata0_.`id` IN (?) s.close(); idList.add(2); s = ... // create new session s.enableFilter("memberOf").setParameterList("items", idList); s.createQuery(hql).list(); // <--- error here with 3.3.2 but works with 3.3.1 // SQL (3.3.2): select .... `News` newsdata0_ where newsdata0_.`id` IN (?) // SQL (3.3.1): select .... `News` newsdata0_ where newsdata0_.`id` IN (?, ?) I am attaching a file with TRACE-level output from both Hibernate 3.3.2 and 3.3.1. The relevant difference seems to be that in Hibernate 3.3.2 the query plan is cached with: [SQL_TOKEN] SqlFragment: 'newsdata0_.`id` IN (?)' but in Hibernate 3.3.1 with: [SQL_TOKEN] SqlFragment: 'newsdata0_.`id` IN (:memberOf.items)'