[hibernate-issues] [Hibernate-JIRA] Created: (HHH-2496) Parameter binding in wrong order when filter adds params

[David Sheldon (JIRA)]

Parameter binding in wrong order when filter adds params
--------------------------------------------------------

         Key: HHH-2496
         URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2496
     Project: Hibernate3
        Type: Bug

  Components: query-hql  
 Environment: Hibernate version: 3.2
Database: Postgresql 8.1.6
    Reporter: David Sheldon


it seems that if you use a filter in the "order by" clause of your query, it binds the parameters in the wrong order. I've been looking through the code, and couldn't work out where the binding of filter parameters take place. Am I doing something wrong here, or is this a bug? I don't get an exception, but the query returns the wrong data because the parameters are sent to the database in the wrong order.


Mapping documents:
<hibernate-mapping>
<class name="bug.Event" table="events">
<id name="id" column="event_id">
<generator class="native"/>
</id>
<property name="date" type="timestamp" column="timestamp" />

</class>
<class name="bug.Thing" table="things">
<id name="id" column="id">
<generator class="native"/>
</id>
<property name="name" type="string" />
<list name="events" lazy="true" table="things_to_events" cascade="save-update,delete,delete-orphan">
<key column="thing_id"/>
<list-index column="index"/>
<many-to-many class="bug.Event" column="event_id"/>
<filter name="dateFilter" condition="event_id in (select events.event_id from events where events.timestamp between :startDate and :endDate)"/>

</list>
</class>
<filter-def name="dateFilter">
<filter-param name="startDate" type="date"/>
<filter-param name="endDate" type="date"/>
</filter-def>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():
Thing t = new Thing();
t.setName("bob");

Event e = new Event();
e.setDate(new Date());

t.setEvents(Collections.singletonList(e));

session.save(e);
session.save(t);

session.enableFilter("dateFilter").setParameter("startDate", new GregorianCalendar(2007, 1, 20).getTime()).setParameter("endDate", new GregorianCalendar(2007, 2, 20).getTime());
Query query = session.createQuery("From Thing Where name = :name order by size(events)");
query.setParameter("name", "bob");
System.err.println("Size: " + query.list().size());


The generated SQL (show_sql=true):
Hibernate: select thing0_.id as id1_, thing0_.name as name1_ from things thing0_ where thing0_.name=? order by (select count(events1_.thing_id) from things_to_events events1_ where thing0_.id=events1_.thing_id and events1_.event_id in (select events.event_id from events where events.timestamp between ? and ?))

Debug level Hibernate log excerpt:
[java] 11:21:31,176 DEBUG AbstractBatcher:476 - preparing statement
[java] 11:21:31,177 DEBUG DateType:133 - binding '20 February 2007' to parameter: 1
[java] 11:21:31,178 DEBUG DateType:133 - binding '20 March 2007' to parameter: 2
[java] 11:21:31,179 DEBUG StringType:133 - binding 'bob' to parameter: 3

As you can see, "bob" should be bound to parameter 1, 20th Feb to 2, and 20th March to 3.

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