Joining the same entity twice results in wrong final query

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.

Hi,
I have tried to reproduce the problem but it seems in my project everything is OK. I use PostgreSQL.

Have I missed something? Could you bring more details?

My jpql-query and actual SQL are as follows.


select e from test$NewEntity e where e.id in ( select coalesce(neo.id, net.id) 
from test$NewEntity neo left join test$NewEntity net 
on neo.id = net.id 
where neo.country.id = '3eb171df-063b-f5fc-ab8d-e2d532c34ebf' and neo.sector is null 
and (current_date between neo.createTs and coalesce(neo.createTs,neo.deleteTs)) ), max=10000

SELECT t0.ID AS a1, t0.calls AS a2, t0.DELETE_TS AS a3, t0.DELETED_BY AS a4, t0.name AS a5, t0.sector AS a6, t0.VERSION AS a7 
FROM new_entity t0 WHERE (t0.ID IN (SELECT COALESCE(t1.ID,t2.ID ) 
FROM new_entity t1 LEFT OUTER JOIN new_entity t2 ON (t1.ID = t2.ID) 
WHERE (((((t1.country_id = ?) AND (t1.sector IS NULL)) AND (CURRENT_DATE BETWEEN t1.CREATE_TS AND COALESCE(t1.CREATE_TS,t1.DELETE_TS ))) AND (t1.DELETE_TS IS NULL)) AND (t2.DELETE_TS IS NULL))) AND (t0.DELETE_TS IS NULL)) LIMIT ? OFFSET ?
bind => [3eb171df-063b-f5fc-ab8d-e2d532c34ebf, 10000, 0]

Hello,

The only difference between your select and mine is the coalesce, if you changed your coalesce to coalesce(net.id, neo.id) you will get my error. i confirmed this with my select.

Thank you. It helped to reproduced the problem.

It is not necessary to join the entity with itself. On different entities the issue also occurs. See the issue on GitHub: Incorrect SQL is built when COALESCE arguments order differs from tables order in JOIN-clause · Issue #1068 · cuba-platform/cuba · GitHub

Thanks for creating the issue, is it a bug in CUBA or eclipse link?

CUBA Platform useses Fork of EclipseLink. So if it is Eclipselink bug we probably can fix it in the fork.

Hi,
“Won’t” fix is set to the issue.
It is EclipseLink bug and it will take too much time to fix it on our side.

1 Like