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

[David Momper (JIRA)]

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

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