Invalid SQL generated using SetMaxResults with DB2

schmurgon · **Joined:** Thu Nov 24, 2005 5:52 pm **Posts:** 2

When using SetMaxResults with other criteria, hibernate is inserting the "rownumber() over() as rownum" into the WHERE clause, and creating a correlated query. Removing the SetMaxResults() call produces the correct result.

NHibernate version:
NHibernate 1.0

Mapping documents:

Code between sessionFactory.openSession() and session.close():

Code:

ICriteria criteria = HibernateSession.CreateCriteria( typeof ( MachineQuote ) );
                
// Top 10 (fails if this line is included
//criteria.SetMaxResults( 10 );

NullableInt32 userId = User.Id;

// Status Filter
ICriterion excludeDeleted 
    = Expression.Eq( "Status", ReferenceType.Status.Deleted );
                
criteria.Add( Expression.Not( excludeDeleted ) );    

// State Filter
criteria.Add( Expression.Eq( "StateCode", stateCode ));

results = ( ArrayList ) criteria.List();

Full stack trace of any exception that occurs:

2006-02-22 09:08:51,250 [5776] DEBUG NHibernate.SQL [(null)] <(null)> - select * from (SELECT this.QUOTE_ID as QUOTE_ID0_, this.COMM_FACTOR as COMM_2_40_0_, this.HAD_CLAIMS_OVER as HAD_C4_40_0_, this.COMMISSION_TYPE as COMMI3_40_0_, this.NO_OF_LOCATIONS as NO_OF7_40_0_, this.HAS_MAINTENANCE as HAS_M5_40_0_, this.PRORATA_PREMIUM as PRORA9_40_0_, this.OCCUPANCY_CODE as OCCUP8_40_0_, this.HAS_NO_DEFECTS as HAS_N6_40_0_, "this_1_".IS_PUBLISHED as IS_P14_39_0_, "this_1_".STATUS as STATUS39_0_, "this_1_".OLD_POLICY_NUMBER as OLD_P5_39_0_, "this_1_".CREATED_BY as CREA18_39_0_, "this_1_".CREATED_DATETIME as CREA19_39_0_, "this_1_".MODIFIED_BY as MODI20_39_0_, "this_1_".MODIFIED_DATETIME as MODI21_39_0_, "this_1_".TRADING_AS_NAME as TRAD13_39_0_, "this_1_".STATE_CODE as STAT16_39_0_, "this_1_".POLICY_VOID_DATE as POLIC9_39_0_, "this_1_".PRODUCT_ID as PROD22_39_0_, "this_1_".QUOTE_NUMBER as QUOT10_39_0_, "this_1_".BUSINESS_DESC as BUSIN3_39_0_, "this_1_".AGENT_NUMBER as AGEN17_39_0_, "this_1_".INSURED_NAME as INSUR4_39_0_, "this_1_".POLICY_NUMBER as POLIC7_39_0_, "this_1_".REFERRAL_STATUS as REFE11_39_0_, "this_1_".DELTA as DELTA39_0_, "this_1_".IS_SNAPSHOT as IS_S15_39_0_, "this_1_".POLICY_START_DATE as POLIC8_39_0_, "this_1_".QUOTE_OWNED_BY as QUOT23_39_0_, "this_1_".POLICY_END_DATE as POLIC6_39_0_ FROM DB2ADMIN.MACHINE_QUOTE this inner join DB2ADMIN.QUOTE "this_1_" on this.QUOTE_ID="this_1_".QUOTE_ID WHERE not "this_1_".STATUS = rownumber() over() as rownum, ? and "this_1_".STATE_CODE = ?) as tempresult where rownum <= ?
2006-02-22 09:08:51,468 [5776] DEBUG NHibernate.Util.ADOExceptionReporter [(null)] <(null)> - SQL Exception
IBM.Data.DB2.DB2Exception: ERROR [42601] [IBM][DB2/AIX64] SQL0104N An unexpected token "as" was found following "= rownumber() over()". Expected tokens may include: ")". SQLSTATE=42601

