[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

[Nicklas Nordborg (JIRA)]

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

Nicklas Nordborg commented on HHH-4065:
---------------------------------------

Since this is a rather pressing issue for me I made a very ugly hack that in practice disables the query plan cache. This might not be a good idea in general but since there are other fixes in 3.3.2 that I need more it is a price I am willing to pay. The hack uses reflection to replace the 'private final SoftLimitMRUCache planCache' variable used in the QueryPlanCache by a subclass that simply ignores everything that is added to it. Here is the code for the hack. It needs to be executed once right after that session factory has been created.

SessionFactory sf = ....
try
{
   SessionFactoryImpl sfi = (SessionFactoryImpl)sf;
   QueryPlanCache qpc = sfi.getQueryPlanCache();
   Field pcf = QueryPlanCache.class.getDeclaredField("planCache");
   pcf.setAccessible(true);
   pcf.set(qpc, 
      new SoftLimitMRUCache() 
     {
         private static final long serialVersionUID = -7082205080525320488L;
         @Override
         public Object put(Object key, Object value)
         {
             return null;
         }
      }
   );
}
catch (Exception ex)
{
   log.warn("Could not replace query plan cache in Hibernate", ex);
}



> 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