[hibernate-issues] [Hibernate-JIRA] Created: (HHH-7233) setMaxResults or getSingleResult, combined with pessimistic locking, fails on Oracle with ORA-02014
setMaxResults or getSingleResult, combined with pessimistic locking, fails on Oracle with
ORA-02014
---------------------------------------------------------------------------------------------------
Key: HHH-7233
URL: https://hibernate.onjira.com/browse/HHH-7233
Project: Hibernate ORM
Issue Type: Bug
Components: core
Affects Versions: 4.1.2
Environment: Hibernate 4.1.2, Oracle 11gR2
Reporter: Scott Van Wart
I have a DAO method that does the following:
return this.createNamedQuery(
"systemTasks(machineId,status,maxExecuteTimestamp)" )
.setParameter( "machineId", machineId )
.setParameter( "status", status )
.setParameter( "maxExecuteTimestamp", maxExecuteTimestamp )
.setLockMode( LockModeType.PESSIMISTIC_WRITE )
.setMaxResults( 1 )
.getSingleResult();
HQL is:
select st from SystemTask st
where st.machineId = :machineId and st.status = :status
and st.executeTimestamp <= :maxExecuteTimestamp
order by st.executeTimestamp
Hibernate generates the following SQL:
select * from (
select [column list]
from jaseadm.system_task systemtask0_
where systemtask0_.machine_id=?
and systemtask0_.task_status_id=?
and systemtask0_.execute_timestamp<=?
order by systemtask0_.execute_timestamp )
where rownum <= ? for update
Which fails with:
ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.
This means that:
- setLockMode (pessimistic) can't be used with setMaxResults when 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.
- setLockMode (pessimistic) can't be used with getSingleResult when there's an
order by specified, because Hibernate indiscriminately calls setMaxResults in
org.hibernate.ejb.getSingleResult():
boolean mucked = false;
// IMPL NOTE : the mucking with max results here is attempting to help the user from
shooting themselves
// in the foot in the case where they have a large query by limiting the query results
to 2 max
// SQLQuery cannot be safely paginated, leaving the user's choice here.
if ( getSpecifiedMaxResults() != 1 &&
! ( SQLQuery.class.isAssignableFrom( query.getClass() ) ) ) {
mucked = true;
query.setMaxResults( 2 ); //avoid OOME if the list is huge
}
List<X> result = query.list();
if ( mucked ) {
query.setMaxResults( getSpecifiedMaxResults() );
}
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:
select * from ( ... ) where rownum <= 1
When I haven't called setMaxResults myself.
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.
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira