False Positives In ActiveRecord Tests

Every now and then when I am working on making the SQL Server adapter pass all the ActiveRecord tests, I notice one that looks like a false positive in the core DBs. Here is one case in relations_test.rb. Can someone look at this and just tell me it is normal for other DBs and just odd for SQL Server? Or maybe it is just my understand of some SQL standard. I'm really not that SQL smart. Honestly.

  def test_count_explicit_columns
    Post.update_all(:comments_count => nil)
    posts = Post.scoped
    ...
    assert_equal 0, posts.where('id is not null').select('comments_count').count
    ...
  end

OK, this passes in standard DBs, postgresql, sqlite3, and mysql. But why? There are 11 rows, all where comments_count is null. Do other DBs at this point make a count some sort of aggregate sum?

- Ken

Every now and then when I am working on making the SQL Server adapter pass all the ActiveRecord tests, I notice one that looks like a false positive in the core DBs. Here is one case in relations_test.rb. Can someone look at this and just tell me it is normal for other DBs and just odd for SQL Server? Or maybe it is just my understand of some SQL standard. I'm really not that SQL smart. Honestly.

def test_count_explicit_columns
Post.update_all(:comments_count => nil)
posts = Post.scoped
...
assert_equal 0, posts.where('id is not null').select('comments_count').count
...
end

OK, this passes in standard DBs, postgresql, sqlite3, and mysql. But why? There are 11 rows, all where comments_count is null. Do other DBs at this point make a count some sort of aggregate sum?

I'm not sure if it's a sql92 thing but count is documented as:

Returns a count of the number of non-NULL values of expr in the rows
retrieved by a SELECT statement. The result is a BIGINT value.

You can reproduce it with mysql easily enough.

create table tmp ( a int );

Query OK, 0 rows affected (0.15 sec)

insert into tmp () values ();

Query OK, 1 row affected (0.03 sec)

I’m not sure if it’s a sql92 thing but count is documented as:
Returns a count of the number of non-NULL values of expr in the rows
retrieved by a SELECT statement. The result is a BIGINT value.

Ah, that’s good info!

SQLServer doesn’t do this?

UPDATE [posts] SET [comments_count] = NULL

SELECT COUNT([comments_count])

FROM [posts]

WHERE [id] IS NOT NULL

That does indeed return 0. But that does not mean I am constructing the correct SQL via the Arel visitor or not :slight_smile:

  • Thanks!

Ken