Skip to content

The repository countDistinctBy** method generates the wrong sql query [DATAJPA-1036] #1380

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
spring-projects-issues opened this issue Jan 4, 2017 · 1 comment
Closed

The repository countDistinctBy** method generates the wrong sql query [DATAJPA-1036] #1380

spring-projects-issues opened this issue Jan 4, 2017 · 1 comment
Assignees
@odrotbohm @gregturn
Labels
in: query-parser Everything related to parsing JPQL or SQL type: bug A general bug
Milestone
3.0 M4 (2022.0.0)

Comments

@spring-projects-issues
Copy link

Anthony opened DATAJPA-1036 and commented

When we use a count query with a distinct:

long countDistinctVersionIdBy*(...)

The generated sql is:

select distinct count(version_id) ..

instead of

select count(distinct version_id) ..

The results of the two queries are different, adding a distinct before the count does not change the result, while adding it inside the count can change the result

Affects: 1.10.5 (Hopper SR5)

1 votes, 1 watchers
@spring-projects-issues spring-projects-issues added the type: enhancement A general enhancement label Dec 30, 2020
@spring-projects-issues spring-projects-issues added the in: query-parser Everything related to parsing JPQL or SQL label Dec 30, 2020
@schauder schauder added the status: waiting-for-triage An issue we've not yet triaged label Mar 3, 2021
@gregturn gregturn mentioned this issue May 7, 2022
Closed
@gregturn
Copy link
Contributor

gregturn commented May 7, 2022 •
edited
Loading

This issue is definitely valid. It reveals that JpaCountQueryCreator needs its own patch to handle boolean distinct than how JpaQueryCreator does.

A small patch can fix this from being select distinct count(distinct ...) ... to select count(distinct ...)....

However, this is incomplete, since countDistinctByXXX() will still render select count(distinct u.id) ..., which has no net benefit. So I went and captured this bigger issue of how Spring Data JPA deals with DISTINCT operations in #2525.

@gregturn gregturn self-assigned this May 9, 2022
@gregturn gregturn added type: bug A general bug and removed status: waiting-for-triage An issue we've not yet triaged type: enhancement A general enhancement labels May 9, 2022
gregturn added a commit that referenced this issue May 9, 2022
@gregturn
Remove duplicate 'distinct' applied in count operations.
bfdadfc 
When performing a count operation, we are using countDistinct from JPA, and hence, don't need the JpaQueryCreator applying distinct outside the whole thing.

Also added some details in the ref docs to help guide users on writing proper distinct-based queries.

See #1380.
gregturn added a commit that referenced this issue May 9, 2022
@gregturn
Remove duplicate 'distinct' applied in count operations.
8ea70c1 
When performing a count operation, we are using countDistinct from JPA, and hence, don't need the JpaQueryCreator applying distinct outside the whole thing.

Also added some details in the ref docs to help guide users on writing proper distinct-based queries.

See #1380.
@gregturn gregturn closed this as completed May 9, 2022
@gregturn gregturn added this to the 3.0 M4 (2022.0.0) milestone May 9, 2022
@gregturn gregturn mentioned this issue May 9, 2022
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@odrotbohm odrotbohm

@gregturn gregturn

Labels
in: query-parser Everything related to parsing JPQL or SQL type: bug A general bug
Projects
None yet
Milestone
3.0 M4 (2022.0.0)
Development

No branches or pull requests
4 participants
@odrotbohm @schauder @gregturn @spring-projects-issues