sql error running JPQL query using the IN operator

i have the following 2 JPA classes:

@Entity
public class AnalysisPolicy {
    private Set rules;
    //stuff omitted
 开发者_JS百科   @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    public Set getRules() {
        return rules;
    }
}

@Entity
public class AnalysisRule {
    //stuff omitted
}

and the following JPQL query:

select p from AnalysisPolicy p, AnalysisRule r where r.id=:ruleId and r in p.rules

which accepts a single parameter, ruleId, of type java.util.UUID (thats the class i use as @Id for all my entities). trying to execute the query i get the following results:

Hibernate: 
    /* select
        p 
    from
        AnalysisPolicy p,
        AnalysisRule r 
    where
        r.id=:ruleId 
        and r in p.rules */ select
            analysispo0_.f_id as f1_12_,
            analysispo0_.f_lastmodified as f2_12_,
            analysispo0_.global as global12_ 
        from
            AnalysisPolicy analysispo0_ cross 
        join
            AnalysisRule analysisru1_ cross 
        join
            AnalysisPolicy_AnalysisRule rules2_, AnalysisRule analysisru3_ 
        where
            analysispo0_.f_id=rules2_.AnalysisPolicy_f_id 
            and rules2_.rules_f_id=analysisru3_.f_id 
            and analysisru1_.f_id=? 
            and (
                analysisru1_.f_id in (
                    .
                )
            ) limit ?
[org.hibernate.util.JDBCExceptionReporter] SQL Error: 0, SQLState: 42601
[org.hibernate.util.JDBCExceptionReporter] ERROR: syntax error at or near "."

which means my JPQL is accepted by hibernate, but for some reason the SQL it produces is invalid (the "analysisru1_.f_id in (.)" part).

im using hibernate 3.6 and postgresql 9 for the DB. what am i doing wrong?

---

The query is wrong, IN cannot be used that way. You should write

select p from AnalysisPolicy p, AnalysisRule r 
    where r.id=:ruleId and r in elements(p.rules)

or

select p from AnalysisPolicy p, AnalysisRule r 
    where r.id=:ruleId and r member of p.rules

But the following query perhaps would be the best of all:

select p from AnalysisPolicy p join p.rules r where r.id=:ruleId

---

Your HQL looks confusing to me. I think you could rewrite it as this:

select r.p from AnalysisRule r where r.id=:ruleId

It would return all policies which contains a given rule. You may even put a distinct there :-)

Edit: axtavt was quicker and provided a better answer than me. It also seems that we got a similar idea about rewriting the HQL query.