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

[Nicklas Nordborg (JIRA)]

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
         Attachments: 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)'



-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira