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

[Gail Badner (JIRA)]

     [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-5743?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Gail Badner updated HHH-5743:
-----------------------------

    Fix Version/s: 4.0.0.Alpha1
                   3.6.1

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

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