[ https://hibernate.onjira.com/browse/HHH-7041?page=com.atlassian.jira.plug... ] George Gastaldi commented on HHH-7041: -------------------------------------- The fix is simple. Just check whether the DISTINCT statement occurs before the FROM clause -- This message is automatically generated by JIRA. For more information on JIRA, see: http://www.atlassian.com/software/jira

[ https://hibernate.onjira.com/browse/HHH-7041?page=com.atlassian.jira.plug... ] Hardy Ferentschik updated HHH-7041: ----------------------------------- Description: SQLServer2005Dialect messes with the SQL when Eg: {code:sql} select xxx from yyy where yyy.id not in (select distinct(aaa) from bbb) {code} becomes: {code:sql} WITH query AS (select xxx from yyy where yyy.id not in ( select aaa from bbb ) ) SELECT * FROM query WHERE __hibernate_row_nr__ >= ? AND __hibernate_row_nr__ < ? {code} was: SQLServer2005Dialect messes with the SQL when Eg: {code:sql} select xxx from yyy where yyy.id not in (select distinct(aaa) from bbb) {code} becomes: {code:sql} WITH query AS (select xxx from yyy where yyy.id not in ( select aaa from bbb ) ) SELECT * FROM query WHERE __hibernate_row_nr__ >= ? AND __hibernate_row_nr__ < ? -- This message is automatically generated by JIRA. For more information on JIRA, see: http://www.atlassian.com/software/jira

[ https://hibernate.onjira.com/browse/HHH-7041?page=com.atlassian.jira.plug... ] Hardy Ferentschik commented on HHH-7041: ---------------------------------------- Thanks :-) -- This message is automatically generated by JIRA. For more information on JIRA, see: http://www.atlassian.com/software/jira

[ https://hibernate.onjira.com/browse/HHH-7041?page=com.atlassian.jira.plug... ] George Gastaldi commented on HHH-7041: -------------------------------------- The following SQL is parsed incorrectly: {code:sql} select distinct this_.ID_SOLICITACAO_ATENDIMENTO as y0_, this_.ID_ATENDIMENTO as y1_, atendiment1_.CD_PROTOCOLO as y2_, hierarquia2_.ID_GRUPO_FINANCEIRO as y3_, grupofinan3_.DS_GRUPO_FINANCEIRO as y4_, this_.NR_SEMAFORO as y5_, this_.ID_ASSUNTO as y6_, assunto5_.DS_ASSUNTO as y7_, assunto5_.ID_ASSUNTO as y8_, this_.ID_STATUS_SOLICITACAO_ATENDIMENTO as y9_, this_.ID_TIPO_PRAZO as y10_, this_.ID_TIPO_ATENDIMENTO as y11_, this_.ID_TIPO_SOLICITACAO as y12_, tiposolici6_.DS_TIPO_SOLICITACAO as y13_, tiposolici6_.ID_TIPO_SOLICITACAO as y14_, this_.ID_CLASSIFICACAO_SOLICITACAO as y15_, classifica8_.DS_CLASSIFICACAO_SOLICITACAO as y16_, this_.ID_PESSOA_ATENDIDA as y17_, pessoaaten9_.NM_PESSOA as y18_, pessoaaten9_.ID_PESSOA as y19_, this_.NR_SEQUENCIA as y20_, this_.DT_CRIACAO as y21_, this_.NR_PRAZO as y22_, tiposolici6_.ID_PRIORIDADE as y23_, status4_.DS_STATUS as y24_, status4_.ID_STATUS as y25_, this_1_.ID_RESPONSAVEL as y26_ from AD_SOLICITACAO_ATENDIMENTO this_ left outer join AD_SOLICITACAO_ATENDIMENTO_RESPONSAVEL this_1_ on this_.ID_SOLICITACAO_ATENDIMENTO=this_1_.ID_SOLICITACAO inner join AD_ASSUNTO assunto5_ on this_.ID_ASSUNTO=assunto5_.ID_ASSUNTO inner join AD_ATENDIMENTO atendiment1_ on this_.ID_ATENDIMENTO=atendiment1_.ID_ATENDIMENTO left outer join AD_ATENDIMENTO_HIERARQUIA_CONTRATO hierarquia2_ on atendiment1_.ID_ATENDIMENTO=hierarquia2_.ID_ATENDIMENTO left outer join AD_GRUPO_FINANCEIRO grupofinan3_ on hierarquia2_.ID_GRUPO_FINANCEIRO=grupofinan3_.ID_GRUPO_FINANCEIRO inner join AD_CLASSIFICACAO_SOLICITACAO classifica8_ on this_.ID_CLASSIFICACAO_SOLICITACAO=classifica8_.ID_CLASSIFICACAO_SOLICITACAO left outer join AD_PESSOA pessoaaten9_ on this_.ID_PESSOA_ATENDIDA=pessoaaten9_.ID_PESSOA left outer join AD_PESSOA_FISICA pessoaaten9_1_ on pessoaaten9_.ID_PESSOA=pessoaaten9_1_.ID_PESSOA left outer join AD_PESSOA_JURIDICA pessoaaten9_2_ on pessoaaten9_.ID_PESSOA=pessoaaten9_2_.ID_PESSOA inner join AD_STATUS_SOLICITACAO_ATENDIMENTO status4_ on this_.ID_STATUS_SOLICITACAO_ATENDIMENTO=status4_.ID_STATUS inner join AD_TIPO_PRAZO tipoprazo7_ on this_.ID_TIPO_PRAZO=tipoprazo7_.ID_TIPO_PRAZO inner join AD_TIPO_SOLICITACAO tiposolici6_ on this_.ID_TIPO_SOLICITACAO=tiposolici6_.ID_TIPO_SOLICITACAO where this_1_.ID_RESPONSAVEL in (?, ?, ?, ?, ?, ?) and status4_.ID_STATUS in (?, ?, ?, ?, ?) order by y21_ desc {code} Turns into: {code:sql} WITH query AS (/* criteria query */ select this_.id_solicitacao_atendimento as y0_, this_.id_atendimento as y1_, atendiment1_.cd_protocolo as y2_, hierarquia2_.id_grupo_financeiro as y3_, grupofinan3_.ds_grupo_financeiro as y4_, this_.nr_semaforo as y5_, this_.id_assunto as y6_, assunto5_.ds_assunto as y7_, assunto5_.id_assunto as y8_, this_.id_status_solicitacao_atendimento as y9_, this_.id_tipo_prazo as y10_, this_.id_tipo_atendimento as y11_, this_.id_tipo_solicitacao as y12_, tiposolici6_.ds_tipo_solicitacao as y13_, tiposolici6_.id_tipo_solicitacao as y14_, this_.id_classificacao_solicitacao as y15_, classifica8_.ds_classificacao_solicitacao as y16_, this_.id_pessoa_atendida as y17_, pessoaaten9_.nm_pessoa as y18_, pessoaaten9_.id_pessoa as y19_, this_.nr_sequencia as y20_, this_.dt_criacao as y21_, this_.nr_prazo as y22_, tiposolici6_.id_prioridade as y23_, status4_.ds_status as y24_, status4_.id_status as y25_, this_1_.id_responsavel as y26_, ROW_NUMBER() OVER ( order by y21_ desc) as __hibernate_row_nr__ from ad_solicitacao_atendimento this_ left outer join ad_solicitacao_atendimento_responsavel this_1_ on this_.id_solicitacao_atendimento=this_1_.id_solicitacao inner join ad_assunto assunto5_ on this_.id_assunto=assunto5_.id_assunto inner join ad_atendimento atendiment1_ on this_.id_atendimento=atendiment1_.id_atendimento left outer join ad_atendimento_hierarquia_contrato hierarquia2_ on atendiment1_.id_atendimento=hierarquia2_.id_atendimento left outer join ad_grupo_financeiro grupofinan3_ on hierarquia2_.id_grupo_financeiro=grupofinan3_.id_grupo_financeiro inner join ad_classificacao_solicitacao classifica8_ on this_.id_classificacao_solicitacao=classifica8_.id_classificacao_solicitacao left outer join ad_pessoa pessoaaten9_ on this_.id_pessoa_atendida=pessoaaten9_.id_pessoa left outer join ad_pessoa_fisica pessoaaten9_1_ on pessoaaten9_.id_pessoa=pessoaaten9_1_.id_pessoa left outer join ad_pessoa_juridica pessoaaten9_2_ on pessoaaten9_.id_pessoa=pessoaaten9_2_.id_pessoa inner join ad_status_solicitacao_atendimento status4_ on this_.id_status_solicitacao_atendimento=status4_.id_status inner join ad_tipo_prazo tipoprazo7_ on this_.id_tipo_prazo=tipoprazo7_.id_tipo_prazo inner join ad_tipo_solicitacao tiposolici6_ on this_.id_tipo_solicitacao=tiposolici6_.id_tipo_solicitacao where this_1_.id_responsavel in (?, ?, ?, ?, ?, ?) and status4_.id_status in (?, ?, ?, ?, ?) group by this_.id_solicitacao_atendimento, this_.id_atendimento, atendiment1_.cd_protocolo, hierarquia2_.id_grupo_financeiro, grupofinan3_.ds_grupo_financeiro, this_.nr_semaforo, this_.id_assunto, , , this_.id_status_solicitacao_atendimento, this_.id_tipo_prazo, this_.id_tipo_atendimento, this_.id_tipo_solicitacao, tiposolici6_.ds_tipo_solicitacao, tiposolici6_.id_tipo_solicitacao, this_.id_classificacao_solicitacao, classifica8_.ds_classificacao_solicitacao, this_.id_pessoa_atendida, pessoaaten9_.nm_pessoa, pessoaaten9_.id_pessoa, this_.nr_sequencia, this_.dt_criacao, this_.nr_prazo, tiposolici6_.id_prioridade, status4_.ds_status, status4_.id_status, this_1_.id_responsavel) SELECT * FROM query WHERE __hibernate_row_nr__ >= ? AND __hibernate_row_nr__ < ? {code} -- This message is automatically generated by JIRA. For more information on JIRA, see: http://www.atlassian.com/software/jira

[ https://hibernate.onjira.com/browse/HHH-7041?page=com.atlassian.jira.plug... ] George Gastaldi commented on HHH-7041: -------------------------------------- Another Pull Request coming: https://github.com/hibernate/hibernate-orm/pull/270 :) -- This message is automatically generated by JIRA. For more information on JIRA, see: http://www.atlassian.com/software/jira

[ https://hibernate.onjira.com/browse/HHH-7041?page=com.atlassian.jira.plug... ] George Gastaldi edited comment on HHH-7041 at 2/8/12 10:21 PM: --------------------------------------------------------------- The Order by clause inside the OVER() statement is wrong also, as http://msdn.microsoft.com/en-us/library/ms189461.aspx states that {quote} When used in the context of a ranking window function, <ORDER BY Clause> can only refer to columns made available by the FROM clause. An integer cannot be specified to represent the position of the name or alias of a column in the select list. <ORDER BY Clause> cannot be used with aggregate window functions. {quote} This fix is NOT on the previous pull request was (Author: gastaldi): The Order by clause inside the OVER() statement is wrong also, as http://msdn.microsoft.com/en-us/library/ms189461.aspx states that {quote} When used in the context of a ranking window function, <ORDER BY Clause> can only refer to columns made available by the FROM clause. An integer cannot be specified to represent the position of the name or alias of a column in the select list. <ORDER BY Clause> cannot be used with aggregate window functions. {quote} -- This message is automatically generated by JIRA. For more information on JIRA, see: http://www.atlassian.com/software/jira

[ https://hibernate.onjira.com/browse/HHH-7041?page=com.atlassian.jira.plug... ] George Gastaldi commented on HHH-7041: -------------------------------------- Steve, can you apply the fix in https://github.com/hibernate/hibernate-orm/pull/270 before launching this release ? -- This message is automatically generated by JIRA. For more information on JIRA, see: http://www.atlassian.com/software/jira