Why does jpa change parameter type for null values?-CodePudding

I'm using springboot and jpa with postgres database. For some reason the inputs to this query are changing when the value is null. Why is that?

@Query(nativeQuery = true, value = "select ?1 a, ?2 b, ?3 c, ?4 d")
Optional<List<List<Object>>> testDates(Instant i, ZonedDateTime zdt, Timestamp ts, LocalDateTime ldt);

first test with nulls:

testRepo.testDates(null,null,null,null);

logs:
binding parameter [1] as [VARBINARY] - [null]
binding parameter [2] as [VARBINARY] - [null]
binding parameter [3] as [VARBINARY] - [null]
binding parameter [4] as [VARBINARY] - [null]

second query:

testRepo.testDates(Instant.now(), 
        Instant.now().atOffset(ZoneOffset.of("-05:00")).toZonedDateTime(), 
        Timestamp.from(Instant.now()), 
        Timestamp.from(Instant.now()).toLocalDateTime());

logs:
binding parameter [1] as [TIMESTAMP] - [2022-07-08T09:10:53.683Z]
binding parameter [2] as [TIMESTAMP] - [2022-07-08T04:10:53.683-05:00]
binding parameter [3] as [TIMESTAMP] - [2022-07-08 09:10:53.683]
binding parameter [4] as [TIMESTAMP] - [2022-07-08T09:10:53.683]

Why is the datatype changing when its null? Shouldnt JPA be looking at the method signature and determining its a timestamp even when the value is null? I want to allow nulls in another query and want to do something like below but it fails when the parameter is null because its being passed as a different type:

select * 
from x 
where (?1 is null or ?1 between x.enroll_start and x.enroll_end)
and (?2 is null or ?2 between x.active_start and x.active_end)

CodePudding user response：

It looks like this works:

@Query(nativeQuery = true, value = "select * from myTable "   
    "where cast(?1 as TEXT) is null "  
    "or cast(CAST(?1 as TEXT) as TIMESTAMP WITH TIME ZONE) "   
    "between start and end")
Optional<List<List<Object>>> search(ZonedDateTime date);

The key being I have to cast the value to text in both the null check and the actual date range check. Its ugly but I can pass null and it will return everything or I can pass a date and it will return just the records where the parameter is between start/end. I was about to give up and just pass the date as a string which is very much bad don't.