Skip to content

Using native Query with distinct is returning wrong total count #2177

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
OctavioCega opened this issue Mar 14, 2021 · 6 comments
Closed

Using native Query with distinct is returning wrong total count #2177

OctavioCega opened this issue Mar 14, 2021 · 6 comments
Assignees
@schauder
Labels
in: query-parser Everything related to parsing JPQL or SQL type: bug A general bug
Milestone
3.0 M4 (2022.0.0)

Comments

@OctavioCega
Copy link

OctavioCega commented Mar 14, 2021 •
edited
Loading

Hello, It seems that Hibernate is removing the "distinct" key word from the native Query when using the count to get the total elements.

Any Workaround for this?

Springboot 2.3.7

A fragment for the logs:

The log for the get entities

select distinct e.* from schema.entity e 
(more statements...)
...

The log for the count query generated by hibernate (as you can see, for some reason, it is removing the "distinct" keyword)

select count(e) from  schema.entity e 
(more statements...)
...

This seems like bug for me. I saw this issue from a long ago time but without any solution (a part from generating my own count query) #750
@spring-projects-issues spring-projects-issues added the status: waiting-for-triage An issue we've not yet triaged label Mar 14, 2021
@christophstrobl christophstrobl added type: bug A general bug and removed status: waiting-for-triage An issue we've not yet triaged labels Mar 22, 2021
@schauder schauder added the in: query-parser Everything related to parsing JPQL or SQL label Mar 22, 2021
@kaliberr44 kaliberr44 mentioned this issue Oct 19, 2021
Closed
DiegoKrupitza added a commit to DiegoKrupitza/spring-data-jpa that referenced this issue Dec 17, 2021
@DiegoKrupitza
spring-projectsGH-2341 - Fix missing DISTINCT in count query bug.
8f49df7 
The `COUNT_MATCH` did not consider line breaks after the `from` clause or the `where` clause. This lead to a no match scenario in the construction of the count query. With the fix we now consider line breaks/whitespaces after the `from` and `where` clause.

Closes spring-projects#2341
Related tickets spring-projects#2177
@DiegoKrupitza DiegoKrupitza mentioned this issue Dec 18, 2021
Closed
3 tasks
gregturn pushed a commit that referenced this issue Mar 28, 2022
@DiegoKrupitza @gregturn
Fix missing DISTINCT in count queries.
2545e6d 
The `COUNT_MATCH` does not consider line breaks after the `from` clause or the `where` clause. This leads to a no match scenario in the construction of the count query. With the fix we now consider line breaks/whitespaces after the `from` and `where` clause.

See #2341
Related tickets: #2177
gregturn pushed a commit that referenced this issue Mar 28, 2022
@DiegoKrupitza @gregturn
Fix missing DISTINCT in count queries.
a9fbf4c 
The `COUNT_MATCH` does not consider line breaks after the `from` clause or the `where` clause. This leads to a no match scenario in the construction of the count query. With the fix we now consider line breaks/whitespaces after the `from` and `where` clause.

See #2341
Related tickets: #2177
gregturn pushed a commit that referenced this issue Mar 28, 2022
@DiegoKrupitza @gregturn
Fix missing DISTINCT in count queries.
390e154 
The `COUNT_MATCH` does not consider line breaks after the `from` clause or the `where` clause. This leads to a no match scenario in the construction of the count query. With the fix we now consider line breaks/whitespaces after the `from` and `where` clause.

See #2341
Related tickets: #2177
gregturn pushed a commit that referenced this issue Mar 28, 2022
@DiegoKrupitza @gregturn
Fix missing DISTINCT in count queries.
3e64d9a 
The `COUNT_MATCH` does not consider line breaks after the `from` clause or the `where` clause. This leads to a no match scenario in the construction of the count query. With the fix we now consider line breaks/whitespaces after the `from` and `where` clause.

See #2341
Related tickets: #2177
@gregturn gregturn added this to the 3.0 M4 (2022.0.0) milestone Mar 28, 2022
@ruslanys
Copy link

Looks like changes leads to the following: #2260 (comment)

@ruslanys
Copy link

ruslanys commented Apr 28, 2022 •
edited
Loading

I found out that this commit has bug: 3e64d9a

Especially, the line 494: && !variable.contains("*");

The invocation of method

createCountQueryFor("SELECT * FROM User WHERE created_at > $1")

returns select count(WHERE) FROM User WHERE created_at > $1.

However, if we change the query to createCountQueryFor("SELECT * FROM User user WHERE created_at > $1"), it works fine: select count(user) FROM User user WHERE created_at > $1

@ruslanys ruslanys mentioned this issue Apr 28, 2022
Closed
@ruslanys
Copy link

Commits and tests marked with issue #2341

@DiegoKrupitza
Copy link
Contributor

DiegoKrupitza commented Apr 28, 2022 •
edited
Loading

I found out that this commit has bug: 3e64d9a

Ok I will take a look at this. I think I have a fix for this. PR will come soon.
@ruslanys

DiegoKrupitza added a commit to DiegoKrupitza/spring-data-jpa that referenced this issue Apr 28, 2022
@DiegoKrupitza
Fixes * bug in createCountQueryFor.
6f8170b 
In commit 3e64d9a a bug got introduced that uses the next symbol after the table name for the count function. With this commit this should be now resolved. The count query will use `*` when there is no alias present nor a variable.

Related tickets spring-projects#2177, spring-projects#2260, spring-projects#2511
@DiegoKrupitza DiegoKrupitza mentioned this issue Apr 28, 2022
Closed
4 tasks
@DiegoKrupitza
Copy link
Contributor

DiegoKrupitza commented Apr 28, 2022 •
edited
Loading

I hope with this PR (#2514 ) it should not be a problem anymore. @ruslanys

@ruslanys
Copy link

@DiegoKrupitza Thank you!

gregturn pushed a commit that referenced this issue May 4, 2022
@DiegoKrupitza @gregturn
Fixes * bug in createCountQueryFor.
e4e153d 
In commit 3e64d9a a bug got introduced that uses the next symbol after the table name for the count function. With this commit this should be now resolved. The count query will use `*` when there is no alias present nor a variable.

Related tickets #2341, #2177, #2260, #2511
gregturn pushed a commit that referenced this issue May 4, 2022
@DiegoKrupitza @gregturn
Fixes * bug in createCountQueryFor.
a5cf01b 
In commit 3e64d9a a bug got introduced that uses the next symbol after the table name for the count function. With this commit this should be now resolved. The count query will use `*` when there is no alias present nor a variable.

Related tickets #2341, #2177, #2260, #2511
gregturn pushed a commit that referenced this issue May 4, 2022
@DiegoKrupitza @gregturn
Fixes * bug in createCountQueryFor.
a69aa22 
In commit 3e64d9a a bug got introduced that uses the next symbol after the table name for the count function. With this commit this should be now resolved. The count query will use `*` when there is no alias present nor a variable.

Related tickets #2341, #2177, #2260, #2511
gregturn pushed a commit that referenced this issue May 4, 2022
@DiegoKrupitza @gregturn
Fixes * bug in createCountQueryFor.
038ac99 
In commit 3e64d9a a bug got introduced that uses the next symbol after the table name for the count function. With this commit this should be now resolved. The count query will use `*` when there is no alias present nor a variable.

Related tickets #2341, #2177, #2260, #2511
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@schauder schauder

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
7 participants
@schauder @gregturn @christophstrobl @ruslanys @spring-projects-issues @DiegoKrupitza @OctavioCega