I'm trying to upgrade my application that is currently running on Spring Boot 3.0.7 to the latest version (3.1.1). After the upgrade, the application fails to start during a query validation. I was able to figure out this is only affecting the queries with a distinct in the select clause. I'm guessing this might be related to the new query parser but I couldn't find anything that effectively confirms this.
Here is the repository with the query:
@Repository
public interface CountryRepository extends PagingAndSortingRepository<Country, Long> {
@Query(value = """
select c.name as name
from Country c
where c.name like :name
""")
Page<CountryProjection> customFindCountryByName(@Param("name") String name, Pageable pageable);
}
If you add a "distinct" to the query in the CountryRepository, the issue is reproducible.
You can find a simplified version of my application here
Here is a relevant part of the stack trace:
Caused by: java.lang.IllegalArgumentException: org.hibernate.query.SemanticException: A query exception occurred [select count(distinct me.name as name) from Country me where me.name like :name]
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:138) ~[hibernate-core-6.2.5.Final.jar:6.2.5.Final]
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:162) ~[hibernate-core-6.2.5.Final.jar:6.2.5.Final]
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:168) ~[hibernate-core-6.2.5.Final.jar:6.2.5.Final]
at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:795) ~[hibernate-core-6.2.5.Final.jar:6.2.5.Final]
at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:704) ~[hibernate-core-6.2.5.Final.jar:6.2.5.Final]
at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:120) ~[hibernate-core-6.2.5.Final.jar:6.2.5.Final]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:360) ~[spring-orm-6.0.10.jar:6.0.10]
at jdk.proxy2/jdk.proxy2.$Proxy104.createQuery(Unknown Source) ~[na:na]
at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:94) ~[spring-data-jpa-3.1.1.jar:3.1.1]
... 48 common frames omitted
Caused by: org.hibernate.query.SemanticException: A query exception occurred [select count(distinct me.name as name) from Country me where me.name like :name]
at org.hibernate.query.hql.internal.StandardHqlTranslator.parseHql(StandardHqlTranslator.java:130) ~[hibernate-core-6.2.5.Final.jar:6.2.5.Final]
at org.hibernate.query.hql.internal.StandardHqlTranslator.translate(StandardHqlTranslator.java:77) ~[hibernate-core-6.2.5.Final.jar:6.2.5.Final]
at org.hibernate.internal.AbstractSharedSessionContract.lambda$interpretHql$2(AbstractSharedSessionContract.java:744) ~[hibernate-core-6.2.5.Final.jar:6.2.5.Final]
at org.hibernate.query.internal.QueryInterpretationCacheStandardImpl.createHqlInterpretation(QueryInterpretationCacheStandardImpl.java:141) ~[hibernate-core-6.2.5.Final.jar:6.2.5.Final]
at org.hibernate.query.internal.QueryInterpretationCacheStandardImpl.resolveHqlInterpretation(QueryInterpretationCacheStandardImpl.java:128) ~[hibernate-core-6.2.5.Final.jar:6.2.5.Final]
at org.hibernate.internal.AbstractSharedSessionContract.interpretHql(AbstractSharedSessionContract.java:741) ~[hibernate-core-6.2.5.Final.jar:6.2.5.Final]
at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:786) ~[hibernate-core-6.2.5.Final.jar:6.2.5.Final]
... 57 common frames omitted
Caused by: org.hibernate.query.sqm.ParsingException: line 1:30 no viable alternative at input 'selectcount(distinctme.nameas'
at org.hibernate.query.hql.internal.StandardHqlTranslator$1.syntaxError(StandardHqlTranslator.java:46) ~[hibernate-core-6.2.5.Final.jar:6.2.5.Final]
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.hibernate.grammars.hql.HqlParser.queryExpression(HqlParser.java:1780) ~[hibernate-core-6.2.5.Final.jar:6.2.5.Final]
at org.hibernate.grammars.hql.HqlParser.selectStatement(HqlParser.java:400) ~[hibernate-core-6.2.5.Final.jar:6.2.5.Final]
at org.hibernate.grammars.hql.HqlParser.statement(HqlParser.java:331) ~[hibernate-core-6.2.5.Final.jar:6.2.5.Final]
at org.hibernate.query.hql.internal.StandardHqlTranslator.parseHql(StandardHqlTranslator.java:116) ~[hibernate-core-6.2.5.Final.jar:6.2.5.Final]
... 63 common frames omitted
This seem to be a Spring Data JPA bug in my opinion and I opened a ticket for it
https://github.com/spring-projects/spring-data-jpa/issues/3098
The generated count query is syntactically not proper in spring data jpa's side and the HQL->SQL transition of it will not work. Follow up that ticket for more info. I have no better idea than to revert back to spring 3.0.9 for now and wait for a bugfix.
As for another workaround, if you desperately need to upgrade, try to avoid the distinct somehow in the query, if possible any by means, or to not use Page and use Slice, if you workflow allows (that will not use count, but then you can forget total count and total pages numbers), or try to define the count query manually until they fix it (there is an annotation parameter for it). My side has a lot of similar queries so I am not really ok with any of these, my only alternative now is spring boot 3.0.9
EDIT: since my last comment, the authors declined my ticket and because (very logical of course) the count query for such distincts were not correct. Their suggestion was to make the count query manually and add it to the @Query annotation. I made another comment on the ticket for a "proposal", but I doubt it will go over, so overall, this is now a breaking change in spring 3.1, which - because it is actually "fixing" a bug - will not be reverted in any way.