Hibernate HQL Query : How to properly use ANY function in where clause?

Question

I'm struggling to understand my error in an HQL query:

public List<Pats> getIds(List<String> patIds) {
    Session session = getSession();
    String hql = "from OurPats where patId = any (:patIds)";
    // String hql = "from OurPats where patId in (:patIds)";
    return session.createQuery(hql).setParameterList("patIds", patIds).list();

}

...the commented out line works properly, but I want the functionality of the non-working ANY comparison as patIds.size() can be greater than 2^15 (causing postgresql to break).

Judging from http://docs.jboss.org/hibernate/core/3.3/reference/en/html/queryhql.html the ANY function should work. In other forum questions people say not to use the elements function as is stipulated in the above link (I've tried with elements and I get an IDENT error). The above code produces an org.hibernate.hql.ast.QuerySyntaxException: unexpected token: : error.

Any ideas? Thanks for help.

Answer 1

Not sure what you mean by wanting the functionality of 'any' rather than 'in.' Every DBMS I know treats them as synonymous.

= ANY is not allowed for lists in MySQL, only for subqueries. Ostensibly you have your dialect set to MySQL and Hibernate knows this?

http://dev.mysql.com/doc/refman/5.5/en/any-in-some-subqueries.html

IN and = ANY are not synonyms when used with an expression list. IN can take an expression list, but = ANY cannot. See Section 11.3.2, “Comparison Functions and Operators”.

Answer 2

As far as I know, = ANY is equivalent to IN (and I think they will be transformed into the same queries by the optimizer). From the PostgreSQL documentation:

9.16.4. ANY/SOME

expression operator ANY (subquery)
expression operator SOME (subquery)

The right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subquery result using the given operator, which must yield a Boolean result. The result of ANY is "true" if any true result is obtained. The result is "false" if no true result is found (including the special case where the subquery returns no rows).

SOME is a synonym for ANY. IN is equivalent to = ANY.

So I don't think using = ANY will solve your problem anyway.

Of course, I don't have the context but are you sure that performing more than 2^15 OR comparisons really makes sense?