<style>
/* Changing the layout to use less space for mobiles */
@media screen and (max-device-width: 480px), screen and (-webkit-min-device-pixel-ratio: 2) {
#email-body { min-width: 30em !important; }
#email-page { padding: 8px !important; }
#email-banner { padding: 8px 8px 0 8px !important; }
#email-avatar { margin: 1px 8px 8px 0 !important; padding: 0 !important; }
#email-fields { padding: 0 8px 8px 8px !important; }
#email-gutter { width: 0 !important; }
}
</style>
<div id="email-body">
<table id="email-wrap" align="center" border="0" cellpadding="0" cellspacing="0" style="background-color:#f0f0f0;color:#000000;width:100%;">
<tr valign="top">
<td id="email-page" style="padding:16px !important;">
<table align="center" border="0" cellpadding="0" cellspacing="0" style="background-color:#ffffff;border:1px solid #bbbbbb;color:#000000;width:100%;">
<tr valign="top">
<td bgcolor="#3e4c4e" style="background-color:#3e4c4e;color:#ffffff;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;line-height:1;"><img src="https://www.jboss.org/dms/hibernate/images/jira/jiraheader_hibernate.png" alt="" style="vertical-align:top;" /></td>
</tr><tr valign="top">
<td id="email-banner" style="padding:32px 32px 0 32px;">
<table align="left" border="0" cellpadding="0" cellspacing="0" width="100%" style="width:100%;">
<tr valign="top">
<td style="color:#505050;font-family:Arial,FreeSans,Helvetica,sans-serif;padding:0;">
<img id="email-avatar" src="https://secure.gravatar.com/avatar/50519fe2f08ad467c47e9c14431e6d5a?d=mm&s=48" alt="" height="48" width="48" border="0" align="left" style="padding:0;margin: 0 16px 16px 0;" />
<div id="email-action" style="padding: 0 0 8px 0;font-size:12px;line-height:18px;">
<a class="user-hover" rel="dave-r12" id="email_dave-r12" href="https://hibernate.atlassian.net/secure/ViewProfile.jspa?name=dave-r12" style="color:#6c797f;">Dave Roberge</a>
created <img src="https://hibernate.atlassian.net/images/icons/issuetypes/bug.png" height="16" width="16" border="0" align="absmiddle" alt="Bug"> <a style='color:#6c797f;text-decoration:none;' href='https://hibernate.atlassian.net/browse/HHH-8281'>HHH-8281</a>
</div>
<div id="email-summary" style="font-size:16px;line-height:20px;padding:2px 0 16px 0;">
<a style='color:#6c797f;text-decoration:none;' href='https://hibernate.atlassian.net/browse/HHH-8281'><strong>Paginated query produces non-deterministic result set ordering</strong></a>
</div>
</td>
</tr>
</table>
</td>
</tr>
<tr valign="top">
<td id="email-fields" style="padding:0 32px 32px 32px;">
<table border="0" cellpadding="0" cellspacing="0" style="padding:0;text-align:left;width:100%;" width="100%">
<tr valign="top">
<td id="email-gutter" style="width:64px;white-space:nowrap;"></td>
<td>
<table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr valign="top">
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 10px 10px 0;white-space:nowrap;">
<strong style="font-weight:normal;color:#505050;">Issue Type:</strong>
</td>
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 0 10px 0;width:100%;">
<img src="https://hibernate.atlassian.net/images/icons/issuetypes/bug.png" height="16" width="16" border="0" align="absmiddle" alt="Bug"> Bug
</td>
</tr> <tr valign="top">
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 10px 10px 0;white-space:nowrap;">
<strong style="font-weight:normal;color:#505050;">Affects Versions:</strong>
</td>
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 0 10px 0;width:100%;">
3.6.9 </td>
</tr>
<tr valign="top">
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 10px 10px 0;white-space:nowrap;">
<strong style="font-weight:normal;color:#505050;">Assignee:</strong>
</td>
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 0 10px 0;width:100%;">
Unassigned
</td>
</tr> <tr valign="top">
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 10px 10px 0;white-space:nowrap;">
<strong style="font-weight:normal;color:#505050;">Components:</strong>
</td>
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 0 10px 0;width:100%;">
core </td>
</tr>
<tr valign="top">
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 10px 10px 0;white-space:nowrap;">
<strong style="font-weight:normal;color:#505050;">Created:</strong>
</td>
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 0 10px 0;width:100%;">
30/May/13 7:39 AM
</td>
</tr> <tr valign="top">
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 10px 10px 0;white-space:nowrap;">
<strong style="font-weight:normal;color:#505050;">Description:</strong>
</td>
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 0 10px 0;width:100%;">
<p style='margin-top:0;margin-bottom:10px;'>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:</p>
<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<pre class="code-java">
Query query = session.createQuery(<span class="code-quote">"select c from Customer c order by c.customerId"</span>);
query.setMaxResults(10);
query.setFirstResult(5);
</pre>
</div></div>
<p style='margin-top:0;margin-bottom:10px;'>The SQL Server dialect will generate a query that looks like:</p>
<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<pre class="code-java">
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 ?
</pre>
</div></div>
<p style='margin-top:0;margin-bottom:10px;'>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.</p>
<p style='margin-top:0;margin-bottom:10px;'>We did some investigative work on this and here is what we found:</p>
<ul>
<li>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.</li>
<li>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.</li>
</ul>
<p style='margin-top:0;margin-bottom:10px;'>Some helpful links that relate to parallel queries:</p>
<ul>
<li><a href="http://msdn.microsoft.com/en-us/library/ms189914(v=sql.105).aspx" class="external-link" rel="nofollow">http://msdn.microsoft.com/en-us/library/ms189914(v=sql.105).aspx</a></li>
<li><a href="http://msdn.microsoft.com/en-us/library/ms187002.aspx" class="external-link" rel="nofollow">http://msdn.microsoft.com/en-us/library/ms187002.aspx</a></li>
</ul>
<p style='margin-top:0;margin-bottom:10px;'>It is somewhat difficult to force SQL Server to choose a parallel execution plan. I did find this article <a href="http://sqlblog.com/blogs/paul_white/archive/2011/12/23/forcing-a-parallel-query-execution-plan.aspx" class="external-link" rel="nofollow">http://sqlblog.com/blogs/paul_white/archive/2011/12/23/forcing-a-parallel-query-execution-plan.aspx</a> 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</p>
</td>
</tr>
<tr valign="top">
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 10px 10px 0;white-space:nowrap;">
<strong style="font-weight:normal;color:#505050;">Environment:</strong>
</td>
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 0 10px 0;width:100%;">
3.6.9.Final, SQL Server 2008
</td>
</tr>
<tr valign="top">
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 10px 10px 0;white-space:nowrap;">
<strong style="font-weight:normal;color:#505050;">Project:</strong>
</td>
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 0 10px 0;width:100%;">
<a style="color:#6c797f;" href="https://hibernate.atlassian.net/browse/HHH">Hibernate ORM</a>
</td>
</tr> <tr valign="top">
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 10px 10px 0;white-space:nowrap;">
<strong style="font-weight:normal;color:#505050;">Priority:</strong>
</td>
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 0 10px 0;width:100%;">
<img src="https://hibernate.atlassian.net/images/icons/priorities/major.png" height="16" width="16" border="0" align="absmiddle" alt="Major"> Major
</td>
</tr>
<tr valign="top">
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 10px 10px 0;white-space:nowrap;">
<strong style="font-weight:normal;color:#505050;">Reporter:</strong>
</td>
<td style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 0 10px 0;width:100%;">
<a class="user-hover" rel="dave-r12" id="email_dave-r12" href="https://hibernate.atlassian.net/secure/ViewProfile.jspa?name=dave-r12" style="color:#6c797f;">Dave Roberge</a>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td><!-- End #email-page -->
</tr>
<tr valign="top">
<td style="color:#505050;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:10px;line-height:14px;padding: 0 16px 16px 16px;text-align:center;">
This message is automatically generated by JIRA.<br />
If you think it was sent incorrectly, please contact your JIRA administrators<br />
For more information on JIRA, see: <a style='color:#6c797f;' href='http://www.atlassian.com/software/jira'>http://www.atlassian.com/software/jira</a>
</td>
</tr>
</table><!-- End #email-wrap -->
</div><!-- End #email-body -->