hello,
i'm a wildfly 9.0.2 developer with a "big" problem

first of all let me indicate the wildfly pom.xml with hibernate versions:

<version.org.hibernate>4.3.10.Final</version.org.hibernate>
<version.org.hibernate.commons.annotations>4.0.5.Final</version.org.hibernate.commons.annotations>
<version.org.hibernate.validator>5.1.3.Final</version.org.hibernate.validator>
<version.org.hibernate.javax.persistence.hibernate-jpa-2.1-api>1.0.0.Final</version.org.hibernate.javax.persistence.hibernate-jpa-2.1-api>
<version.org.hibernate.hql>1.1.0.Final</version.org.hibernate.hql>
<version.org.hibernate.search>5.2.0.Final</version.org.hibernate.search>

now let's go to the problem

i have an entity bean "realted" with other:

@ManyToOne
@JoinColumns({ @JoinColumn(referencedColumnName = "id", name = "idFornitoreRisorsa", nullable = false) })
private FornitoreRisorsa fornitoreRisorsa;

@ManyToOne
@JoinColumns({ @JoinColumn(referencedColumnName = "id", name = "idFornitoreTariffa", nullable = false) })
private FornitoreTariffa fornitoreTariffa;


my named query is:

@NamedQuery(name = CalendarioAttivitaView.findByTest,
query = "SELECT "
+ "a.fornitoreRisorsa,"
+ "a.fornitoreTariffa,"
+ "SUM(a.quantita), "
+ "FROM "
+ "CalendarioAttivitaView a "
+ "WHERE "
+ "a.data BETWEEN :dataInizio AND :dataFine "
+ "GROUP BY "
+ "a.fornitoreRisorsa,"
+ "a.fornitoreTariffa")

the generated sql statement is:

SELECT
calendario0_.idFornitoreRisorsa AS col_1_0_,
calendario0_.idFornitoreTariffa AS col_2_0_,
SUM(calendario0_.quantita) AS col_7_0_,
fornitorer2_.dataCreazione AS dataCrea2_6_1_,
fornitorer2_.dataModifica AS dataModi3_6_1_,
fornitorer2_.utenteCreazione AS utenteCr4_6_1_,
fornitorer2_.utenteModifica AS utenteMo5_6_1_,
fornitorer2_.cognome AS cognome6_6_1_,
fornitorer2_.idFornitore AS idForni10_6_1_,
fornitorer2_.login AS login7_6_1_,
fornitorer2_.nome AS nome8_6_1_,
fornitorer2_.note AS note9_6_1_,
fornitorer2_.idReferente AS idRefer11_6_1_,
fornitoret3_.dataCreazione AS dataCrea2_7_2_,
fornitoret3_.dataModifica AS dataModi3_7_2_,
fornitoret3_.utenteCreazione AS utenteCr4_7_2_,
fornitoret3_.utenteModifica AS utenteMo5_7_2_,
fornitoret3_.idAliquotaIva AS idAliqu13_7_2_,
fornitoret3_.assegnabile AS assegnab6_7_2_,
fornitoret3_.idFornitore AS idForni14_7_2_,
fornitoret3_.nome AS nome7_7_2_,
fornitoret3_.note AS note8_7_2_,
fornitoret3_.quantitaMassima AS quantita9_7_2_,
fornitoret3_.quantitaMinima AS quantit10_7_2_,
fornitoret3_.righeGiornaliere AS righeGi11_7_2_,
fornitoret3_.tipoTariffa AS tipoTar12_7_2_
FROM VW_CalendarioAttivita calendario0_
INNER JOIN TB_FornitoreRisorsa fornitorer2_ ON calendario0_.idFornitoreRisorsa=fornitorer2_.id
INNER JOIN TB_FornitoreTariffa fornitoret3_ ON calendario0_.idFornitoreTariffa=fornitoret3_.id
WHERE calendario0_.data BETWEEN ? AND ?
GROUP BY
calendario0_.idFornitoreRisorsa ,
calendario0_.idFornitoreTariffa


as you can see not all of the selected columns are "grouped by" (because they are part of the join) ant this works fine on mySQL (my dev env)
unfortunatelly, my production system has ms sql intalled and this doesn't works

the error is:

