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.
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.