Rails core - Apr 2011 - 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



-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com.
To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

On Mon, Apr 25, 2011 at 10:05 AM, Ken Collins <ken@metaskills.net>
wrote:
>
> 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.

mysql> create table tmp ( a int );
Query OK, 0 rows affected (0.15 sec)

mysql> insert into tmp () values ();
Query OK, 1 row affected (0.03 sec)

mysql> select * from tmp;
+------+
| a    |
+------+
| NULL |
+------+
1 row in set (0.02 sec)

mysql> select count(*) from tmp;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.02 sec)

mysql> select count(a) from tmp;
+----------+
| count(a) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)


SQLServer doesn''t do this?

>
>
>  - Ken
>
>
>
> --
> You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
> To post to this group, send email to rubyonrails-core@googlegroups.com.
> To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com.
> For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.
>
>


-- 
Cheers

Koz

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com.
To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

> 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 :)


 - Thanks!
    Ken


-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com.
To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.