2

I am trying to fetch a list of records between two Instants using Spring Data's @Query.

 /**
   * Finds all the non-duplicate customers.
   * 
   * @return The list of non-duplicate customers.
   */
  //@formatter:off
  @Query("SELECT c "
      + "FROM Customer c "
      + "WHERE c.isDuplicate = false "
      + "  AND c.created BETWEEN :start AND :end "
      + "ORDER BY c.created")
  //@formatter:on
  List<Customer> findAllNonDuplicateCustomers(
      @Param("start") Instant start,
      @Param("end") Instant end
  );

While testing I found that:

this.customerRepository.findAllNonDuplicateCustomers(
    Instant.MIN, 
    Instant.MAX
)

returns an empty list, but:

this.customerRepository.findAllNonDuplicateCustomers(
    this.customers.get("rob").getCreated(), 
    this.customers.get("robba").getCreated()
)

returns the desired results.

Test scenario:

My test inserts 6 customers into an embedded H2 database and tries to run the query.

Since Instant is Comparable, and it works fine for exact customers, is it wrong to use Instant.MIN and Instant.MAX for boundary testing?

sidmishraw
  • 390
  • 4
  • 15

1 Answers1

1

I created a little example project and enabled hibernates logging for the bind parameters resulting in this: 

binding parameter [1] as [TIMESTAMP] - [-1000000000-01-01T00:00:00Z]
binding parameter [2] as [TIMESTAMP] - [+1000000000-12-31T23:59:59.999999999Z]

As one can see the arguments get passed to the database without any modification. The database just can't handle it properly, but it obviously can handle more "normal" values.

I'd consider it a bug or at least a limitation of the JDBC driver. I also tried it with HSQLDB. It has a similar limitation but instead of returning empty results it throws an exception.

So what can you do:

  • Submit an issue with H2. They might add a check and throw an exception but I doubt that they will add proper support.

  • Define your own MIN, MAX values that actually work. After a little experimentation, this seems to work reasonably well in the sense that H2 can handle it and it's hard to come up with real-world scenarios where these are sufficiently far in the future/past.

    static final Instant CUSTOM_MIN = new Date(Long.MIN_VALUE / 2).toInstant();
static final Instant CUSTOM_MAX = new Date(Long.MAX_VALUE / 2).toInstant();

Notes:

There don't exist database independent min/max dates.

The minimum/maximum java.util.Date values here don't work either but obviously inspired my solution.

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
  • I converted the java.time.Instant into a java.sql.Timestamp using the Timestamp.from(Instant) method. I got the following: ` Timestamp.from(Instant.MIN) = 169108098-07-03 21:51:43.0 Timestamp.from(Instant.MAX) = 169104627-12-11 11:08:15.999999999 Timestamp.from(Instant.now()) = 2018-07-22 00:46:17.518 ` – sidmishraw Jul 22 '18 at 07:52
  • Continuing from the previous comment, when I do a `present.after(past)` –– where present is the Timestamp.from(Instant.now()) and past is the Timestamp.from(Instant.MIN) –– I get `false`. Could this be a contributing factor? P.S. The javadoc for Timestamp#after(Timestamp) reads: Indicates whether this Timestamp object is later than the given Timestamp object. – sidmishraw Jul 22 '18 at 08:02
  • `past.after(present)` gives `true`. – sidmishraw Jul 22 '18 at 08:04
  • #Continuing# However, simulating it with the example below `Timestamp present_1 = Timestamp.from(Instant.now());` `Thread.sleep(1000);` `Timestamp present_2 = Timestamp.from(Instant.now());` `System.out.println("is present2 after present1 = " + present_2.after(present_1)); // true` `System.out.println("is present1 after present2 = " + present_1.before(present_2)); // true` provides the expected results. – sidmishraw Jul 22 '18 at 08:09
  • Since H2 is written in Java this might actually be the reason, but that is just speculation on my side. – Jens Schauder Jul 22 '18 at 09:07