[hibernate-issues] [Hibernate-JIRA] Created: (HHH-2844) Limit and 'For Update' do not work on Oracle

[Michael Kopp (JIRA)]

Limit and 'For Update' do not work on Oracle
--------------------------------------------

                 Key: HHH-2844
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2844
             Project: Hibernate3
          Issue Type: Bug
          Components: query-sql
    Affects Versions: 3.2.2
            Reporter: Michael Kopp


Limits on oracle lead too:

  select * from (select x.y as xy_1 from table x) where rownum <= 5

when doing a for update that leads too

  select * from (select x.y as xy_1 from table x) where rownum <= 5 for update of x.y

The problem is that the x.y is invalid and not found within the temporary view and leads to an oracle error. 
what would be valid is the name of the view column xy_1, meaning

  select * from (select x.y as xy_1 from table x) where rownum <= 5 for update of xy_1

Actually this should be valid in all cases when doing a alias for update lock.

My Solution thus was to override the following in my own Oracle Dialect

    public String applyLocksToSql(final String sql, final Map aliasedLockModes, final Map keyColumnNames)
    {
        final String s = new ForUpdateFragment(this, aliasedLockModes, keyColumnNames)
        {

            @Override
            public ForUpdateFragment addTableAlias(final String alias)
            {
                // search for alias in sql
                final int i = sql.indexOf(alias);
                // check if the found string is followed by an ' as ' and thus has a column alias
                if (i != -1 && sql.length() > (i + alias.length() + 4) && sql.substring(i + alias.length(), i + alias.length() + 4).equals(
                    " as "))
                {
                    // use the column alias
                    return super.addTableAlias(sql.substring(i + alias.length() + 4, sql.indexOf(',',i + alias.length() + 4)));
                }
                return super.addTableAlias(alias);
            }
        }.toFragmentString();

        return sql + s;


-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira