views:

58

answers:

2

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.

A: 

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”.

Affe
I'm using PostgreSQL, so http://bit.ly/9ObRdJ is more appropriate. That's got me looking..."expression IN (value [, ...])The right-hand side is a parenthesized list of scalar expressions.""expression operator ANY (array expression)The right-hand side is a parenthesized expression, which must yield an array value."perhaps if I modify this: return session.createQuery(hql).setParameterList("patIds", patIds).list();to this: return session.createQuery(hql).setParameterList("patIds", patIds.toArray()).list();...it will work? Nope. Same error. Hmm
shlsevencee
huh, dunno how I got mySQL in my head, must have been reading two pages at once. Perhaps your dialect isn't set to postgres and hibernate doesn't know to do the funky ARRAY['foo','bar'] thing? you can set the hibernate.show_sql property to true or turn logging on package org.hibernate.SQL up to debug to see what it's actually sending to postgres
Affe
A: 

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?

Pascal Thivent