We're updating the issue view to help you get more done. 

PostgreSQL & H2 dialect incorrect for count distinct tuples

Description

Hello guys!
When executing the following HQL:
select count(distinct c) from ControlVO c
(note - ControlVO has a composite key made trough EmbbededID)
The following SQL outpu on 4.0.1 is:
select count(distinct (control0_.cve_cod_cooperat_singular,
control0_.cve_dt_criacao, control0_.cve_nro_doc_dev)) as
col_0_0_ from [....]
This works fine on Postgres 9.2.

However, when using 4.2.0.SP1 and newer versions, the generated SQL is:
select count(distinct control0_.cve_cod_cooperat_singular, control0_.cve_dt_criacao, control0_.cve_nro_doc_dev) as col_0_0_ from [...]

Note it's missing a '(' after distinct clause. This causes a dam problem on Postgres, generating the a SQLGrammarException:

[org.hibernate.engine.jdbc.spi.SqlExceptionHelper]
(http-localhost/127.0.0.1:8080-2) ERROR: function count(numeric, timestamp
without time zone, numeric) does not exist
No function matches the given name and argument types. You might
need to add explicit type casts.
Posição: 8
11:57:14,725 ERROR [stderr] (http-localhost/127.0.0.1:8080-2)
org.hibernate.exception.SQLGrammarException: could not extract ResultSet
11:57:14,726 ERROR [stderr] (http-localhost/127.0.0.1:8080-2) at
org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:122)
11:57:14,727 ERROR [stderr] (http-localhost/127.0.0.1:8080-2) at
org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)

Well, I dig into the code and tried to change to PostgreSQL81Dialect or PostgreSQL82Dialect or even PostgreSQL9Dialect for the 4.3.0.Final version and it kept wrong. I could notice the following changelog at 4.1.2 - HH-7165. I took a look into the method

Dialect.java

1 2 3 4 5 6 7 8 9 /** * Does this dialect support `count(distinct a,b)`? * * @return True if the database supports counting distinct tuples; false otherwise. */ public boolean supportsTupleDistinctCounts() { // oddly most database in fact seem to, so true is the default. return true; }

PostgreSQLDialect82.java returns false for this method. Is this right? It should consider counting distinct tuples.

I don't know if this is thr problem. I think not, since this method always returned false. But why on 4.0.1 we had the proper SQL and now on 4.2.0.SP1 we don't?

Environment

Hibernate version - 4.2.0.SP1 running on EAP 6.1.1
Databse - Postgres 9.2
RHEL 6
JDBC driver - postgresql-9.2-1002.jdbc4.jar
Dialect - PostgreSQLDialect

Status

Assignee

Brett Meyer

Reporter

Hanneli Tavante

Fix versions

backPortable

None

Suitable for new contributors

Yes, likely

Requires Release Note

Affirmative

Pull Request

None

backportDecision

None

Time tracking

4h

Components

Affects versions

4.3.0.Final
4.2.0.Final

Priority

Major