Hello,

I have a problem with a query using the PostgreSQL dialect.

A query on a table called "ne" with the lock mode set to UPGRADE is translated into an SQL-query, which ends this way:
"for update of this"
(see complete statement below),
which leads to a hibernate exception (ERROR: relation "this" in FOR UPDATE clause not found in FROM clause).

This happens with the PostgreSQL dialect; the query worked well with Oracle, as the Oracle-dialect does not support "forUpdateOf".

Workarounds:
add qp.setLockModeAlias("ne"); in the DAO object
or
build own version of hibernate2.jar, with supportsForUpdateOf returning false in PostgreSQLDialect.java.

I would have expected hibernate to add the table name automatically to the "for update of" clause.

Did I forget something in the hibernate configuration or is this a hibernate bug?

Regards
Ralf


Hibernate version:2.1.6

Code in overloaded DAO object
QueryProperties qp = new HibernateQueryProperties();
qp.setLockMode(HibernateLockMode.UPGRADE);

List l = getNamedQuery(QUERY_GET_NES_BY_ACTIVATION_AND_EMID, new Serializable[]
{ new Integer(EnableSwitch.ENABLED.getOrdinal()),emId},qp);

query statement in mapping file
<query name="getNesByActivationAndEmid">
from Ne ne
where ne.activationAsInt= ?
and ne.emAsId= ?
</query>


Name and version of the database you are using:PostgreSQL 8.0.0 beta 4

The generated SQL (show_sql=true):
2004-11-03 12:34:23,062 DEBUG [net.sf.hibernate.hql.QueryTranslator] SQL: select ne0_.ID as ID, ne0_.MO_VERSION as MO_VERSION, ne0_.LAST_UPDATED as LAST_UPD3_, ne0_.USED_BY as USED_BY, ne0_.CAPABILITIES as CAPABILI5_, ne0_.LOCATION as LOCATION, ne0_.DISPLAY_ADDRESS as DISPLAY_7_, ne0_.ACTIVATION as ACTIVATION, ne0_.ADMINISTRATIVE_STATE as ADMINIST9_, ne0_.RECONNECT_INTERVAL as RECONNE10_, ne0_.DISPLAY_DEVICE_DATA as DISPLAY11_, ne0_.LOGIN_PASSWORD_MD5 as LOGIN_P12_, ne0_.NAME as NAME, ne0_.LOGIN_USER_NAME as LOGIN_U14_, ne0_.SUPPORTED_LAYERS as SUPPORT15_, ne0_.COM_STATE as COM_STATE, ne0_.INIT_STATE as INIT_STATE, ne0_.PROPERTIES_VALID as PROPERT18_, ne0_.ADD_INFO as ADD_INFO, ne0_.RETRY_COUNTER as RETRY_C20_, ne0_.NE_TYPE_ID as NE_TYPE_ID, ne0_.PARENT_NE_ID as PARENT_22_, ne0_.ICON_ID as ICON_ID, ne0_.EM_ID as EM_ID, ne0_.GLOBALID as GLOBALID, ne0_.INIT_TIME as INIT_TIME, ne0_.ACCESS_CONTROL as ACCESS_27_ from NE ne0_ where (ne0_.ACTIVATION=? )and(ne0_.EM_ID=? )
2004-11-03 12:34:23,078 DEBUG [net.sf.hibernate.impl.BatcherImpl] about to open: 0 open PreparedStatements, 0 open ResultSets
2004-11-03 12:34:23,078 DEBUG [net.sf.hibernate.SQL] select ne0_.ID as ID, ne0_.MO_VERSION as MO_VERSION, ne0_.LAST_UPDATED as LAST_UPD3_, ne0_.USED_BY as USED_BY, ne0_.CAPABILITIES as CAPABILI5_, ne0_.LOCATION as LOCATION, ne0_.DISPLAY_ADDRESS as DISPLAY_7_, ne0_.ACTIVATION as ACTIVATION, ne0_.ADMINISTRATIVE_STATE as ADMINIST9_, ne0_.RECONNECT_INTERVAL as RECONNE10_, ne0_.DISPLAY_DEVICE_DATA as DISPLAY11_, ne0_.LOGIN_PASSWORD_MD5 as LOGIN_P12_, ne0_.NAME as NAME, ne0_.LOGIN_USER_NAME as LOGIN_U14_, ne0_.SUPPORTED_LAYERS as SUPPORT15_, ne0_.COM_STATE as COM_STATE, ne0_.INIT_STATE as INIT_STATE, ne0_.PROPERTIES_VALID as PROPERT18_, ne0_.ADD_INFO as ADD_INFO, ne0_.RETRY_COUNTER as RETRY_C20_, ne0_.NE_TYPE_ID as NE_TYPE_ID, ne0_.PARENT_NE_ID as PARENT_22_, ne0_.ICON_ID as ICON_ID, ne0_.EM_ID as EM_ID, ne0_.GLOBALID as GLOBALID, ne0_.INIT_TIME as INIT_TIME, ne0_.ACCESS_CONTROL as ACCESS_27_ from NE ne0_ where (ne0_.ACTIVATION=? )and(ne0_.EM_ID=? ) for update of this
2004-11-03 12:34:23,078 DEBUG [net.sf.hibernate.impl.BatcherImpl] preparing statement
2004-11-03 12:34:23,078 DEBUG [net.sf.hibernate.type.IntegerType] binding '1' to parameter: 1
2004-11-03 12:34:23,078 DEBUG [net.sf.hibernate.type.IntegerType] binding '21' to parameter: 2
2004-11-03 12:34:23,078 DEBUG [net.sf.hibernate.util.JDBCExceptionReporter] SQL Exception
java.sql.SQLException: ERROR: relation "this" in FOR UPDATE clause not found in FROM clause
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1187)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:990)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:138)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:347)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:294)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:209)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:314)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:800)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:189)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:955)
at net.sf.hibernate.loader.Loader.list(Loader.java:946)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:846)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1543)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)

Please submit a runnable main method to JIRA.