Greetings All,

I'm experiencing an issue with Hibernate generated SQL when I attempt to retrieve a union-subclass entity via a concrete mapped superclass with LockMode.UPGRADE.
Hibernate will generate SQL with a UNION ALL of the sub-classes and then apply FOR UPDATE to the query which is not allowed with Oracle.

This leads to the following error:
java.sql.SQLException: ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.

It seems that "etc." part includes UNION and UNION ALL.

Example with three classes:
Entity (abstract) <--extends--- ConcreteInheritorOne (concrete) <--extends-- ConcreteInheritorTwo (concrete)

assume:
inheritorTwoId == the id of an item of class ConcreteInheritorTwo
session == a Hibernate Session

// These lookups will cause the error
Entity e = (Entity) session.get(Entity.class, inheritorTwoId, LockMode.UPGRADE);
Entity e = (Entity) session.get(ConcreteInheritorOne.class, inheritorTwoId, LockMode.UPGRADE);

// This will not cause the error
Entity e = (Entity) session.get(ConcreteInheritorTwo.class, inheritorTwoId, LockMode.UPGRADE);

I have attached entity classes and a Test class which illustrate this effect.
I spelunked into the code a bit, but an easy fix did not present itself to my hibernate-code-virgin eyes.

Thanks for making Hibernate great!
--Mje

1.  

   1.  

      1.  

         1. Workaround ####
            If anyone else is experiencing this problem, I've implemented code similar to the method below as a work-around in an abstraction layer:
            public Object getEntityForUpdate(Class entityClass, String entityId)
            {
            Session s = getSession();
            ClassMetadata meta = s.getSessionFactory().getClassMetadata(entityClass);

Boolean doTwoPhaseLock = false;

if(meta instanceof UnionSubclassEntityPersister)
{
if(((UnionSubclassEntityPersister)meta).getEntityMetamodel().getSubclassEntityNames().size() > 1)
doTwoPhaseLock = true;
}

if(doTwoPhaseLock)
{
Entity e = (Entity)s.get(entityClass, entityId);
System.out.println("Using Two-Phase Lock on: EntityId[" + entityId + "] EntityClass[" + entityClass.getName() + "]");
s.lock(e, LockMode.UPGRADE);
return e;
}
else
{
System.out.println("Using One-Phase Lock on: EntityId[" + entityId + "] EntityClass[" + entityClass.getName() + "]");
return s.get(entityClass, entityId, LockMode.UPGRADE);
}
}

1.  

   1.  

      1.  

         1. Hibernate Generated SQL Example ####
            select
            entity0_.id as id6_0_,
            entity0_.versionNumber as versionN2_6_0_,
            entity0_.clazz_ as clazz_0_
            from
            (
            select versionNumber, id, 2 as clazz_
            from BASE_ConcreteInheritorTwo

union all

select versionNumber, id, 1 as clazz_
from BASE_ConcreteInheritorOne

) entity0_ where entity0_.id=?
for update

1.  

   1.  

      1.  

         1. Stack Trace ####
            15:18:14,484 ERROR JDBCExceptionReporter:78 - ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.
            15:18:14,484 INFO DefaultLoadEventListener:111 - Error performing load command
            org.hibernate.exception.SQLGrammarException: could not load an entity: [org.example.domain.hibernate.Entity#1211505494256]
            at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
            at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
            at org.hibernate.loader.Loader.loadEntity(Loader.java:1874)
            at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:48)
            at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:42)
            at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:3049)
            at org.hibernate.event.def.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:399)
            at org.hibernate.event.def.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:375)
            at org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListener.java:139)
            at org.hibernate.event.def.DefaultLoadEventListener.lockAndLoad(DefaultLoadEventListener.java:297)
            at org.hibernate.event.def.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:106)
            at org.hibernate.impl.SessionImpl.fireLoad(SessionImpl.java:878)
            at org.hibernate.impl.SessionImpl.get(SessionImpl.java:869)
            at org.hibernate.impl.SessionImpl.get(SessionImpl.java:864)
            at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
            at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
            at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
            at java.lang.reflect.Method.invoke(Unknown Source)
            at org.hibernate.context.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:301)
            at $Proxy0.get(Unknown Source)
            at org.example.test.Test.doTest2(Test.java:68)
            at org.example.test.Test.main(Test.java:35)
            Caused by: java.sql.SQLException: ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.
            at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
            at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
            at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
            at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
            at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
            at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:810)
            at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1039)
            at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:850)
            at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1134)
            at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
            at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3384)
            at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
            at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
            at org.hibernate.loader.Loader.doQuery(Loader.java:674)
            at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
            at org.hibernate.loader.Loader.loadEntity(Loader.java:1860)
            ... 19 more