Hibernate creates a SQL-statement with position based parameters in the wrong order. That is, the sql statement creation works as expected but the parameters that are placed into the place holders are in the wrong order.
This only happens if a filter ist used in a query and a subquery with entities that have a composite-id . For some reason hibernate does not set the parameter of the second filter in the subquery but uses the next parameter instead.
Constructing such a statement is very easy. All you need is a filter, and an entities with a composite-id.
Make sure the filter is used by the entity. (in the entitie's (*.).hbm.xml or annotation)
Then create a Createria from the class and add a Restriction for that classes composite id.
Furthermore create a DetachedCriteria that will be used in a subquery, which consists of a restriction and any Projection. After that, append the DetachedCriteria with a Property to the first Criteria.
and then call list() on the Criteria.
The result is a SQL statement with the filter in the main statement and the sub statement, and parameters assigned by position. But in the subquery, the parameters are mixed and therefore appended at the wrong place in the query.
In the beste case this statement crashes because of parameter type missmatch, but it's also possible that the parameter types fit and an invalid entry will be created silently.
A complete Test is appended to this ticket.