Currently, I’m migrating my application from spring-boot 2.3 to 3.2 and one of the tests stopped working.

There is a simple entity

@Entity
public class MyEntity {
   // .... other properties
   private LocalDatetime created;

}

There is a Repository with native query:

public interface MyEntityRepository extends JpaRepository<MissiveEntity, Long> {

   @Query(
      value = """
       with entities as (
       select * 
       from my_entity 
       where created >= :startTime
       )
       ....
      """,
      native = true
   )
   List<MyEntity> fetchAllWithoutAcknowledgement(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);
}

There is a big SQL statement but there is only one usage of startTime and endTime properties.

The problem is when I’m testing this code this method returns an empty list. I have test data filled with @Sql annotation before the test. If I set a break point during debug and evaluate myEntityRepository.findAll() I will see all the records, and dates are correct in all of them. If I remove WHERE clause from this SQL query it’ll return all records.

I can even create a method in repository

List<MissiveEntity> findAllByCreatedBetween(LocalDateTime start, LocalDateTime end);

evaluate this method during debug with the same time values and it’ll return me correct records, but native query doesn’t work.

I enabled debug logging here is how table is created:

Hibernate: 
    create table my_entity (
        created timestamp(6),
        id bigint not null,
        ...
        primary key (id),
    )

and the SQL the is executed:

2024-05-24T18:31:57.130+03:00  INFO 1468150 --- [           main] h2database                               : jdbc[13] 
/*SQL l:1081 t:3*/with entities as (n    SELECT *n    FROM my_entityn    WHERE created >= ?n)... {1: TIMESTAMP '2024-04-17 07:59:00', 2: TIMESTAMP '2024-04-17 07:59:50'};

So internally in h2 timestamp(6) type is used, also Hibernate substitute named param with looks like correct value TIMESTAMP '2024-04-17 07:59:00', but still I get 0 rows.

The same logic works fine with PostgreSQL only failing in tests with h2.

Previous working h2 version was 1.4.200, current is 2.2.224