sql error running JPQL query using the IN operator

Question

i have the following 2 JPA classes:


@Entity
public class AnalysisPolicy {
    private Set rules;
    //stuff omitted
    @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?

axtavt · Accepted Answer · 2011-03-15 11:12:20Z

3

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

answered Mar 15, 2011 at 11:12

axtavt

243k42 gold badges518 silver badges486 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

radai Over a year ago

first of all thank you very much - your suggestion worked (only tried the 1st one so far). could you please elaborate on why there's a difference between your 3 suggestions (what makes the 3rd one best) and why hibernate accepted my obviously wrong query and didnt throw something ? :-)

axtavt Over a year ago

@radai: The first two approaches are more verbose and generate extra subquery (see generated SQL). Although modern DB optimizers should be able to eliminate it, the last approach is more clear. Regarding the behaviour of Hibernate - Hibernate has many quirks. Perhaps it could be called a bug, however, the wrong query finally resulted in exception :).

araqnid Over a year ago

Hibernate's HQL to SQL translation started out as very basic, and only got given a proper parser in Hibernate 3. It's still possible to write a lot of things in HQL that the parser accepts but still don't make sense.

jpkroehling · Accepted Answer · 2011-03-15 11:16:45Z

1

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.

answered Mar 15, 2011 at 11:16

jpkroehling

14.1k1 gold badge41 silver badges40 bronze badges

Collectives™ on Stack Overflow

sql error running JPQL query using the IN operator

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related