# Pagination in JPA query with IN and a null value list

**URL:** <https://discourse.hibernate.org/t/pagination-in-jpa-query-with-in-and-a-null-value-list/11204>\
**Category:** Hibernate ORM\
**Created:** [March 12, 2025, 5:13pm UTC](https://discourse.hibernate.org/t/pagination-in-jpa-query-with-in-and-a-null-value-list/11204 "2025-03-12T17:13:15Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![ado](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/ado/32/3902_2.png) [@ado](https://discourse.hibernate.org/u/ado)\
**Post date:** [March 12, 2025, 5:13pm UTC](https://discourse.hibernate.org/t/pagination-in-jpa-query-with-in-and-a-null-value-list/11204/1 "2025-03-12T17:13:15Z")

</div>

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

```auto
  @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:

```auto
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:

```auto
        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));

```

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [March 13, 2025, 1:28pm UTC](https://discourse.hibernate.org/t/pagination-in-jpa-query-with-in-and-a-null-value-list/11204/2 "2025-03-13T13:28:43Z")

</div>

This is a Hibernate ORM forum, not a Spring Data forum. I don’t know what Spring Data does behind the scenes, so if you seek help here, please remove Spring from the equation. It would be best if you try to create a reproducer with our [test case template](https://github.com/hibernate/hibernate-test-case-templates/blob/master/orm/hibernate-orm-6/src/test/java/org/hibernate/bugs/JPAUnitTestCase.java) and if you are able to reproduce the issue, create a bug ticket in our [issue tracker](https://hibernate.atlassian.net) and attach that reproducer.