Skip to content
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.

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

Count distinct broken for PostgreSQL #587

Closed
timowest opened this issue Dec 9, 2013 · 3 comments
Closed

Count distinct broken for PostgreSQL #587

timowest opened this issue Dec 9, 2013 · 3 comments
Labels
Milestone

Comments

@timowest
Copy link
Member

@timowest timowest commented Dec 9, 2013

For Postgres, SQLTemplates should be changed to:

private String distinctCountStart = "count(distinct(";
private String distinctCountEnd = "))";

As it is not possible today, reflection is used to set these two attributes.

The query produced without the correction is:
select count(distinct "menu"."id", "menu"."name", "menu"."screen", "menu"."parent_menu") from "utils"."menu" "menu"

And error is:

Exception in thread "main" com.mysema.query.QueryException: ERROR: function count(bigint, character varying, bigint, bigint) does not exist
  Dica: No function matches the given name and argument types. You might need to add explicit type casts.
  Posição: 8
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273)
at com.mysema.query.sql.AbstractSQLQuery.unsafeCount(AbstractSQLQuery.java:772)
... 5 more

The correct sql is:
select count(distinct("menu"."id", "menu"."name", "menu"."screen", "menu"."parent_menu")) from "utils"."menu" "menu"

@timowest
Copy link
Member Author

@timowest timowest commented Dec 12, 2013

Released in 3.3.0.BETA2

@timowest timowest closed this Dec 12, 2013
@khushbuadav
Copy link

@khushbuadav khushbuadav commented Jan 17, 2019

@timowest am using querydsl-jpa with Postgres and am facing this exact issue.

The code below:

jpa.select(QTable1.colA, QTable1.colB).from(QTable1)
.groupBy(QTable1.colA, QTable1.colB).fetchResults();

produces the following error:

java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: 
Exception Description: Syntax error parsing [select count(distinct QTable1.colA, QTable1.colB)
from QTable1 qTable1]. 
[159, 159] A select statement must have a FROM clause.
[56, 56] The right parenthesis is missing from the COUNT expression.
[159, 205] The query contains a malformed ending.

I know that the fix has gone into the querydsl-sql. But we would like to continue with querydsl-jpa.
Is there any alternative to get this working with querydsl-jpa which still produces the above query?

@Shredder121
Copy link
Member

@Shredder121 Shredder121 commented Jan 18, 2019

Pretty sure that's in the hands of the ORM tool
We cannot influence the SQL being generated, only provide the JPQL to the ORM tool
If you could do some research as to 1) having extra parentheses will "write-through" to the SQL, and 2) whether that's valid JPQL we could add workarounds for badly behaving ORM tools
But I think point 2 will pose a problem

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
3 participants