Select Case When case that works in JPA but not CUBA

michael.renaud · March 5, 2019, 10:12am

Hi

In my model I have Product entity with an optional one to one relationship to an Agent entity.

   @Lookup(type = LookupType.DROPDOWN)
    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "AGENT_ID")
    protected Agent agent;

With a simple query like select e.id, e.agent.internalCode from busy$Product e, there will be an implicit inner join, which hides products with no agent.

SELECT t0.ID, t1.INTERNAL_CODE FROM BUSY_PRODUCT t0, BUSY_AGENT t1 WHERE ((t0.DELETE_TS IS NULL) AND ((t1.ID = t0.AGENT_ID) AND (t1.DELETE_TS IS NULL)))

As far as I know it is not possible in JPA for force an implicit left outer join for relationships. So I tried another query : select e.id, case when (a.id is null) then null else a.internalCode end from busy$Product e left join e.agent a

Which by using JPA yields expected SQL.

 @Test
    public void leftJoinCaseWhenJPA() {
        String query = "select e.id, case when (a.id is null) then null else a.internalCode end from busy$Product e left join e.agent a"
        persistence.createTransaction().execute((em)->{
            dump(em.getDelegate().createQuery(query).getResultList());
        });
    }

SELECT t0.ID, CASE  WHEN (t1.ID IS NULL) THEN ? ELSE t1.INTERNAL_CODE END FROM BUSY_PRODUCT t0 LEFT OUTER JOIN BUSY_AGENT t1 ON (t1.ID = t0.AGENT_ID) WHERE (t0.DELETE_TS IS NULL)

But does not work with CUBA.

@Test
public void leftJoinCaseWhenCUBA() {
    String query = "select e.id, case when (a.id is null) then null else a.internalCode end from busy$Product e left join e.agent a"
    persistence.createTransaction().execute((em)->{
        dump(em.createQuery(query).getResultList());
    });
}

com.haulmont.cuba.core.sys.jpql.JpqlSyntaxException: Errors found for input jpql:[select e.id, case when (a.id is null) then null else a.internalCode end from busy$Product e left join e.agent a]
CommonErrorNode [<unexpected: [@10,19:22=‘when’,<60>,1:19], resync=case when (>]
CommonErrorNode [<mismatched token: [@14,26:26=‘.’,<68>,1:26], resync=.id is null) then null else a.internalCode end from busy$Product e left join e.agent a>]

While documentation claims that case when is ok except in update query : JPQL Functions - CUBA Platform. Developer’s Manual.

It would be handy to have an option on DataManager/LoadContext to bypass CUBA.

Using 6.9.7 but could move to 6.10 to get a patch.

Best Regards
Michael

s.fedorov · March 6, 2019, 12:54pm

Hi, @michael.renaud!

In that case you do not need case when function if you use left join, query select e.id, a.internalCode from busy$Product e left join e.agent a should work.

The problem with case when caused by part then null. If you put any value compatible with a.internalCode after then it will work. I will investigate further how should it work with null.

Regards,
Sergey

michael.renaud · March 6, 2019, 4:05pm

Yes you are right @s.fedorov, but of course the real query is more complex.

If you look at the JPQL + SQL below despite the “left join agent a”, an inner join is generated.

select e.id, e.internalCode, e.label, f.lastUpdate, f.available, f.minQty, a.internalCode 
from busy$Product e 
join e.stock f 
left join e.agent a 
where (f.available <= f.minQty and f.minQty > 0 
and ((f.lastUpdate >= :f_lastUpdate_1_1 and f.lastUpdate < :f_lastUpdate_1_2) 
or (f.updateTs >= :f_updateTs_2_1 and f.updateTs < :f_updateTs_2_2))) 
and (e.agent.id is null or exists (select 1 from busy$UserAgent ua 
where ua.agent.id = e.agent.id and ua.user.id = :session$userId)) 
order by f.lastUpdate desc, f.updateTs desc

SELECT t0.id, 
       t0.internal_code, 
       t0.label, 
       t1.last_update, 
       t1.available, 
       t1.min_qty, 
       t2.internal_code 
FROM   busy_product t0, 
       busy_agent t2, 
       busy_product_stock t1 
WHERE  ( ( ( ( ( ( t1.available <= t1.min_qty ) 
                 AND ( t1.min_qty > ? ) ) 
               AND ( ( ( t1.last_update >= ? ) 
                       AND ( t1.last_update < ? ) ) 
                      OR ( ( t1.update_ts >= ? ) 
                           AND ( t1.update_ts < ? ) ) ) ) 
             AND ( ( t2.id IS NULL ) 
                    OR EXISTS (SELECT ? 
                               FROM   busy_user_agent t4, 
                                      busy_agent t3 
                               WHERE  ( ( ( ( t3.id = t2.id ) 
                                            AND ( t4.user_id = ? ) ) 
                                          AND ( t4.delete_ts IS NULL ) ) 
                                        AND ( ( t3.id = t4.agent_id ) 
                                              AND ( t3.delete_ts IS NULL ) ) )) 
                 ) ) 
           AND ( t0.delete_ts IS NULL ) ) 
         AND ( ( ( t1.id = t0.stock_id ) 
                 AND ( t1.delete_ts IS NULL ) ) 
               AND ( ( t2.id = t0.agent_id ) 
                     AND ( t2.delete_ts IS NULL ) ) ) ) 
ORDER  BY t1.last_update DESC, 
          t1.update_ts DESC

