<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0" /> <base href="https://hibernate.atlassian.net" />
<title>Message Title</title>
</head>
<body class="jira" style="color: #333; font-family: Arial, sans-serif; font-size: 14px; line-height: 1.429">
<table id="background-table" cellpadding="0" cellspacing="0" width="100%" style="border-collapse: collapse; mso-table-lspace: 0pt; mso-table-rspace: 0pt; background-color: #f5f5f5; border-collapse: collapse; mso-table-lspace: 0pt; mso-table-rspace: 0pt">
<!-- header here -->
<tr>
<td id="header-pattern-container" style="padding: 0px; border-collapse: collapse; padding: 10px 20px">
<table id="header-pattern" cellspacing="0" cellpadding="0" border="0" style="border-collapse: collapse; mso-table-lspace: 0pt; mso-table-rspace: 0pt">
<tr>
<td id="header-avatar-image-container" valign="top" style="padding: 0px; border-collapse: collapse; vertical-align: top; width: 32px; padding-right: 8px"> <img id="header-avatar-image" class="image_fix" src="cid:jira-generated-image-avatar-metalpegleg-25d64bbd-1717-4093-822b-80495b5baf47" height="32" width="32" border="0" style="border-radius: 3px; vertical-align: top" />
</td>
<td id="header-text-container" valign="middle" style="padding: 0px; border-collapse: collapse; vertical-align: middle; font-family: Arial, sans-serif; font-size: 14px; line-height: 20px; mso-line-height-rule: exactly; mso-text-raise: 1px"> <a class="user-hover" rel="metalpegleg" id="email_metalpegleg" href="https://hibernate.atlassian.net/secure/ViewProfile.jspa?name=metalpegleg" style="color:#6c797f;; color: #3b73af; text-decoration: none">kurtkashi</a> <strong>created</strong> an issue
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td id="email-content-container" style="padding: 0px; border-collapse: collapse; padding: 0 20px">
<table id="email-content-table" cellspacing="0" cellpadding="0" border="0" width="100%" style="border-collapse: collapse; mso-table-lspace: 0pt; mso-table-rspace: 0pt; border-spacing: 0; border-collapse: separate">
<tr>
<!-- there needs to be content in the cell for it to render in some clients -->
<td class="email-content-rounded-top mobile-expand" style="padding: 0px; border-collapse: collapse; color: #fff; padding: 0 15px 0 16px; height: 15px; background-color: #fff; border-left: 1px solid #ccc; border-top: 1px solid #ccc; border-right: 1px solid #ccc; border-bottom: 0; border-top-right-radius: 5px; border-top-left-radius: 5px; height: 10px; line-height: 10px; padding: 0 15px 0 16px; mso-line-height-rule: exactly">
</td>
</tr>
<tr>
<td class="email-content-main mobile-expand " style="padding: 0px; border-collapse: collapse; border-left: 1px solid #ccc; border-right: 1px solid #ccc; border-top: 0; border-bottom: 0; padding: 0 15px 0 16px; background-color: #fff">
<table class="page-title-pattern" cellspacing="0" cellpadding="0" border="0" width="100%" style="border-collapse: collapse; mso-table-lspace: 0pt; mso-table-rspace: 0pt">
<tr>
<td class="page-title-pattern-first-line " style="padding: 0px; border-collapse: collapse; font-family: Arial, sans-serif; font-size: 14px; padding-top: 10px"> <a href="https://hibernate.atlassian.net/browse/HHH" style="color: #3b73af; text-decoration: none">Hibernate ORM</a> / <a href="https://hibernate.atlassian.net/browse/HHH-9317" style="color: #3b73af; text-decoration: none"><img src="cid:jira-generated-image-static-improvement-763edbdd-cd45-4000-8a03-4840530cc533" height="16" width="16" border="0" align="absmiddle" alt="Improvement" style="vertical-align: text-bottom" /></a> <a href="https://hibernate.atlassian.net/browse/HHH-9317" style="color: #3b73af; text-decoration: none">HHH-9317</a>
</td>
</tr>
<tr>
<td style="vertical-align: top;; padding: 0px; border-collapse: collapse; padding-right: 5px; font-size: 20px; line-height: 30px; mso-line-height-rule: exactly" class="page-title-pattern-header-container"> <span class="page-title-pattern-header" style="font-family: Arial, sans-serif; padding: 0; font-size: 20px; line-height: 30px; mso-text-raise: 2px; mso-line-height-rule: exactly; vertical-align: middle"> <a href="https://hibernate.atlassian.net/browse/HHH-9317" style="color: #3b73af; text-decoration: none">Generated SQL using setMaxResults/setFirstResult possible duplicate entities in pages in Oracle when using rownum and ordering by non unique column - adding rowid would remove chances of duplicates in oracle</a> </span>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td class="email-content-main mobile-expand wrapper-special-margin" style="padding: 0px; border-collapse: collapse; border-left: 1px solid #ccc; border-right: 1px solid #ccc; border-top: 0; border-bottom: 0; padding: 0 15px 0 16px; background-color: #fff; padding-top: 10px; padding-bottom: 5px">
<table class="keyvalue-table" style="border-collapse: collapse; mso-table-lspace: 0pt; mso-table-rspace: 0pt">
<tr>
<th style="color: #707070; font: normal 14px/20px Arial, sans-serif; text-align: left; vertical-align: top; padding: 2px 0">
Issue Type:
</th>
<td class="has-icon" style="padding: 0px; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top"> <img src="cid:jira-generated-image-static-improvement-763edbdd-cd45-4000-8a03-4840530cc533" height="16" width="16" border="0" align="absmiddle" alt="Improvement" style="vertical-align: text-bottom" /> Improvement
</td>
</tr>
<tr>
<th style="color: #707070; font: normal 14px/20px Arial, sans-serif; text-align: left; vertical-align: top; padding: 2px 0">
Assignee:
</th>
<td style="padding: 0px; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top">
Unassigned
</td>
</tr>
<tr>
<th style="color: #707070; font: normal 14px/20px Arial, sans-serif; text-align: left; vertical-align: top; padding: 2px 0">
Attachments:
</th>
<td style="padding: 0px; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top">
oracle-orderby-rownnum-hibernate-test.zip
</td>
</tr>
<tr>
<th style="color: #707070; font: normal 14px/20px Arial, sans-serif; text-align: left; vertical-align: top; padding: 2px 0">
Components:
</th>
<td style="padding: 0px; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top">
query-sql
</td>
</tr>
<tr>
<th style="color: #707070; font: normal 14px/20px Arial, sans-serif; text-align: left; vertical-align: top; padding: 2px 0">
Created:
</th>
<td style="padding: 0px; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top">
06/Aug/2014 14:08 PM
</td>
</tr>
<tr>
<th style="color: #707070; font: normal 14px/20px Arial, sans-serif; text-align: left; vertical-align: top; padding: 2px 0">
Environment:
</th>
<td style="padding: 0px; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top">
Hibernate 4.2.8.FINAL, Oracle 11.2
</td>
</tr>
<tr>
<th style="color: #707070; font: normal 14px/20px Arial, sans-serif; text-align: left; vertical-align: top; padding: 2px 0">
Priority:
</th>
<td class="has-icon" style="padding: 0px; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top"> <img src="cid:jira-generated-image-static-minor-db9c6e7e-fb1e-41d0-9841-f4c3125306a2" height="16" width="16" border="0" align="absmiddle" alt="Minor" style="vertical-align: text-bottom" /> Minor
</td>
</tr>
<tr>
<th style="color: #707070; font: normal 14px/20px Arial, sans-serif; text-align: left; vertical-align: top; padding: 2px 0">
Reporter:
</th>
<td style="padding: 0px; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top"> <a class="user-hover" rel="metalpegleg" id="email_metalpegleg" href="https://hibernate.atlassian.net/secure/ViewProfile.jspa?name=metalpegleg" style="color:#6c797f;; color: #3b73af; text-decoration: none">kurtkashi</a>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td class="email-content-main mobile-expand issue-description-container" style="padding: 0px; border-collapse: collapse; border-left: 1px solid #ccc; border-right: 1px solid #ccc; border-top: 0; border-bottom: 0; padding: 0 15px 0 16px; background-color: #fff; padding-top: 5px; padding-bottom: 10px">
<table class="text-paragraph-pattern" cellspacing="0" cellpadding="0" border="0" width="100%" style="border-collapse: collapse; mso-table-lspace: 0pt; mso-table-rspace: 0pt; font-family: Arial, sans-serif; font-size: 14px; line-height: 20px; mso-line-height-rule: exactly; mso-text-raise: 2px">
<tr>
<td class="text-paragraph-pattern-container mobile-resize-text " style="padding: 0px; border-collapse: collapse; padding: 0 0 10px 0">
<p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0 0"><a href="http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html" class="external-link" rel="nofollow" style="color: #3b73af; text-decoration: none">http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html</a> contains a section titled 'Pagination with ROWNUM' where an example is given on some possible scenarios that can occur when using rownum in oracle.</p>
<p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0 0">Specifically looking at "One important thing about using this pagination query is that the ORDER BY statement should order by something unique. If what you are ordering by is not unique, you should add something to the end of the ORDER BY to make it so. If you sort 100 records by SALARY, for example, and they all have the same SALARY value, then specifying rows 20 to 25 does not really have any meaning. In order to see this, use a small table with lots of duplicated ID values...."</p>
<p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0 0">Example oracle sql from link using oracle rowid:</p>
<div class="code panel" style="border-width: 1px;; border: 1px solid #ccc; background: #f5f5f5; font-size: 12px; line-height: 1.333; font-family: monospace; border: 1px solid #ccc; -moz-border-radius: 3px 3px 3px 3px; border-radius: 3px 3px 3px 3px; margin: 9px 0">
<div class="codeContent panelContent" style="padding: 9px 12px">
<pre class="code-java" style="margin: 10px 0 0 0; max-height: 30em; overflow: auto; white-space: pre-wrap; word-wrap: normal">
create table t as select mod(level,5) id, trunc(dbms_random.value(1,100)) data from dual connect by level <= 10000;
select * from (select a.*, rownum rnum from (select id, data from t order by id) a where rownum <= 150) where rnum >= 148;
select * from (select a.*, rownum rnum from (select id, data from t order by id) a where rownum <= 151) where rnum >= 148;
select * from (select a.*, rownum rnum from (select id, data from t order by id, rowid) a where rownum <= 150) where rnum >= 148;
select * from (select a.*, rownum rnum from (select id, data from t order by id, rowid) a where rownum <= 151) where rnum >= 148;
</pre>
</div>
</div>
<p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0 0">I have attached an example test project which is based on example in link and uses hibernate. Test runs similar sql and shows the differences when rowid is appended at the end of order by.</p>
<p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0 0">Can always add another order by that is on a unique column like link states, but was wondering if this might be something to always add to sql with order by when using oracle and hibernate setMaxResults/setFirstResult.</p>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td class="email-content-main mobile-expand " style="padding: 0px; border-collapse: collapse; border-left: 1px solid #ccc; border-right: 1px solid #ccc; border-top: 0; border-bottom: 0; padding: 0 15px 0 16px; background-color: #fff">
<table id="actions-pattern" cellspacing="0" cellpadding="0" border="0" width="100%" style="border-collapse: collapse; mso-table-lspace: 0pt; mso-table-rspace: 0pt; font-family: Arial, sans-serif; font-size: 14px; line-height: 20px; mso-line-height-rule: exactly; mso-text-raise: 1px">
<tr>
<td id="actions-pattern-container" valign="middle" style="padding: 0px; border-collapse: collapse; padding: 10px 0 10px 24px; vertical-align: middle; padding-left: 0">
<table align="left" style="border-collapse: collapse; mso-table-lspace: 0pt; mso-table-rspace: 0pt">
<tr>
<td class="actions-pattern-action-icon-container" style="padding: 0px; border-collapse: collapse; font-family: Arial, sans-serif; font-size: 14px; line-height: 20px; mso-line-height-rule: exactly; mso-text-raise: 0px; vertical-align: middle"> <a href="https://hibernate.atlassian.net/browse/HHH-9317#add-comment" target="_blank" title="Add Comment" style="color: #3b73af; text-decoration: none"> <img class="actions-pattern-action-icon-image" src="cid:jira-generated-image-static-comment-icon-b4737924-cf58-4137-98b0-2486ed284426" alt="Add Comment" title="Add Comment" height="16" width="16" border="0" style="vertical-align: middle" /> </a>
</td>
<td class="actions-pattern-action-text-container" style="padding: 0px; border-collapse: collapse; font-family: Arial, sans-serif; font-size: 14px; line-height: 20px; mso-line-height-rule: exactly; mso-text-raise: 4px; padding-left: 5px"> <a href="https://hibernate.atlassian.net/browse/HHH-9317#add-comment" target="_blank" title="Add Comment" style="color: #3b73af; text-decoration: none">Add Comment</a>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
</tr>
<!-- there needs to be content in the cell for it to render in some clients -->
<tr>
<td class="email-content-rounded-bottom mobile-expand" style="padding: 0px; border-collapse: collapse; color: #fff; padding: 0 15px 0 16px; height: 5px; line-height: 5px; background-color: #fff; border-top: 0; border-left: 1px solid #ccc; border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; border-bottom-right-radius: 5px; border-bottom-left-radius: 5px; mso-line-height-rule: exactly">
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td id="footer-pattern" style="padding: 0px; border-collapse: collapse; padding: 12px 20px">
<table id="footer-pattern-container" cellspacing="0" cellpadding="0" border="0" style="border-collapse: collapse; mso-table-lspace: 0pt; mso-table-rspace: 0pt">
<tr>
<td id="footer-pattern-text" class="mobile-resize-text" width="100%" style="padding: 0px; border-collapse: collapse; color: #999; font-size: 12px; line-height: 18px; font-family: Arial, sans-serif; mso-line-height-rule: exactly; mso-text-raise: 2px">
This message was sent by Atlassian JIRA <span id="footer-build-information">(v6.4-OD-02-003#64000-<span title="c12ebbcd8fcc903510487eeeffbf170200b272ad" data-commit-id="c12ebbcd8fcc903510487eeeffbf170200b272ad}">sha1:c12ebbc</span>)</span>
</td>
<td id="footer-pattern-logo-desktop-container" valign="top" style="padding: 0px; border-collapse: collapse; padding-left: 20px; vertical-align: top">
<table style="border-collapse: collapse; mso-table-lspace: 0pt; mso-table-rspace: 0pt">
<tr>
<td id="footer-pattern-logo-desktop-padding" style="padding: 0px; border-collapse: collapse; padding-top: 3px"> <img id="footer-pattern-logo-desktop" src="cid:jira-generated-image-static-footer-desktop-logo-3d05bbb2-e167-45ef-8c4d-e41cf936536e" alt="Atlassian logo" title="Atlassian logo" width="169" height="36" class="image_fix" />
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
</tr>
</table>
</body>
</html>