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

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: Hibernate Core 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.