JPQL: OR won't work as expected

Question

I have the following method defined in a Repository:

@Query("SELECT t FROM Treatment t WHERE " +
        " (t.promotionCode.promotion.id=:promotionId)  " +
        " order by t.id desc")
Page<Treatment> findByPromotionId(@Param("promotionId")Integer id, Pageable pr);

And it worked as expected: I got a list of Treatments that have a PromotionCode that belongs to a given Promotion.

But then I needed to add a second promotion code, so a Treatment can be linked to up to two Promotions (both promotion codes may belong to the same promotion, that is not an issue). So I tried to add the new requirement to the query:

@Query("SELECT t FROM Treatment t WHERE " +
            " (t.promotionCode.promotion.id=:promotionId)  " +
            " OR " +
            " (t.promotionCode2.promotion.id=:promotionId)  " +
            " order by t.id desc")
Page<Treatment> findByPromotionId(@Param("promotionId")Integer id, Pageable pr);

But I won't work. The generated SQL is

select ...
from treatment treatment0_ 
  cross join promotion_code promotionc1_ 
  cross join promotion_code promotionc2_ 
where 
  treatment0_.promotion_code_id=promotionc1_.id and
  treatment0_.promotion_code2_id=promotionc2_.id and 
  (promotionc1_.promo_id=? or promotionc2_.promo_id=?)
order by 
  treatment0_.id desc limit ?

As you can notice, as soon as one of the promotion codes is null, the condition is not fulfilled.

Some details, even if they are obvious from the code:

• Besides treatment there is a table named promotion_code and another one named promotion.
• All tables have a numeric ID (autoincremented).
• promotion_code_id and promotion_code2_id are FKs pointig at promotion_code, which also has a FK to promotion and it cannot be null (all promotion codes belong to a promotion).

I want to find all treatments that are linked to a promotion by any of the promotion code columns. Both fields may be null.

How can I solve this?

Answer 1

I tried to mimic the UNION, and it worked:

SELECT t
FROM Treatment t 
WHERE t IN (SELECT t FROM Treatment t 
      WHERE t.promotionCode.promotion.id = :promotionId)
  OR t IN (SELECT t FROM Treatment t
      WHERE t.promotionCode2.promotion.id = :promotionId)
ORDER BY t.id desc

Before that I tried the LEFT JOIN FETCH option with

SELECT t 
FROM Treatment t 
LEFT JOIN t.promotionCode.promotion as pc
LEFT JOIN t.promotionCode2.promotion as pc2
WHERE 
  (pc.id=:promotionId)  
OR (pc2.id=:promotionId)  
order by t.id desc

and with

SELECT t 
FROM Treatment t 
LEFT JOIN t.promotionCode as pc
LEFT JOIN t.promotionCode2 as pc2
WHERE 
  (pc.promotion.id=:promotionId)  
OR (pc2.promotion.id=:promotionId)  
order by t.id desc

but they did not work (I got long stack trace saying the query was not correct while starting the application),