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

[Matt Todd (JIRA)]

    [ https://hibernate.onjira.com/browse/HHH-5743?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=46471#comment-46471 ] 

Matt Todd commented on HHH-5743:
--------------------------------

I have just encountered the same problem, in almost exactly the same relational circumstances.  Bit of background:

* Hibernate 4.1.2
* Postgres 9 db

After reading your comment, whilst writing a unit test to establish a workaround, I discovered that in our situation if you added the isMember predicate before the first one, the query executes successfully.  Did you find another workaround for this problem?


> Criteria isMember() creates broken SQL in joined queries
> --------------------------------------------------------
>
>                 Key: HHH-5743
>                 URL: https://hibernate.onjira.com/browse/HHH-5743
>             Project: Hibernate ORM
>          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
>         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.
For more information on JIRA, see: http://www.atlassian.com/software/jira