I've been having unexpected results using OR inside a WHERE clause with EJB3 QL (Preview 5). I've narrowed it down a bit as follows.
In our project we deal with Providers which may have a collection of Contacts which have phone numbers. The area code '999' appears in our test database for only one Contact belonging to one Provider.
So...
resultCollection = this.entityManager.createQuery(
"SELECT DISTINCT p FROM Provider AS p JOIN p.contacts AS c WHERE c.contact.homeNumber.areaCode = '999'").
getResultList();
...works fine and get me my one Provider.
However, if I do...
resultCollection = this.entityManager.createQuery(
"SELECT DISTINCT p FROM Provider AS p JOIN p.contacts AS c WHERE c.contact.homeNumber.areaCode = '999' OR c.contact.homeNumber.areaCode = '999'").
getResultList();
...my results are all of the Providers that have Contacts.
Looks like a bug to me, or am I missing something here? (I don't really want to run redundant query clauses, we have various types of phone numbers I'm searching and it seems using "OR" trips things up like this.)
Thanks!
Aivar
I find that pretty hard to believe. This is an hibernate feature for ages.
How does the sql look like (show_sql=true).
If you're sure of you, post a working test case in JIRA http://opensource.atlassian.com/projects/hibernate/secure/Dashboard.jspa
I'll check that.