OrmLite 5.11 is producing invalid (MS SQL) Count query

ServiceStack.OrmLite
1

After upgrading ServiceStack.OrmLite from 5.9 to 5.11 we have noticed that some of ORM queries are producing invalid MS SQL queries.

The issue can be reproduced with the following tests:

using NUnit.Framework;

namespace ServiceStack.OrmLite.SqlServerTests.Issues
{
    [TestFixture]
    public class CountDistinctTests : OrmLiteTestBase
    {
        public class CountDistinctIssueTable
        {
            public long AutoId { get; set; }
        }

        [Test]
        public void CountDistinctTest()
        {
            Execute(SqlServerDialect.Provider);
        }

        [Test]
        public void CountDistinctTest2008()
        {
            Execute(SqlServer2008Dialect.Provider);
        }

        [Test]
        public void CountDistinctTest2012()
        {
            Execute(SqlServer2012Dialect.Provider);
        }

        private void Execute(IOrmLiteDialectProvider dialectProvider)
        {
            using (var db = OpenDbConnection("Data Source=(localdb);Integrated Security=True;Initial Catalog=OrmTest", dialectProvider))
            {
                db.DropAndCreateTable<CountDistinctIssueTable>();

                var query = db.From<CountDistinctIssueTable>();
                query.Skip(0);

                db.Scalar<long>(query.Select(x => Sql.CountDistinct(x.AutoId)));
            }
        }
    }
}

SQL produced before (version 5.9 - SQL2008 dialect):

SELECT COUNT(DISTINCT "AutoId") 
FROM "CountDistinctIssueTable"

SQL produced after (version 5.11 - SQL2012 dialect):

SELECT COUNT(DISTINCT "AutoId") 
FROM "CountDistinctIssueTable" ORDER BY "CountDistinctIssueTable"."AutoId" OFFSET 0 ROWS

We believe this is a bug in a latest version of OrmLite. Can you please investigate this?

2

It’s because by using one of the paging APIs (i.e. Skip/Take/Limit) it triggers paging functionality which if no custom OrderBy is configured, it will implicitly include an Order By Primary Key in order to return a query with consistent ordering.

However paging functions (other than Limit(1) or Take(1)) shouldn’t be used in Single or Scalar APIs, to support this syntax a major refactor was required to pass the query type down to the generated SQL API to effectively ignore paging functions on scalar or single query APIs.

This change is available in the latest v5.11.1 that’s now available on MyGet.

This change effectively guards against improper usage but the real fix should be not to include paging functions (e.g. q.Skip(0)) in Single or Scalar APIs, if you need to reset Skip/Take you can use q.ClearLimits() (or q.Limit() without args) to reset it back to its original undefined values.