[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-956) MSSQL: Distinct queries require all ORDER BY attributes to be selected.

[vikas (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-956?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_29979 ] 

vikas commented on HHH-956:
---------------------------

We also have same issue for mssql:

org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not execute query; uncategorized SQLException for SQL [select distinct this_.ID as y0_ from CSMenuNode this_ left outer join CSMenuNode parentnode1_ on this_.CSParentNodeID=parentnode1_.ID where parentnode1_.ID is null order by this_.DisplayOrder asc]; SQL state [S1000]; error code [145]; ORDER BY items must appear in the select list if SELECT DISTINCT is specified.; nested exception is java.sql.SQLException: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
	org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:120)
	org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:276)
	org.springframework.orm.hibernate3.HibernateAccessor.convertJdbcAccessException(HibernateAccessor.java:424)
	org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:410)
	org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:377)
	org.springframework.orm.hibernate3.HibernateTemplate.findByCriteria(HibernateTemplate.java:974)

PLease let me know how to reslove this issue

> MSSQL: Distinct queries require all ORDER BY attributes to be selected.
> -----------------------------------------------------------------------
>
>                 Key: HHH-956
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-956
>             Project: Hibernate3
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 3.0.5, 3.1 beta 2
>         Environment: Running HB 3.0.5 but I tried this on the 3.1 beta 3 and got the same error.  DB:  MS SQL Server 2000 on Windows 2003 Server, JDK 1.4.2_05
>            Reporter: Shanon Levenherz
>         Attachments: order-by-bug.zip
>
>
> Using Criteria queries with a Distinct projection AND an ORDER BY clause  with Microsoft SQL Server cause the following SQL error:
> Caused by: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
> this is the culprit query:
> 2005-09-14 11:12:47,570 DEBUG org.hibernate.SQL - select distinct this_.content_id as y0_ from iwsc_content this_ left outer join iwsc_news this_1_ on this_.content_id=this_1_.content_id left outer join iwsc_event this_2_ on this_.content_id=this_2_.content_id left outer join iwsc_document this_3_ on his_.content_id=this_3_.content_id inner join iwsc_content_category categories3_ on this_.content_id=categories3_.content_id inner join iwsc_category category1_ on categories3_.category_id=category1_.category_id where (this_.expiration_date is null or this_.expiration_date>?) and category1_.category_id in (?, ?, ?, ?) and this_.branch in (?) order by this_.creation_date desc
> 2005-09-14 11:12:47,570 DEBUG org.hibernate.jdbc.AbstractBatcher - preparing statement
> 2005-09-14 11:12:47,570 DEBUG org.hibernate.type.TimestampType - binding '2005-09-14 00:00:00' to parameter: 1
> 2005-09-14 11:12:47,580 DEBUG org.hibernate.type.LongType - binding '100000005' to parameter: 2
> 2005-09-14 11:12:47,580 DEBUG org.hibernate.type.LongType - binding '100000010' to parameter: 3
> 2005-09-14 11:12:47,580 DEBUG org.hibernate.type.LongType - binding '100000015' to parameter: 4
> 2005-09-14 11:12:47,580 DEBUG org.hibernate.type.LongType - binding '100000020' to parameter: 5
> 2005-09-14 11:12:47,580 DEBUG org.hibernate.type.StringType - binding '/default/main/solutions/SalesSolution' to parameter: 6
> 2005-09-14 11:12:47,600 DEBUG org.hibernate.jdbc.AbstractBatcher - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
> 2005-09-14 11:12:47,600 DEBUG org.hibernate.jdbc.AbstractBatcher - closing statement
> and this is how i fixed it ( added in the creation date in the select )-- note that this isn't in code, i just did it in sql query analyzer.   
> select distinct this_.content_id as y0_, this_.creation_date from iwsc_content this_ left outer join iwsc_news this_1_ on this_.content_id=this_1_.content_id left outer join iwsc_event this_2_ on this_.content_id=this_2_.content_id left outer join iwsc_document this_3_ on this_.content_id=this_3_.content_id inner join iwsc_content_category categories3_ on this_.content_id=categories3_.content_id inner join iwsc_category category1_ on categories3_.category_id=category1_.category_id where category1_.category_id in (100000005,100000010,100000015,100000020)  and this_.branch in ('/default/main/solutions/SalesSolution') order by this_.creation_date desc
> in attachment:
> tomcat_out.log -- full debug on.
> code.txt - java snippets of how i'm building and executing the criteria.
> mapping_files.zip - the hbm's used to map... only Content and Category should apply here.
> please let me know if you need any further information.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira