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_.idINNER JOIN TB_FornitoreTariffa fornitoret3_ ON calendario0_.idFornitoreTariffa=fornitoret3_.idWHERE 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.

Post subject: Re: Column is invalid in the select list because it is not...

Posted: Mon Jul 17, 2017 2:17 pm

Hibernate Team

Joined: Thu Sep 11, 2014 2:50 amPosts: 1630Location: Romania

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 identifiers2. 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.

and in a loop i've queried the two joined entities, but the performances was awful

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

Quote:

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.

if i execute the first query to obtaint the two ids and the total, after that (in my opinion) i need TWO other queriesthe first one to load all the entities based on the first id selected and the the second one based on the second id selectedin 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

i need TWO other queriesthe first one to load all the entities based on the first id selected and the the second one based on the second id selectedin any case it is not very "confortable"

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).

Quote:

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

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 querywhat 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:

Code:

GROUP BYcalendario0_.idFornitoreRisorsa ,calendario0_.idFornitoreTariffa

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

Code:

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_

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 syntaxSO having i specified to use a mssql dialect, that's why i call it BUG

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?