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

[David Momper (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-5743?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=39176#action_39176 ] 

David Momper commented on HHH-5743:
-----------------------------------

The join is somehow causing this problem. This modified criteria query is created and executed correctly:
{quote}
groupQuery.where(cb.and(
    				//cb.like(gRoot.get(Group_.groupLeader)
    				//		.get(Person_.personName), "%John%"),
    				cb.isMember(c2,
        					gRoot.get(Group_.groupLeader)
        					.get(Person_.validVisas)
        					)
        			));
{quote}

The following SQL is generated:
{quote}
    select
        group0_.GROUP_ID as GROUP1_2_,
        group0_.LEADER_ID as LEADER3_2_,
        group0_.GROUP_NAME as GROUP2_2_ 
    from
        GROUPS group0_ 
    where
        ? 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}

> 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