[hibernate-issues] [Hibernate-JIRA] Updated: (HHH-5743) Criteria isMember() creates broken SQL in joined queries

Criteria isMember() creates broken SQL in joined queries -------------------------------------------------------- Key: HHH-5743 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5743 Project: Hibernate Core Issue Type: Bug Components: entity-manager Affects Versions: 3.6.0, 3.6.1 Environment: Hibernate 3.6.0,3.6.1, all databases Reporter: David Momper Priority: Critical Fix For: 3.6.1, 4.0.0.Alpha1 Attachments: Test.zip This problem occurred both when using Oracle, or Derby. I assume it occurs for all databases. I also ran my test case with EclipseLink, and the test passed. A criteria query like this: {quote} CriteriaQuery<Group> groupQuery = cb.createQuery(Group.class); Root<Group> gRoot = groupQuery.from(Group.class); //Get all groups whose leader's name is John, and has a valid visa for given country groupQuery.where(cb.and( cb.like(gRoot.get(Group_.groupLeader) .get(Person_.personName), "%John%"), cb.isMember(country, gRoot.get(Group_.groupLeader) .get(Person_.validVisas) ) )); {quote} produces the following query: {quote} select group0_.GROUP_ID as GROUP1_2_, group0_.LEADER_ID as LEADER3_2_, group0_.GROUP_NAME as GROUP2_2_ from GROUPS group0_, PERSON person1_ where group0_.LEADER_ID=person1_.PERSON_ID and ( person1_.PERSON_NAME like ? ) and ( ? in ( select person1_.PERSON_ID from PERSON person1_ ) ) {quote} The query fails because a (character) country code is being checked for inclusion in a subquery of (int) person ids. The subselect in the above query should be on a country, not a person. I think the whole query should look like this: {quote} select group0_.GROUP_ID as GROUP1_2_, group0_.LEADER_ID as LEADER3_2_, group0_.GROUP_NAME as GROUP2_2_ from GROUPS group0_, PERSON person1_ where group0_.LEADER_ID=person1_.PERSON_ID and ( person1_.PERSON_NAME like ? ) and ( ? in ( select country3_.COUNTRY_CODE from PERSON person1_, PERSON_COUNTRY_VISA validvisas2_, COUNTRY country3_ where group0_.LEADER_ID=person1_.PERSON_ID and person1_.PERSON_ID=validvisas2_.PERSON_ID and validvisas2_.COUNTRY_CODE=country3_.COUNTRY_CODE ) ) {quote}