at IBM.Data.DB2.DB2Connection.b(IntPtr A_0, m A_1, x A_2)
at IBM.Data.DB2.DB2Command.a(CommandBehavior A_0, String A_1)
at IBM.Data.DB2.DB2Command.ExecuteReader(CommandBehavior behavior)
at IBM.Data.DB2.DB2Command.ExecuteReader()
at IBM.Data.DB2.DB2Command.System.Data.IDbCommand.ExecuteReader()
at NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd)
at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, RowSelection selection, ISessionImplementor session)
2006-02-22 09:08:51,484 [5776] WARN NHibernate.Util.ADOExceptionReporter [(null)] <(null)> - IBM.Data.DB2.DB2Exception: ERROR [42601] [IBM][DB2/AIX64] SQL0104N An unexpected token "as" was found following "= rownumber() over()". Expected tokens may include: ")". SQLSTATE=42601

at IBM.Data.DB2.DB2Connection.b(IntPtr A_0, m A_1, x A_2)
at IBM.Data.DB2.DB2Command.a(CommandBehavior A_0, String A_1)
at IBM.Data.DB2.DB2Command.ExecuteReader(CommandBehavior behavior)
at IBM.Data.DB2.DB2Command.ExecuteReader()
at IBM.Data.DB2.DB2Command.System.Data.IDbCommand.ExecuteReader()
at NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd)
at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, RowSelection selection, ISessionImplementor session)
2006-02-22 09:08:51,500 [5776] ERROR NHibernate.Util.ADOExceptionReporter [(null)] <(null)> - ERROR [42601] [IBM][DB2/AIX64] SQL0104N An unexpected token "as" was found following "= rownumber() over()". Expected tokens may include: ")". SQLSTATE=42601

2006-02-22 09:08:51,500 [5776] DEBUG NHibernate.Impl.BatcherImpl [(null)] <(null)> - Closed IDbCommand, open IDbCommands :0
2006-02-22 09:08:51,531 [5776] DEBUG NHibernate.Util.ADOExceptionReporter [(null)] <(null)> - Unable to perform find
IBM.Data.DB2.DB2Exception: ERROR [42601] [IBM][DB2/AIX64] SQL0104N An unexpected token "as" was found following "= rownumber() over()". Expected tokens may include: ")". SQLSTATE=42601

at IBM.Data.DB2.DB2Connection.b(IntPtr A_0, m A_1, x A_2)
at IBM.Data.DB2.DB2Command.a(CommandBehavior A_0, String A_1)
at IBM.Data.DB2.DB2Command.ExecuteReader(CommandBehavior behavior)
at IBM.Data.DB2.DB2Command.ExecuteReader()
at IBM.Data.DB2.DB2Command.System.Data.IDbCommand.ExecuteReader()
at NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd)
at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, RowSelection selection, ISessionImplementor session)
at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Object optionalObject, Object optionalId, Object[] optionalCollectionKeys, Boolean returnProxies)
at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Object optionalObject, Object optionalId, Object[] optionalCollectionKeys, Boolean returnProxies)
at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet querySpaces, IType[] resultTypes)
at NHibernate.Loader.CriteriaLoader.List(ISessionImplementor session)
at NHibernate.Impl.SessionImpl.Find(CriteriaImpl criteria)
2006-02-22 09:08:51,531 [5776] WARN NHibernate.Util.ADOExceptionReporter [(null)] <(null)> - IBM.Data.DB2.DB2Exception: ERROR [42601] [IBM][DB2/AIX64] SQL0104N An unexpected token "as" was found following "= rownumber() over()". Expected tokens may include: ")". SQLSTATE=42601

at IBM.Data.DB2.DB2Connection.b(IntPtr A_0, m A_1, x A_2)
at IBM.Data.DB2.DB2Command.a(CommandBehavior A_0, String A_1)
at IBM.Data.DB2.DB2Command.ExecuteReader(CommandBehavior behavior)
at IBM.Data.DB2.DB2Command.ExecuteReader()
at IBM.Data.DB2.DB2Command.System.Data.IDbCommand.ExecuteReader()
at NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd)
at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, RowSelection selection, ISessionImplementor session)
at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Object optionalObject, Object optionalId, Object[] optionalCollectionKeys, Boolean returnProxies)
at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Object optionalObject, Object optionalId, Object[] optionalCollectionKeys, Boolean returnProxies)
at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet querySpaces, IType[] resultTypes)
at NHibernate.Loader.CriteriaLoader.List(ISessionImplementor session)
at NHibernate.Impl.SessionImpl.Find(CriteriaImpl criteria)
2006-02-22 09:08:51,546 [5776] ERROR NHibernate.Util.ADOExceptionReporter [(null)] <(null)> - ERROR [42601] [IBM][DB2/AIX64] SQL0104N An unexpected token "as" was found following "= rownumber() over()". Expected tokens may include: ")". SQLSTATE=42601

