Ken - The strange thing is that the results are consistently inconsistent.
I have a query:
SELECT TOP 10 Id15_1_, Version15_1_, Id16_0_ FROM (SELECT ROW_NUMBER()
OVER(ORDER BY __hibernate_sort_expr_0__) as row, query.Id15_1_,
query.Version15_1_, query.Id16_0_, query.__hibernate_sort_expr_0__ FROM
(SELECT this_.Id as Id15_1_, this_.Version as Version15_1_, root_tempo1_.Id
as Id16_0_, root_tempo1_.LotNumber as __hibernate_sort_expr_0__ FROM
SelectTest.dbo.Inventory this_ inner join SelectTest.dbo.Inventory_Data
root_tempo1_ on this_.Id=root_tempo1_.InventoryId and '11/3/2008 2:22:59 PM'
>= root_tempo1_.TransactionStart and '11/3/2008 2:22:59 PM' <
root_tempo1_.TransactionStop WHERE (Select count(*) from Inventory_Data data
WHERE data.InventoryId = this_.Id AND '11/3/2008 2:22:59 PM' >=
data.TransactionStart and '11/3/2008 2:22:59 PM' < data.TransactionStop) > 0
AND root_tempo1_.EquipmentId = 'd7930b93-bc00-4cce-9f13-9b2d00e192eb' and
root_tempo1_.Quantity > 0) query ) page WHERE page.row > 20 ORDER BY
__hibernate_sort_expr_0__;
and it returns 10 items with this being the final row:
D6D9DD42-1D3A-4743-B342-9B2D00E19302 1 0C25FC7C-76C6-43EF-AE96-9B2D00E19302
then if I alter the query by removing the #2 and #3 items in the SELECT
statement I get this as the final item
0F2C067B-BA21-4156-AAD9-9B2D00E19385
All other items are the same. Im able to reproduce it with a different
auto-gen guid set. I've already sent Oren a sample db with this. So do you
still think that this isn't a bug? It seems VERY odd to me that having
different columns in the select could yeild entirely different results. I
suppose that perhaps this is because they are in different tables?
On Tue, Oct 7, 2008 at 12:12 AM, Ken Egozi <[EMAIL PROTECTED]> wrote:
> this is not a bug.
>
> when you select with no ORDER BY clause, then the order of the results is
> non deterministic, and is up to the execution plan. The order will be down
> to the order of reads from memory and disk.
>
> when you sort on a non unique field, the order of records with the same
> sorted values is again non deterministic, and again will be up to the
> execution plan.
>
> Now it's possible that SQL Server will choose different query plans for the
> two queries in your scenario, as after the first one, the engine knows more
> about the query, and has other stuff in cache. So I find it non surprising
> at all that you'd get different paged results when the sort order is non
> deterministic.
>
> The solution of adding a deterministic column to the sort expression will
> solve the determinism problem, ensuring the same exact results.
>
>
> On Mon, Oct 6, 2008 at 9:04 PM, Ayende Rahien <[EMAIL PROTECTED]> wrote:
>
>> I think that this is an issue with SQL Server if you are doing sorts on
>> multi proc machine. Can you give more details about the issue?
>>
>>
>> On Mon, Oct 6, 2008 at 10:01 PM, Will Shaver <[EMAIL PROTECTED]>wrote:
>>
>>> Here's two days of my life solved that was solved with by adding a
>>> single line to my project. I'm posting it here for the benefit of others.
>>> It's an odd enough case that I don't know that it should go into a FAQ.
>>>
>>> It appears as though for some queries involving sql server 2005 and
>>> ROW_NUMBER() the sort order is not always deterministic. I have the
>>> following pieces which all lined up against me:
>>>
>>> * One to Many relationship mapped with lazy=false, fetch=subselect
>>> * Guid IDs for both entities
>>> * Query with limits for selecting 10 out of the middle
>>> * Filters affecting the mapped relationship
>>> * Sorting on a non-unique field such as quantity ordered
>>> * Using Criteria query
>>>
>>> NH does this:
>>>
>>> 1. Grab results using the highly convoluted ROW_NUMBER() OVER(...
>>> 2. Determine that filters are in place and relation needs to be grabbed
>>> 3. Grab the relation with a sub-select as specified in mapping via
>>> ROW_NUMBER() ...
>>> 4. Error!
>>>
>>> What was occuring is that the results of the subselect for the second
>>> query are in a different order than for the first select. This is NOT an NH
>>> bug and is a bug with Sql Server. (I'm using 2005 express.)
>>>
>>> More details can be provided on the bug if anyone wants them. Luckly the
>>> solution is simple:
>>>
>>> Always sort by ID at the end of your list of "Order By"s. Note that if
>>> you're not using filters and fetch=subselect this probably won't affect you.
>>>
>>>
>>> if(!string.IsNullOrEmpty(sort))
>>> {
>>> criteria.AddOrder(new Order(sort, dir));
>>> }
>>> criteria.AddOrder(new Order("Id", true));
>>>
>>>
>>>
>>> -Will
>>>
>>>
>>>
>>
>>
>>
>
>
> --
> Ken Egozi.
> http://www.kenegozi.com/blog
> http://www.musicglue.com
> http://www.castleproject.org
> http://www.gotfriends.co.il
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
