Restriction on ElementCollection not working using criteria API --------------------------------------------------------------- Key: HHH-7211 URL: https://hibernate.onjira.com/browse/HHH-7211 Project: Hibernate ORM Issue Type: Bug Components: query-criteria Affects Versions: 4.1.1 Reporter: Lars Heller Attachments: criteria-elementcollection.zip I have a User object containing an ElementCollection of roles. The following is the roles' mapping. {code:java} @ElementCollection( fetch = FetchType.EAGER ) @CollectionTable( name = "user_roles", joinColumns = @JoinColumn( name = "user_id" ) ) @Column( name = "role", length = 20 ) @Fetch( FetchMode.SELECT ) public Set<String> getRoles() { return this.roles; } {code} Now I want to count all users having one of a given collection of roles. Using HQL I execute {code:sql}select count(distinct u) from User u join u.roles r where r in (:roles){code} binding an array of roles to {{roles}}. This works fine. But actually I want to use the criteria API, since I have a bunch of filter options to apply dynamically. So I tried {code} Criteria criteria = currentSession().createCriteria( User.class, "u" ); criteria.createAlias( "roles", "r", JoinType.INNER_JOIN ); criteria.add( Restrictions.in( "roles", roles ) ); criteria.setProjection( Projections.countDistinct( "id" ) ); return ( (Number) criteria.uniqueResult() ).intValue(); {code} which does not work. It results in {noformat} nested exception is org.hibernate.exception.GenericJDBCException: Parameter "#1" is not set; SQL statement: select count(distinct this_.id) as y0_ from acme.users this_ inner join acme.user_roles r1_ on this_.id=r1_.user_id where this_.id in (?) [90012-163] {noformat} The problem here in my opinions is, that Hibernate creates the SQL {code:sql} select count(distinct this_.id) as y0_ from acme.users this_ inner join acme.user_roles r1_ on this_.id=r1_.user_id where this_.id in (?) {code} which is absolutely not what I expect, since the where references the user-id instead of the role. It didn't work leaving out the join or using {{r}} instead of {{roles}} or using {{r.role}}. My workaround was to create the sql myself and add a sql restriction: {code} Criteria roleCriteria = criteria.createCriteria( "roles", JoinType.INNER_JOIN ); String questionMarks = StringUtils.repeat( "?", ", ", roles.size() ); Type[] types = new Type[roles.size()]; Arrays.fill( types, currentSession().getTypeHelper().custom( RoleUserType.class ) ); roleCriteria.add( Restrictions.sqlRestriction( "{alias}.role in (" + questionMarks + ")", roles.toArray(), types ) ); {code} Not a nice solution. :-( I added a Maven project for testing. It uses Spring and stuff, but I have reduced the code down to the problem (UserDaoHibernate and a simple test class). -- This message is automatically generated by JIRA. For more information on JIRA, see: http://www.atlassian.com/software/jira