Bug with Paged ICriteria Projection query + Functions

cmaslen · **Posted:** Sat May 24, 2008 7:21 am

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 1.2.1GA

SQL Dialect: SQL Server 2005

Hi All,

I'm using a Projection query with the ICriteria API. I'm also generating a paged result. One of the columns I'd like in my projection is generated from a UDF call. The issue I"m having is the call will not work with a function that has more than one parameter where one of the parameters is a column from the underlying query. This is the case if it's a UDF or one of SQL Server's standard functions

Below is a sample showing the issue calling SQL Server's Round function.

Code:

// other code omitted
string sqlFragment = String.Format(
    "round( {{alias}}.Amount, 2 ) as RoundedAmount" );
var sqlAliases = new[] { "RoundedAmount" };
var sqlTypes = new IType[ 1 ] { NHibernateUtil.Double };
storeCriteria.SetProjection( Projections.ProjectionList()
    .Add( Projections.Property( "store.Id" ), "StoreId" )
// Etc
    .Add( Projections.SqlProjection( sqlFragment, sqlAliases, sqlTypes ), 
        "RoundedAmount" ) 
    );

The SQL Generated (intercepted by SQL Server's profiler) is similar to below:

Code:

SELECT  TOP 8 y0_, y1_, y2_, y3_, y4_, y5_, y6_, y7_, 
        y8_, y9_, Amount, RoundedAmount
FROM    (SELECT ROW_NUMBER() OVER(ORDER BY __hibernate_sort_expr_1__) as row, 
             query.y0_, query.y1_, query.y2_, query.y3_, query.y4_, query.y5_, 
             query.y6_, query.y7_, query.y8_, query.y9_, [color=red]query.Amount,[/color] 
             query.RoundedAmount, query.__hibernate_sort_expr_1__ 
        FROM (SELECT this_.StoreID as y0_, 
// etc
                 round( this_.Amount, 2 ) as RoundedAmount, 
                 CURRENT_TIMESTAMP as __hibernate_sort_expr_1__ FROM Store this_
             ) query 
        ) page 
WHERE   page.row > 0 ORDER BY __hibernate_sort_expr_1__

As you can see the column used inside the function call has been selected in the nested table expression "query" as though it was a column from the inner select clause.

Is this actually a bug or have I done something silly? If it a bug, is there a workaround short of using HQL or SQL. I'd like to avoid these as the query is a search query and I'd prefer not to have SQL or HQL fragments used to construct a final query.

Thanks in advance,

cmaslen · **Posted:** Tue Jun 10, 2008 7:25 am

It turns out the bug was with 1.2.0?? Either way the code in the NHibernate\Bin folder was not compiled from the source folders. Maybe the assemblies were left there from Windows' uninstall and not replaced on installation of the current version. So my bad for not manually cleaning up. I figure this is worth posting in case someone else has the issue.

I recompiled from source to debug it only to find the code works. The limit string now looks along the lines of (my formatting):

Code:

WITH query AS (
  SELECT TOP 8 ROW_NUMBER() OVER (ORDER BY  CURRENT_TIMESTAMP) as __hibernate_row_nr__,  
    this_.Col1 as y0_, 
    dbo.FunctionCall( this_.Col1, ...) as CorrelationName 
  FROM TableName this_
) 
SELECT * 
FROM query 
WHERE __hibernate_row_nr__ > 0 
ORDER BY __hibernate_row_nr__

a much nicer looking limit string.