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:
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 )
when executing this for :countryList = ['ES'] all worked fine, when having :countryList=['ES', 'PT'] this was happening:
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_ ) ]
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' )
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:
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
I am unsure how this:
select item from Item item where item.name in :names and ( item.descr is null or item.descr in :descrs )
worked for you though when :names was let's say ['Mary', 'Joe'].
|