wildfly 9.0.2 ejb jpa - Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
it.mamato Jul 17, 2017 10:17 AMi'm writing a web application on wildfly 9.0.2 one of my entity beans has a query like:
@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")
where
a.fornitoreRisorsa a.fornitoreTariffa
are "related beans"
@ManyToOne @JoinColumns({ @JoinColumn(referencedColumnName = "id", name = "idFornitoreRisorsa", nullable = false) }) private FornitoreRisorsa fornitoreRisorsa; @ManyToOne @JoinColumns({ @JoinColumn(referencedColumnName = "id", name = "idFornitoreTariffa", nullable = false) }) private FornitoreTariffa fornitoreTariffa;
the executed SQL statement logged in consolle 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
if executed on mySQL it works fine if executed on MS SQL Server it throws the error:
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.
this is because the columns in joined tables are not included in the GROUP BY clause
i can see two options to solve the problem: - tell ms sql server to be not so strict in query ececution - tell hibernate (in wildfly 9.0.2) to be more strict generating sql code
i have tried to insert
property name="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect"
in the persistence.xml file on the jpa project but the error persists
any idea?