[hibernate-issues] [Hibernate-JIRA] Created: (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
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira