HQL query parser does not support all fields in extract()

[dfschneid]

The HQL query parser does not seem to support all fields in the extract() function. According to the Hibernate user guide, fields such as day of week should be possible. However, this does not work in practice.

An example:

@Query("select extract(day of week from departureTime) AS day, sum(duration) as duration from JourneyEntity" +
        " group by extract(day of week from departureTime)")
List<Object[]> findJourneyDurationByDayOfWeek();

When running this query, I get the following stack trace, which apparently originates in the HQL parser of Spring Data JPA:

org.springframework.data.jpa.repository.query.BadJpqlGrammarException: Line 1:19 no viable alternative at input 'extract(dayof'; Bad JPQL grammar [select extract(day of week from departureTime) AS day, count(*) as count from JourneyEntity group by extract(day of week from departureTime)]
    at org.springframework.data.jpa.repository.query.BadJpqlGrammarErrorListener.syntaxError(BadJpqlGrammarErrorListener.java:39) ~[spring-data-jpa-3.1.5.jar:3.1.5]
    at org.antlr.v4.runtime.ProxyErrorListener.syntaxError(ProxyErrorListener.java:41) ~[antlr4-runtime-4.10.1.jar:4.10.1]
    at org.antlr.v4.runtime.Parser.notifyErrorListeners(Parser.java:543) ~[antlr4-runtime-4.10.1.jar:4.10.1]
    at org.antlr.v4.runtime.DefaultErrorStrategy.reportNoViableAlternative(DefaultErrorStrategy.java:310) ~[antlr4-runtime-4.10.1.jar:4.10.1]
    at org.antlr.v4.runtime.DefaultErrorStrategy.reportError(DefaultErrorStrategy.java:136) ~[antlr4-runtime-4.10.1.jar:4.10.1]
    at org.springframework.data.jpa.repository.query.HqlParser.expressionOrPredicate(HqlParser.java:9218) ~[spring-data-jpa-3.1.5.jar:3.1.5]
...

I am using Hibernate 6.2.13 and Spring Data JPA 3.1.5 (as defined by Spring Boot 3.1.5).

A quick glance at the grammars used for the Hibernate HqlParser and the Spring Data JPA HqlParser reveals a difference in the definition of extractFunction that seems to explain the behavior.

Allowing arbitrary expressions in the first argument position of the function also appears to me to be too general. Perhaps the grammar of the Hibernate HQL parser could be used for the first argument instead?

[gregturn]

Resolved.

[gregturn]

Backported to 3.1.x.