Failing ActiveRecord unit test based on false expectation

[Nick Tidey]

Well, this has been an adventure. Whilst looking into the final
ActiveRecord unit test that was failing for me, I discovered
interesting things and overcame an assumption I was making about SQL
Server behaviour. I'm putting this into a new thread to highlight it.

Here's the test that fails. I've put an asterisk beside the first
failing assertion, but any of the four final assertions are also
liable to fail because they are based on behaviour that isn't
guaranteed by SQL Server. I wonder where the other RDBMS's usable with
Rails stand?

1) Failure:
test_named_scopes_honor_current_scopes_from_when_defined
(NamedScopeTest)

def test_named_scopes_honor_current_scopes_from_when_defined
assert !Post.ranked_by_comments.limit(5).empty?
assert !authors(:david).posts.ranked_by_comments.limit(5).empty?
* assert_not_equal Post.ranked_by_comments.limit(5),
authors(:david).posts.ranked_by_comments.limit(5)
assert_not_equal Post.top(5), authors(:david).posts.top(5)
assert_equal authors(:david).posts.ranked_by_comments.limit(5),
authors(:david).posts.top(5)
assert_equal Post.ranked_by_comments.limit(5), Post.top(5)
end

Here's what the Post class looks like:

class Post < ActiveRecord::Base
named_scope :ranked_by_comments, :order => "comments_count DESC"
end

Here's what the test data looks like in SQL Server Manager (I've
removed columns irrelevant to the issue):

SELECT * FROM posts

id author_id comments_count
-- --------- --------------
1 1 2
2 1 1
3 0 0
4 1 0
5 1 0
6 1 0
7 2 0

If I replicate the failing assertion in the Rails console, you can see
it's definitely failing for a reason:

>> a = Author.find_by_name("David")
=> #<Author id: 1, name: "David", author_address_id: 1,
author_address_extra_id: 2>
>> pp a.posts.ranked_by_comments.limit(5).map{|p| {:id => p.id, :author_id => p.author_id, :comments_count => p.comments_count}}
[{:comments_count=>2, :author_id=>1, :id=>1},
{:comments_count=>1, :author_id=>1, :id=>2},
{:comments_count=>0, :author_id=>1, :id=>6},
{:comments_count=>0, :author_id=>1, :id=>5},
{:comments_count=>0, :author_id=>1, :id=>4}]
=> nil

>> pp Post.ranked_by_comments.limit(5).map{|p| {:id => p.id, :author_id => p.author_id, :comments_count => p.comments_count}}
[{:comments_count=>2, :author_id=>1, :id=>1},
{:comments_count=>1, :author_id=>1, :id=>2},
{:comments_count=>0, :author_id=>1, :id=>6},
{:comments_count=>0, :author_id=>1, :id=>5},
{:comments_count=>0, :author_id=>1, :id=>4}]
=> nil

Both queries return the same result set.

The SQL generated is:

SELECT TOP 5 * FROM [posts] WHERE ([posts].author_id = 1) AND
([posts].author_id = 1) ORDER BY comments_count DESC

SELECT TOP 5 * FROM [posts] ORDER BY comments_count DESC

That first SQL looks a bit strange, no? But it gives the desired
effect and it wouldn't be the first time Rails has generated ugly SQL.

Now, if I run that SQL directly in SQL Server Manager:

SELECT TOP 5 * FROM [posts] WHERE ([posts].author_id = 1) AND
([posts].author_id = 1) ORDER BY comments_count DESC

id author_id comments_count
-- ---------- --------------
1 1 2
2 1 1
6 1 0
5 1 0
4 1 0

SELECT TOP 5 * FROM [posts] ORDER BY comments_count DESC

id author_id comments_count
-- --------- --------------
1 1 2
2 1 1
6 1 0
5 1 0
4 1 0

So my assumption, and that failing test's assumption, was that the
rows with a comments_count of 0 would be returned in the same order by
different queries with a an order by clause of 'comments_count DESC'
but SQL Server makes no such guarantee.

Here's a couple of related threads:

http://stackoverflow.com/questions/1424560/sql-server-2008-unexpected-results-using-select-top

http://blogs.msdn.com/conor_cunningham_msft/archive/2008/08/27/no-seatbelt-expecting-order-without-order-by.aspx

--
Nick

[Nick Tidey]

Not sure I stated my assumption correctly. The upshot is (and it seems
obvious to me now): You can't guarantee the order of any particular
column if that column is not explicitly included in the ORDER BY
clause.

The other nugget I gleaned from the MSDN blog entry above is that only
the outermost ORDER BY clause can be guaranteed to sort. If you have a
(contrived) query as such:

SELECT * FROM (
SELECT TOP 100 *
FROM employees
ORDER BY name
)

In theory the result set can be unordered. You need to specify an
outer ORDER BY clause to guarantee the presentational order.

--
Nick

> http://stackoverflow.com/questions/1424560/sql-server-2008-unexpected...
>
> http://blogs.msdn.com/conor_cunningham_msft/archive/2008/08/27/no-sea...
>
> --
> Nick

[Ken Collins]

> Not sure I stated my assumption correctly. The upshot is (and it seems
> obvious to me now): You can't guarantee the order of any particular
> column if that column is not explicitly included in the ORDER BY
> clause.

OK... when you condense it down like that... EXACTLY.

This took me by surprise too a long time ago when I first started
integrating WillPagination into my main app. The lib had tests that
other DB's were just fine with since you could leave off an :order
option and still get a result set. Not in SQL Server, you have to be
specific. Which technically is a good thing for clarity. Perhaps other
DBs just take the first PK found in the schema and automagically
applies it if no order is given.

BTW, that SQL that you made a comment on... SQL Server in my opinion
has had very poor or what I call "around your elbow to get to your
ass" support for paginated results. That SQL is proof, however, there
are modern tricks in 2005/2008 using the ROW_COUNT function and that
is something I want to optimize for in the future.

Lastly, I can not say why that one test failed for you where it does
not for me. Perhaps a collation issue locally, perhaps I'm lucky. I
dunno. But I can say that the adapter test suite has support for
redefining a core test in rails to coerce it into an expectation that
SQL likes. If you look at any of the coerced test cases, you can see
how I did it. If you want, you could submit a patch that coerces that
test.

- Ken

[Nick Tidey]

Yeah, I think, basically, you got lucky. Or I got unlucky. The query
optimiser, in generating the results of "SELECT TOP 5 * FROM posts
ORDER BY comments_count" for me, decided on a different approach to
you: be that not relying on the index of the primary key or splitting
the query across CPUs and joining the result sets, I don't know.

I'll see what I can do about that test case.

Cheers
Nick