I agree that it would be a good idea to add the ordering but I don't see how that would fix the original problem that I posted. The problem I have with the rownum generated is how nHibernate is translating the same criteria between SQL Server and Oracle differently, yielding different results with the same data in the database. In Oracle, I am getting less objects in the set than expected. Ordering would have helped if I was getting the correct number of rows but not the exptected rows.
On Monday, February 11, 2013 10:16:11 AM UTC-6, Rippo wrote: > I would still add a order by to the query, just because the data is the > same doesn’t guarantee that the results will be the same... > > > > > > *From:* [email protected] <javascript:> [mailto: > [email protected] <javascript:>] *On Behalf Of *wsadiq > *Sent:* 11 February 2013 14:45 > *To:* [email protected] <javascript:> > *Subject:* Re: [nhusers] Issue with SetFirstResult and SetMaxResults > > > > I am getting the same result with 3.3.1 as well as 3.2.0. > > > > Thanks. > > > On Wednesday, February 6, 2013 11:29:04 PM UTC-6, Alexander I. Zaytsev > wrote: > > What version of NHibernate are you using? Also, you missed ordering in > query. To get correct paged results query must be ordered. > > > > Best Regards, Alex > > > > 2013/1/30 unhaddins <[email protected]> > > I am executing the same criteria against SQLServer and Oracle and get two > differen tresults. the data in both databases is exactly the same. > > > > First, here is my code. > > > > ICriteria crit = CurrentSession.CreateCriteria(typeof(T)); > > crit.SetFirstResult(3); > > crit.SetMaxResults(1); > > IList<T> set = crit.List<T>(); > > > > I am wanting to get 1 row starting from record 4 (assuming 0 base). That > is exactly the result I get from SQL Server but the query generated for > Oracle returns 0. Here are the queries generated by nHibernate captured > through NHProfiler. > > SQL SERVER QUERY > > SELECT TOP (1 /* @p0 */) Id64_0_, > Code64_0_, > Name64_0_, > Targeted64_0_, > StartDate64_0_, > EndDate64_0_, > DisplayO7_64_0_, > CreateDate64_0_, > UpdateDate64_0_ > FROM (SELECT this_.Id as Id64_0_, > this_.Code as Code64_0_, > this_.Name as Name64_0_, > this_.Targeted as > Targeted64_0_, > this_.StartDate as > StartDate64_0_, > this_.EndDate as > EndDate64_0_, > this_.DisplayOrder as > DisplayO7_64_0_, > this_.CreateDate as > CreateDate64_0_, > this_.UpdateDate as > UpdateDate64_0_, > ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as > __hibernate_sort_row > FROM LW_Promotion this_ > WHERE this_.Id in (2 /* @p1 */, 4 /* @p2 */, 5 /* @p3 */, 6 /* > @p4 */, > 7 /* @p5 */, 8 /* @p6 */)) as query > WHERE query.__hibernate_sort_row > 3 /* @p7 */ > ORDER BY query.__hibernate_sort_row > > ORACLE QUERY > > > > select Id64_0_, > Code64_0_, > Name64_0_, > Targeted64_0_, > StartDate64_0_, > EndDate64_0_, > DisplayO7_64_0_, > CreateDate64_0_, > UpdateDate64_0_ > from (select row_.*, > rownum rownum_ > from (SELECT this_.Id as Id64_0_, > this_.Code as Code64_0_, > this_.Name as Name64_0_, > this_.Targeted as Targeted64_0_, > this_.StartDate as StartDate64_0_, > this_.EndDate as EndDate64_0_, > this_.DisplayOrder as DisplayO7_64_0_, > this_.CreateDate as CreateDate64_0_, > this_.UpdateDate as UpdateDate64_0_ > FROM LW_Promotion this_ > WHERE this_.Id in (44 /* :p0 */, 162 /* :p1 */, 165 /* > :p2 */, 168 /* :p3 */, > 171 /* :p4 */, 174 /* :p5 */)) row_ > where rownum <= 1 /* :p6 */) > where rownum_ > 3 /* :p7 */ > > > > Can anybody shed some light as to what is nHobernate tryign to do with the > oracle query. > > > > Thanks in advance. > > > > Waqar Sadiq > > > > -- > You received this message because you are subscribed to the Google Groups > "nhusers" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/nhusers?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > > > > > -- > You received this message because you are subscribed to the Google Groups > "nhusers" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected] <javascript:>. > To post to this group, send email to [email protected] <javascript:> > . > Visit this group at http://groups.google.com/group/nhusers?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- You received this message because you are subscribed to the Google Groups "nhusers" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/nhusers?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
