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 org.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.
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));