We're updating the issue view to help you get more done. 

UpgradeSkipLockedTest, PessimisticReadSkipLockedTest and OracleFollowOnLockingTest fail with Oracle12c

Description

org.hibernate.test.locking.UpgradeSkipLockedTest.testOracleSkipLocked
org.hibernate.test.locking.PessimisticReadSkipLockedTest.testOracleSkipLocked
org.hibernate.test.dialect.functional.OracleFollowOnLockingTest.testPessimisticLockWithMaxResultsThenNoFollowOnLocking

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 Caused by: java.sql.SQLSyntaxErrorException: ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc. at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494) at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:226) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:59) at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:747) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:904) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1082) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3780) at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1343) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3822) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1165) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60) ... 35 more Caused by: Error : 2014, Position : 0, Sql = select abstractsk0_.id as id1_0_, abstractsk0_.processed as processed2_0_ from BatchJob abstractsk0_ fetch first :1 rows only for update skip locked, OriginalSql = select abstractsk0_.id as id1_0_, abstractsk0_.processed as processed2_0_ from BatchJob abstractsk0_ fetch first ? rows only for update skip locked, Error Msg = ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.

These tests pass with older versions of Oracle databases, but fail with Oracle12. It looks like that Oracle12c doesn't support FOR UPDATE together with FETCH FIRST ....

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 16:20:04,395 DEBUG SQL:94 - select abstractsk0_.id as id1_0_, abstractsk0_.processed as processed2_0_ from BatchJob abstractsk0_ fetch first ? rows only for update skip locked Hibernate: select abstractsk0_.id as id1_0_, abstractsk0_.processed as processed2_0_ from BatchJob abstractsk0_ fetch first ? rows only for update skip locked 16:20:04,409 WARN SqlExceptionHelper:137 - SQL Error: 2014, SQLState: 42000 16:20:04,409 ERROR SqlExceptionHelper:142 - ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.

Oracle11gR2 (with Oracle10gDialect) uses different query

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 14:05:20,381 DEBUG SQL:94 - select * from ( select abstractsk0_.id as id1_0_, abstractsk0_.processed as processed2_0_ from BatchJob abstractsk0_ ) where rownum <= ? for update skip locked Hibernate: select * from ( select abstractsk0_.id as id1_0_, abstractsk0_.processed as processed2_0_ from BatchJob abstractsk0_ ) where rownum <= ? for update skip locked

Difference with Oracle12cDialect is LimitHandler

1 2 3 4 @Override public LimitHandler getLimitHandler() { return SQL2008StandardLimitHandler.INSTANCE; }

It's probably only test issue, but I'm not sure.

Environment

Oracle12c

Status

Assignee

Guillaume Smet

Reporter

Martin Šimka

Fix versions

Labels

backPortable

Backport?

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

5.3.3

Priority

Major