I'm using springboot 3.4.0 and via JPA I wrote this query:

  @Query(value = "select c from Movement c left join c.currency cu WHERE c.user.id=?1 and (?2 IS NULL or cu.iso IN ?2)")
  Page<Movement> findAllByUser( UUID userId, List<String> currencies,Pageable pageable);

SQL trace shows me these parameters passed:

2025-03-12 17:49:07,518 [http-nio-9090-exec-4] TRACE o.h.o.jdbc.bind - binding parameter (1:VARCHAR) <- [d55f237c-7eab-4bcd-a688-10fdb3ed09a3]
2025-03-12 17:49:07,518 [http-nio-9090-exec-4] TRACE o.h.o.jdbc.bind - binding parameter (2:JAVA_OBJECT) <- [null]
2025-03-12 17:49:07,518 [http-nio-9090-exec-4] TRACE o.h.o.jdbc.bind - binding parameter (3:VARCHAR) <- [null]
2025-03-12 17:49:07,518 [http-nio-9090-exec-4] TRACE o.h.o.jdbc.bind - binding parameter (4:INTEGER) <- [50]

and the native query, just the where statement:

where a1_0.core_user_id=? and (? is null or c1_0.iso in (?)) fetch first ? rows only

The error I got is this:

2025-03-12 17:49:07,593 [http-nio-9090-exec-4] ERROR c.e.c.c.CustomExceptionHandlerResolver - At least 3 parameter(s) provided but only 2 parameter(s) present in query .springframework.dao.InvalidDataAccessApiUsageException: At least 3 parameter(s) provided but only 2 parameter(s) present in query

I did some test, if I use only ?2 IS NULL it works, the problem seems to be with this IN ?2 when the list is null, if I pass a populated list everything works fine.

UPDATE I figure out that the problem seems the pagination. If I remove the pagination the query works fine, if I remove the List parameters and keep the pagination it works. The pagination is simple like this:

        Sort.Direction sortDirection = Sort.Direction.DESC;
        String sortField = "c.id";
        Sort.Order queryOrder = new Sort.Order(sortDirection, sortField);
        Pageable pagingSort = PageRequest.of(1, 10,Sort.by(queryOrder));

I'm using springboot 3.4.0 and via JPA I wrote this query:

  @Query(value = "select c from Movement c left join c.currency cu WHERE c.user.id=?1 and (?2 IS NULL or cu.iso IN ?2)")
  Page<Movement> findAllByUser( UUID userId, List<String> currencies,Pageable pageable);

SQL trace shows me these parameters passed:

2025-03-12 17:49:07,518 [http-nio-9090-exec-4] TRACE o.h.o.jdbc.bind - binding parameter (1:VARCHAR) <- [d55f237c-7eab-4bcd-a688-10fdb3ed09a3]
2025-03-12 17:49:07,518 [http-nio-9090-exec-4] TRACE o.h.o.jdbc.bind - binding parameter (2:JAVA_OBJECT) <- [null]
2025-03-12 17:49:07,518 [http-nio-9090-exec-4] TRACE o.h.o.jdbc.bind - binding parameter (3:VARCHAR) <- [null]
2025-03-12 17:49:07,518 [http-nio-9090-exec-4] TRACE o.h.o.jdbc.bind - binding parameter (4:INTEGER) <- [50]

and the native query, just the where statement:

where a1_0.core_user_id=? and (? is null or c1_0.iso in (?)) fetch first ? rows only

The error I got is this:

2025-03-12 17:49:07,593 [http-nio-9090-exec-4] ERROR c.e.c.c.CustomExceptionHandlerResolver - At least 3 parameter(s) provided but only 2 parameter(s) present in query .springframework.dao.InvalidDataAccessApiUsageException: At least 3 parameter(s) provided but only 2 parameter(s) present in query

I did some test, if I use only ?2 IS NULL it works, the problem seems to be with this IN ?2 when the list is null, if I pass a populated list everything works fine.

UPDATE I figure out that the problem seems the pagination. If I remove the pagination the query works fine, if I remove the List parameters and keep the pagination it works. The pagination is simple like this:

        Sort.Direction sortDirection = Sort.Direction.DESC;
        String sortField = "c.id";
        Sort.Order queryOrder = new Sort.Order(sortDirection, sortField);
        Pageable pagingSort = PageRequest.of(1, 10,Sort.by(queryOrder));

• spring-boot
• hibernate

• 1 did I write something wrong? why the downvote? Please advice so I will not repeat the error – NiBE Commented Mar 12 at 17:08
• What are you passing to it for ?2? It may need to be an array. – Andrew Morton Commented Mar 12 at 17:26
• yes I pass null as ?2 but I can also pass a populated String array. I expects that if is null then it will not go to the IN statement due to the OR – NiBE Commented Mar 12 at 17:29
• 1 @AndrewMorton the example is a JPA query it's not native SQL – NiBE Commented Mar 12 at 18:04

1 Answer 1

I expects that if is null then it will not go to the IN statement due to the OR

query params are statically binded before execution and short-circuit paths are not involved in this phase so you have to convert cu.iso in ?2 in a valid IN clause; you can do this

1. passing an empty currencies instead of a null value
2. do a coalesce(?2, <empty list>) (looks if JPA has some functions to create empty list)
3. rewrite the and clause as

case
 when ?2 is null then true
 else cu.iso IN (?2)
end