Sun Java System Application Server Platform Edition 8.1 2005Q2 Update 2 Release Notes

An EJBQL query may not contain all matching results if the where clause contains an OR operator and a single-valued cmr navigation. (ID 6184864)

If the where clause in an EJBQL query contains an OR operator and a single-valued cmr navigation, the query result will not contain the result for rows in which the navigation path is null even though the navigation path is in a different OR clause.

For example, consider a schema comprising Employee, Department, and Insurance. Employee has a 1:Many relationship with Department and a 1:1 relationship with Insurance:

select Distinct Object(e) from Employee e
        where e.name = ’John’ OR e.department.name = ’Engineering’

The above query will not return employees whose name is John and does not belong to any department.

select Distinct Object(e) from Employee e
        where e.department.name = ’Engineering’ OR e.insurance.name = ’xyz’

The above query will not return any employee whose insurance name is xyz and does not belong to any department. It will also not return any employee whose department name is Engineering and does not have any insurance.

Solution

Execute the query for each OR condition separately and merge the results.