Hi,
I think I can bring some light on this matter for all people having problems similar to this. These are my findings.
On the project I currently work on I had the following case:
{quote}
select data from Data data where (
data.billingCountry in :countryList
or data.customerCountry in :countryList
or data.deliveryCountry in :countryList
or data.collectionCountry in :countryList
)
{quote}
when executing this for :countryList = ['ES'] all worked fine, when having :countryList=['ES', 'PT'] this was happening:
{quote}
org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: \{vector\} [select data from Data data where ( data.billingCountry in (:countryList_0_, :countryList_1_) or data.customerCountry in (:countryList_0_, :countryList_1_) or data.deliveryCountry in (:countryList_0_, :countryList_1_) or data.collectionCountry in :countryList_0_, :countryList_1_ ) ]
{quote}
After debugging looked like the that the method *org.hibernate.internal.AbstractQueryImpl.expandParameterList* was expanding the last instance of the parameter wrong. The simple fix was to enclose all the list typed parameters in parentheses.
Why? Because that's how you use the *in* clause.
I was investigating this last night, and did not go deeper into the sources, putting the list parameters in parentheses was just a moment of inspiration. I was looking at the SQL expected query and I just thought maybe my list should be enclosed in parentheses in Hql just like in the SQL query syntax. And it worked so I just committed my changes and started on something else, but this kept bugging me so I went through the documentation and found this:
[from DomesticCat cat where cat.name in ( 'Foo', 'Bar', 'Baz' )|http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/queryhql.html]
But, most developers when writing Hql queries as text and then calling *query.setParameter* tend to skip things. This happened to me and most probably happens to others, otherwise the internet would not be full o people going crazy and calling this a hibernate bug.
So mr. Steve Ebersole this is not a bug, is Hql misuse. The original query posted as an example, would work just fine if the :companies parameter would be enclosed in parantheses:
{quote}
SELECT ed
FROM ExternalDocument ed
LEFT JOIN FETCH ed.documentType dt
JOIN FETCH ed.company co
WHERE co IN (:companies) AND (dt IS NULL OR dt IN :docTypes)
ORDER BY ed.fileName
{quote}
I am unsure how this:
{quote}
select item from Item item where item.name in :names and ( item.descr is null or item.descr in :descrs )
{quote}
worked for you though when :names was let's say ['Mary', 'Joe'].
Hibernate version used: *4.2.8.Final*
|