2006-02-22 09:08:51,562 [5776] ERROR NHibernate.ADOException [(null)] <(null)> - Unable to perform find
IBM.Data.DB2.DB2Exception: ERROR [42601] [IBM][DB2/AIX64] SQL0104N An unexpected token "as" was found following "= rownumber() over()". Expected tokens may include: ")". SQLSTATE=42601

at IBM.Data.DB2.DB2Connection.b(IntPtr A_0, m A_1, x A_2)
at IBM.Data.DB2.DB2Command.a(CommandBehavior A_0, String A_1)
at IBM.Data.DB2.DB2Command.ExecuteReader(CommandBehavior behavior)
at IBM.Data.DB2.DB2Command.ExecuteReader()
at IBM.Data.DB2.DB2Command.System.Data.IDbCommand.ExecuteReader()
at NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd)
at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, RowSelection selection, ISessionImplementor session)
at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Object optionalObject, Object optionalId, Object[] optionalCollectionKeys, Boolean returnProxies)
at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Object optionalObject, Object optionalId, Object[] optionalCollectionKeys, Boolean returnProxies)
at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet querySpaces, IType[] resultTypes)
at NHibernate.Loader.CriteriaLoader.List(ISessionImplementor session)
at NHibernate.Impl.SessionImpl.Find(CriteriaImpl criteria)
2006-02-22 09:14:14,234 [5776] ERROR Vero.Common.ExceptionHandling.Log4NetExceptionHandler [(null)] <(null)> - HandlingInstanceID: d92feb33-62fb-4dbe-b704-c710d570c74a
An exception of type 'NHibernate.ADOException' occurred and was caught.
-----------------------------------------------------------------------
02/22/2006 09:14:14
Type : NHibernate.ADOException, NHibernate, Version=1.0.1.0, Culture=neutral, PublicKeyToken=154fdcb44c4484fc
Message : Unable to perform find
Source : NHibernate
Help link :
TargetSite : System.Collections.IList Find(NHibernate.Impl.CriteriaImpl)
Stack Trace : at NHibernate.Impl.SessionImpl.Find(CriteriaImpl criteria)
at NHibernate.Impl.CriteriaImpl.List()
at Vero.Cud.Persistence.Impl.QuoteDao.RetrieveRecentQuotes(String stateCode) in c:\subversion\cud\branches\dev\ben\source\vero.cud.persistence.impl\quotedao.cs:line 190

Additional Info:

MachineName : KEKR82A
TimeStamp : 21/02/2006 10:14:14 PM
FullName : Microsoft.Practices.EnterpriseLibrary.ExceptionHandling, Version=1.1.0.0, Culture=neutral, PublicKeyToken=bd1997643193348b
AppDomainName : domain-nunit.addin.dll
ThreadIdentity : bscott
WindowsIdentity : AUS\sbzs
Inner Exception
---------------
Type : IBM.Data.DB2.DB2Exception, IBM.Data.DB2, Version=8.1.2.1, Culture=neutral, PublicKeyToken=7c307b91aa13d208
Message : ERROR [42601] [IBM][DB2/AIX64] SQL0104N An unexpected token "as" was found following "= rownumber() over()". Expected tokens may include: ")". SQLSTATE=42601

Source : IBM.Data.DB2
Help link :
Errors : IBM.Data.DB2.DB2ErrorCollection
TargetSite : Void b(IntPtr, m, x)
Stack Trace : at IBM.Data.DB2.DB2Connection.b(IntPtr A_0, m A_1, x A_2)
at IBM.Data.DB2.DB2Command.a(CommandBehavior A_0, String A_1)
at IBM.Data.DB2.DB2Command.ExecuteReader(CommandBehavior behavior)
at IBM.Data.DB2.DB2Command.ExecuteReader()
at IBM.Data.DB2.DB2Command.System.Data.IDbCommand.ExecuteReader()
at NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd)
at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, RowSelection selection, ISessionImplementor session)
at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Object optionalObject, Object optionalId, Object[] optionalCollectionKeys, Boolean returnProxies)
at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Object optionalObject, Object optionalId, Object[] optionalCollectionKeys, Boolean returnProxies)
at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet querySpaces, IType[] resultTypes)
at NHibernate.Loader.CriteriaLoader.List(ISessionImplementor session)
at NHibernate.Impl.SessionImpl.Find(CriteriaImpl criteria)

