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:
(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.
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
11:57:14,727 ERROR [stderr] (http-localhost/127.0.0.1:8080-2) at
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
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?
Hibernate version - 4.2.0.SP1 running on EAP 6.1.1
Databse - Postgres 9.2
JDBC driver - postgresql-9.2-1002.jdbc4.jar
Dialect - PostgreSQLDialect