Using the by keyword on a duration object causes a BadJpqlGrammarException

[rhpkegel]

We recently migrated from Spring boot 3.3.5 to 3.4.0, which upgrades the underlying Spring Data and Hibernate dependencies. However, we encounter a BadJpqlGrammarException when booting up the application, as the following method in a JPARepository (simplified) fails to parse:

@Query(value = "SELECT a FROM afspraak a WHERE (cast(a.startDatumTijd as date) - CURRENT_DATE) BY day - 2 = 0") Set<Afspraak> springDataParserError();

A minimal example of the entity in question:

@Entity(name = "afspraak")
public class Afspraak
{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    private Date startDatumTijd;
}

A truncated stack trace for convenience:

Caused by: org.springframework.data.jpa.repository.query.BadJpqlGrammarException: Line 1:79 mismatched input 'BY' expecting {')', '+', '-', '/', '||', AND, '*', BY, DAY, EPOCH, HOUR, MINUTE, MONTH, NANOSECOND, OR, QUARTER, SECOND, WEEK, YEAR}; Bad JPQL grammar [SELECT a FROM afspraak a WHERE (cast(a.startDatumTijd as date) - CURRENT_DATE) BY day - 2 = 0]
	at org.springframework.data.jpa.repository.query.BadJpqlGrammarErrorListener.syntaxError(BadJpqlGrammarErrorListener.java:39) ~[spring-data-jpa-3.4.0.jar:3.4.0]
	at org.antlr.v4.runtime.ProxyErrorListener.syntaxError(ProxyErrorListener.java:41) ~[antlr4-runtime-4.13.0.jar:4.13.0]
	at org.antlr.v4.runtime.Parser.notifyErrorListeners(Parser.java:544) ~[antlr4-runtime-4.13.0.jar:4.13.0]
	at org.antlr.v4.runtime.DefaultErrorStrategy.reportInputMismatch(DefaultErrorStrategy.java:327) ~[antlr4-runtime-4.13.0.jar:4.13.0]
	at org.antlr.v4.runtime.DefaultErrorStrategy.reportError(DefaultErrorStrategy.java:139) ~[antlr4-runtime-4.13.0.jar:4.13.0]
	at org.springframework.data.jpa.repository.query.HqlParser.start(HqlParser.java:266) ~[spring-data-jpa-3.4.0.jar:3.4.0]
	at org.springframework.data.jpa.repository.query.JpaQueryEnhancer.parse(JpaQueryEnhancer.java:76) ~[spring-data-jpa-3.4.0.jar:3.4.0]
	at org.springframework.data.jpa.repository.query.JpaQueryEnhancer$HqlQueryParser.<init>(JpaQueryEnhancer.java:240) ~[spring-data-jpa-3.4.0.jar:3.4.0]
	at org.springframework.data.jpa.repository.query.JpaQueryEnhancer$HqlQueryParser.parseQuery(JpaQueryEnhancer.java:252) ~[spring-data-jpa-3.4.0.jar:3.4.0]
	at org.springframework.data.jpa.repository.query.JpaQueryEnhancer.forHql(JpaQueryEnhancer.java:122) ~[spring-data-jpa-3.4.0.jar:3.4.0]
	at org.springframework.data.jpa.repository.query.QueryEnhancerFactory.forQuery(QueryEnhancerFactory.java:68) ~[spring-data-jpa-3.4.0.jar:3.4.0]
	at org.springframework.data.jpa.repository.query.StringQuery.<init>(StringQuery.java:88) ~[spring-data-jpa-3.4.0.jar:3.4.0]
	at org.springframework.data.jpa.repository.query.ExpressionBasedStringQuery.<init>(ExpressionBasedStringQuery.java:65) ~[spring-data-jpa-3.4.0.jar:3.4.0]
	at org.springframework.data.jpa.repository.query.AbstractStringBasedJpaQuery.<init>(AbstractStringBasedJpaQuery.java:84) ~[spring-data-jpa-3.4.0.jar:3.4.0]
	at org.springframework.data.jpa.repository.query.SimpleJpaQuery.<init>(SimpleJpaQuery.java:65) ~[spring-data-jpa-3.4.0.jar:3.4.0]
	at org.springframework.data.jpa.repository.query.JpaQueryFactory.fromMethodWithQueryString(JpaQueryFactory.java:49) ~[spring-data-jpa-3.4.0.jar:3.4.0]

According to the documentation at https://docs.jboss.org/hibernate/orm/6.6/querylanguage/html_single/Hibernate_Query_Language.html#Datetime-arithmetic this should still be valid syntax according to Hibernate 6.6, and it did in fact compile and run on Spring Boot 3.3.5.

[mp911de]

This is a problem on our side as we're parsing declared queries much earlier now, resulting in early parsing errors in case our HQL parser isn't fully aligned (which is the case here). Thanks for the report.

[mp911de]

It turns out that some internal issues with grammar are causing this issue. They have some ambiguity leading to parser ambiguities in the SLL mode, which is fast, but it doesn't consider contextual parsing information to resolve ambiguities. These then lead parser failures.

Trying to parse the query with a slower, but context-aware LL model fixes the issue. However, there is nothing really you can do so we need to ship a fix.