Adding SetMaxResults to HQL query causes parameters to break

fraserjames · **Joined:** Sun May 17, 2009 5:53 am **Posts:** 4

I've got a simple query between two dates on a table Performance

Code:

IQuery query = m_session.CreateQuery("from Performance p " +
"where p.PerformanceDate >= :fromDate " +
"and p.PerformanceDate <= :toDate ")
.SetDateTime("fromDate", (DateTime)fromDate)
.SetDateTime("toDate", (DateTime)toDate);

That creates the following SQL query

Code:

select performanc0_.Id as Id25_, performanc0_.CreatedDate as CreatedD2_25_, 
performanc0_.CreatedId as CreatedId25_, 
performanc0_.ModifiedDate as Modified4_25_, performanc0_.ModifiedId as ModifiedId25_, 
performanc0_.Deleted as Deleted25_, performanc0_.PerformanceDate as Performa7_25_, 
performanc0_.VenueId as VenueId25_, performanc0_.CountryId as CountryId25_, 
performanc0_.Provisional as Provisi10_25_, performanc0_.Premiere as Premiere25_, 
performanc0_.ProductionPremiere as Product12_25_, performanc0_.AgencyId as AgencyId25_, 
performanc0_.Details as Details25_, performanc0_.WorkId as WorkId25_ 
from Performance performanc0_ where ( (performanc0_.deleted is null or performanc0_.deleted = 0)) 
and ((performanc0_.PerformanceDate>=@p0 )and(performanc0_.PerformanceDate<=@p1 )); 
@p0 = '06/05/2009 00:00:00', @p1 = '18/05/2009 00:00:00'

Which works fine and returns the data I expect.

But if I add a SetMaxResults to the HQL

Code:

IQuery query = m_session.CreateQuery("from Performance p " +
"where p.PerformanceDate >= :fromDate " +
"and p.PerformanceDate <= :toDate ")
.SetDateTime("fromDate", (DateTime)fromDate)
.SetDateTime("toDate", (DateTime)toDate)
.SetMaxResults(100);

The SQL produced is not correct - the last parameter is used for all parameters in the query string - in this instance @p1

Code:

SELECT TOP 100 Id25_, CreatedD2_25_, CreatedId25_, Modified4_25_, ModifiedId25_, Deleted25_, 
Performa7_25_, VenueId25_, CountryId25_, Provisi10_25_, Premiere25_, Product12_25_, AgencyId25_, 
Details25_, WorkId25_ 
FROM (select performanc0_.Id as Id25_, performanc0_.CreatedDate as CreatedD2_25_, 
performanc0_.CreatedId as CreatedId25_, performanc0_.ModifiedDate as Modified4_25_, 
performanc0_.ModifiedId as ModifiedId25_, performanc0_.Deleted as Deleted25_, 
performanc0_.PerformanceDate as Performa7_25_, performanc0_.VenueId as VenueId25_, 
performanc0_.CountryId as CountryId25_, performanc0_.Provisional as Provisi10_25_, 
performanc0_.Premiere as Premiere25_, performanc0_.ProductionPremiere as Product12_25_, 
performanc0_.AgencyId as AgencyId25_, performanc0_.Details as Details25_, 
performanc0_.WorkId as WorkId25_, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) 
as __hibernate_sort_row from Performance performanc0_ 
where ( (performanc0_.deleted is null or performanc0_.deleted = 0)) 
and ((performanc0_.PerformanceDate>=@p1 )and(performanc0_.PerformanceDate<=@p1 ))) 
as query WHERE query.__hibernate_sort_row > 0 
ORDER BY query.__hibernate_sort_row; @p0 = '06/05/2009 00:00:00', @p1 = '18/05/2009 00:00:00'

I've extended this to use three parameters and get the same result - the last parameter is put inside the query for all parameters.

Am i using SetMaxResults correctly? Why would adding SetMaxResults cause the result set to change?

Thanks

wolli · **Posted:** Mon May 18, 2009 7:27 am

Dumb question, but have you checked that this is not only a problem with logging ? Try and disable the filter that seems to be used (delete = ....).

What version do you use ? There are some issues with the order of parameters when filter and subqueries are used together (NH-1098).

fraserjames · **Joined:** Sun May 17, 2009 5:53 am **Posts:** 4

Thanks for the reply.

I'm using NHibernate version 2.1.0.1001

The data being returned is incorrect - it's not just the log. So for the example below only performances for the 18/05/09 are returned. I've also disabled the filter but get the same results - again see the log below.

Code:

SELECT TOP 100 Id25_, CreatedD2_25_, CreatedId25_, Modified4_25_, ModifiedId25_, Deleted25_, Performa7_25_, 
VenueId25_, CountryId25_, Provisi10_25_, Premiere25_, Product12_25_, AgencyId25_, Details25_,
 WorkId25_ FROM (select performanc0_.Id as Id25_, performanc0_.CreatedDate as CreatedD2_25_, 
performanc0_.CreatedId as CreatedId25_, performanc0_.ModifiedDate as Modified4_25_, 
performanc0_.ModifiedId as ModifiedId25_, performanc0_.Deleted as Deleted25_, performanc0_.PerformanceDate as 
Performa7_25_, performanc0_.VenueId as VenueId25_, performanc0_.CountryId as CountryId25_, performanc0_.Provisional as 
Provisi10_25_, performanc0_.Premiere as Premiere25_, performanc0_.ProductionPremiere as Product12_25_, 
performanc0_.AgencyId as AgencyId25_, performanc0_.Details as Details25_, performanc0_.WorkId as WorkId25_, 
ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row 
from Performance performanc0_ where (performanc0_.PerformanceDate>=@p1 )and(performanc0_.PerformanceDate<=@p1 )) 
as query WHERE query.__hibernate_sort_row > 0 ORDER BY query.__hibernate_sort_row; 
@p0 = '06/05/2009 00:00:00', @p1 = '18/05/2009 00:00:00'

Thanks,
fraser

wolli · **Posted:** Mon May 18, 2009 9:22 am

Sounds like a bug ... and this one sounds pretty much like yours:

Quote:

http://nhjira.koah.net/browse/NH-1577

It should be fixed for 2.1.0.Alpha1 (don't know if build 1001 is newer or older than Alpha1).

fraserjames · **Joined:** Sun May 17, 2009 5:53 am **Posts:** 4

I'll test with the later version. My version is older than the Aplha1.

In the meantime if anyone else has this problem i've managed to implement the same functionality using filters and that works fine.

fraserjames · **Joined:** Sun May 17, 2009 5:53 am **Posts:** 4

I tried this with the 2.1.0.Alpha2 version and the problem is fixed.

Thanks for pointing me in the right direction.