Hello team,
I have been playing around with JPQL trying to get my screen to work, when i found a very frustrating issue, i am not quite sure if this is a problem with CUBA or eclipse link, the issue is that when i join the same entity twice it takes into account only the first occurence of the entity, for example the following JPQL:
select e
from vp$WorkSchedule e
where e.id in
(
select coalesce(eSector.id, eCountry.id)
from vp$WorkSchedule eCountry left join vp$WorkSchedule eSector on eCountry.id = eSector.id
where eCountry.country.id = 'e6afb2c2-73b9-456a-2b0a-ec69a11d3995' and eCountry.sector is null and (current_date between eCountry.startDate and coalesce(eCountry.endDate, current_date))
)
the query doesn’t make sense as it’s joining the same record with itself, I removed the actual ON condition to get down to the bottom of the issue and as you can see from the following actual SQL issued by eclipse it’s not reflecting the second table “vp$WorkSchedule eSector” in the final SQL:
SELECT t0.ID AS a1, t0.CREATE_TS AS a2, t0.CREATED_BY AS a3, t0.END_DATE AS a4, t0.NAME AS a5, t0.NO_OF_HOURS AS a6, t0.START_DATE AS a7, t0.TYPE_ AS a8, t0.UPDATE_TS AS a9, t0.UPDATED_BY AS a10, t0.VERSION AS a11, t0.WEEKEND_DAYS AS a12, t0.COUNTRY_ID AS a13, t0.EMPLOYEES_GROUP_ID AS a14, t0.SECTOR_ID AS a15
FROM VP_WORK_SCHEDULE t0
WHERE t0.ID IN
(SELECT COALESCE(t1.ID,t2.ID )
FROM VP_WORK_SCHEDULE t1
WHERE (((t1.COUNTRY_ID = ?) AND (t1.SECTOR_ID IS NULL)) AND (CURRENT_DATE BETWEEN t1.START_DATE AND COALESCE(t1.END_DATE,CURRENT_DATE )))) LIMIT ? OFFSET ?
as you see in the code "SELECT COALESCE(t1.ID,t2.ID ) FROM VP_WORK_SCHEDULE t1 " it assumed there’s a table aliased t2, but actually it didn’t put a t2 in the FROM.
is this a bug or i am doing something wrong.