Using OR in Constraint definition results in incorrect data

I have run into an issue when adding a constraint definition which applies multiple conditions using an OR statement. The where clause I am using is as follows:

(select from xam$ExtUser u where = :session$userId) = {E}
OR (select from xam$ExtUser u where = :session$userId) = {E}

The issue is that records which contain a null value in either the AssignedSupervisor or Assigned Person field do not show up in the results. I believe the issue may reside in the SQL that is generated joining to the Person table. There are two joins to this table created and the results are filtered using the below statement:



I think the fact that this statement is using ANDs and the joins are not specified as left joins is causing the records containing a NULL value in either of the two fields to get filtered out.

EclipseLink uses INNER JOIN for path expressions like: {E}
As a workaround, you can write security constraints with left join. e.g:

JOIN clause:
left join {E}.assignedSupervisor s left join {E}.assignedPerson p

WHERE clause:
(select from xam$ExtUser u where = :session$userId) = OR (select from xam$ExtUser u where = :session$userId) =

Subbotin Andrey

That works perfectly. Thank you.

I need to add another level of complexity to this query. The things I’ve added are:

Added to Join Clause: left join {E}.assignedTeam t

Added to Where Clause: OR (select u.person from xam$ExtUser u where = :session$userId) MEMBER OF t.people

After adding these statements I am getting the error below. You will notice a few additional statements in the query. These are due to the system appending the constraint to a query used to populate a table on our main window. I am not sure how to interpret the error as the query syntax looks correct. Is this perhaps something to do with the MEMEBER OF statement?
Exception Description: The field [XAM_PERSON.ID] in this expression has an invalid table in this context.

ReadAllQuery(referenceClass=WorkOrder jpql= 
"select e from xam$WorkOrder e  
join xam$ExtUser u  
left join e.assignedSupervisor s  
left join e.assignedPerson p  
left join e.assignedTeam t  
where ( = :session_userId and = and e.state not in ( 'Completed', 'Closed', 'Canceled')) and ((select from xam$ExtUser u where = :session$userId) = OR (select from xam$ExtUser u where = :session$userId) = OR e.createdBy = :session$userLogin OR (select u.person from xam$ExtUser u where = :session$userId) MEMBER OF t.people) order by e.workOrderId")


Could you provide sources for Team entity?


Attached is the POJO for the team entity. Please let me know if you need anything (1.2 KB)