Count query derivation from DISTINCT queries seems to be broken

[da-z]

I have a large chunk of JPA code that was working fine in Spring Boot 2.x but not in Spring Boot 3.x (Hibernate 6 on Oracle and H2 in Oracle mode in tests):

Sample JPQL query:

SELECT DISTINCT abc AS x FROM T -- (note the AS alias)

In SB 3.x I am getting the error: At 1:n and token 'AS', no viable alternative at input 'SELECT count(DISTINCT abc *AS x

And indeed, that's not a valid SQL query because aliases are not allowed in that scenario.

My take on this is: either strip the alias, or derive the Count query like this, which means you don't need to mess with the original query:

SELECT COUNT(*) FROM ({original_sql}) -- I assume this is how it is working in SB 2.x

[christophstrobl]

Thanks for reporting, sounds like you already have a test that you could share to help us diagnose the issue. Would you mind taking the time to provide a complete minimal sample (something that we can unzip or git clone, build, and deploy) that reproduces the problem?

[da-z]

Sure: https://github.com/da-z/spring-data-jpa-issue-3269 (just run the test)

[gtiburcio]

I'm having the same problem here.

[christophstrobl]

We're working on a solution. Early snapshots (branch builds) should be available soon.

[da-z]

Hello. Will this fix be merged into main?

[THD-Thomas-Lang]

I have the same problem and i could it solve by using a dedicated countQuery() in addition to your @Query(). So kind of this way:@Query("SELECT DISTINCT abc AS x FROM T", countQuery="select count(*) FROM T")

[da-z]

@THD-Thomas-Lang The regression has already been fixed. All we need is more upvoters for this PR to be merged: #3276

[THD-Thomas-Lang]

Didn´t know that there is an upvote function? Thank you!

[da-z]

There is no upvote function but you can leave a comment or react to one of the existing comments. Thanks!