Hi all!
I'm using NH 2.0.1 GA and don't really know whether it was fixed in
the latest release, but today I've found the problem. NH generates
incorrect SQL when paging is used on SQL Server 2005 Express -
something like (code copied from NH Profiler):
[code]
SELECT TOP 15 Id6_1_,
Email6_1_,
FirstName6_1_,
LastName6_1_,
MiddleName6_1_,
Id29_0_,
Descript2_29_0_,
Name29_0_
FROM (SELECT ROW_NUMBER()
OVER(ORDER BY __hibernate_sort_expr_0__) as row,
query.Id6_1_,
query.Email6_1_,
query.FirstName6_1_,
query.LastName6_1_,
query.MiddleName6_1_,
query.Id29_0_,
query.Descript2_29_0_,
query.Name29_0_,
query.__hibernate_sort_expr_0__
FROM (SELECT this_.Id as Id6_1_,
this_.Email as Email6_1_,
this_.FirstName as
FirstName6_1_,
this_.LastName as
LastName6_1_,
this_.MiddleName as
MiddleName6_1_,
type1_.Id as Id29_0_,
type1_.Description as
Descript2_29_0_,
type1_.Name as Name29_0_,
type1_.Id as
__hibernate_sort_expr_0__
FROM Persons this_
inner join PersonTypes type1_
on this_.PersonTypeId = type1_.Id
WHERE (type1_.Id = 9 /* @p0 */
or type1_.Id = 10 /* @p1 */)) query) page
WHERE page.row > 0
ORDER BY __hibernate_sort_expr_0__
[/code]
would not return rows 1-15 as expected, but instead rows 2-16 would be
retrieved.
If last ORDER BY expression is removed correct rows are returned. I
don't know whether it is important but rows 1-16 have the same value
of ordering expression (__hibernate_sort_expr_0__).
At the same time I've found that if I have for example 100 rows total
it would not matter whether last WHERE clause contains page.row > 84
or page.row > 85 - the same set would be returned!, i.e. first query
is wrong and other is correct. Digging dipper I've found that all
queries which should return last row are also correct (if page.row >
97 then result set would contain 3 rows: 98, 99 and 100)
Does anybody know how to fix that issue? Is it possible to remove
ORDER BY clause?
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en
-~----------~----~----~----~------~----~------~--~---
