I'm working on Java 21 with Spring Boot. I have a batch of Junit tests for my project. As per my last task, I implemented a scheduler that physically deletes rows on my MySql DB which where logically deleted for a fixed time period.

I pass the number of days that should have passed since a logical deletion to my query, and based on this input, the records will be physically deleted. For example, if I pass 120, it means that any records that were logically deleted more than 120 days ago should be physically deleted now.

This is the query:

@Query("SELECT o FROM OutputStreamJpa o WHERE o.deleted IS NOT NULL " +
            "AND DATEDIFF(CURRENT_DATE, o.deleted) >= :days")
    List<OutputStreamJpa> findDeletedOlderThanFixedDays(Integer days);

Thing is, this breaks all my JUnit tests. It gives really strange errors, like that certain classes have not been autowired correctly. Removing this query fixes it.

Caused by: java.lang.IllegalArgumentException: .hibernate.query.sqm.produce.function.FunctionArgumentException: Parameter 1 of function 'timestampdiff()' has type 'TEMPORAL_UNIT', but argument is of type 'java.lang.Object'

I'm using an H2 DB for my JUnit Tests, if it can be of any help to you.

I'm working on Java 21 with Spring Boot. I have a batch of Junit tests for my project. As per my last task, I implemented a scheduler that physically deletes rows on my MySql DB which where logically deleted for a fixed time period.

I pass the number of days that should have passed since a logical deletion to my query, and based on this input, the records will be physically deleted. For example, if I pass 120, it means that any records that were logically deleted more than 120 days ago should be physically deleted now.

This is the query:

@Query("SELECT o FROM OutputStreamJpa o WHERE o.deleted IS NOT NULL " +
            "AND DATEDIFF(CURRENT_DATE, o.deleted) >= :days")
    List<OutputStreamJpa> findDeletedOlderThanFixedDays(Integer days);

Thing is, this breaks all my JUnit tests. It gives really strange errors, like that certain classes have not been autowired correctly. Removing this query fixes it.

Caused by: java.lang.IllegalArgumentException: .hibernate.query.sqm.produce.function.FunctionArgumentException: Parameter 1 of function 'timestampdiff()' has type 'TEMPORAL_UNIT', but argument is of type 'java.lang.Object'

I'm using an H2 DB for my JUnit Tests, if it can be of any help to you.

• java
• spring-boot
• hibernate
• h2

• According to the error message, the code executed has timestampdiff function, not datediff function. Which one do you actually use? – Shadow Commented Mar 31 at 8:56
• The error looks like @Query function (?) decides that DATEDIFF literal is not a function name in the query text which must be transferred as-is but a placeholder which must be substituted or Java function name which must be evaluated. I cannot find any otherr reason why the error refers on timestampdiff() function which is not present in the query text.. – Akina Commented Mar 31 at 8:57
• Moreover, this error message does not come from mysql, this is a hibernate one. Is this a native sql query or a HQL one? – Shadow Commented Mar 31 at 8:59
• 1 Try to rewrite the condition to .. AND CURRENT_DATE - INTERVAL :days DAY >= o.deleted. As a bonus the condition will become SARGable. – Akina Commented Mar 31 at 9:00
• 2 I would also caution you against testing on product A and then use product B in production - this is recipe for disaster! – Shadow Commented Mar 31 at 9:06

1 Answer 1

In both the production MySQL-DB and the H2-DB you are executing a function called DATEDIFF. That is simply not the same function though, they don't even have compatible signatures.

mysql DATEDIFF(expr1,expr2)

h2 DATEDIFF(datetimeField, aDateAndTime, bDateAndTime)

So this is not an issue with your code, query or configuration. This is a hard limit on testing a program intended for MySQL-production-use with an H2-replacement.

What are your options here?

(1) Verify the query manually and mock it out for tests. Leave a comment explaining this, it will be valuable to anyone changing the query or writing another test using the method.

(2) Test with a real MySQL-Database - the problems of testing with a real database can be greatly mitigated with testcontainers.

(3) Don't use the datediff-function and pull the logic up into your java-program.

I gathered from comments that H2 has a MySQL 'compatibility mode' which is documented (thanks @ pebble unit). The list of MySQL-features that H2 supports in this mode is limited. DATEDIFF is nowhere to be seen in any variant of this mode, and as I understand the wording in this documentation, this is intended to be a complete list of adjustments H2 makes to offer some level of MySQL-compatibility. What they do warn is:

However, only a small subset of the differences between databases are implemented [...]

So this is no solution to this particular problem.