Name and version of the database you are using:
IBM DB2 8.2.2

The generated SQL (show_sql=true):
(in exception dump above)

Debug level Hibernate log excerpt:
(in exception dump above)

sergey · **Posted:** Wed Feb 22, 2006 5:59 am

Please report this, it's a bug in DB2Dialect.GetLimitString. If you will be able to provide a patch, I will be grateful because I don't have a DB2 database to test.

As a workaround, try using HQL query, the bug should not occur then.

martijnb · **Joined:** Sat May 14, 2005 8:40 am **Posts:** 130

If actions are taken to fix this issue, it might be wise to combine this with creating a mssql2005 dialect because it requires exactly the same limit syntax (with rownumber()).

sergey · **Posted:** Wed Feb 22, 2006 9:29 am

Yes, I noticed that too when looking closer.

schmurgon · **Joined:** Thu Nov 24, 2005 5:52 pm **Posts:** 2

I've updated the bug in JIRA (and posted my fix below). What do I do now?

Code:

/// <summary>
/// Add a <c>LIMIT</c> clause to the given SQL <c>SELECT</c>
/// </summary>
/// <param name="querySqlString">A Query in the form of a SqlString.</param>
/// <param name="hasOffset">Offset of the first row is not zero</param>
/// <returns>A new SqlString that contains the <c>LIMIT</c> clause.</returns>
public override SqlString GetLimitString( SqlString querySqlString, bool hasOffset )
{
/*
* "select * from (select row_number() over(orderby_clause) as rownum, "
* querySqlString_without select
* " ) as tempresult where rownum between ? and ?"
*/
bool isInOrderBy = false;
StringBuilder orderByStringBuilder = new StringBuilder();

// extract the order by part
foreach( object sqlPart in querySqlString.SqlParts )
{
string sqlPartString = sqlPart as string;
if( sqlPartString != null )
{
if( sqlPartString.ToLower().TrimStart().StartsWith( "order by" ) )
{
isInOrderBy = true;
}
}

if( isInOrderBy && sqlPart is string )
{
orderByStringBuilder.Append( ( string ) sqlPart );
}
}

     string rownumClause = "rownumber() over(" + orderByStringBuilder.ToString() + ") as rownum, ";

            SqlStringBuilder pagingBuilder = new SqlStringBuilder();

            bool isFirstSelect = true;

            // build a new query and add the rownumber()
            foreach( object sqlPart in querySqlString.SqlParts )
            {
                string sqlPartString = sqlPart as string;
                if( sqlPartString != null )
                {
                    if( sqlPartString.ToLower().TrimStart().StartsWith( "select" ) && isFirstSelect )
                    {
                        // Add the rownum to the first select part
                        sqlPartString = sqlPartString.Insert(
                            sqlPartString.ToLower().IndexOf( "select ", 0 ) + 7, rownumClause );
                        pagingBuilder.Add( sqlPartString );
                        isFirstSelect = false;
                    }
                    else
                    {
                        // add the other string components
                        pagingBuilder.AddObject( sqlPart );
                    }
                }
                else
                {
                    // add non string components
                    pagingBuilder.AddObject( sqlPart );
                }
            }
            
            // Add the rest
            pagingBuilder.Insert( 0, "select * from (" );
pagingBuilder.Add( ") as tempresult " );

// Add the where clause
pagingBuilder.Add( "where rownum " );

if( hasOffset )
{
pagingBuilder.Add( "between " );
pagingBuilder.Add( new Parameter( "p1", new Int32SqlType() ) ).Add("+1");
pagingBuilder.Add( " and " );
pagingBuilder.Add( new Parameter( "p2", new Int32SqlType() ) );
}
else
{
pagingBuilder.Add( "<= " );
pagingBuilder.Add( new Parameter( "p1", new Int32SqlType() ) );
}

return pagingBuilder.ToSqlString();
}

sergey · **Posted:** Fri Apr 28, 2006 3:45 am

Thanks for the patch, I will include it in the next version.