But if I use the ‘case when’ trick then I can force the left outer join with agent, see below.

select e.id, e.internalCode, e.label, f.lastUpdate, f.available, f.minQty, case when (a.id is null) then null else a.internalCode end 
from busy$Product e  
join e.stock f 
left join e.agent a 
where (f.available <= f.minQty and f.minQty > 0 
and (@between(f.lastUpdate, now-:lastdays, now+1, day) 
or @between(f.updateTs, now-:lastdays, now+1, day))) 
order by f.lastUpdate desc, f.updateTs desc

SELECT t0.id, 
       t0.internal_code, 
       t0.label, 
       t1.last_update, 
       t1.available, 
       t1.min_qty, 
       CASE 
         WHEN ( t2.id IS NULL ) THEN ? 
         ELSE t2.internal_code 
       END 
FROM   busy_product t0 
       LEFT OUTER JOIN busy_agent t2 
                    ON ( t2.id = t0.agent_id ), 
       busy_product_stock t1 
WHERE  ( ( ( ( ( t1.available <= t1.min_qty ) 
               AND ( t1.min_qty > ? ) ) 
             AND ( ( ( t1.last_update >= ? ) 
                     AND ( t1.last_update < ? ) ) 
                    OR ( ( t1.update_ts >= ? ) 
                         AND ( t1.update_ts < ? ) ) ) ) 
           AND ( t0.delete_ts IS NULL ) ) 
         AND ( ( t1.id = t0.stock_id ) 
               AND ( t1.delete_ts IS NULL ) ) ) 
ORDER  BY t1.last_update DESC, 
          t1.update_ts DESC

However if I take your workaround and replace then null by then '' (empty string)

select e.id, e.internalCode, e.label, f.lastUpdate, f.available, f.minQty, case when (a.id is null) then '' else a.internalCode end 
from busy$Product e 
join e.stock f 
left join e.agent a 
where (f.available <= f.minQty and f.minQty > 0 and ((f.lastUpdate >= :f_lastUpdate_1_1 
and f.lastUpdate < :f_lastUpdate_1_2) or (f.updateTs >= :f_updateTs_2_1 and f.updateTs < :f_updateTs_2_2))) and (e.agent.id is null or exists (select 1 from busy$UserAgent ua where ua.agent.id = e.agent.id and ua.user.id = :session$userId)) 
order by f.lastUpdate desc, f.updateTs desc

Then the generated SQL gets back to an inner join.

SELECT t0.id, 
       t0.internal_code, 
       t0.label, 
       t1.last_update, 
       t1.available, 
       t1.min_qty, 
       CASE 
         WHEN ( t2.id IS NULL ) THEN ? 
         ELSE t2.internal_code 
       END 
FROM   busy_product t0, 
       busy_agent t2, 
       busy_product_stock t1 
WHERE  ( ( ( ( ( ( t1.available <= t1.min_qty ) 
                 AND ( t1.min_qty > ? ) ) 
               AND ( ( ( t1.last_update >= ? ) 
                       AND ( t1.last_update < ? ) ) 
                      OR ( ( t1.update_ts >= ? ) 
                           AND ( t1.update_ts < ? ) ) ) ) 
             AND ( ( t2.id IS NULL ) 
                    OR EXISTS (SELECT ? 
                               FROM   busy_user_agent t4, 
                                      busy_agent t3 
                               WHERE  ( ( ( ( t3.id = t2.id ) 
                                            AND ( t4.user_id = ? ) ) 
                                          AND ( t4.delete_ts IS NULL ) ) 
                                        AND ( ( t3.id = t4.agent_id ) 
                                              AND ( t3.delete_ts IS NULL ) ) )) 
                 ) ) 
           AND ( t0.delete_ts IS NULL ) ) 
         AND ( ( ( t1.id = t0.stock_id ) 
                 AND ( t1.delete_ts IS NULL ) ) 
               AND ( ( t2.id = t0.agent_id ) 
                     AND ( t2.delete_ts IS NULL ) ) ) ) 
ORDER  BY t1.last_update DESC, 
          t1.update_ts DESC

I don’t see myself extending and reprogramming DataManager & RdbmsStore to pass through a bypassCuba boolean so that EntityManager.getDelegate() is used.

Especially when this query relies on CUBA specifics : security constraints and JPQL macros.

So If you have another idea to force a left outer join agent with this query, that’s great.

Michael

s.fedorov · March 7, 2019, 7:02am

The problem with first query may be caused by using e.agent.id with left join e.agent a. Try not to use construction e.agent paired with left join e.agent because EclipseLink may then prioritize inner join, use alias from join instead.

I’ve created GitHub issue about supporting null for case when function.

Regards,
Sergey

michael.renaud · March 7, 2019, 7:40am

The fact is the clause on Agent is in a security constraint : exists (select 1 from busy$UserAgent ua where ua.agent = {E}.agent and ua.user.id = :session$userId).

Tried to rewrite it with a IN, to set left joins everywhere, as long as I request field a.internalCode innter join is produced, left join in all other cases.

EDIT : but I finally managed to make it work using a join clause in the security constraints

    String FILTER_BY_AGENT_OPEN_JOIN = "left join {E}.agent _a_";
    String FILTER_BY_AGENT_OPEN = "(_a_ is null or exists (select 1 from busy$UserAgent ua where ua.agent = _a_ and ua.user.id = :session$userId))";

Thanks Sergey