I think that that would be best, can you create a patch + tests for this? On Wed, Oct 22, 2008 at 3:25 PM, Jason Meckley <[EMAIL PROTECTED]>wrote:
> > PagingWithNhibernateSpec.Should_get_foos_01_through_10 > NHibernate: SELECT this_.Id as Id0_0_ FROM Foo this_ ORDER BY this_.Id > asc limit 10; @p0 = '10' > > PagingWithNhibernateSpec.Should_get_foos_11_through_20 > NHibernate: SELECT this_.Id as Id0_0_ FROM Foo this_ ORDER BY this_.Id > asc limit 10, 10; @p0 = '10', @p1 = '10' > > PagingWithNhibernateSpec.Should_get_foos_21_through_30 > NHibernate: SELECT this_.Id as Id0_0_ FROM Foo this_ ORDER BY this_.Id > asc limit 10, 20; @p0 = '20', @p1 = '10' > > PagingWithNhibernateSpec.Should_get_foos_31_through_40 > NHibernate: SELECT this_.Id as Id0_0_ FROM Foo this_ ORDER BY this_.Id > asc limit 10, 30; @p0 = '30', @p1 = '10' > > PagingWithNhibernateSpec.Should_get_foos_41_through_50 > NHibernate: SELECT this_.Id as Id0_0_ FROM Foo this_ ORDER BY this_.Id > asc limit 10, 40; @p0 = '40', @p1 = '10' > > I also found this post http://sqlite.phxsoftware.com/forums/t/569.aspx > which describes the same problem with the raw SqLite. If I follow this > correctly the syntax is backwards. > > And this came straight from the SqLite documentation > http://www.sqlite.org/lang_select.html > "The LIMIT clause places an upper bound on the number of rows returned > in the result. A negative LIMIT indicates no upper bound. The optional > OFFSET following LIMIT specifies how many rows to skip at the > beginning of the result set. In a compound query, the LIMIT clause may > only appear on the final SELECT statement. The limit is applied to the > entire query not to the individual SELECT statement to which it is > attached. Note that if the OFFSET keyword is used in the LIMIT clause, > then the limit is the first number and the offset is the second > number. If a comma is used instead of the OFFSET keyword, then the > offset is the first number and the limit is the second number. This > seeming contradition is intentional - it maximizes compatibility with > legacy SQL database systems." > > so if this is true, should the Limit and Offset keywords be used to > create the sql statement and remove confusion about the parameters? > > On Oct 22, 6:23 am, "Ayende Rahien" <[EMAIL PROTECTED]> wrote: > > Can you show the generated SQL? > > > > On Wed, Oct 22, 2008 at 4:04 AM, Jason Meckley <[EMAIL PROTECTED] > >wrote: > > > > > > > > > I updated my criteria to include order. > > > > > private IList<Foo> GetResultsStartingAt(int firstResult) > > > { > > > return session > > > .CreateCriteria(typeof (Foo)) > > > .AddOrder(Order.Asc("id")) > > > .SetFirstResult(firstResult) > > > .SetMaxResults(PAGE_SIZE) > > > .List<Foo>(); > > > } > > > > > but the results are the same. > > > the 1st 2 tests passs > > > the last 2 fail. > > > > > the test fail with the respective messages > > > Expected 21 was Actually 11 > > > Expected 31 was Actually 11 > > > > > which looks like it's pulling the 2nd page of results instead of the > > > 3rd/4th respectively. > > > > > If it makes a difference I'm using a SqLite in-memory database for > > > this test. Could this be a possible bug with the driver? > > > > > On Oct 21, 9:46 pm, Jason Meckley <[EMAIL PROTECTED]> wrote: > > > > interesting... I assumed that a default order (PK) would be used if > no > > > > other sort was applied. I will add sorting to the tests above and > see > > > > how that effects it. > > > > > > On Oct 21, 9:23 pm, "Ayende Rahien" <[EMAIL PROTECTED]> wrote: > > > > > > > Unless you specify order, the DB is free to choose whatever order > it > > > likes.Let > > > > > us take this: > > > > > > > select top 10 * from Foo > > > > > > > The DB is free to return completely different result sets for this > > > query if > > > > > you execute it twice > > > > > > > On Wed, Oct 22, 2008 at 3:16 AM, Jason Meckley < > [EMAIL PROTECTED] > > > >wrote: > > > > > > > > I don't follow. What do you mean "orders may be indeterministic"? > > > > > > > > On Oct 21, 5:02 pm, "Tuna Toksöz" <[EMAIL PROTECTED]> wrote: > > > > > > > The orders may be indeterministic > > > > > > > > > On Tue, Oct 21, 2008 at 11:58 PM, Jason Meckley < > > > [EMAIL PROTECTED] > > > > > > >wrote: > > > > > > > > > > I'm having a difficult time understanding page, specifically > > > > > > > > SetFirstResult() and SetMaxResults() > > > > > > > > > > from what I read on different posts SetFirstResult is the > > > zero-based > > > > > > > > index of the record you want to start with. SetMaxResults is > the > > > > > > > > number of records you want to return. > > > > > > > > > > if this is correct then why do tests: > > > > > > > > Should_get_foos_21_through_30 > > > > > > > > Should_get_foos_31_through_40 > > > > > > > > fail? > > > > > > > > > > //Foo has 1 property Id which is readonly. The id is assigned > via > > > > > > > > ctor. > > > > > > > > > > namespace Paging.Spike.Core.Test > > > > > > > > { > > > > > > > > [TestFixture] > > > > > > > > public class PagingWithNhibernateSpec > > > > > > > > { > > > > > > > > private ISessionFactory factory; > > > > > > > > private Configuration cfg; > > > > > > > > private ISession session; > > > > > > > > private const int PAGE_SIZE = 10; > > > > > > > > > > [TestFixtureSetUp] > > > > > > > > public void TestFixtureSetup() > > > > > > > > { > > > > > > > > cfg = new Configuration().Configure(); > > > > > > > > factory = cfg.BuildSessionFactory(); > > > > > > > > } > > > > > > > > > > [TestFixtureTearDown] > > > > > > > > public void TestFixtureTeardown() > > > > > > > > { > > > > > > > > factory.Dispose(); > > > > > > > > } > > > > > > > > > > [SetUp] > > > > > > > > public void SetUp() > > > > > > > > { > > > > > > > > session = factory.OpenSession(); > > > > > > > > new SchemaExport(cfg).Execute(false, true, false, > > > true, > > > > > > > > session.Connection, null); > > > > > > > > using(ITransaction transaction = > > > > > > > > session.BeginTransaction()) > > > > > > > > { > > > > > > > > for (int i = 1; i <= 100; i++) > > > > > > > > session.Save(new Foo(i)); > > > > > > > > transaction.Commit(); > > > > > > > > } > > > > > > > > } > > > > > > > > > > [TearDown] > > > > > > > > public void TearDown() > > > > > > > > { > > > > > > > > session.Dispose(); > > > > > > > > } > > > > > > > > > > [Test] > > > > > > > > public void Should_get_foos_01_through_10() > > > > > > > > { > > > > > > > > ValidateIdsStartingWithId(1, > > > > > > > > GetResultsStartingAtIndex(0)); > > > > > > > > } > > > > > > > > > > [Test] > > > > > > > > public void Should_get_foos_11_through_20() > > > > > > > > { > > > > > > > > ValidateIdsStartingWithId(11, > > > > > > > > GetResultsStartingAtIndex(10)); > > > > > > > > } > > > > > > > > > > [Test] > > > > > > > > public void Should_get_foos_21_through_30() > > > > > > > > { > > > > > > > > ValidateIdsStartingWithId(21, > > > > > > > > GetResultsStartingAtIndex(20)); > > > > > > > > } > > > > > > > > > > [Test] > > > > > > > > public void Should_get_foos_31_through_40() > > > > > > > > { > > > > > > > > ValidateIdsStartingWithId(31, > > > > > > > > GetResultsStartingAtIndex(30)); > > > > > > > > } > > > > > > > > > > private IList<Foo> GetResultsStartingAtIndex(int > > > firstResult) > > > > > > > > { > > > > > > > > return session > > > > > > > > .CreateCriteria(typeof (Foo)) > > > > > > > > .SetFirstResult(firstResult) > > > > > > > > .SetMaxResults(PAGE_SIZE) > > > > > > > > .List<Foo>(); > > > > > > > > } > > > > > > > > > > private static void ValidateIdsStartingWithId(int id, > > > > > > > > ICollection<Foo> foos) > > > > > > > > { > > > > > > > > foreach (Foo foo in foos) > > > > > > > > Console.WriteLine(foo); > > > > > > > > > > Assert.AreEqual(PAGE_SIZE, foos.Count); > > > > > > > > > > foreach (Foo foo in foos) > > > > > > > > Assert.AreEqual(id++, foo.Id); > > > > > > > > } > > > > > > > > } > > > > > > > > } > > > > > > > > > -- > > > > > > > Tuna Toksöz > > > > > > > > > Typos included to enhance the readers attention! > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/nhusers?hl=en -~----------~----~----~----~------~----~------~--~---
