COALESCE function in JPA

Question

I'm developing an application in the MVC layer pattern. In a part of the code where I noticed a GET query, from RecordRepository using the COALESCE function, it is returning the following error in the console, as shown in the image below. I'm configuring this function, because in Postgres it doesn't just work (:min IS NULL).

org.h2.jdbc.JdbcSQLNonTransientException: Unknown data type: "NULL, ?"; SQL statement: select record0_.id as id1_2_, record0_.age as age2_2_, record0_.game_id as game_id5_2_, record0_.moment as moment3_2_, record0_.name as name4_2_ from tb_record record0_ where (coalesce(?, null) is null or record0_.moment>=?) and (coalesce(?, null) is null or record0_.moment<=?) order by record0_.moment desc limit ? [50004-214]

Erro Run Project

My code:

@Repository
public interface RecordRepository extends JpaRepository<Record, Long>{

    @Query("SELECT obj FROM Record obj WHERE "
            + "(COALESCE(:min, null) IS NULL OR obj.moment >= :min) AND "
            + "(COALESCE(:max, null) IS NULL OR obj.moment <= :max)")
    Page<Record> findByMoments(Instant min, Instant max, Pageable pageable);

}

According to [COALESCE documentation](https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL) it returns first argument that is not null. What are you hoping to achieve with `COALESCE(:min, null)`? — Lesiak, Nov 14 '22 at 19:22
The test is as follows, I'm ensuring that the condition (obj.monet >= :min) is true if it doesn't have a value of (min) or (max). Query, no coalesce - (:min IS NULL OR obj.moment >= :min) When I use it without the coalesce function, the endpoint query works, but with the coalesce function, I am not able to perform the query. — dlima, Nov 14 '22 at 19:35
This error is showing when I perform the test on the H2 database, in postgres, I managed to run the Query — dlima, Nov 14 '22 at 20:12
`coalesce()` returns the first non-null value. `coalesce(:min, null)` makes no sense and is the same as writing `:min` — , Nov 14 '22 at 21:32
You don't need the coalesce function for that at all just remove it and use `(:min IS NULL OR obj.moment >= :min)` as you mentioned that you want the query to run whether the parameter exists or not. — Jorge Campos, Nov 14 '22 at 21:42
Yes, it doesn't make sense. However, I use two development environments, one for H2 tests and one for production, which is Postgres. In H2 I can use (:min IS NULL OR obj.moment >= :min), without the need to coalesce and perform the query using the GET method In Postgress, it needs to use (COALESCE(:min, null) IS NULL OR obj.moment >= :min), if not, I can't perform the GET method. In Postgress it does not recognize IS NULL. The problem is that all the time I need to change my Query in the (RecordRepository interface) — dlima, Nov 14 '22 at 23:20

Giovanni · Answer 1 · 2023-02-02T21:33:13.797

I had the very same problem: COALESCE works with Postgress and it crashs the same exception with H2 database, so I created a workaround implementing two repositories interfaces (Product entity) and used annotation @profile, then in compilation time the correct query will be built. First of all I created a base repository interface without extends no other interface:

public interface ProductRepositoryBase {
    
    Page<Product>      findCustomized(List<Category> categories, String name, Pageable page);
    Optional<Product>  findById(Long id);
    Product save(Product entity);
    void deleteById(Long id);
}

Then I created two other interfaces (one for each profile), one for development with postgres db (dev):

@Profile("dev")  
public interface ProductRepositoryDev extends ProductRepositoryBase,JpaRepository<Product, Long>  {
    
    @Query("SELECT DISTINCT obj FROM Product obj INNER JOIN obj.categories cats "
             + "WHERE (COALESCE(:categories,null) IS NULL OR cats IN :categories) AND "
             + "(LOWER(obj.name) LIKE LOWER(CONCAT('%',:name,'%' )))")
        Page<Product> findCustomized(List<Category> categories, String name, Pageable page);

}

And another for test (where H2 database is used):

@Profile("test")
public interface ProductRepositoryTest extends ProductRepositoryBase,JpaRepository<Product, Long> {

    @Query("SELECT DISTINCT obj FROM Product obj INNER JOIN obj.categories cats "
             + "WHERE (:categories IS NULL OR cats IN :categories) AND "
             + "(LOWER(obj.name) LIKE LOWER(CONCAT('%',:name,'%' )))")
    Page<Product> findCustomized(List<Category> categories, String name, Pageable page);

}

Note that my problematic query was associated with findCustomized method, but in the base interface you must declare any other repository ,method you use in the Product entity service layer, otherwise when running the code it will crash as these functions are not defined in the base interface.

Also in the service layer you need to declare the base interface

@Service
public class ProductService {

    @Autowired
    private ProductRepositoryBase productRepository;

Another note: It seems that Spring Boot has tagged COALESCE as deprecated, a colegue of mine tested for Spring Boot 2.4.4 the query with COALESCE with H2 and it worked fine, but as I´m using 2.7.3 it raises the exception. Anyway the query writen in @Profile("test") worked fine for both databases (H2 and postgres).

COALESCE function in JPA

1 Answers1