i'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?