[hibernate-issues] [Hibernate-JIRA] Created: (HHH-7233) setMaxResults or getSingleResult, combined with pessimistic locking, fails on Oracle with ORA-02014

[Scott Van Wart (JIRA)]

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