Add support for Hibernate CTE in HQL parser

[clementdenis]

Hibernate 6.2 introduced CTE support.

But in Spring Data JPA 3.1 (that depends on Hibernate 6.2); such queries are rejected by the HQL parser:

org.springframework.data.jpa.repository.query.BadJpqlGrammarException: Line 1:0 mismatched input 'with' expecting {'(', DELETE, FROM, INSERT, SELECT, UPDATE}; Bad JPQL grammar [with maxId as(
select max(sr.snapshot.id) snapshotId from SnapshotReference sr
    where sr.id.selectionId = ?1 and sr.enabled
    group by sr.userId
    )
select sr from maxId m join SnapshotReference sr on sr.snapshot.id = m.snapshotId
]
    at org.springframework.data.jpa.repository.query.BadJpqlGrammarErrorListener.syntaxError(BadJpqlGrammarErrorListener.java:39)
    at org.antlr.v4.runtime.ProxyErrorListener.syntaxError(ProxyErrorListener.java:41)
    at org.antlr.v4.runtime.Parser.notifyErrorListeners(Parser.java:543)
    at org.antlr.v4.runtime.DefaultErrorStrategy.reportInputMismatch(DefaultErrorStrategy.java:327)
    at org.antlr.v4.runtime.DefaultErrorStrategy.reportError(DefaultErrorStrategy.java:139)
    at org.springframework.data.jpa.repository.query.HqlParser.ql_statement(HqlParser.java:341)
    at org.springframework.data.jpa.repository.query.HqlParser.start(HqlParser.java:250)
    at org.springframework.data.jpa.repository.query.HqlQueryParser.parseQuery(HqlQueryParser.java:53)
    at org.springframework.data.jpa.repository.query.HqlQueryParser.parse(HqlQueryParser.java:63)
    at org.springframework.data.jpa.repository.query.JpaQueryParserSupport$ParseState.lambda$new$0(JpaQueryParserSupport.java:182)
    at org.springframework.data.util.Lazy.getNullable(Lazy.java:245)
    at org.springframework.data.util.Lazy.get(Lazy.java:114)
    at org.springframework.data.jpa.repository.query.JpaQueryParserSupport$ParseState.getContext(JpaQueryParserSupport.java:194)
    at org.springframework.data.jpa.repository.query.JpaQueryParserSupport.renderSortedQuery(JpaQueryParserSupport.java:54)

My query here works just fine when creating the query directly with the entity manager.

[clementdenis]

Until the HQL parser gets updated for Hibernate 6.2, is there a way to disable the query parser for specific queries?

[gregturn]

There isn't any way to disable the query parser.

You can work around this with a custom implementation. Check out https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.custom-implementations for more details on how to hook such a solution into your repository.

However, it won't take long for me to add support.

[gregturn]

Merged to main and backported to 3.1.x.

[Aniket-Singla]

Hi @gregturn / @clementdenis can you please point me to examples of using CTE with Spring Data JPA + Hibernate. I am getting exception

Caused by: org.hibernate.query.sqm.EntityTypeException: Could not resolve entity name 'samCTE'

More Details on https://stackoverflow.com/questions/78004995/cte-usage-with-spring-data-jpa-3-2-hibernate-6-4-does-not-work . Hibernate Query checks is expecting to define an Entity for name of CTE .

[CatPlanet]

polite bump

[christophstrobl]

Thank you @Aniket-Singla / @CatPlanet for getting in touch - We prefer to use GitHub issues only for bugs and enhancements. If you encounter problems with the data-jpa integration (something that cannot be reproduced with plain hibernate) please create a new issue, potentially linking to this closed one. Also make sure to provide more context - ideally take the time to provide a complete minimal sample (something that we can unzip or git clone, build, and deploy) that reproduces the problem.