Case in order clause causes bad page queries

[KallDrexx]

I tried to fix this myself, but unfortunately the rxOrderBy regular expression is gibberish to me :(.

To reproduct this I added the following to the top of PagingHelper.cs file:

#if DEBUG
  // If we are in debug mode, allow petapoco.Tests to see internal classes
  [assembly: InternalsVisibleTo("PetaPoco.Tests")]
#endif

I then added the following test case to Misc.cs:

    [Test]
    public void CaseStatementInOrderByClauseCorrectlyParses()
    {
      const string sql = "select * from table where column = 'abc' order by case when PublishDate is null then CreationDate else PublishDate end desc";
      const string expectedCountSql = "select count(*) from table where column = 'abc'";
      const string expectedOrderBySql = "order by case when PublishDate is null then CreationDate else PublishDate end desc";
      const string expectedSelectRemoved =
        "* from table where column = 'abc' order by case when PublishDate is null then CreationDate else PublishDate end desc";

      PagingHelper.SQLParts parts;
      bool result = PagingHelper.SplitSQL(sql, out parts);

      Assert.IsTrue(result);
      Assert.AreEqual(sql, parts.sql);
      Assert.AreEqual(expectedCountSql, parts.sqlCount);
      Assert.AreEqual(expectedOrderBySql, parts.sqlOrderBy);
      Assert.AreEqual(expectedSelectRemoved, parts.sqlSelectRemoved);
    }

This fails because the regex does not correctly handle case statments inside of an order by, and thus causes sqlCount to be "select COUNT(*) from table where column = 'abc' when PublishDate is null then CreationDate else PublishDate end desc", which is incorrect sql and causes an error.

If I understood regex better I'd fix it myself and submit a patch, but I can't even get http://derekslager.com/blog/posts/2007/09/a-better-dotnet-regular-expression-tester.ashx to parse it out for me, sorry :(

[Blatant]

I too have encountered this, any fixes??

My Query involves ordering on 2 columns (PreSort and Distance), where Distance is calculated double and PreSort is 1 or 2 depending on if distance could be calculated.

My Order by Clause is PreSort ASC, Distance ASC.

Works fine with 'fetch' but exception is thrown using 'page'. Was there ever a solution??