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.
Execute the query for each OR condition separately and merge the results.