NH-3594 - MSSql2012Dialect generates invalid order by clause for paging distinct query

[nhibernate-bot]

Gerke Geurts created an issue — 24th January 2014, 16:13:56:

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
----> System.Data.SqlClient.SqlException : ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Generated 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

---

Gerke Geurts added a comment — 24th January 2014, 16:27:19:

Fix proposed in https://github.com/nhibernate/nhibernate-core/pull/251

---

Oskar Berggren added a comment — 26th July 2014, 10:35:00:

Merged in 8531dafd9e42fc4de9b00dc27934038520e28112.

---

Oskar Berggren added a comment — 26th July 2014, 13:08:14:

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

[xumix]

Hi! Is there any workaround?

[bahusoid]

Is there any workaround?

Adding explicitly order by clause to query:

select distinct p from Person p order by p.id desc