[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-5743) Criteria isMember() creates broken SQL in joined queries
[
https://hibernate.onjira.com/browse/HHH-5743?page=com.atlassian.jira.plug...
]
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