Column 'TB_FornitoreRisorsa.dataCreazione' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

property name="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect"

in the persistence.xml file on the jpa project but the error persists

any idea?

thanks a lot!

While you can group by a single entity which will use the entity id for grouping by, you can't group by multiple entities.

Therefore, you need to split this query in 2:

1. The first one will GROUP BY the entity identifiers
2. The second one will use the previously fetched identifiers and fetch the desired entities, possibly joined in case there is an association between them. Otherwise, you need a separate query for every entity type.

hi,
thanks for the answer

in the last days, i've tried something like:

@NamedQuery(name = CalendarioAttivitaView.findByTest,
query = "SELECT "
+ "a.idFornitoreRisorsa,"
+ "a.idFornitoreTariffa,"
+ "SUM(a.quantita), "
+ "FROM "
+ "CalendarioAttivitaView a "
+ "WHERE "
+ "a.data BETWEEN :dataInizio AND :dataFine "
+ "GROUP BY "
+ "a.idFornitoreRisorsa,"
+ "a.idFornitoreTariffa")

so the result was a list with just two integers (for the ids) and one double (for the sum)
and in a loop i've queried the two joined entities, but the performances was awful

can you write a simple sample of the tho queries?
i'm sure having understood what you mean

Don't do it in a loop. Read carefully what I have told you already:

i've read carefully your answer and that's why i've asked you for a sample

if i execute the first query to obtaint the two ids and the total, after that (in my opinion) i need TWO other queries
the first one to load all the entities based on the first id selected and the the second one based on the second id selected
in any case it is not very "confortable"

i think that having group by working only on id and not giving errors on entity is a bug

thanks

3 simple queries can outperform a very complex one if the Execution Plan is much more complicated. While the N+1 query problem is a serious issue because it's an accidental complexity, splitting a complex query in 2 or 3 simpler might actually be beneficial, dependding on the underlying RDBMS (e.g. MySQL).



This feature is not a JPA requirement, and Hibernate supports it only for a single entity. However, you can simply write a native SQL query and GROUP BY all columns if you really want it. Using JPQL for projections is a code smell anyway.

i agree that splitting the "problem" in multiple queries can help, but a bigger effort in "data union" is requested

suppose that the first query returns a list of array with the tho ids end the sum

i have to create and array of id and execute a query like "... WHERE id IN :list) to obtain all the relater beans
same thing fot the third query

now i have to write the algorithm for "data union" that takes data from the three lists finding the correct ones by id

this is a job that usually the DB with JOIN or LEFT JOIN does very easily without requesting coding and execution on the client side

But you don't have to do that if that does not work for you. You can always execute the native SQL with JOINs. Check out what I said previously:

that's correct, but if i have to write native queries why i loose a big part of hibernate beauty and portability

There are many ways to address portability, like offering specialized data access components. Check out this article for more details.

i thank you very much for all the time you're spending with me, but, reading again all the treead, i don't change my mind also if surely there are several workarounds that can be used

if grouping is allowed only for one entity, i'm expectong an exception before executing the query
what we can see, instead, is that the system has been able to work with all the related entities and the group by verb includes all the relation fields:



the system has also been able to retrieve all the fields of the related entities as we can see in the "select" clause:



and this works on mySQL because theese columns are part of jined tables and specifing them in the group by is useless
BUT mssql doesn't agree with this because it needs a strict syntax
SO having i specified to use a mssql dialect, that's why i call it BUG

You can write a replicating test case and open a Jira issue for it, but the priority for this one is going to be rather low. If it's really that important to you, then you will supply a Pull Request that fixes it, right?

now i'm pretty late with my project so i'll find the best workaround
i just hope that someone will understand the bug and fix it

thanx a lot

We are just 5 people in the Hibernate team and we have to address:

- the development for 6.0
- fixes for 5.x
- writing documentation
- answering forum and StackOverflow questions
- taking care of infrastructure issues (site, Jenkins, etc)

So, I understand what you mean.

Since there is already a workaround (the native SQL query), it's a minor issue. If it's a major issue, you'll surely send a Pull Request.

i understand, thanks a lot for your great work

we cant spend some days tring to understand where the problem is, but we have never worked with hibernate sources

knowing that the version user by wildfly is:

can you give us some raccomentation to start this activity?