We're updating the issue view to help you get more done. 

Paginated query produces non-deterministic result set ordering

Description

The query generated by Hibernate when using setMaxResults/setStartResults does not always return an order result set. As a simple example, assume I have a class Customer and I issue the following:

1 2 3 Query query = session.createQuery("select c from Customer c order by c.customerId"); query.setMaxResults(10); query.setFirstResult(5);

The SQL Server dialect will generate a query that looks like:

1 2 3 4 5 WITH query AS ( select ROW_NUMBER() OVER (order by customer0_.cust_id) as __hibernate_row_nr__, customer0_.id as id0_, customer0_.cust_id as cust2_0_ from homer.droberge.dev_test_customer customer0_ ) SELECT * FROM query WHERE __hibernate_row_nr__ BETWEEN ? AND ?

Notice there is no order by clause on the outer query. This seems incorrect because there is no guarantee the query will be ordered. We actually ran into an issue in one of our production applications where the result set is coming back in random order.

We did some investigative work on this and here is what we found:

  • The generated Hibernate query works when a serial execution plan is used by the query optimizer. In fact, appending an order by clause to the outer query results in an execution plan that is exactly the same as the plan that is generated without the order by clause.

  • It appears to fail when SQL Server query optimizer selects a parallel query execution plan. In this case, work is distributed over a number of threads. MSDN indicates that relative order is maintained among the threads, but there is no guarantee the final output will be ordered unless there is an order by predicate. Appending an order by clause will guarantee the final output is ordered correctly.

Some helpful links that relate to parallel queries:

It is somewhat difficult to force SQL Server to choose a parallel execution plan. I did find this article http://sqlblog.com/blogs/paul_white/archive/2011/12/23/forcing-a-parallel-query-execution-plan.aspx which does go into detail about forcing SQL to choose a parallel execution plan. If you force the parallel execution plan you can easily reproduce the non-deterministic ordering

Environment

3.6.9.Final, SQL Server 2008

Status

Assignee

Unassigned

Reporter

Dave Roberge

Labels

None

Worked in

None

Feedback Requested

None

Feedback Requested By

None

backPortable

None

Community Help Wanted

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

backportReEvaluate

None

Components

Affects versions

3.6.9

Priority

Major