<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://hibernate.onjira.com/secure/useravatar?ownerId=steve&avatarId=10346" 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="steve" id="email_steve" href="https://hibernate.onjira.com/secure/ViewProfile.jspa?name=steve" style="color:#6c797f;">Steve Ebersole</a>
updated <img src="https://hibernate.onjira.com/images/icons/bug.gif" height="16" width="16" border="0" align="absmiddle" alt="Bug"> <a style='color:#6c797f;text-decoration:none;' href='https://hibernate.onjira.com/browse/HHH-7233'>HHH-7233</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.onjira.com/browse/HHH-7233'><strong>unmuck EntityManager#getSingleResult wrt auto-setting of max results</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;">Change By:</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="steve" id="email_steve" href="https://hibernate.onjira.com/secure/ViewProfile.jspa?name=steve" style="color:#6c797f;">Steve Ebersole</a>
(26/Nov/12 10:08 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%;">
<span class="diffcontext">I have a DAO method that does the following:<br><br></span>
<span class="diffaddedchars" style="background-color:#ddfade;">{code:borderStyle=solid}<br></span>
<span class="diffcontext">return this.createNamedQuery( "systemTasks(machineId,status,maxExecuteTimestamp)" )<br> .setParameter( "machineId", machineId )<br> .setParameter( "status", status )<br> .setParameter( "maxExecuteTimestamp", maxExecuteTimestamp )<br> .setLockMode( LockModeType.PESSIMISTIC_WRITE )<br> .setMaxResults( 1 )<br> .getSingleResult();<br></span>
<span class="diffaddedchars" style="background-color:#ddfade;">{code}</span>
<span class="diffcontext"><br></span>
<span class="diffremovedchars" style="background-color:#ffe7e7;text-decoration:line-through;">HQL</span>
<span class="diffaddedchars" style="background-color:#ddfade;"><br>The JPQL</span>
<span class="diffcontext"> is:<br><br></span>
<span class="diffaddedchars" style="background-color:#ddfade;">{code:borderStyle=solid}<br></span>
<span class="diffcontext"> select st from SystemTask st<br> where st.machineId = :machineId and st.status = :status<br> and st.executeTimestamp <= :maxExecuteTimestamp<br> order by st.executeTimestamp<br></span>
<span class="diffaddedchars" style="background-color:#ddfade;">{code}</span>
<span class="diffcontext"><br></span>
<span class="diffaddedchars" style="background-color:#ddfade;"><br></span>
<span class="diffcontext">Hibernate generates the following SQL:<br><br></span>
<span class="diffaddedchars" style="background-color:#ddfade;">{code:borderStyle=solid}<br></span>
<span class="diffcontext"> select * from (<br> select [column list]<br> from jaseadm.system_task systemtask0_<br> where systemtask0_.machine_id=?<br> and systemtask0_.task_status_id=?<br> and systemtask0_.execute_timestamp<=?<br> order by systemtask0_.execute_timestamp )<br> where rownum <= ? for update<br></span>
<span class="diffaddedchars" style="background-color:#ddfade;">{code}</span>
<span class="diffcontext"><br></span>
<span class="diffaddedchars" style="background-color:#ddfade;"><br></span>
<span class="diffcontext">Which fails with:<br><br></span>
<span class="diffaddedchars" style="background-color:#ddfade;">{noformat}<br></span>
<span class="diffcontext"> ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.<br></span>
<span class="diffaddedchars" style="background-color:#ddfade;">{noformat}</span>
<span class="diffcontext"><br></span>
<span class="diffremovedchars" style="background-color:#ffe7e7;text-decoration:line-through;">This means that:</span>
<span class="diffcontext"><br></span>
<span class="diffremovedchars" style="background-color:#ffe7e7;text-decoration:line-through;"> - setLockMode (pessimistic) can't be used with setMaxResults</span>
<span class="diffaddedchars" style="background-color:#ddfade;">The problem is automatic setting of max results</span>
<span class="diffcontext"> when</span>
<span class="diffremovedchars" style="background-color:#ffe7e7;text-decoration:line-through;"> there's an order specified, because the oracle dialect only accommodates the "for update" clause and doesn't do anything with an "order by" clause.<br> - setLockMode (pessimistic) can't be used with</span>
<span class="diffaddedchars" style="background-color:#ddfade;"> {{</span>
<span class="diffcontext"> getSingleResult</span>
<span class="diffremovedchars" style="background-color:#ffe7e7;text-decoration:line-through;"> when there's an order by specified, because Hibernate indiscriminately calls setMaxResults in org.hibernate.ejb.getSingleResult()</span>
<span class="diffaddedchars" style="background-color:#ddfade;">}} is called</span>
<span class="diffcontext">:<br><br></span>
<span class="diffaddedchars" style="background-color:#ddfade;">{code:borderStyle=solid}<br></span>
<span class="diffcontext"> boolean mucked = false;<br> // IMPL NOTE : the mucking with max results here is attempting to help the user from shooting themselves<br> //                in the foot in the case where they have a large query by limiting the query results to 2 max<br> // SQLQuery cannot be safely paginated, leaving the user's choice here.<br> if ( getSpecifiedMaxResults() != 1 &&<br> ! ( SQLQuery.class.isAssignableFrom( query.getClass() ) ) ) {<br> mucked = true;<br> query.setMaxResults( 2 ); //avoid OOME if the list is huge<br> }<br> List<X> result = query.list();<br> if ( mucked ) {<br> query.setMaxResults( getSpecifiedMaxResults() );<br> }<br></span>
<span class="diffaddedchars" style="background-color:#ddfade;">{code}</span>
<span class="diffcontext"><br></span>
<span class="diffaddedchars" style="background-color:#ddfade;"><br></span>
<span class="diffcontext">Honestly I would prefer the OOM prevention stuff be removed, or configurable through the session factory, as I end up with a lot of natural key queries that look like:<br><br> select * from ( ... ) where rownum <= 1<br><br>When I haven't called setMaxResults myself.<br><br></span>
<span class="diffremovedchars" style="background-color:#ffe7e7;text-decoration:line-through;">Related to HHH-3298 and HHH-1168 (7 years unresolved). Note that unlike HH-1168, this breaks getSingleResult as well due to the whole "mucked" code.<br></span>
</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 -->