MSSql2012Dialect generates invalid order by clause for paging distinct query

Description

The SQL Server 2012 dialect generates invalid SQL when paging or limits are used on SELECT DISTINCT queries. The NHibernate.Test.NHSpecificTest.NH2214.Fixture.ShouldWorkUsingDistinctAndLimits() results in the following exception:

NHibernate.Exceptions.GenericADOException : could not execute query
[ select distinct person0_.Id as Id5061_, person0_.FirstName as FirstName5061_ from Person person0_ ORDER BY CURRENT_TIMESTAMP OFFSET 0 ROWS FETCH FIRST @p0 ROWS ONLY ]
[SQL: select distinct person0_.Id as Id5061_, person0_.FirstName as FirstName5061_ from Person person0_ ORDER BY CURRENT_TIMESTAMP OFFSET 0 ROWS FETCH FIRST @p0 ROWS ONLY]
----> System.Data.SqlClient.SqlException : ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Environment

Test environment using SQL Server 2012 and MsSql2012Dialect.

Activity

Show:

Oskar Berggren 
July 26, 2014 at 1:08 PM

Merge reverted due to many new test failures for e.g. sql server 2008.

Oskar Berggren 
July 26, 2014 at 10:35 AM

Merged in 8531dafd9e42fc4de9b00dc27934038520e28112.

Gerke Geurts 
January 24, 2014 at 4:27 PM

Details

Assignee

Reporter

Labels

Components

Affects versions

Priority

Who's Looking?

Open Who's Looking?
Created January 24, 2014 at 4:13 PM
Updated August 13, 2016 at 10:29 AM
Who's Looking?