Table Join for sorting in dataGrid breaks row level condition

[dtaimanov]

See https://forum.jmix.ru/t/row-level-rol-i-sortirovka-v-grouptable/1342

Steps:

1. Download project and role
2. Run project
3. Import role and assign to some user
4. Open "Notes" and sort by "Assigned too"
5. Click "Refresh"
   ER item with empty assignedTo present in table
   AR item with empty assignedTo absent

Cause

Inner join used instead of left join in case of appearing constraint and sorting simultaneously:

-- constraint: yes,  sorting: no
SELECT .. FROM NOTE WHERE (((ASSIGNED_TO_ID IS NULL) OR (ASSIGNED_TO_ID = ?)) AND (DELETED_DATE IS NULL))
-- constraint: yes,  sorting: yes
SELECT .. FROM USER_ t0, NOTE t1 WHERE ((((t1.ASSIGNED_TO_ID IS NULL) OR (t1.ASSIGNED_TO_ID = ?)) AND (t1.DELETED_DATE IS NULL)) AND (t0.ID = t1.ASSIGNED_TO_ID)) ORDER BY t0.FIRST_NAME ASC, t0.LAST_NAME ASC, t0.USERNAME ASC, t1.ID ASC

-- constraint: no,  sorting: yes
SELECT .. FROM NOTE t1 LEFT OUTER JOIN USER_ t0 ON (t0.ID = t1.ASSIGNED_TO_ID) WHERE (t1.DELETED_DATE IS NULL) ORDER BY t0.FIRST_NAME DESC, t0.LAST_NAME DESC, t0.USERNAME DESC, t1.ID ASC

Workaround

Use explicitly specified left join in role definition (see forum reply)

[dtaimanov]

Eclipselink issue eclipse-ee4j/eclipselink#2025 has been reported.