[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

[Anderson Teymeny (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-4065?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=37016#action_37016 ] 

Anderson Teymeny commented on HHH-4065:
---------------------------------------

Does anyone know if this issue is the same discribed here https://forum.hibernate.org/viewtopic.php?f=1&t=1002162 ?
I have just updated from 3.3.1.GA to 3.3.2.GA and I got this problem, and it looks a lot with the one addressed by this issue.
Thanks

> 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.0-Beta-3
>
>         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)'

-- 
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